Bug 1097160

Hi Eldad, which query is using this view? Do you have the query test/stored procedure name?

It's ok - I managed. 10x!

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

Eldad - I need to know the flow. What is running that is causing this query to be executed?

clicking on storage tab.

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

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

this bug reproduced in 3.4.4 AV13
any chance for backport?

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.

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.

Moving back to ASSIGNED based on comment #11

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.

when i comment out the following it runs by 200ms instead of 3 sec.


SELECT * FROM
(
-- View: storage_domains
SELECT storage_domain_static.id,
       storage_domain_static.storage,
       storage_domain_static.storage_name,
       storage_domain_static.storage_description,
       storage_domain_static.storage_comment,
       storage_pool_iso_map.storage_pool_id,
       storage_domain_dynamic.available_disk_size,
       storage_domain_dynamic.used_disk_size,
       --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_iso_map.status,
       storage_pool.name AS storage_pool_name,
       storage_domain_static.storage_type,
       storage_domain_static.storage_domain_type,
       storage_domain_static.storage_domain_format_type,
       storage_domain_static.last_time_used_as_master,
       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,
       storage_domain_static.recoverable
       --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';

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