This morning I checked in on one of the MySQL slaves I administrate, and saw an error!

Error 'The total number of locks exceeds the lock table size' on query.

What does this mean, and how do you fix it?

A Google search led me to a blog post over at Mike R’s blog, where he explained things nicely.

This is an error specific to InnoDB tables.  Certain SQL queries will attempt to set locks on all records scanned, probably to ensure the data returned doesn’t change before you can read it.  These locks are put into an area of memory allocated by the innodb_buffer_pool_size variable.

A quick edit to the /etc/my.cnf on the system should fix the problem.

innodb_buffer_pool_size=500M

Obviously adjust this to be more in line with the amount of memory you have in your system. The MySQL manual says it should be safe to set this value up to 80% of your total system memory.

The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables. The default value is 8MB. 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.