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...
6 Comments
What tool did you use to make the graphs They are quite nice.
Performance with various innodb_thread_concurrency settings depends on the workload quite a bit. You will likely not hit the same concurrency checking bottleneck when running an I/O intensive workload, but you will probably hit other bottlenecks.
--Justin
"Numbers", part of iWork for Mac. Â
Simple joins should perform worse than what you tested, and transactions even worse. By simple join I mean scan one table and join to the primary key of another. This is more likely to make the adapative hash index a hotspot on 8+ core servers. And update/insert should be even worse because there is another hotspot on the transaction log mutex.
Thanks for the numbers.
We're all still waiting for your code Mark....
I just started doing benchmarking for Pervasive and I have clocked some rediculous speedups on their Java library. Check out Datarush, it's a free library that automatically scales java. I was giddy when I got it to light up a 32 core machine. http://www.pervasivedatarush.com/downloads
http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#option_mys...
I'm guessing that the overhead and presumed mutex around the queue is the likely culprit.