There's been a lot of rumors floating around internally at Yahoo that it's best to turn off some of your CPU cores when using Innodb, especially if you have a machine with > 4 cores. At this point there's no question in my mind that Innodb doesn't perform much better when you double your cores from 4 to 8, but I really wanted to know if 8 actually performed worse.
To test, I used a Dell 2950 with 6 drives and a simple mysqlslap test script. There's basically no I/O going on here, just a small table in memory being queried a lot. To be fair, I actually got this test from Venu
. I used maxcpu=4 in my grub.conf to limit the cpus (I also tested with tasksel and it seemed to have the same effect as maxcpu).
Here are the results:
The results are in seconds to complete the test, so lower is better. The lines in the top graph are by # of test clients, while the lines in the bottom graph are by the innodb_thread_concurrency setting.
As you can see, the interesting graph showing a bottleneck is on the top left. With 8 cores, and thread_concurrency at a value > 4 and > 2 test clients, performance plateaus until thread concurrency checking is simply disabled ( innodb_thread_concurrency=0).
With concurrency checking disabled, the best performance goes to the 8 core test (though not by much), see the lower left graph and the lowest line.
My conclusion in this is that there is a bottleneck in the thread concurrency checking itself that is causing part of the problem and that disabling it (=0), at least on boxes with plenty of resources, is probably a best practice.
The deficiencies of this test are that it doesn't reflect how a database that can't fit into memory would perform. I suspect there may be instances with higher I/O requirements where limiting thread concurrency would be good.
I still haven't found a test case where performance is more than negligibly worse with the higher number of cores, but I'm still looking...
Trackback URL for this post: