Bug 1302794

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