Bug 984973 - SearchQuery generates slow query on vds_with_tags and storage_domains
SearchQuery generates slow query on vds_with_tags and storage_domains
Product: Red Hat Enterprise Virtualization Manager
Classification: Red Hat
Component: ovirt-engine (Show other bugs)
Unspecified Unspecified
unspecified Severity unspecified
: ---
: 3.4.0
Assigned To: Liran Zelkha
Eldad Marciano
: 735340 984962 (view as bug list)
Depends On:
Blocks: rhev3.4beta 1142926
  Show dependency treegraph
Reported: 2013-07-16 09:51 EDT by Liran Zelkha
Modified: 2016-02-10 14:29 EST (History)
14 users (show)

See Also:
Fixed In Version: ovirt-3.4.0-beta3
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Last Closed:
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
Verified Versions:
Category: ---
oVirt Team: Infra
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---

Attachments (Terms of Use)

External Trackers
Tracker ID Priority Status Summary Last Updated
oVirt gerrit 20914 None None None Never

  None (edit)
Description Liran Zelkha 2013-07-16 09:51:56 EDT
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:
Comment 3 Liran Zelkha 2013-08-07 05:03:13 EDT
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.
Comment 4 Liran Zelkha 2013-11-05 08:20:44 EST
*** Bug 984962 has been marked as a duplicate of this bug. ***
Comment 5 Liran Zelkha 2014-01-06 04:36:45 EST
*** Bug 735340 has been marked as a duplicate of this bug. ***
Comment 8 Tareq Alayan 2014-02-20 09:18:09 EST
Hi Gil, do you want to do some scale tests to verify this?
Comment 9 Gil Klein 2014-02-20 11:57:00 EST
Eldad, See if you can address this for 3.4
Comment 10 Eldad Marciano 2014-05-12 12:03:18 EDT
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
Comment 11 Eldad Marciano 2014-05-12 12:04:31 EDT
this is not reproduced in small scale setup*
Comment 12 Eldad Marciano 2014-05-12 12:37:44 EDT
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
Comment 13 Liran Zelkha 2014-05-13 02:09:33 EDT
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?
Comment 14 Eldad Marciano 2014-05-13 04:59:48 EDT
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.
Comment 15 Eldad Marciano 2014-05-13 05:27:22 EDT

Bug 1097160 - [Scale] - storage_domains_with_hosts_view generate slow query
Comment 16 Itamar Heim 2014-06-12 10:11:23 EDT
Closing as part of 3.4.0

Note You need to log in before you can comment on or make changes to this bug.