Bug 984973

Summary:	SearchQuery generates slow query on vds_with_tags and storage_domains
Product:	Red Hat Enterprise Virtualization Manager	Reporter:	Liran Zelkha <lzelkha>
Component:	ovirt-engine	Assignee:	Liran Zelkha <lzelkha>
Status:	CLOSED CURRENTRELEASE	QA Contact:	Eldad Marciano <emarcian>
Severity:	unspecified	Docs Contact:
Priority:	unspecified
Version:	unspecified	CC:	acathrow, amureini, bazulay, emarcian, emesika, gklein, iheim, lpeer, lzelkha, pstehlik, Rhev-m-bugs, talayan, yeylon, yzaslavs
Target Milestone:	---
Target Release:	3.4.0
Hardware:	Unspecified
OS:	Unspecified
Whiteboard:	infra
Fixed In Version:	ovirt-3.4.0-beta3	Doc Type:	Bug Fix
Doc Text:		Story Points:	---
Clone Of:		Environment:
Last Closed:		Type:	Bug
Regression:	---	Mount Type:	---
Documentation:	---	CRM:
Verified Versions:		Category:	---
oVirt Team:	Infra	RHEL 7.3 requirements from Atomic Host:
Cloudforms Team:	---	Target Upstream Version:
Embargoed:
Bug Depends On:
Bug Blocks:	1078909, 1142926

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

Done,

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