Bug 1097160 - [SCALE] - storage_domains_with_hosts_view generate slow query
Summary: [SCALE] - storage_domains_with_hosts_view generate slow query
Status: CLOSED DUPLICATE of bug 1141543
Alias: None
Product: Red Hat Enterprise Virtualization Manager
Classification: Red Hat
Component: ovirt-engine
Version: 3.3.0
Hardware: Unspecified
OS: Linux
Target Milestone: ---
: 3.5.1
Assignee: Liran Zelkha
QA Contact: Eldad Marciano
Whiteboard: infra
Depends On:
Blocks: rhev3.5beta3 1186763
TreeView+ depends on / blocked
Reported: 2014-05-13 09:26 UTC by Eldad Marciano
Modified: 2016-02-10 19:13 UTC (History)
14 users (show)

Fixed In Version: org.ovirt.engine-root-3.5.0-13
Doc Type: Bug Fix
Doc Text:
Clone Of:
Last Closed: 2015-02-03 12:47:51 UTC
oVirt Team: Infra
Target Upstream Version:

Attachments (Terms of Use)

System ID Priority Status Summary Last Updated
oVirt gerrit 27634 master MERGED core: storage_domains_with_hosts_view generate slow query Never

Description Eldad Marciano 2014-05-13 09:26:12 UTC
Description of problem:
the 'storage_domains_with_hosts_view' running very slow query in very large scale setup

in that view query scan id from vds_static using left join and selecting subquery using the function 'fn_get_disk_commited_value_by_storage(storage_domain_static.id)'
both of them combined together makes that view running very slow(on scale setup).

this is reproduced for 3.3 and also for 3.4

Version-Release number of selected component (if applicable):

How reproducible:

Steps to Reproduce:
1. build scale setup running 550 hosts, 5700 vms.
2. running 'storage_domains_with_hosts_view query'

Actual results:
storage_domains_with_hosts_view query running around 38 sec

Expected results:
investigate the option to ignore the LEFT JOIN for vds_static 
ignore the 'fn_get_disk_commited_value_by_storage'

Additional info:

postgres connection settings:
host = host01-rack04.scale.openstack.engineering.redhat.com
maintenance db = engine
username = engine
pass = qum5*** (the default one)

Comment 1 Liran Zelkha 2014-05-13 11:45:40 UTC
Hi Eldad, which query is using this view? Do you have the query test/stored procedure name?

Comment 2 Liran Zelkha 2014-05-13 13:12:31 UTC
It's ok - I managed. 10x!

Comment 4 Eldad Marciano 2014-09-01 11:18:04 UTC
this bug hit the performance, priority urgent.
up to 120 sec for get results back, the CPU influenced too around ~30 of CPU.

version 3.4.2-0.1-el6

Comment 5 Liran Zelkha 2014-09-09 06:26:41 UTC
Eldad - I need to know the flow. What is running that is causing this query to be executed?

Comment 6 Eldad Marciano 2014-09-09 13:36:08 UTC
clicking on storage tab.

Comment 7 Liran Zelkha 2014-09-15 08:34:18 UTC
This was fixed in 3.5. The query itself is not important as it doesn't run anymore as part of the dynamic search rewrite

Comment 8 Eyal Edri 2014-09-28 11:29:51 UTC
this bug was moved to MODIFIED before vt4 build date thus moving to ON_QA.
if you belive this bug isn't in vt4, please report to rhev-integ@redhat.com

Comment 9 Eldad Marciano 2014-11-12 15:03:11 UTC
this bug reproduced in 3.4.4 AV13
any chance for backport?

Comment 10 Gil Klein 2014-11-13 06:39:05 UTC
I don't see any customer ticket pending on this issue, and 3.4.4 content is already closed. We might consider for next 3.4.z.

Comment 11 Eldad Marciano 2015-01-26 15:32:21 UTC
reproduced in 3.5 VT13.7

when clicking on storage tab:
SELECT * FROM ((SELECT distinct storage_domains_for_search.* FROM  storage_domains_for_search  )  ORDER BY storage_name ASC ) as T1 OFFSET (1 -1) LIMIT 100

execution time ~5-9 sec.

Comment 12 Gil Klein 2015-01-27 08:47:59 UTC
Moving back to ASSIGNED based on comment #11

Comment 15 Eldad Marciano 2015-01-28 11:42:40 UTC
Liran actually the issue is around storage_domains view.

there is many long sql logged in the pg log:
select * from  getstorage_domains_by_id_and_by_storage_pool_id('2aa91150-274a-4bb5-a264-d50a713fb10d','6b588963-6490-4093-a778-699094c0b07b')

I tried to add some index's around storage_domain_* id but it doesn't helped. 

looks like when ignoring fn_get* (inside the view) it runs much better.

Comment 16 Eldad Marciano 2015-01-28 11:59:51 UTC
when i comment out the following it runs by 200ms instead of 3 sec.

-- View: storage_domains
SELECT storage_domain_static.id,
       --fn_get_disk_commited_value_by_storage(storage_domain_static.id) AS commited_disk_size,
       --fn_get_actual_images_size_by_storage(storage_domain_static.id) AS actual_images_size,
       storage_pool.name AS storage_pool_name,
       fn_get_storage_domain_shared_status_by_domain_id(storage_domain_static.id, storage_pool_iso_map.status, storage_domain_static.storage_domain_type) AS storage_domain_shared_status,
       --unregistered_entities.storage_domain_id IS NOT NULL AS contains_unregistered_entities
FROM storage_domain_static
JOIN storage_domain_dynamic ON storage_domain_static.id = storage_domain_dynamic.id
LEFT JOIN storage_pool_iso_map ON storage_domain_static.id = storage_pool_iso_map.storage_id
LEFT JOIN storage_pool ON storage_pool_iso_map.storage_pool_id = storage_pool.id
--LEFT JOIN (SELECT DISTINCT unregistered_ovf_of_entities.storage_domain_id FROM unregistered_ovf_of_entities) unregistered_entities ON unregistered_entities.storage_domain_id = storage_domain_static.id
) AS cc
WHERE id = '2aa91150-274a-4bb5-a264-d50a713fb10d' and storage_pool_id = '6b588963-6490-4093-a778-699094c0b07b';

Comment 18 Liran Zelkha 2015-02-03 12:47:51 UTC

*** This bug has been marked as a duplicate of bug 1141543 ***

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