Bug 1302794 - [scale] - storage_domains view generate inefficient query
Summary: [scale] - storage_domains view generate inefficient query
Keywords:
Status: CLOSED CURRENTRELEASE
Alias: None
Product: ovirt-engine
Classification: oVirt
Component: Database.Core
Version: 3.6.2
Hardware: x86_64
OS: Linux
unspecified
high
Target Milestone: ovirt-4.0.0-alpha
: 4.0.0
Assignee: Allon Mureinik
QA Contact: eberman
URL:
Whiteboard:
Depends On:
Blocks: 1341661
TreeView+ depends on / blocked
 
Reported: 2016-01-28 16:21 UTC by Eldad Marciano
Modified: 2016-08-17 14:36 UTC (History)
7 users (show)

Fixed In Version: ovirt 4.0.0 alpha1
Doc Type: Bug Fix
Doc Text:
Clone Of:
: 1341661 (view as bug list)
Environment:
Last Closed: 2016-08-17 14:36:09 UTC
oVirt Team: Storage
Embargoed:
tnisan: ovirt-4.0.0?
rule-engine: planning_ack+
amureini: devel_ack+
rule-engine: testing_ack+


Attachments (Terms of Use)


Links
System ID Private Priority Status Summary Last Updated
oVirt gerrit 52912 0 None None None 2016-02-01 00:59:41 UTC

Description Eldad Marciano 2016-01-28 16:21:42 UTC
Description of problem:
storage_domains generate unefficient query caused by 'Hash Left Join  (cost=17.840..30.040 rows=14 width=1886) (actual time=422.963..5452.542 rows=14 loops=1)'


#	exclusive	inclusive	rows x	rows	loops	node
1.	5,452.362	5,452.542	↑ 1.0	14	1	
→
Hash Left Join (cost=17.840..30.040 rows=14 width=1,886) (actual time=422.963..5,452.542 rows=14 loops=1)
Hash Cond: (hotel.quebec = uniform.seven)
2.	0.024	0.133	↑ 1.0	14	1	
→
Hash Left Join (cost=15.370..16.890 rows=14 width=1,768) (actual time=0.052..0.133 rows=14 loops=1)
Hash Cond: (hotel.quebec = sierra.delta)
3.	0.067	0.106	↑ 1.0	14	1	
→
Hash Join (cost=1.320..2.650 rows=14 width=1,752) (actual time=0.043..0.106 rows=14 loops=1)
Hash Cond: (hotel.quebec = whiskey.quebec)
4.	0.017	0.017	↑ 1.0	14	1	
→
Seq Scan on hotel (cost=0.000..1.140 rows=14 width=1,740) (actual time=0.006..0.017 rows=14 loops=1)
5.	0.012	0.022	↑ 1.0	14	1	
→
Hash (cost=1.140..1.140 rows=14 width=28) (actual time=0.022..0.022 rows=14 loops=1)
6.	0.010	0.010	↑ 1.0	14	1	
→
Seq Scan on whiskey (cost=0.000..1.140 rows=14 width=28) (actual time=0.007..0.010 rows=14 loops=1)
7.	0.001	0.003	↓ 0.0	0	1	
→
Hash (cost=12.930..12.930 rows=90 width=16) (actual time=0.003..0.003 rows=0 loops=1)
8.	0.002	0.002	↓ 0.0	0	1	
→
HashAggregate (cost=11.120..12.030 rows=90 width=16) (actual time=0.002..0.002 rows=0 loops=1)
9.	0.000	0.000	↓ 0.0	0	1	
→
Seq Scan on sierra (cost=0.000..10.900 rows=90 width=16) (actual time=0.000..0.000 rows=0 loops=1)
10.	0.006	0.047	↑ 1.0	12	1	
→
Hash (cost=2.330..2.330 rows=12 width=134) (actual time=0.047..0.047 rows=12 loops=1)
11.	0.020	0.041	↑ 1.0	12	1	
→
Hash Left Join (cost=1.040..2.330 rows=12 width=134) (actual time=0.026..0.041 rows=12 loops=1)
Hash Cond: (uniform.golf = romeo.quebec)
12.	0.006	0.006	↑ 1.0	12	1	
→
Seq Scan on uniform (cost=0.000..1.120 rows=12 width=36) (actual time=0.002..0.006 rows=12 loops=1)
13.	0.011	0.015	↑ 1.0	2	1	
→
Hash (cost=1.020..1.020 rows=2 width=114) (actual time=0.015..0.015 rows=2 loops=1)
14.	0.004	0.004	↑ 1.0	2	1	
→
Seq Scan on romeo (cost=0.000..1.020 rows=2 width=114) (actual time=0.002..0.004 rows=2 loops=1)

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

How reproducible:
100 % 

Steps to Reproduce:
1. run this query on large scale env.

Actual results:
"Hash Left Join  (cost=17.84..30.04 rows=14 width=1886) (actual time=417.726..5394.149 rows=14 loops=1)"
"  Hash Cond: (storage_domain_static.id = storage_pool_iso_map.storage_id)"

takes ~5 sec.

Expected results:
fast execution time, may be using sub query or other join implementation

Additional info:

Comment 1 Eldad Marciano 2016-01-31 11:13:32 UTC
the most heavy part generated by storage_domains view

and these lines is the most expensive parts:

select storage_domain_static.id, ( select fn_get_disk_commited_value_by_storage(storage_domain_static.id) AS commited_disk_size ) from storage_domain_static

"Seq Scan on storage_domain_static  (cost=0.00..4.64 rows=14 width=16) (actual time=204.207..2843.699 rows=14 loops=1)"
"  Output: id, fn_get_disk_commited_value_by_storage(id)"
"Total runtime: 2843.724 ms

Comment 2 Allon Mureinik 2016-01-31 19:07:40 UTC
Thanks for the report. A few questions though:

1. How many rows do you have in this view? 
1.a. Preferably, if you could attach a dump of the database, it would be awesome.
2. On what hardware/load was this result observed?
3. The expect result is "fast execution time" - can you assign a numeric value to fast? 1 sec? 1 msec?

Comment 3 Eldad Marciano 2016-02-01 10:28:53 UTC
(In reply to Allon Mureinik from comment #2)
> Thanks for the report. A few questions though:
> 
> 1. How many rows do you have in this view? 
14

> 1.a. Preferably, if you could attach a dump of the database, it would be
> awesome.
I will to arrange that, or may be provide you access.

> 2. On what hardware/load was this result observed?
24 cores 120Gb ram.

> 3. The expect result is "fast execution time" - can you assign a numeric
> value to fast? 1 sec? 1 msec?
we currently monitor SQL duration which longer than 3 sec.
lets try to make it faster as we can.

Comment 4 Eldad Marciano 2016-04-21 10:31:42 UTC
any chance to backport it ?

Comment 6 Allon Mureinik 2016-04-21 12:04:49 UTC
(In reply to Eldad Marciano from comment #4)
> any chance to backport it ?
Given triple acks, I don't think there should be a problem. Eli - keep me honest here - this patch creates a new view, rewrites some existing ones, and removes a stored procedure.
Should there be a problem with backporting that wrt shcema upgrades?

Comment 7 Eli Mesika 2016-05-02 14:11:35 UTC
(In reply to Allon Mureinik from comment #6)
> (In reply to Eldad Marciano from comment #4)
> > any chance to backport it ?
> Given triple acks, I don't think there should be a problem. Eli - keep me
> honest here - this patch creates a new view, rewrites some existing ones,
> and removes a stored procedure.
> Should there be a problem with backporting that wrt shcema upgrades?

As long as the views returns teh same result set , I see no problem in back porting that

Comment 8 Allon Mureinik 2016-06-01 12:50:29 UTC
Cloned BZ to bug 1341661, as we need to verify this fix separately for either version.
Removed z-stream flag from THIS bug.

Comment 9 eberman 2016-08-17 12:07:40 UTC
verified on 500 host and 10k vms (usinf FakeVDSM) setup

this time we got results really fast:

real	0m0.198s
user	0m0.004s
sys	0m0.003s

-closing the bug 


see below for actual run:


engine=> \q
[root@bkr-hv01 ~]# time psql -U engine -c "select * from storage_domains;"
                  id                  |               storage                | storage_name | storage_description | storage_comment |           storage_pool_id    
        | available_disk_size | used_disk_size | commited_disk_size | actual_images_size | status | storage_pool_name | storage_type | storage_domain_type | storag
e_domain_format_type | last_time_used_as_master | wipe_after_delete | storage_domain_shared_status | recoverable | contains_unregistered_entities | warning_low_spa
ce_indicator | critical_space_action_blocker | external_status 
--------------------------------------+--------------------------------------+--------------+---------------------+-----------------+------------------------------
--------+---------------------+----------------+--------------------+--------------------+--------+-------------------+--------------+---------------------+-------
---------------------+--------------------------+-------------------+------------------------------+-------------+--------------------------------+----------------
-------------+-------------------------------+-----------------
 4682a5d0-fba1-495c-8b7e-35992467d4bf | fdf7ff0a-daf5-4366-895b-f7d78022aa3c | stg1         |                     |                 | 6d36b668-6eed-46a2-98b1-06731
ddbf2a2 |                  55 |            200 |                  0 |                  0 |      3 | dc_scale_fake     |            1 |                   1 | 3     
                     |                        0 | f                 |                            1 | t           | f                              |                
          10 |                             5 |               0
 6867de53-24b4-4d59-aa58-bc7b1901eeee | 3dae439d-ea09-47f1-9698-7312c60b397d | storage_fake |                     |                 | 6d36b668-6eed-46a2-98b1-06731
ddbf2a2 |                  55 |            200 |             271524 |                  3 |      3 | dc_scale_fake     |            1 |                   0 | 3     
                     |                        0 | f                 |                            1 | t           | f                              |                
          10 |                             5 |               0
(2 rows)


real	0m0.198s
user	0m0.004s
sys	0m0.003s


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