If you’re running an operation on a large number of rows within a table that uses the InnoDB storage engine, you might see this error: ERROR 1206 (HY000): The total number of locks exceeds the lock table size MySQL is trying to tell you that it doesn’t have enough room to store all of the row locks that it would need to execute your query. The only way to fix it for sure is to adjust innodb_buffer_pool_size and restart MySQL.
This problem has cropped up for me a few times, but I’ve always forgotten to make a post about it. If you’re working with a large InnoDB table and you’re updating, inserting, or deleting a large volume of rows, you may stumble upon this error: InnoDB stores its lock tables in the main buffer pool. This means that the number of locks you can have at the same time is limited by the innodb_buffer_pool_size variable that was set when MySQL was started.
Many applications that are used on a standard server perform quite a few of small writes to the disk (like MySQL or Apache). These writes can pile up and limit the performance of your applications. If you have kernel 2.6.9 or later, you can adjust how these small writes are handled to allow for better performance. There’s two main kernel variables to know: vm.dirty_ratio - The highest % of your memory that can be used to hold dirty data.