MySQL: Lock wait timeout exceeded

Wednesday, December 12, 2007

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”


More DST and Database Systems

Thursday, January 25, 2007

I gathered some notes in an earlier post on how the new Daylight Savings Time change effected my Oracle database systems. Now I had to do the research for MySQL and MS SQL Server.

MySQL
Found this forum thread which references this link. Whether you need to perform any actions is dependent upon whether your OS includes zoneinfo files or not (e.g. Microsoft Windows, HP-UX). My database installations appear to use the system to obtain the time information. I don’t believe there is a need for me to populate the MySQL time zone tables using the mysql_tzinfo_to_sql utility.

mysql> SELECT @@global.time_zone, @@session.time_zone;
+------------------------------------------------------------+---------------------------------------------------------------+
| @@global.time_zone | @@session.time_zone |
+------------------------------------------------------------+---------------------------------------------------------------+
| SYSTEM             | SYSTEM              |
+------------------------------------------------------------+---------------------------------------------------------------+
1 row in set (0.02 sec)

MySQL documentation about timezones can be found here for 4.1 and here for 5.0.

MS SQL Server
Found this link for Microsoft products, but SQL Server does not appear to be listed as an affected product. An MSDN forum posting has a response saying SQL Server is not affected because it uses time information from the OS. This reason is also mentioned on Chad Boyd’s blog post.


MySQL – Restore from Compressed Backup

Monday, January 15, 2007

Copied a compressed database backup from the Production server to the Development server. The database backup file was compressed using bzip2 while piping from mysqldump.

mysqldump -u root -p –databases perfparse | bzip2 -z1 > perfparse.bkp.bz2

bzip2 Options

z = compression
1 = fastest compression (compared to 9 = best compression)

Used bunzip2 to uncompress while piping to mysql due to limited disk space.

bunzip2 < perfparse.bkp.bz2 | mysql -uroot -p

Very basic information, but I do use this blog for referring myself and others back to my notes.

References
Compressing mysqldump output
bzip2 Home


MySQL Falcon Storage Engine

Tuesday, January 9, 2007

Saw Sean Hull’s post regarding the new MySQL storage engine called Falcon. For now it is only available in a “forked release of MySQL 5.1″. Additional documentation can be found in the MySQL 5.1 Reference Manual.

References
Two New MySQL Storage Engines
Understanding the Falcon Transaction Storage Engine
New Falcon White Papers ready!


MySQL – Tuning After Installation

Friday, October 27, 2006

Read this post at the MySQL Performance Blog. I found it informative and will need to take an in-depth look at modifying the MySQL variables discussed.

I will quickly admit I am guilty of using default values for MySQL variables. Luckily, none of our MySQL installations are “mission critical”.

I wrote a recent post about the MySQL Error 1206 I encountered. Could possibly have avoided this problem with the information I found in the MySQL Performance Blog. For example, by increasing the innodb_buffer_pool_size value it not only resolved the 1206 error, it also improved query performance. Chalk it up as another lesson learned.


Follow

Get every new post delivered to your Inbox.