Bug 1073093
| Summary: | Admin -> Metrics/Alert/Drift query does not scale well when alert definition count high | ||
|---|---|---|---|
| Product: | [Other] RHQ Project | Reporter: | Elias Ross <genman> |
| Component: | Core UI | Assignee: | Nobody <nobody> |
| Status: | NEW --- | QA Contact: | |
| Severity: | unspecified | Docs Contact: | |
| Priority: | unspecified | ||
| Version: | 4.9 | CC: | hrupp |
| Target Milestone: | --- | ||
| Target Release: | --- | ||
| Hardware: | Unspecified | ||
| OS: | Unspecified | ||
| Whiteboard: | |||
| Fixed In Version: | Doc Type: | Bug Fix | |
| Doc Text: | Story Points: | --- | |
| Clone Of: | Environment: | ||
| Last Closed: | Type: | Bug | |
| Regression: | --- | Mount Type: | --- |
| Documentation: | --- | CRM: | |
| Verified Versions: | Category: | --- | |
| oVirt Team: | --- | RHEL 7.3 requirements from Atomic Host: | |
| Cloudforms Team: | --- | Target Upstream Version: | |
| Embargoed: | |||
Fix is this commit:
commit 4dd517a72c7ab2f77c450ac42832a4044960567f
Author: Elias Ross <elias_ross>
Date: Fri Jun 13 10:55:04 2014 -0700
BZ1073093 Alert template query does not scale well
With a high number of alert definitions, the count query does not scale well.
Adding index for resource_type_id
modules/core/dbutils/src/main/scripts/dbsetup/alert-schema.xml | 3 +++
modules/core/dbutils/src/main/scripts/dbupgrade/db-upgrade.xml | 8 ++++++++
2 files changed, 11 insertions(+)
|
Description of problem: The following query is run when the UI is rendered for the Template screen: SELECT resourcety0_.ID AS col_0_0_, (SELECT count(measuremen1_.ID) FROM RHQ_MEASUREMENT_DEF measuremen1_, RHQ_RESOURCE_TYPE resourcety2_ WHERE measuremen1_.RESOURCE_TYPE_ID=resourcety2_.ID AND measuremen1_.RESOURCE_TYPE_ID=resourcety0_.ID AND measuremen1_.DEFAULT_ON=1) AS col_1_0_, (SELECT count(measuremen3_.ID) FROM RHQ_MEASUREMENT_DEF measuremen3_, RHQ_RESOURCE_TYPE resourcety4_ WHERE measuremen3_.RESOURCE_TYPE_ID=resourcety4_.ID AND measuremen3_.RESOURCE_TYPE_ID=resourcety0_.ID AND measuremen3_.DEFAULT_ON=0) AS col_2_0_, (SELECT count(alertdefin5_.ID) FROM RHQ_ALERT_DEFINITION alertdefin5_, RHQ_RESOURCE_TYPE resourcety6_ WHERE alertdefin5_.RESOURCE_TYPE_ID=resourcety6_.ID AND alertdefin5_.RESOURCE_TYPE_ID=resourcety0_.ID AND alertdefin5_.DELETED=0 AND alertdefin5_.ENABLED=1) AS col_3_0_, (SELECT count(alertdefin7_.ID) FROM RHQ_ALERT_DEFINITION alertdefin7_, RHQ_RESOURCE_TYPE resourcety8_ WHERE alertdefin7_.RESOURCE_TYPE_ID=resourcety8_.ID AND alertdefin7_.RESOURCE_TYPE_ID=resourcety0_.ID AND alertdefin7_.DELETED=0 AND alertdefin7_.ENABLED=0) AS col_4_0_, (SELECT count(driftdefin9_.ID) FROM RHQ_DRIFT_DEF_TEMPLATE driftdefin9_, RHQ_RESOURCE_TYPE resourcety10_ WHERE driftdefin9_.RESOURCE_TYPE_ID=resourcety10_.ID AND driftdefin9_.RESOURCE_TYPE_ID=resourcety0_.ID AND driftdefin9_.IS_USER_DEFINED=0) AS col_5_0_, (SELECT count(driftdefin11_.ID) FROM RHQ_DRIFT_DEF_TEMPLATE driftdefin11_, RHQ_RESOURCE_TYPE resourcety12_ WHERE driftdefin11_.RESOURCE_TYPE_ID=resourcety12_.ID AND driftdefin11_.RESOURCE_TYPE_ID=resourcety0_.ID AND driftdefin11_.IS_USER_DEFINED=1) AS col_6_0_ FROM RHQ_RESOURCE_TYPE resourcety0_ WHERE resourcety0_.DELETED=0 The query can take about 90 seconds on Oracle, the explain plan is as follows: SELECT STATEMENT 0.0 % 8 546815 8 ALL_ROWS 895 6265 8 SORT (AGGREGATE) 0.0 % 1 8 1 RHQ.RHQ_MEASUREMENT_DEF TABLE ACCESS (BY INDEX ROWID) 25.0 % 4 33279 4 ANALYZED 2 16 1 RHQ.RHQ_METRIC_DEF_KEY_IDX INDEX (RANGE SCAN) 25.0 % 2 15843 2 ANALYZED 8 1 SORT (AGGREGATE) 0.0 % 1 8 2 RHQ.RHQ_MEASUREMENT_DEF TABLE ACCESS (BY INDEX ROWID) 25.0 % 4 33279 4 ANALYZED 6 48 1 RHQ.RHQ_METRIC_DEF_KEY_IDX INDEX (RANGE SCAN) 25.0 % 2 15843 2 ANALYZED 8 1 SORT (AGGREGATE) 0.0 % 1 7 3 **** RHQ.RHQ_ALERT_DEFINITION TABLE ACCESS (FULL) 3900.0 % 312 42738529 310 ANALYZED 1 7 1 SORT (AGGREGATE) 0.0 % 1 7 4 **** RHQ.RHQ_ALERT_DEFINITION TABLE ACCESS (FULL) 3900.0 % 312 42738506 310 ANALYZED 1 7 1 SORT (AGGREGATE) 0.0 % 1 7 5 RHQ.RHQ_DRIFT_DEF_TEMPLATE TABLE ACCESS (BY INDEX ROWID) 12.5 % 2 15196 2 ANALYZED 1 7 1 RHQ.RHQ_DRIFT_DEF_TEMPLATE_UNIQUE INDEX (RANGE SCAN) 12.5 % 1 7521 1 ANALYZED 1 1 SORT (AGGREGATE) 0.0 % 1 7 6 RHQ.RHQ_DRIFT_DEF_TEMPLATE TABLE ACCESS (BY INDEX ROWID) 12.5 % 2 15196 2 ANALYZED 1 7 1 RHQ.RHQ_DRIFT_DEF_TEMPLATE_UNIQUE INDEX (RANGE SCAN) 12.5 % 1 7521 1 ANALYZED 1 1 RHQ.RHQ_RESOURCE_TYPE TABLE ACCESS (FULL) 100.0 % 8 546815 8 ANALYZED 895 6265 7 The size is approximately 75,000 rows in rhq_alert_definition. The fix seems to be adding the following indexes: create index idx_resource_type on rhq.rhq_alert_definition(resource_type_id) create index idx_enabled on rhq.rhq_alert_definition(enabled) create index idx_deleted on rhq.rhq_alert_definition(deleted) Version-Release number of selected component (if applicable): 4.9 How reproducible: Based on size Steps to Reproduce: 1. Create a large number of alert definitions Actual results: UI does not render (> 30 seconds) Expected results: UI renders quickly Additional info: The query should probably be optimized to simply show if there is an alert definition (1/0) rather than the count of the definitions. Or, possibly not show a count at all.