Bug 560803

Summary:	fix baseline queries
Product:	[Other] RHQ Project	Reporter:	John Mazzitelli <mazz>
Component:	Database	Assignee:	Heiko W. Rupp <hrupp>
Status:	CLOSED CURRENTRELEASE	QA Contact:	Heiko W. Rupp <hrupp>
Severity:	medium	Docs Contact:
Priority:	high
Version:	1.4.0.B01	CC:	mwagner
Target Milestone:	---	Keywords:	SubBug
Target Release:	---
Hardware:	All
OS:	All
Whiteboard:
Fixed In Version:	2.4	Doc Type:	Bug Fix
Doc Text:		Story Points:	---
Clone Of:		Environment:
Last Closed:	2010-08-12 16:55:55 UTC	Type:	---
Regression:	---	Mount Type:	---
Documentation:	---	CRM:
Verified Versions:		Category:	---
oVirt Team:	---	RHEL 7.3 requirements from Atomic Host:
Cloudforms Team:	---	Target Upstream Version:
Embargoed:
Bug Depends On:
Bug Blocks:	565628, 577041, 593121

Description John Mazzitelli 2010-02-01 21:36:15 UTC

Mark Wagner has some fixes to the baseline job to make the db query faster.

a) add distinct keyword
b) add a new index on a measurement table

Mark knows the specifics. Perhaps he can check in a patch file.

Comment 1 wes hayutin 2010-02-16 16:55:53 UTC

Temporarily adding the keyword "SubBug" so we can be sure we have accounted for all the bugs.

keyword:
new = Tracking + FutureFeature + SubBug

Comment 2 wes hayutin 2010-02-16 17:01:00 UTC

making sure we're not missing any bugs in rhq_triage

Comment 3 John Mazzitelli 2010-02-18 13:35:15 UTC

Mark - can you attach a patch or describe the changes you determined were needed?

Comment 4 Mark Wagner 2010-02-18 14:00:59 UTC

I modified the posgres query in modules/core/domain/src/main/java/org/rhq/core/domain/measurement/MeasurementBaseline.java
to be  

     static {
        /*
         * we only want to compute baselines for measurements that are DYNAMIC
         */
        NATIVE_QUERY_CALC_FIRST_AUTOBASELINE_POSTGRES = "" //
            + "    INSERT INTO RHQ_MEASUREMENT_BLINE ( id, BL_MIN, BL_MAX, BL_MEAN, BL_COMPUTE_TIME, SCHEDULE_ID ) " //
            + "         SELECT nextval('RHQ_MEASUREMENT_BLINE_ID_SEQ'), " //
            + "                MIN(data1h.minvalue) AS bline_min, " //
            + "                MAX(data1h.maxvalue) AS bline_max, " //
            + "                AVG(data1h.value) AS bline_mean, " //
            + "                ? AS bline_ts, " // ?1=computeTime
            + "                data1h.SCHEDULE_ID AS bline_sched_id " //
            + "           FROM RHQ_MEASUREMENT_DATA_NUM_1H data1h  " // baselines are 1H data statistics
            + "     INNER JOIN RHQ_MEASUREMENT_SCHED sched  " // baselines are aggregates of schedules
            + "             ON data1h.SCHEDULE_ID = sched.id  " //
            + "LEFT OUTER JOIN RHQ_MEASUREMENT_BLINE bline " // we want null entries on purpose
            + "             ON sched.id = bline.SCHEDULE_ID  " //
            + "          WHERE ( bline.id IS NULL ) " // no baseline means it was deleted or never calculated
            + "            AND ( data1h.TIME_STAMP BETWEEN ? AND ? ) " // ?2=startTime, ?3=endTime
            + "       GROUP BY data1h.SCHEDULE_ID " // baselines are aggregates per schedule
            // but only calculate baselines for schedules where we have data that fills (startTime, endTime)
            + "         HAVING data1h.SCHEDULE_ID in ( SELECT distinct (mdata.SCHEDULE_ID) "
            + "                                          FROM RHQ_MEASUREMENT_DATA_NUM_1H mdata  " //
            + "                                         WHERE mdata.TIME_STAMP <= ? ) " // ?4=startTime
            + "         "; // batch at most 100K inserts at a time to shrink the xtn size

Comment 5 John Mazzitelli 2010-02-18 17:56:24 UTC

Here is the index Mark created:

CREATE UNIQUE INDEX rhq_meas_data_1h_sid_idx
  ON rhq_measurement_data_num_1h
  USING btree
  (schedule_id,time_stamp);

Comment 6 Charles Crouch 2010-05-17 22:09:35 UTC

We need to analyse whether equivalent changes should be made on the oracle side too.

Comment 9 Charles Crouch 2010-06-18 17:40:35 UTC

Heiko
Can you create a patch for these changes and send it out for review. I'd like to get these changes in if they are agreed to be beneficial and don't have a destabilizing impact.

Comment 10 Heiko W. Rupp 2010-06-29 18:30:24 UTC

Index has been added in 1d53a6b on release-3.0.0

Comment 11 Charles Crouch 2010-06-30 17:26:51 UTC

Reducing priority to clarify QA testing order

Comment 12 Charles Crouch 2010-07-19 18:26:30 UTC

Heiko, can you confirm that this is working as expected in the perf env with the new index. Thanks

Comment 13 Heiko W. Rupp 2010-07-20 08:38:43 UTC

Charles, yes it is.

Comment 14 Corey Welton 2010-08-12 16:55:55 UTC

Mass-closure of verified bugs against JON.