Innodb Multi-core Performance

Submitted by jay on July 16, 2008 - 9:08am

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:

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.


jay's picture

"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....

hint hint :)

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.

I'm guessing that the overhead and presumed mutex around the queue is the likely culprit.


Comment abuse is not tolerated on this site, besides all the comments are moderated, so don't bother posting comments that are not on topic, only for increasing the SEO of your site, or are outright spam.  If you've got something intelligent to contribute, by all means, post a link to your blog.  

About Me

Jay Janssen
Yahoo!, Inc.
jayj at yahoo dash inc dot com
High Availability
Global Load Balancing
View Jay Janssen on Twitter  View Jay Janssen's LinkedIn profile View Jay Janssen's Facebook profile