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:
http://mysqlguy.net/trackback/21
What tool did you use to make
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
"Numbers", part of iWork for Mac.
Simple joins should perform
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
We're all still waiting for your code Mark....
I just started doing
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/refm
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.