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.
Temporarily adding the keyword "SubBug" so we can be sure we have accounted for all the bugs. keyword: new = Tracking + FutureFeature + SubBug
making sure we're not missing any bugs in rhq_triage
Mark - can you attach a patch or describe the changes you determined were needed?
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
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);
We need to analyse whether equivalent changes should be made on the oracle side too.
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.
Index has been added in 1d53a6b on release-3.0.0
Reducing priority to clarify QA testing order
Heiko, can you confirm that this is working as expected in the perf env with the new index. Thanks
Charles, yes it is.
Mass-closure of verified bugs against JON.