Red Hat Bugzilla – Bug 984973
SearchQuery generates slow query on vds_with_tags and storage_domains
Last modified: 2016-02-10 14:29:43 EST
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): How reproducible: Every time Steps to Reproduce: 1. Log in to Admin Console 2. Click on a storage domain 3. Click on the Hosts tab Actual results: 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 Expected results: 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 Additional info:
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 version 3.4 i have also test it on massive scale setup with 500 hosts and 5700 vms version 3.3 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 SELECT fn_get_disk_commited_value_by_storage(storage_domain_static.id) FROM storage_domain_static 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?
another thing.. the fact that query using join for 'vds_static' making some troubles both of them together making that view running around 38 seconds.
Done, Bug 1097160 - [Scale] - storage_domains_with_hosts_view generate slow query
Closing as part of 3.4.0