Bug 1365443

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)

it runs like x100 faster with the same results.

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.

(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.

Alexander - anything we can do about that one?

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(

Are we going to implement it for 4.1? If not, please defer or close.

(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.