Thursday, April 22, 2010

innodb thread concurrency

there is lot of material on the web about how the right number for innodb thread concurrency is kind of a black magic.

recently we changed most of the tables from myisam to innodb for one of our customers. the innodb thread currency was set to 4.

the customers called complaining of slowness and show processlist displayed lots queries seemingly waiting.

and these queries were pretty much optimized.This variable can be set dynamically.
as it was a 16 core machine, we changed it to 18 and there was a complete change in show processlist behavior.

So in summary, if you have optimized queries and lots of queries are being executed and cpu is not being used very much i.e in our case, only about 25% of the 16 core was being used where as once we increased the thread concurrency number, the cpu jumped to 50 to 60% ply with innodb thread concurrecny setting.