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,

(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.

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.

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.

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.

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.

MySQL Error 1206

Friday, October 20, 2006

“The total number of locks exceeds the lock table size”
I encountered this error when executing the SQL below.

DELETE perfdata_service_raw
FROM perfdata_service_raw, perfdata_host
WHERE perfdata_service_raw.host_name = perfdata_host.host_name AND
perfdata_host.is_deleted = 1

Googled to find a resolution and found this link where the resolution is to increase the innodb_buffer_pool_size variable in /etc/my.cnf. The default value is 8M, so I set it to 256M, restart the mysqld service (service mysqld restart), and the problem is resolved.


The part I want to understand is what this variable has to do with table locks. The description of the innodb_buffer_pool_size variable in the MySQL 4.x Documentation states:

“The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables. The larger you set this value, the less disk I/O is needed to access data in tables. On a dedicated database server, you may set this to up to 80% of the machine physical memory size. However, do not set it too large because competition for physical memory might cause paging in the operating system.”

I continue to search, but not finding anything at the moment that will help me understand how lock table size and innodb_buffer_pool_size are related. The quest for an answer continues …

Update (21-Oct-2006)
Found an explanation here which says:

“The SQL statement sets locks on all the records it scans. If you have a small buffer pool, then the InnoDB lock table may indeed grow so big that it does not fit in the buffer pool. “

I want to understand more about the InnoDb lock table, so a little more research will need to be done.

Update (23-Oct-2006)
Found what I was looking for about the InnoDB lock table and it’s behavior at MySQL Performance Blog. It gives some background on the InnoDB lock table:

“in Innodb row level locks are implemented by having special lock table, located in the buffer pool where small record allocated for each hash and for each row locked on that page bit can be set.”

Karol Zielinski: Just a tech stuff
James Wallace:

Password Topics

Friday, October 13, 2006

I really need to implement a better Oracle account and password process for my development environments. Saw this blog entry by Andy Campbell back in March, but still have not gotten around to putting this simple process in place.

Update (30-Oct-2006)
Additional information about Oracle passwords on Robert Vollman’s blog.

Password Manager
I use KeePass Password Safe for keeping my passwords secure. I’ve been using this tool since January 2006. It serves my needs very well for all the passwords I need to remember for work and home. Jeff Hunter recently described his frustration with passwords here.

Update (16-Oct-2006)
New KeePass version 1.06 available here.

Good information here from Frank Mash about storing passwords in MySQL.