Bug 984973 - SearchQuery generates slow query on vds_with_tags and storage_domains
Summary: SearchQuery generates slow query on vds_with_tags and storage_domains
Alias: None
Product: Red Hat Enterprise Virtualization Manager
Classification: Red Hat
Component: ovirt-engine
Version: unspecified
Hardware: Unspecified
OS: Unspecified
Target Milestone: ---
: 3.4.0
Assignee: Liran Zelkha
QA Contact: Eldad Marciano
Whiteboard: infra
: 735340 984962 (view as bug list)
Depends On:
Blocks: rhev3.4beta 1142926
TreeView+ depends on / blocked
Reported: 2013-07-16 13:51 UTC by Liran Zelkha
Modified: 2016-02-10 19:29 UTC (History)
14 users (show)

Fixed In Version: ovirt-3.4.0-beta3
Doc Type: Bug Fix
Doc Text:
Clone Of:
Last Closed:
oVirt Team: Infra
Target Upstream Version:

Attachments (Terms of Use)

System ID Private Priority Status Summary Last Updated
oVirt gerrit 20914 0 None None None Never

Description Liran Zelkha 2013-07-16 13:51:56 UTC
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 09:03:13 UTC
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 13:20:44 UTC
*** Bug 984962 has been marked as a duplicate of this bug. ***

Comment 5 Liran Zelkha 2014-01-06 09:36:45 UTC
*** Bug 735340 has been marked as a duplicate of this bug. ***

Comment 8 Tareq Alayan 2014-02-20 14:18:09 UTC
Hi Gil, do you want to do some scale tests to verify this?

Comment 9 Gil Klein 2014-02-20 16:57:00 UTC
Eldad, See if you can address this for 3.4

Comment 10 Eldad Marciano 2014-05-12 16:03:18 UTC
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 16:04:31 UTC
this is not reproduced in small scale setup*

Comment 12 Eldad Marciano 2014-05-12 16:37:44 UTC
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 06:09:33 UTC
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 08:59:48 UTC
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 09:27:22 UTC

Bug 1097160 - [Scale] - storage_domains_with_hosts_view generate slow query

Comment 16 Itamar Heim 2014-06-12 14:11:23 UTC
Closing as part of 3.4.0

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