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:
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
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?
(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.
any chance to backport it ?
(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?
(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
Cloned BZ to bug 1341661, as we need to verify this fix separately for either version. Removed z-stream flag from THIS bug.
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