Mysql – Innodb mutex locking ?!? Inserts start stacking after 18-24hours, restart required

Since you are running MySQL 5.5, you may want to consider configuring InnoDB to access multiple cores

Here are the settings you should be using

innodb_thread_concurrency sets the upper bound on number of concurrent threads that InnoDB can hold open. Best round number to set for this is (2 X Number of CPUs) + Number of Disks. UPDATE : As I learned firsthand from the Percona NYC Conference, you should set this to 0 in order to alert InnoDB Storage Engine to find the best number of threads for the environment it is running in.

innodb_concurrency_tickets sets the number of threads that can bypass concurrency checking with impunity. After that limit is reached, thread concurrency checking becomes the norm again.

innodb_commit_concurrency sets the number of concurrent transactions that can be committed. Since the default is 0, not setting this allows any number of transactions to commit simultaneously.

innodb_thread_sleep_delay sets the number of milliseconds an InnoDB thread can be dormant before reentering the InnoDB queue. Default is 10000 (10 sec).

innodb_read_io_threads and innodb_write_io_threads (both since MySQL 5.1.38) allocate the specified number of threads for reads and writes. Default is 4 and maximum is 64.

innodb_replication_delay imposes thread delay on a slave is innodb_thread_concurrency is reached.

Here are my past posts on MySQL 5.5 and activating multiple cores for InnoDB

• Jun 01, 2012 : I've got 16GB of ram, how should I configure MySQL Server?
• May 07, 2012 : MySQL Server Performance
• Apr 26, 2012 : Is the CPU performance relevant for a database server?
• Mar 16, 2012 : Using multiple cores for single MySQL queries on Debian
• Oct 07, 2011 : Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks?
• Sep 20, 2011 : Multi cores and MySQL Performance
• Sep 12, 2011 : Possible to make MySQL use more than one core?

I found the problem, in the my.cnf file I had turned on TABLE_CACHE.
The way I resolved it without having to restart it was by using the command FLUSH TABLES. Shortly after that the inserts/updates/deletes resumed working at normal speeds.