Tuesday, April 21, 2009

MySQL 5.4 Scaling to 16 way x86 and 64-way CMT Servers

The release of the MySQL 5.4 contains patches which
increases the scalability of the MySQL Server. I am planning to blog
about those changes in some detail over the next few days. This blog
will give an introduction and show what the overall results we have
achieved are.

The changes we have done in MySQL 5.4 to improve scalability and
the ability to monitor the MySQL Server are:

1) Google SMP patch
2) Google IO patches
3) Update of many antiquated defaults in the MySQL Server
4) New InnoDB Thread Concurrency algorithm
5) Improved Spinloop in InnoDB mutexes and RW-locks
6) A couple of performance fixes backported from 6.0
7) Operating system specific optimisations
8) Ported the Google SMP patch to Solaris x86 and SPARC and work
underway for Windows and Intel compiler as well
9) Introducing DTrace probes in the MySQL Server
10) A build script to make it easier for community to build an efficient
MySQL Server based on source code

As an example of the improvements made available through this work we
have some benchmarks using sysbench readwrite and readonly.

We have consistently seen improvements in the order of 30-40% of
sysbench top numbers and on large number of threads 5.4.0 drops
much less in performance than 5.1. The new InnoDB Thread Concurrency
patch makes the results on high number of threads even more
impressive where the results have gone up by another 5-15% at the
expense of 1% less on the top results (there are even some DBT2
runs that gave 200% improvement with the new algorithm).

There is also a benchmark on EAStress which shows a 59% increase in
performance from 5.1 to 5.4 using the new 16-way x86 Nehalem servers.


Garen said...

On Windows are you talking about the Intel compiler because of something lacking with GCC (what?)

Mikael Ronstrom said...

On Windows MySQL is compiled normally with Microsoft Visual C++, thus one
needs to use Windows API functions
to get things like atomic variables
on Windows. This is in the works.
Also atomic support through the Intel
compiler is in the works.