MySQL: Lock wait timeout exceeded

Running MySQL version 4.1.20. Encountered this error 1205 when attempting to purge my PerfParse database. Recently modified the data retention from 90 days to 60 days.

One of many errors from the /usr/local/nagios/var/perfparse.log.20071212 log file.

INSERT perfdata_bin_summary_data (metric_id,
frequency, ctime, val_count, sum_val,
sum_square_val, max_val, min_val)
VALUES (21, 1, FROM_UNIXTIME(1197446400),
1, 2.85000000000000e+00,
8.12250000000000e+00,2.85000000000000e+00,
2.85000000000000e+00)

(Lock wait timeout exceeded; try restarting transaction)

The tables in PerfParse use the InnoDB storage engine. I believe the purge is holding a table lock longer due to the amount of data being purged by the “perfparse-db-purge” process and is causing normal PerfParse performance data transactions to fail.

To resolve this, I had to reduce the data retention gradually over time instead of trying to purge 30 days all at once.

References
MySQL DBA - “MySQL: Replication stopped: Lock wait timeout exceeded”