Description of problem: when browsing to landing page it takes very long time to fetch the dashboard the query is: SELECT SUM(COALESCE(history.cpu_per_socket, 0) * COALESCE (history.number_of_sockets, 0)) AS cpu_total_vms, SUM(CASE WHEN samples.vm_status = 1 THEN COALESCE (history.cpu_per_socket, 0) * COALESCE (history.number_of_sockets, 0) ELSE 0 END ) AS cpu_used_vms, CAST(SUM(COALESCE(history.memory_size_mb, 0.00)) AS float) AS mem_total_vms, CAST(SUM(CASE WHEN samples.vm_status = 1 THEN COALESCE(history.memory_size_mb, 0.00) ELSE 0.00 END) AS float) AS mem_used_vms FROM v4_0_statistics_vms_resources_usage_samples samples INNER JOIN v4_0_configuration_history_vms history ON history.history_id = samples.vm_configuration_version INNER JOIN v4_0_latest_configuration_vms a ON a.vm_id = samples.vm_id WHERE history_datetime >= CURRENT_TIMESTAMP - INTERVAL '5 minute' AND history_datetime <= CURRENT_TIMESTAMP AND samples.history_id IN (SELECT MAX(history_id) FROM v4_0_statistics_vms_resources_usage_samples b GROUP BY vm_id ) Version-Release number of selected component (if applicable): 4.0.7 How reproducible: 100% Steps to Reproduce: 1. large scale env (39 hosts, 3K vms) 2. fetch the dashboard. 3. Actual results: very long response time and sometimes the UI get stuck Expected results: fast response time, stable UI Additional info:
I forgot to mention this is the most heavy query and it takes like 132s to resolve it.
Reducing severity - this is taking a lot of time on high scale. I assume it is working in a sane manner on a smaller scale setup.
I am analyzing the queries now, looks like we might need some indexes on some tables that we are using.
Got the query down to about 20 seconds. Not sure what else to do to improve it.
Moving verification to Scale team, as they have the appropriate environment for this.
verified execution time is 2 sec.