Note: This is a beta release of Red Hat Bugzilla 5.0. The data contained within is a snapshot of the live data so any changes you make will not be reflected in the production Bugzilla. Also email is disabled so feel free to test any aspect of the site that you want. File any problems you find or give feedback here.
Bug 1365443 - [scale] - improve performance of v4_0_latest_configuration_vms_disks view
Summary: [scale] - improve performance of v4_0_latest_configuration_vms_disks view
Keywords:
Status: CLOSED DEFERRED
Alias: None
Product: ovirt-engine-dwh
Classification: oVirt
Component: Database
Version: 4.0.0
Hardware: x86_64
OS: Linux
unspecified
high vote
Target Milestone: ---
: ---
Assignee: Shirly Radco
QA Contact: Eldad Marciano
URL:
Whiteboard:
Depends On:
Blocks: 1345944 1345947
TreeView+ depends on / blocked
 
Reported: 2016-08-09 09:38 UTC by Eldad Marciano
Modified: 2017-12-22 07:44 UTC (History)
5 users (show)

Fixed In Version:
Doc Type: If docs needed, set a value
Doc Text:
Clone Of:
Environment:
Last Closed: 2017-06-19 11:46:53 UTC
oVirt Team: Metrics
rule-engine: ovirt-4.2?
rule-engine: planning_ack?
rule-engine: devel_ack?
lsvaty: testing_ack+


Attachments (Terms of Use)

Description Eldad Marciano 2016-08-09 09:38:44 UTC
Description of problem:
inefficient query - execution time is 33.5 sec.

SELECT SUM (vm_disks.vm_disk_size_mb ) / 1024 AS total_vms, SUM (samples.vm_disk_actual_size_mb) / 1024 AS used_vms FROM v4_0_latest_configuration_storage_domains config INNER JOIN v4_0_latest_map_datacenters_storage_domains domains ON domains.storage_domain_id = config.storage_domain_id INNER JOIN v4_0_latest_configuration_vms_disks vm_disks ON domains.storage_domain_id = vm_disks.storage_domain_id LEFT OUTER JOIN v4_0_statistics_vms_disks_resources_usage_samples AS samples ON vm_disks.vm_disk_id = samples.vm_disk_id WHERE storage_domain_type IN (0, 1) AND samples.history_id IN (SELECT MAX(a.history_id) FROM v4_0_statistics_vms_disks_resources_usage_samples AS a WHERE history_datetime >= CURRENT_TIMESTAMP - INTERVAL '5 minute' AND history_datetime <= CURRENT_TIMESTAMP GROUP BY a.vm_disk_id)

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

How reproducible:
100%

Steps to Reproduce:
1. login to engine and wait for the landing page (dashboard).


Actual results:
slow UI loading, over CPU utilization for the SQL.

Expected results:
fast UI loading

Additional info:

Comment 1 Eldad Marciano 2016-08-09 09:47:07 UTC
seems like when using it this way, we use the correct indexes:

(as patch set 6) - https://gerrit.ovirt.org/#/c/59089/6..8/frontend/webadmin/modules/frontend/src/main/resources/org/ovirt/engine/ui/frontend/server/dashboard/dao/VmDwhDAO.properties

SELECT 
            SUM (vm_disks.vm_disk_size_mb ) / 1024 AS total_vms, 
            SUM (samples.vm_disk_actual_size_mb) / 1024 AS used_vms 
      FROM 
            v4_0_latest_configuration_storage_domains config 
      INNER JOIN 
            v4_0_latest_map_datacenters_storage_domains domains 
      ON 
            domains.storage_domain_id = config.storage_domain_id 
      INNER JOIN 
            v4_0_latest_configuration_vms_disks vm_disks 
      ON 
            domains.storage_domain_id = vm_disks.storage_domain_id 
      LEFT OUTER JOIN 
            v4_0_statistics_vms_disks_resources_usage_samples AS samples 
      ON (
            vm_disks.vm_disk_id = samples.vm_disk_id AND 
            samples.history_id IN (
            SELECT 
                MAX(a.history_id) 
            FROM 
                v4_0_statistics_vms_disks_resources_usage_samples AS a 
            WHERE 
                history_datetime >= CURRENT_TIMESTAMP - INTERVAL '5 minute' AND 
                history_datetime <= CURRENT_TIMESTAMP 
            GROUP BY 
                a.vm_disk_id
            )
      ) 
      WHERE 
          storage_domain_type IN (0, 1)

Comment 2 Eldad Marciano 2016-08-09 09:47:42 UTC
it runs like x100 faster with the same results.

Comment 3 Alexander Wels 2016-08-09 15:27:10 UTC
The problem is the v4_0_latest_configuration_vms_disks view. The way it is defined it will cause a table scan of the vm_disk_configuration table due to the max(history_id) lookup. The more disks and disk changes you have the slower this query will be.

Comment 4 Alexander Wels 2016-08-09 17:13:26 UTC
(In reply to Eldad Marciano from comment #2)
> it runs like x100 faster with the same results.

I tried this query on the machine you provided me, it was slightly faster, but not significantly. Its in the 250-300ms range if I replace the v4_0_latest_configuration_vms_disks view with the underlying table. This is however not the correct result as the view looks up the 'latest' history_id for each disk. This is the part that is killing the performance.

Comment 5 Oved Ourfali 2016-08-21 07:39:43 UTC
Alexander - anything we can do about that one?

Comment 6 Alexander Wels 2016-08-22 12:16:07 UTC
Not while I am tied to the view which is doing the inefficient part of the query. I talked to Shirly and she is working on making the view better (I believe putting a flag in the tag to mark which one is the latest(

Comment 7 Yaniv Kaul 2016-12-01 14:26:50 UTC
Are we going to implement it for 4.1? If not, please defer or close.

Comment 8 Yaniv Kaul 2017-06-19 11:46:53 UTC
(In reply to Yaniv Kaul from comment #7)
> Are we going to implement it for 4.1? If not, please defer or close.

Closing for the time being, as dashboard is running its queries in the background and providing cached values to the user.


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