Description of problem:
When user clicks the Hosts tab in the Storage section of the Admin console, an extremely slow query is executed.
Version-Release number of selected component (if applicable):
Steps to Reproduce:
1. Log in to Admin Console
2. Click on a storage domain
3. Click on the Hosts tab
Query generated is:
SELECT * FROM (SELECT * FROM vds WHERE ( vds_id IN (SELECT vds_with_tags.vds_id FROM vds_with_tags LEFT OUTER JOIN storage_domains_with_hosts_view ON vds_with_tags.storage_id=storage_domains_with_hosts_view.id WHERE storage_domains_with_hosts_view.storage_name LIKE 'Test2' )) ORDER BY vds_name ASC ,vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 100
Query should be:
SELECT * FROM vds WHERE ( vds_id IN (select vds_id from storage_domains_with_hosts_view WHERE storage_domains_with_hosts_view.storage_name LIKE 'Test2')) ORDER BY vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 100
I suggest checking the parameters sent, and changing the SearchQuery algorithm to stop using subqueries for fields that exist in the only table sent to it.
*** Bug 984962 has been marked as a duplicate of this bug. ***
*** Bug 735340 has been marked as a duplicate of this bug. ***
Hi Gil, do you want to do some scale tests to verify this?
Eldad, See if you can address this for 3.4
this is not reproduced in large scale setup
i have tried to reproduced that on setup with 51 hosts and both of the queries was running around ~200 ms
i have also test it on massive scale setup
with 500 hosts and 5700 vms
and both of the queries running extremely slow further investigation required
scanning 'storage_domains_with_hosts_view' is the expensive part it takes ~70 sec on scale setup beside the fact the entire query using subqueries.
we need to fix the storage_domains_with_hosts_view
this is not reproduced in small scale setup*
looks like the bug in the 'storage_domains_with_hosts_view' happens while
query subselecting this
JOIN storage_domain_dynamic ON storage_domain_static.id = storage_domain_dynamic.id
JOIN storage_pool_iso_map ON storage_domain_static.id = storage_pool_iso_map.storage_id
JOIN storage_pool ON storage_pool_iso_map.storage_pool_id = storage_pool.id
JOIN vds_groups ON storage_pool_iso_map.storage_pool_id = vds_groups.storage_pool_id
JOIN vds_static ON vds_groups.vds_group_id = vds_static.vds_group_id;
this takes up to 38 sec beacuse of the fn_get_disk_commited_value_by_storage(storage_domain_static.id)
ignoring this selecting extremely reduce the query performance
further investigation required
Hi Eldad - I totally agree with your analysis, but it's not the same bug. Can you open a new bug, and provide a connection details to the scale environment so I can check the database?
the fact that query using join for 'vds_static' making some troubles
both of them together making that view running around 38 seconds.
Bug 1097160 - [Scale] - storage_domains_with_hosts_view generate slow query
Closing as part of 3.4.0