Note: This is a beta release of Red Hat Bugzilla 5.0. The data contained within is a snapshot of the live data so any changes you make will not be reflected in the production Bugzilla. Also email is disabled so feel free to test any aspect of the site that you want. File any problems you find or give feedback here.
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 Priority Status Summary Last Updated
oVirt gerrit 20914 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    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(
   FROM storage_domain_static
   JOIN storage_domain_dynamic ON =
   JOIN storage_pool_iso_map ON = storage_pool_iso_map.storage_id
   JOIN storage_pool ON storage_pool_iso_map.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(

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.