Description of problem: GetStorageConnectionsByStorageTypeAndStatus runs for ~3sec and hit the performance. by the stack trace this SP triggered by the 'vdsupdateruntimeinfo'. Version-Release number of selected component (if applicable): VT13.7 How reproducible: 100% Steps to Reproduce: 1. scale setup (500 hosts, 10K vms). 2. profiling the engine. Actual results: the following stored procedure execution time is slow. Expected results: faster results. Additional info: explain plain: "Unique (cost=59.33..59.40 rows=2 width=2112) (actual time=1624.846..1624.847 rows=1 loops=1)" " Output: public.storage_server_connections.id, public.storage_server_connections.connection, public.storage_server_connections.user_name, public.storage_server_connections.password, public.storage_server_connections.iqn, public.storage_server_connections. (...)" " -> Sort (cost=59.33..59.33 rows=2 width=2112) (actual time=1624.844..1624.844 rows=1 loops=1)" " Output: public.storage_server_connections.id, public.storage_server_connections.connection, public.storage_server_connections.user_name, public.storage_server_connections.password, public.storage_server_connections.iqn, public.storage_server_connec (...)" " Sort Key: public.storage_server_connections.id, public.storage_server_connections.connection, public.storage_server_connections.user_name, public.storage_server_connections.password, public.storage_server_connections.iqn, public.storage_server_conn (...)" " Sort Method: quicksort Memory: 25kB" " -> Append (cost=31.77..59.32 rows=2 width=2112) (actual time=1624.824..1624.826 rows=1 loops=1)" " -> HashAggregate (cost=31.77..31.78 rows=1 width=2112) (actual time=0.004..0.004 rows=0 loops=1)" " Output: public.storage_server_connections.id, public.storage_server_connections.connection, public.storage_server_connections.user_name, public.storage_server_connections.password, public.storage_server_connections.iqn, public.storage_s (...)" " -> Nested Loop (cost=19.20..31.74 rows=1 width=2112) (actual time=0.003..0.003 rows=0 loops=1)" " Output: public.storage_server_connections.id, public.storage_server_connections.connection, public.storage_server_connections.user_name, public.storage_server_connections.password, public.storage_server_connections.iqn, public.sto (...)" " -> Nested Loop (cost=19.20..31.06 rows=1 width=118) (actual time=0.002..0.002 rows=0 loops=1)" " Output: lun_storage_server_connection_map.storage_server_connection" " -> Hash Join (cost=19.20..29.90 rows=1 width=516) (actual time=0.002..0.002 rows=0 loops=1)" " Output: luns.lun_id" " Hash Cond: ((luns.volume_group_id)::text = (storage_domains.storage)::text)" " -> Seq Scan on luns (cost=0.00..10.50 rows=50 width=634) (actual time=0.000..0.000 rows=0 loops=1)" " Output: luns.physical_volume_id, luns.lun_id, luns.volume_group_id, luns.serial, luns.lun_mapping, luns.vendor_id, luns.product_id, luns.device_size" " -> Hash (cost=19.19..19.19 rows=1 width=516) (never executed)" " Output: storage_domains.storage" " -> Subquery Scan storage_domains (cost=11.12..19.19 rows=1 width=516) (never executed)" " Output: storage_domains.storage" " -> Nested Loop (cost=11.12..19.18 rows=1 width=1783) (never executed)" " Output: public.storage_domain_static.id, public.storage_domain_static.storage, public.storage_domain_static.storage_name, public.storage_domain_static.storage_description, public.storage_domain_static (...)" " Join Filter: (public.storage_domain_static.id = public.storage_domain_dynamic.id)" " -> Nested Loop Left Join (cost=11.12..17.30 rows=1 width=1791) (never executed)" " Output: public.storage_domain_static.id, public.storage_domain_static.storage, public.storage_domain_static.storage_name, public.storage_domain_static.storage_description, public.storage_domain_ (...)" " Join Filter: (public.storage_pool_iso_map.storage_pool_id = public.storage_pool.id)" " -> Nested Loop Left Join (cost=11.12..16.23 rows=1 width=1783) (never executed)" " Output: public.storage_domain_static.id, public.storage_domain_static.storage, public.storage_domain_static.storage_name, public.storage_domain_static.storage_description, public.storage_d (...)" " Join Filter: (public.unregistered_ovf_of_entities.storage_domain_id = public.storage_domain_static.id)" " -> Nested Loop (cost=0.00..2.18 rows=1 width=1767) (never executed)" " Output: public.storage_domain_static.id, public.storage_domain_static.storage, public.storage_domain_static.storage_name, public.storage_domain_static.storage_description, public.sto (...)" " Join Filter: (public.storage_domain_static.id = public.storage_pool_iso_map.storage_id)" " -> Seq Scan on storage_pool_iso_map (cost=0.00..1.05 rows=1 width=36) (never executed)" " Output: public.storage_pool_iso_map.storage_pool_id, public.storage_pool_iso_map.status, public.storage_pool_iso_map.storage_id" " Filter: ((storage_pool_id = '6b588963-6490-4093-a778-699094c0b07b'::uuid) AND (status = ANY ('{0,3,4}'::integer[])))" " -> Seq Scan on storage_domain_static (cost=0.00..1.06 rows=6 width=1731) (never executed)" " Output: public.storage_domain_static.id, public.storage_domain_static.storage, public.storage_domain_static.storage_name, public.storage_domain_static.storage_domain_type, publ (...)" " -> HashAggregate (cost=11.12..12.03 rows=90 width=16) (never executed)" " Output: public.unregistered_ovf_of_entities.storage_domain_id" " -> Seq Scan on unregistered_ovf_of_entities (cost=0.00..10.90 rows=90 width=16) (never executed)" " Output: public.unregistered_ovf_of_entities.storage_domain_id" " -> Seq Scan on storage_pool (cost=0.00..1.05 rows=1 width=24) (never executed)" " Output: public.storage_pool.id, public.storage_pool.name, public.storage_pool.description, public.storage_pool.storage_pool_type, public.storage_pool.storage_pool_format_type, public.stora (...)" " Filter: (public.storage_pool.id = '6b588963-6490-4093-a778-699094c0b07b'::uuid)" " -> Seq Scan on storage_domain_dynamic (cost=0.00..1.06 rows=6 width=24) (never executed)" " Output: public.storage_domain_dynamic.id, public.storage_domain_dynamic.available_disk_size, public.storage_domain_dynamic.used_disk_size, public.storage_domain_dynamic._update_date" " -> Index Scan using pk_lun_storage_server_connection_map on lun_storage_server_connection_map (cost=0.00..1.15 rows=1 width=634) (never executed)" " Output: lun_storage_server_connection_map.lun_id, lun_storage_server_connection_map.storage_server_connection" " Index Cond: ((lun_storage_server_connection_map.lun_id)::text = (luns.lun_id)::text)" " -> Index Scan using pk_storage_server on storage_server_connections (cost=0.00..0.67 rows=1 width=2112) (never executed)" " Output: public.storage_server_connections.id, public.storage_server_connections.connection, public.storage_server_connections.user_name, public.storage_server_connections.password, public.storage_server_connections.iqn, publ (...)" " Index Cond: ((public.storage_server_connections.id)::text = (lun_storage_server_connection_map.storage_server_connection)::text)" " -> HashAggregate (cost=27.50..27.51 rows=1 width=2112) (actual time=1624.818..1624.818 rows=1 loops=1)" " Output: public.storage_server_connections.id, public.storage_server_connections.connection, public.storage_server_connections.user_name, public.storage_server_connections.password, public.storage_server_connections.iqn, public.storage_s (...)" " -> Nested Loop (cost=11.12..27.47 rows=1 width=2112) (actual time=1624.776..1624.803 rows=1 loops=1)" " Output: public.storage_server_connections.id, public.storage_server_connections.connection, public.storage_server_connections.user_name, public.storage_server_connections.password, public.storage_server_connections.iqn, public.sto (...)" " -> Nested Loop (cost=11.12..19.18 rows=1 width=1783) (actual time=1624.668..1624.692 rows=1 loops=1)" " Output: public.storage_domain_static.id, public.storage_domain_static.storage, public.storage_domain_static.storage_name, public.storage_domain_static.storage_description, public.storage_domain_static.storage_comment, public (...)" " Join Filter: (public.storage_domain_static.id = public.storage_domain_dynamic.id)" " -> Nested Loop Left Join (cost=11.12..17.30 rows=1 width=1791) (actual time=0.054..0.071 rows=1 loops=1)" " Output: public.storage_domain_static.id, public.storage_domain_static.storage, public.storage_domain_static.storage_name, public.storage_domain_static.storage_description, public.storage_domain_static.storage_comment, (...)" " Join Filter: (public.storage_pool_iso_map.storage_pool_id = public.storage_pool.id)" " -> Nested Loop Left Join (cost=11.12..16.23 rows=1 width=1783) (actual time=0.043..0.055 rows=1 loops=1)" " Output: public.storage_domain_static.id, public.storage_domain_static.storage, public.storage_domain_static.storage_name, public.storage_domain_static.storage_description, public.storage_domain_static.storage_com (...)" " Join Filter: (public.unregistered_ovf_of_entities.storage_domain_id = public.storage_domain_static.id)" " -> Nested Loop (cost=0.00..2.18 rows=1 width=1767) (actual time=0.040..0.050 rows=1 loops=1)" " Output: public.storage_domain_static.id, public.storage_domain_static.storage, public.storage_domain_static.storage_name, public.storage_domain_static.storage_description, public.storage_domain_static.stora (...)" " Join Filter: (public.storage_domain_static.id = public.storage_pool_iso_map.storage_id)" " -> Seq Scan on storage_pool_iso_map (cost=0.00..1.05 rows=1 width=36) (actual time=0.016..0.020 rows=1 loops=1)" " Output: public.storage_pool_iso_map.storage_pool_id, public.storage_pool_iso_map.status, public.storage_pool_iso_map.storage_id" " Filter: ((storage_pool_id = '6b588963-6490-4093-a778-699094c0b07b'::uuid) AND (status = ANY ('{0,3,4}'::integer[])))" " -> Seq Scan on storage_domain_static (cost=0.00..1.06 rows=6 width=1731) (actual time=0.015..0.017 rows=6 loops=1)" " Output: public.storage_domain_static.id, public.storage_domain_static.storage, public.storage_domain_static.storage_name, public.storage_domain_static.storage_domain_type, public.storage_domain_static (...)" " -> HashAggregate (cost=11.12..12.03 rows=90 width=16) (actual time=0.001..0.001 rows=0 loops=1)" " Output: public.unregistered_ovf_of_entities.storage_domain_id" " -> Seq Scan on unregistered_ovf_of_entities (cost=0.00..10.90 rows=90 width=16) (actual time=0.001..0.001 rows=0 loops=1)" " Output: public.unregistered_ovf_of_entities.storage_domain_id" " -> Seq Scan on storage_pool (cost=0.00..1.05 rows=1 width=24) (actual time=0.008..0.013 rows=1 loops=1)" " Output: public.storage_pool.id, public.storage_pool.name, public.storage_pool.description, public.storage_pool.storage_pool_type, public.storage_pool.storage_pool_format_type, public.storage_pool.status, public.s (...)" " Filter: (public.storage_pool.id = '6b588963-6490-4093-a778-699094c0b07b'::uuid)" " -> Seq Scan on storage_domain_dynamic (cost=0.00..1.06 rows=6 width=24) (actual time=0.035..0.039 rows=6 loops=1)" " Output: public.storage_domain_dynamic.id, public.storage_domain_dynamic.available_disk_size, public.storage_domain_dynamic.used_disk_size, public.storage_domain_dynamic._update_date" " -> Index Scan using pk_storage_server on storage_server_connections (cost=0.00..8.27 rows=1 width=2112) (actual time=0.098..0.101 rows=1 loops=1)" " Output: public.storage_server_connections.id, public.storage_server_connections.connection, public.storage_server_connections.user_name, public.storage_server_connections.password, public.storage_server_connections.iqn, publ (...)" " Index Cond: ((public.storage_server_connections.id)::text = (public.storage_domain_static.storage)::text)" "Total runtime: 1625.308 ms"
looks like this part is taken to much time: SELECT storage_server_connections.* FROM storage_server_connections INNER JOIN storage_domains ON storage_server_connections.id = storage_domains.storage WHERE (storage_domains.storage_pool_id = '6b588963-6490-4093-a778-699094c0b07b' AND storage_domains.status = any('{0,3,4}'))
this bug might depends on this BZ https://bugzilla.redhat.com/show_bug.cgi?id=1097160, since it pointing to same view 'storage_domains'
What tab/subtab generates this query?
as I mention above the by profiler results it looks like it triggered by 'vdsupdateruntimeinfo'
(In reply to Eldad Marciano from comment #4) > as I mention above the by profiler results it looks like it triggered by > 'vdsupdateruntimeinfo' I missed that sentence in comment 1. Sorry. Can you share the stacktrace please? This doesn't make sense to.
actually there is a stacktraces from the first time i have faced the bug.
(In reply to Eldad Marciano from comment #6) > actually there is a stacktraces from the first time i have faced the bug. And they are where?
Sorry, Adding profile export.
Created attachment 986654 [details] profile export
tested on VT13.7, query runs x2 faster