Bug 1345944
| Summary: | [scale] - dashboard landing page generate inefficient query - COALESCE(AVG(cpu_per_socket) | ||
|---|---|---|---|
| Product: | [oVirt] ovirt-engine-dashboard | Reporter: | Eldad Marciano <emarcian> |
| Component: | Core | Assignee: | Nobody <nobody> |
| Status: | CLOSED DEFERRED | QA Contact: | Eldad Marciano <emarcian> |
| Severity: | high | Docs Contact: | |
| Priority: | high | ||
| Version: | unspecified | CC: | amureini, bugs, emarcian, gklein, mperina, oourfali, sradco, ylavi |
| Target Milestone: | --- | Flags: | rule-engine:
planning_ack+
rule-engine: devel_ack+ pnovotny: testing_ack+ |
| Target Release: | --- | ||
| Hardware: | x86_64 | ||
| OS: | Linux | ||
| Whiteboard: | |||
| Fixed In Version: | Doc Type: | If docs needed, set a value | |
| Doc Text: | Story Points: | --- | |
| Clone Of: | Environment: | ||
| Last Closed: | 2016-12-20 07:08:37 UTC | Type: | Bug |
| Regression: | --- | Mount Type: | --- |
| Documentation: | --- | CRM: | |
| Verified Versions: | Category: | --- | |
| oVirt Team: | Metrics | RHEL 7.3 requirements from Atomic Host: | |
| Cloudforms Team: | --- | Target Upstream Version: | |
| Embargoed: | |||
| Bug Depends On: | 1365443 | ||
| Bug Blocks: | |||
|
Description
Eldad Marciano
2016-06-13 13:20:45 UTC
Patch is included in ovirt-engine-4.0.1 branch, moving to ON_QA Moving verification to Scale team, as they have the appropriate environment for this. verified failed. this query runs for 7.5 sec. LOG: duration: 7529.091 ms execute <unnamed>: SELECT vm_name AS name, COALESCE(AVG(cpu_per_socket), 0) AS cpu_per_socket, COALESCE(AVG(number_of_sockets), 0) AS number_of_sockets, COALESCE(AVG(cpu_usage_percent), 0) AS cpu_usage_percent, MAX(previous_cpu_percent) AS previous_cpu_percent FROM v4_0_statistics_vms _resources_usage_samples AS samples INNER JOIN v4_0_configuration_history_vms AS vms ON samples.vm_id = vms.vm_id LEFT JOIN (SELECT samples.vm_id, COALESCE(A VG(cpu_usage_percent), 0) AS previous_cpu_percent FROM v4_0_statistics_vms_resources_usage_samples AS samples INNER JOIN v4_0_configuration_history_vms AS vm s ON samples.vm_id = vms.vm_id WHERE samples.vm_status = 1 AND samples.history_id IN (SELECT MAX(history_id) FROM v4_0_statistics_vms_resources_usage_samples AS a WHERE history_datetime >= CURRENT_TIMESTAMP - INTERVAL '10 minute' AND history_datetime <= CURRENT_TIMESTAMP - INTERVAL '5 minute' GROUP BY a.vm_id) GR OUP BY samples.vm_id ) AS previous_trend ON vms.vm_id = previous_trend.vm_id WHERE samples.vm_status = 1 AND samples.history_id IN ( SELECT MAX(history_id) F ROM v4_0_statistics_vms_resources_usage_samples AS a WHERE history_datetime >= CURRENT_TIMESTAMP - INTERVAL '5 minute' AND history_datetime <= CURRENT_TIMEST AMP GROUP BY a.vm_id ) GROUP BY vm_name ORDER BY cpu_usage_percent DESC LIMIT 10 I don't think we can improve this further. Are you running with the original number of objects reported in the bug description? How long does it take the dashboard to load? (In reply to Oved Ourfali from comment #4) > I don't think we can improve this further. > Are you running with the original number of objects reported in the bug > description? > How long does it take the dashboard to load? it takes around 40sec to load. we should re test it with 3.1K vms still reproduced on 4.0.3-1 As I understand it is a lot of DWH efforts, it will be fixed by caching. Moving to future for now. Moving to DWH for their decision. For now we will focus on caching on the UX side. This issue was addressed by caching the queries every 5 minutes. I must admit I think the root cause should be fixed, as this pre-fetching can cause load on the DB if the query is inefficient. |