ERROR: canceling statement due to statement timeout
STATEMENT: 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, $1 AS bline_ts, data1h.SCHEDULE_ID AS bline_sched_id FROM RHQ_MEASUREMENT_DATA_NUM_1H data1h INNER JOIN RHQ_MEASUREMENT_SCHED sched ON data1h.SCHEDULE_ID = sched.id INNER JOIN RHQ_MEASUREMENT_DEF def ON sched.definition = def.id LEFT OUTER JOIN RHQ_MEASUREMENT_BLINE bline ON sched.id = bline.SCHEDULE_ID WHERE ( def.numeric_type = 0 ) AND ( bline.id IS NULL ) AND ( data1h.TIME_STAMP BETWEEN $2 AND $3 ) GROUP BY data1h.SCHEDULE_ID HAVING data1h.SCHEDULE_ID in ( SELECT mdata.SCHEDULE_ID FROM RHQ_MEASUREMENT_DATA_NUM_1H mdata WHERE mdata.TIME_STAMP <= $4 ) LIMIT 100000
I have changed the statement_timeout to 120 seconds to stop this error from occurring. Is there a better way to handle this?
Change the work_mem database parameter from 2MB to 16MB. All of the disk I/O needed for the hash join and group by is moved to memory instead. This is where the bottleneck was coming from. Warning: Make sure you have enough system memory to perform this. Every database connection gets its own memory, and so if you have 32 connections that would consume 512MB instead of 64MB.
this is a great finding (and so obvious after one knows the solution)!
It looks like Postgres is able to have this set per connection, so we could just set it before starting the baseline computation:
postgres=# set work_mem=32768;
postgres=# show work_mem;
I have opened the following Bugzilla to track this: Bug 553561