MySQL 5.5 Defaults may pose Performance Issues

We have seen a few instances where upgrades of MySQL 5.1 to 5.5 have resulted in poor performance when using default values. After investigation it would appear that the main culprit appears to be the innodb_thread_concurrency setting.

In MySQL 5.1 the default was 8, while in MySQL 5.5, the default was changed to 0 (unlimited).

So what does innodb_thread_concurrency do and why would it cause performance issues?

The value given to innodb_thread_concurrency sets the number of simultaneous threads the InnoDB engine will create.  If there are more jobs waiting to be processed than there are available thread slots, these jobs will be made to wait until a thread slot becomes free.  So on the face of it, making this unlimited sounds like a good idea - so you won't have any jobs waiting on thread slots.

In an ideal world, unlimited is good.  In the real world, unlimited doesn't exist.  CPUs only have limited resources to run concurrent threads, and while the number of cores in modern CPUs is ever increasing, it is still finite.  When you start creating more threads than the CPU can run, these threads will be put on hold by the operating system.  Quite often this will be at the point when the system is under such load that very little real work will be happening.

So the question is, do you control threads at the InnoDB layer or let the operating system handle it?  Quite clearly from our observations it is far better to manage thread concurrency at the InnoDB layer.  This will depend on a number of issues, the operating system, the scheduler options, the I/O subsystem, and the number and type of CPUs, as well as the type and number of queries being run.

The only way you can tell for certain on your system is to adjust the value of innodb_thread_concurrency and run typical workloads to benchmark.  A reasonable starting point is 2 times the number of CPU cores available.  You could then increase this until the point at which you start to see the system become CPU bound and throttle it back a bit.  This doesn't take into account the disk activity that your transactions will generate, but is a reasonable starting point.  From there you can then look at disk I/O and make adjustments from there.

While it sounds like a bit of work, it is well worth doing as it can mean the difference between high performance and a very sluggish system.

About the Author

Adam Donnison
Adam Donnison, WebOps Manager, has been with SkySQL Ab since 2011. Adam worked as Web Developer for MySQL from 2005 to 2011 through the acquisition by Sun and later Oracle. Adam has a broad insight of web related technologies.