MySQL Configuration

To avoid the scaling problems of MySQL, we compiled version 5.0.26 with Peter Zaitsev's Mutex patch. This Patch gives much better scaling and performance using up to four cores. Eight cores and more give variable results. All testing was done with InnoDB as our storage engine in MySQL 5.0.26. Here is our MySQL configuration:

MySQL Configuration
default-storage-engine InnoDB
skip-external-locking  
skip-locking  
key_buffer 256M
.
table_cache 64
max_allowed_packet 1M
thread_stack 128K
.
sort_buffer_size 2M
read_buffer_size 2M
innodb_buffer_pool_size 1G
.
thread_concurrency 16
innodb_thread_concurrency 16
innodb_additional_mem_pool_size 8MB
read_rnd_buffer_size 8MB
thread_cache 64
max_heap_table 256MB
tmp_table 128MB
.
innodb_log_file_size 250MB
innodb_table_locks 0
innodb_flush_log_at_trx_commit 0
max_user_connections 2000
max_connections 2000

The "query cache" was off, as we wanted to test worst case performance. Our test database is still the same ~1GB database. The workload consists of more than 90% selects, mostly a "read intensive" workload.

MySQL results

All numbers are expressed in queries per second (Y-axis), and the X-axis shows the number of concurrent accesses.


While the Opteron's performance decreases when we add another 4 cores, a second Xeon E5345 pushes the number of queries/s slightly higher. Clearly, MySQL is not ready for more than four cores right now, and it serves as a great reminder for all those with wild "Tens of cores on one die" dreams: making software scale with massive multi-core systems is and will never be easy. Below you can see the scaling of MySQL running on one Xeon 5160 (one core disabled), two (one CPU) and four (Dual CPU configuration).

MySQL Core Scaling
Concurrency 1 core 2 cores 4 cores
5 735 900 1082
10 826 1082 1267
25 823 1105 1323
50 780 1109 1319
100 689 1075 1196

For those running MySQL, clock speed still rules. One 3GHz Xeon 5160 is already capable of no less than 1000-1100 queries/s. Compare this with the clock speed scaling (1 core):

MySQL Clock Scaling
Concurrency 2.33 GHz 3 GHz
5 568 735
10 647 826
25 619 823
50 579 780
100 531 689

You can see that a 28% higher clock speed results in 28% higher performance. We can conclude that clock speed still matters, and that it is often much harder to get more performance out of multiple cores, even in applications that are relatively easy split up into threads.

Although our current DB2 results are "beta" and not ready for publication, we can already say now that DB2 is slower than MySQL but scales much better. We get an 80% increase from 2 to 4 cores.

Secure Socket Layers RSA Performance ERP: SAP Sales & Distribution
POST A COMMENT

15 Comments

View All Comments

  • Antinomy - Wednesday, March 07, 2007 - link

    A great review, very interesting.
    But there are a few things to mention. A mistake in results of Cinebench test. In the overall table the uni Clovertown system got 1272 points, but in the next (per core performance) - 1169. The result was swapped with the one of Xeon 7130. And a comment about the scalability extrapolation. The result of scalability 2.33 Clover vs 3.0 Dual Woodcrest can be hardly compared due to different organization of the systems. These MoBo have two independent FSB so this means, that the two Woodcrests will be provided with twice more peak memory bandwith. This can't make no influence on the result. Also the 4 channel memory mode provides a 5% increase versus 2 channel in real bandwith, so we can't say that theese applications do not suffer from lack of memory bandwith.
    It would be interesting to provide a test of uni Woodcrest system and a test of system based on Woodcrest (both uni and dual) at the same frequency as Clovertown has. And a Kentsfield\Conroe systems (despite they aren't server ones) would be nice to look at because of their more efficient usage of memory bandwith and FSB.
    Reply
  • afuruhed - Thursday, December 28, 2006 - link

    We are getting more Clovertowns. There is a chart at http://www.pantor.com/software.html">pantor.com that indicates that some applications benefit a lot. http://en.wikipedia.org/wiki/FIX_protocol">The FIX protocol is a technical specification for electronic communication of trade-related messages (financial markets). Reply
  • henriks - Thursday, December 28, 2006 - link

    Agree with other responses - good article!

    Some comments on the jbb results page:

    You state that JRockit is (only) available for x86-64 and Itanium. x86 and Sparc should be added to this list.

    The JRockit configuration you're using enables a single-spaced GC. In that configuration, performance is tied to heap size (larger heap means fewer GC events). Increasing the heap size to 3 GB - as for the Sun benchmark results - would increase performance slightly but in particular give much better scalability when you increase the number of warehouses to large numbers.

    It looks like you have not enabled large pages in the OS. Doing this would give a large performance boost and help scalability regardless of chip or JVM vendor.

    Astute readers may note that your results are lower than the published results on www.spec.org. Apart from OS and possibly BIOS tuning, the reason is that the most recent results are using a newer JRockit version (not yet available for public download). This new version improves performance on this benchmark by 20-30% on x86 chips - Intel *and* AMD - with the largest positive effect on high-bin chips from the respective vendors. The effect on other Java applications vary from zero to a lot.

    Cheers!

    Henrik, JRockit team
    Reply
  • dropadrop - Wednesday, December 27, 2006 - link

    Considering how much we just payed for some DL585's compared to DL380's I think the performance is pretty impressive. There is still something the DL380's (and most other two socket servers) can't do, and that is hosting 64GB or more ram.

    I mainly take care of vmware servers, and there the amount of memory becomes a bottleneck long before the processors, atleast in most setups. I don't think I'd have alot of use for octal processors unless I got a minimum of 32GB of ram, probably 64.
    Reply
  • rowcroft - Thursday, December 28, 2006 - link

    I've run into the same challenge when planning for the quads. My take is that I'm getting dual quads for half the price of quad dual cores. With ESX 3's HA functionality I can group the host servers and get the 32GB of ram with double the cores and have host based redundancy for critical vm's.
    Reply
  • mino - Thursday, December 28, 2006 - link

    there is another thing DL380 lacks: no drop-in analog to Barcelona on the horizon... Reply
  • Justin Case - Wednesday, December 27, 2006 - link

    Finally a good article at AT, written by someone who knows what he's talking about. Meaningful benchmarks, meaningful comments, and conclusions that make sense. If only some Johanness could rub off on other AT writers... Reply
  • hans007 - Wednesday, December 27, 2006 - link

    i think an alternative to say a dual dual core AMD though even as a server or workstation is say a quad core socket 775 cpu. I know the lower 3xxx series xeons are made for this (and are exactly the same as core 2 duo) so

    you could do a comparison of 2 amd dual cores vs a single 775 quad with ECC ddr2 etc.
    Reply
  • mino - Thursday, December 28, 2006 - link

    Check QuadFX vs. Kentsfield reviews.

    With ECC both results will be a bit lower but the conparison remains.

    A small hint: NO ONE tested QuadFX as DB server against Kenstfield....

    Gues what: Quad FX is cheaper and would rules the roost on server-like tasks.
    Reply
  • ltcommanderdata - Wednesday, December 27, 2006 - link

    Well it's nice to finally see a review of the 5145, although I was hoping for more detailed power consumption numbers. The performance benchmarks were very detailed though which was great.

    Thought I would point out a few errors I noticed as I was flipping through. First on page 2, in the Cache2Cache Latency chart the 201 for the Xeon DP 5060 that is placed in the "Same die, same package" row should be in the "Different die, same package" row. Dempsey uses a dual die approach like Presler and Cloverton as opposed to a single die approach like Smithfield and Paxville DP. And in the last page in the conclusion, you mentioned Clarksboro having "four DIBs", which implies 8 FSBs. I believe that should read two DIBs or really a Quad Independent Bus (QIB) since I'm pretty sure it only has 4 FSBs. (On a side note, Intel slides showed those 4 FSBs clocked at 1066MHz which is really disappointing. Hopefully, now that Cloverton turns out to come in 1333MHz versions instead of only 1066MHz versions that was first announced, Tigerton (and therefore Clarksboro) which is based on Cloverton will also have 1333MHz versions.)
    Reply

Log in

Don't have an account? Sign up now