Two days ago I checked logs of my MySQL server and saw an error: “The total number of locks exceeds the lock table size”. What’s that and how to solve the problem?

At first… some official informations:

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.

So…

1. This is an error specific to InnoDB tables.
2. We need to change “innodb_buffer_pool_size” variable in our /etc/mysql/my.cnf.
3. 80% is for dedicated database servers, so I will change it to 256M.

So… let fix it:

vim /etc/mysql/my.cnf

and put

innodb_buffer_pool_size=256M

under the line with [mysqld].

Now… restart MySQL and everything should be fine.

More informations:

• blog.ffff.ca
• mrothouse.wordpress.com

Write a comment

Name (required)

Mail (will not be published) (required)

Website

Hello, I'm Karol Zielinski, internet evangelist, an entrepreneur, project manager and a web developer from Gdynia, Poland. I like creative design, good advertisement, social media and all kind of stuff around the web.

Most popular posts

• m2eclipse: Eclipse is running in a JRE, but a JDK is required
• Publish post on Facebook Page wall as a Page (not a user) (python + Facebook REST API)
• Sub domains in Google Analytics
• "decoder jpeg not available" on Mac OS X
• Ruby on Rails: 'require_frameworks': no such file to load -- net/https (RuntimeError)

Much more links

• about me
• all tags
• blog's archive
• contact me
• inspiring sites
• my daily lifestream