Bug 1073093 - Admin -> Metrics/Alert/Drift query does not scale well when alert definition count high
Summary: Admin -> Metrics/Alert/Drift query does not scale well when alert definition ...
Status: NEW
Alias: None
Product: RHQ Project
Classification: Other
Component: Core UI
Version: 4.9
Hardware: Unspecified
OS: Unspecified
unspecified
unspecified vote
Target Milestone: ---
: ---
Assignee: RHQ Project Maintainer
QA Contact: Mike Foley
URL:
Whiteboard:
Keywords:
Depends On:
Blocks:
TreeView+ depends on / blocked
 
Reported: 2014-03-05 18:23 UTC by Elias Ross
Modified: 2014-09-17 00:02 UTC (History)
1 user (show)

(edit)
Clone Of:
(edit)
Last Closed:


Attachments (Terms of Use)

Description Elias Ross 2014-03-05 18:23:09 UTC
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.

Comment 1 Elias Ross 2014-09-17 00:02:24 UTC
Fix is this commit:

commit 4dd517a72c7ab2f77c450ac42832a4044960567f
Author: Elias Ross <elias_ross@apple.com>
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(+)


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