Bug 1057561

Summary: [SCALE] Templates are being pulled from template view in a very inefficient way
Product: Red Hat Enterprise Virtualization Manager Reporter: Tomas Dosek <tdosek>
Component: ovirt-engineAssignee: Liran Zelkha <lzelkha>
Status: CLOSED CURRENTRELEASE QA Contact: Eldad Marciano <emarcian>
Severity: high Docs Contact:
Priority: urgent    
Version: 3.3.0CC: aberezin, acathrow, bazulay, emarcian, emesika, iheim, lpeer, lzelkha, pstehlik, Rhev-m-bugs, talayan, tdosek, tpoitras, yeylon
Target Milestone: ---Keywords: ZStream
Target Release: 3.4.0   
Hardware: Unspecified   
OS: Unspecified   
Whiteboard: infra
Fixed In Version: ovirt-3.4.0-beta2 Doc Type: Bug Fix
Doc Text:
Previously, an inefficient query caused templates pulled from template view to take a long time. This resulted in the query taking longer than expected to display results. Now, an updated query has fixed the problem. As a result, templates are displayed more quickly.
 Story Points: ---
Clone Of: 1056064
: 1061185 (view as bug list) 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: 1056064    
Bug Blocks: 1020228, 1061185, 1078909, 1142926    

Comment 1 Tomas Dosek 2014-01-24 11:17:31 UTC 
Same as the above from audit log was observed with templates:

[pid 3767 127.0.0.1(39427)]DEBUG:  00000: parse <unnamed>: SELECT * FROM (SELECT * FROM vm_templates_view WHERE ( vmt_guid IN (SELECT vm_templates_storage_domain.vmt_guid FROM  vm_templates_storage_domain  ))  ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 100
[pid 3766 127.0.0.1(39426)]DEBUG:  00000: parse <unnamed>: SELECT * FROM (SELECT * FROM vm_templates_view WHERE ( vmt_guid IN (SELECT vm_templates_storage_domain.vmt_guid FROM  vm_templates_storage_domain  ))  ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 100
[pid 3722 127.0.0.1(39425)]DEBUG:  00000: parse <unnamed>: SELECT * FROM (SELECT * FROM vm_templates_view WHERE ( vmt_guid IN (SELECT vm_templates_storage_domain.vmt_guid FROM  vm_templates_storage_domain  ))  ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 100
[pid 3500 127.0.0.1(39413)]DEBUG:  00000: parse <unnamed>: SELECT * FROM (SELECT * FROM vm_templates_view WHERE ( vmt_guid IN (SELECT vm_templates_storage_domain.vmt_guid FROM  vm_templates_storage_domain  ))  ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 100
[pid 3500 127.0.0.1(39413)]DEBUG:  00000: parse <unnamed>: SELECT * FROM (SELECT * FROM vm_templates_view WHERE ( vmt_guid IN (SELECT vm_templates_storage_domain.vmt_guid FROM  vm_templates_storage_domain  ))  ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 100
[pid 3618 127.0.0.1(39415)]DEBUG:  00000: parse <unnamed>: SELECT * FROM (SELECT * FROM vm_templates_view WHERE ( vmt_guid IN (SELECT vm_templates_storage_domain.vmt_guid FROM  vm_templates_storage_domain  ))  ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 100
[pid 3766 127.0.0.1(39426)]DEBUG:  00000: parse <unnamed>: SELECT * FROM (SELECT * FROM vm_templates_view WHERE ( vmt_guid IN (SELECT vm_templates_storage_domain.vmt_guid FROM  vm_templates_storage_domain  ))  ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 100
[pid 3767 127.0.0.1(39427)]DEBUG:  00000: parse <unnamed>: SELECT * FROM (SELECT * FROM vm_templates_view WHERE ( vmt_guid IN (SELECT vm_templates_storage_domain.vmt_guid FROM  vm_templates_storage_domain  ))  ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 100
[pid 3767 127.0.0.1(39427)]DEBUG:  00000: parse <unnamed>: SELECT * FROM (SELECT * FROM vm_templates_view WHERE ( vmt_guid IN (SELECT vm_templates_storage_domain.vmt_guid FROM  vm_templates_storage_domain  ))  ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 100
[pid 3767 127.0.0.1(39427)]DEBUG:  00000: parse <unnamed>: SELECT * FROM (SELECT * FROM vm_templates_view WHERE ( vmt_guid IN (SELECT vm_templates_storage_domain.vmt_guid FROM  vm_templates_storage_domain  ))  ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 100
[pid 3500 127.0.0.1(39413)]DEBUG:  00000: parse <unnamed>: SELECT * FROM (SELECT * FROM vm_templates_view WHERE ( vmt_guid IN (SELECT vm_templates_storage_domain.vmt_guid FROM  vm_templates_storage_domain  ))  ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 100
[pid 3722 127.0.0.1(39425)]DEBUG:  00000: parse <unnamed>: SELECT * FROM (SELECT * FROM vm_templates_view WHERE ( vmt_guid IN (SELECT vm_templates_storage_domain.vmt_guid FROM  vm_templates_storage_domain  ))  ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 100
[pid 3767 127.0.0.1(39427)]DEBUG:  00000: parse <unnamed>: SELECT * FROM (SELECT * FROM vm_templates_view WHERE ( vmt_guid IN (SELECT vm_templates_storage_domain.vmt_guid FROM  vm_templates_storage_domain  ))  ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 100
[pid 3618 127.0.0.1(39415)]DEBUG:  00000: parse <unnamed>: SELECT * FROM (SELECT * FROM vm_templates_view WHERE ( vmt_guid IN (SELECT vm_templates_storage_domain.vmt_guid FROM  vm_templates_storage_domain  ))  ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 100
[pid 3722 127.0.0.1(39425)]DEBUG:  00000: parse <unnamed>: SELECT * FROM (SELECT * FROM vm_templates_view WHERE ( vmt_guid IN (SELECT vm_templates_storage_domain.vmt_guid FROM  vm_templates_storage_domain  ))  ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 100
[pid 3618 127.0.0.1(39415)]DEBUG:  00000: parse <unnamed>: SELECT * FROM (SELECT * FROM vm_templates_view WHERE ( vmt_guid IN (SELECT vm_templates_storage_domain.vmt_guid FROM  vm_templates_storage_domain  ))  ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 100
[pid 3766 127.0.0.1(39426)]DEBUG:  00000: parse <unnamed>: SELECT * FROM (SELECT * FROM vm_templates_view WHERE ( vmt_guid IN (SELECT vm_templates_storage_domain.vmt_guid FROM  vm_templates_storage_domain  ))  ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 100
[pid 3722 127.0.0.1(39425)]DEBUG:  00000: parse <unnamed>: SELECT * FROM (SELECT * FROM vm_templates_view WHERE ( vmt_guid IN (SELECT vm_templates_storage_domain.vmt_guid FROM  vm_templates_storage_domain  ))  ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 100
[pid 3722 127.0.0.1(39425)]DEBUG:  00000: parse <unnamed>: SELECT * FROM (SELECT * FROM vm_templates_view WHERE ( vmt_guid IN (SELECT vm_templates_storage_domain.vmt_guid FROM  vm_templates_storage_domain  ))  ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 100
Comment 2 Liran Zelkha 2014-01-25 18:09:47 UTC 
Hi Tomas,

This should be exactly the same as - https://bugzilla.redhat.com/show_bug.cgi?id=1029106. Can you confirm this is the same, and that the solution works?
Comment 3 Liran Zelkha 2014-01-25 18:10:05 UTC 
Hi Tomas,

This should be exactly the same as - https://bugzilla.redhat.com/show_bug.cgi?id=1029106. Can you confirm this is the same, and that the solution works?
Comment 4 Tomas Dosek 2014-01-25 19:41:17 UTC 
Hi Liran,

Unfortunately not. This has been found on environment that was upgraded to 3.3 GA (which afaik included the patch already).
Comment 5 Liran Zelkha 2014-01-26 08:19:41 UTC 
Hi Tomas,

Just to make sure - the fix was entered in 3.3.2, and was merged on December 10th. Are you sure your version is newer?
Comment 10 Tareq Alayan 2014-02-17 10:14:45 UTC 
what is considered to be OK time  ?
now it takes ~2seconds is it ok?

tested on ovirt-engine-3.4.0-0.7.beta2.el6.noarch
Comment 11 Tareq Alayan 2014-02-17 11:44:09 UTC 
functional testing point of view: i see that the audit_log and event_log are viewed ok. 

Gil, do u see apropriate to do scale testing here by Yuri's team? 
Or shall i move it to verify?
Comment 12 Gil Klein 2014-02-17 16:18:44 UTC 
Eldad, would you be able to verify this on the scale lab?
Comment 14 Eldad Marciano 2014-05-13 11:26:20 UTC 
Not reproduced
Comment 15 Itamar Heim 2014-06-12 14:10:29 UTC 
Closing as part of 3.4.0