Bug 560803 - fix baseline queries
fix baseline queries
Status: CLOSED CURRENTRELEASE
Product: RHQ Project
Classification: Other
Component: Database (Show other bugs)
1.4.0.B01
All All
high Severity medium (vote)
: ---
: ---
Assigned To: Heiko W. Rupp
Heiko W. Rupp
: SubBug
Depends On:
Blocks: rhq_triage jon24-perf jon-sprint11-bugs
  Show dependency treegraph
 
Reported: 2010-02-01 16:36 EST by John Mazzitelli
Modified: 2010-08-12 12:55 EDT (History)
1 user (show)

See Also:
Fixed In Version: 2.4
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2010-08-12 12:55:55 EDT
Type: ---
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:


Attachments (Terms of Use)

  None (edit)
Description John Mazzitelli 2010-02-01 16:36:15 EST
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 11:55:53 EST
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 12:01:00 EST
making sure we're not missing any bugs in rhq_triage
Comment 3 John Mazzitelli 2010-02-18 08:35:15 EST
Mark - can you attach a patch or describe the changes you determined were needed?
Comment 4 Mark Wagner 2010-02-18 09:00:59 EST
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 12:56:24 EST
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 18:09:35 EDT
We need to analyse whether equivalent changes should be made on the oracle side too.
Comment 9 Charles Crouch 2010-06-18 13:40:35 EDT
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 14:30:24 EDT
Index has been added in 1d53a6b on release-3.0.0
Comment 11 Charles Crouch 2010-06-30 13:26:51 EDT
Reducing priority to clarify QA testing order
Comment 12 Charles Crouch 2010-07-19 14:26:30 EDT
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 04:38:43 EDT
Charles, yes it is.
Comment 14 Corey Welton 2010-08-12 12:55:55 EDT
Mass-closure of verified bugs against JON.

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