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”

Advertisements

3 Responses to MySQL: Lock wait timeout exceeded

  1. Frank says:

    It appears you’re reference link is broken…

  2. mrothouse says:

    Thank you for bringing that to my attention Frank. I had a typo in the URL link. It has been corrected. Thanks again.

  3. rupees says:

    Does you site have a contgact page? I’m having troubble locating it but, I’d like to shoot you an e-mail.

    I’ve got some recommendations for your blog you might
    be interested in hearing. Either way, geat site and I look forward to seeing it expand over time.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: