Description of problem:
the 'storage_domains_with_hosts_view' running very slow query in very large scale setup
in that view query scan id from vds_static using left join and selecting subquery using the function 'fn_get_disk_commited_value_by_storage(storage_domain_static.id)'
both of them combined together makes that view running very slow(on scale setup).
this is reproduced for 3.3 and also for 3.4
Version-Release number of selected component (if applicable):
Steps to Reproduce:
1. build scale setup running 550 hosts, 5700 vms.
2. running 'storage_domains_with_hosts_view query'
storage_domains_with_hosts_view query running around 38 sec
investigate the option to ignore the LEFT JOIN for vds_static
ignore the 'fn_get_disk_commited_value_by_storage'
postgres connection settings:
host = host01-rack04.scale.openstack.engineering.redhat.com
maintenance db = engine
username = engine
pass = qum5*** (the default one)
Hi Eldad, which query is using this view? Do you have the query test/stored procedure name?
It's ok - I managed. 10x!
this bug hit the performance, priority urgent.
up to 120 sec for get results back, the CPU influenced too around ~30 of CPU.
Eldad - I need to know the flow. What is running that is causing this query to be executed?
clicking on storage tab.
This was fixed in 3.5. The query itself is not important as it doesn't run anymore as part of the dynamic search rewrite
this bug was moved to MODIFIED before vt4 build date thus moving to ON_QA.
if you belive this bug isn't in vt4, please report to email@example.com
this bug reproduced in 3.4.4 AV13
any chance for backport?
I don't see any customer ticket pending on this issue, and 3.4.4 content is already closed. We might consider for next 3.4.z.
reproduced in 3.5 VT13.7
when clicking on storage tab:
SELECT * FROM ((SELECT distinct storage_domains_for_search.* FROM storage_domains_for_search ) ORDER BY storage_name ASC ) as T1 OFFSET (1 -1) LIMIT 100
execution time ~5-9 sec.
Moving back to ASSIGNED based on comment #11
Liran actually the issue is around storage_domains view.
there is many long sql logged in the pg log:
select * from getstorage_domains_by_id_and_by_storage_pool_id('2aa91150-274a-4bb5-a264-d50a713fb10d','6b588963-6490-4093-a778-699094c0b07b')
I tried to add some index's around storage_domain_* id but it doesn't helped.
looks like when ignoring fn_get* (inside the view) it runs much better.
when i comment out the following it runs by 200ms instead of 3 sec.
SELECT * FROM
-- View: storage_domains
--fn_get_disk_commited_value_by_storage(storage_domain_static.id) AS commited_disk_size,
--fn_get_actual_images_size_by_storage(storage_domain_static.id) AS actual_images_size,
storage_pool.name AS storage_pool_name,
fn_get_storage_domain_shared_status_by_domain_id(storage_domain_static.id, storage_pool_iso_map.status, storage_domain_static.storage_domain_type) AS storage_domain_shared_status,
--unregistered_entities.storage_domain_id IS NOT NULL AS contains_unregistered_entities
JOIN storage_domain_dynamic ON storage_domain_static.id = storage_domain_dynamic.id
LEFT JOIN storage_pool_iso_map ON storage_domain_static.id = storage_pool_iso_map.storage_id
LEFT JOIN storage_pool ON storage_pool_iso_map.storage_pool_id = storage_pool.id
--LEFT JOIN (SELECT DISTINCT unregistered_ovf_of_entities.storage_domain_id FROM unregistered_ovf_of_entities) unregistered_entities ON unregistered_entities.storage_domain_id = storage_domain_static.id
) AS cc
WHERE id = '2aa91150-274a-4bb5-a264-d50a713fb10d' and storage_pool_id = '6b588963-6490-4093-a778-699094c0b07b';
*** This bug has been marked as a duplicate of bug 1141543 ***