Tuesday, April 13, 2010

Pointers to my presentations of MySQL 5.5 Scalability enhancements

Here is some pointers to my MySQL conference slides.
Here is the presentation on MySQL 5.5 Performance and
Scalability improvements
here

Here is the presentation on the MySQL 5.5 Performance
and Scalability benchmarks
here

Scalability enhancements of MySQL 5.5.4-m3

The MySQL 5.5.4-m3 beta version contains a number of
interesting new scalability features.

It contains the following InnoDB improvements:
Multiple Buffer Pool instances
- For example if the buffer pool is 8 GByte in size
the buffer pool can be split into 4 buffer pools
each containing 2 GBytes. Each page is mapped into
one and only one of these buffer pools.
Split Log_sys mutex
- We have ensured that the Log mutex and the buffer
pool mutex is more independent of each other. Also
the log_sys mutex is through this split less
contended.
Split out flush list from buffer pool mutex
Split Rollback Segment mutex into 128 instances
Separate Purge Thread from Master Thread
- Splitting out the purge thread from the master thread
is very important to ensure that performance is stable.
Extended Change buffering, now also Deletes and purges are
possible to buffer.

It contains the following MySQL Server improvements:
Split LOCK_open into
- MDL hash mutex
- MDL table lock mutex
- atomic variable refresh_version
- LOCK_open
We also removed some parts not needing mutex
protection from LOCK_open. All these mutexes are taken
independently of each other in almost all places.

Remove LOCK_alarm (used in network handling)
Remove LOCK_thread_count as scalability bottleneck

In addition the InnoDB recovery has been improved by
decreasing recovery time by 10x.

MySQL 5.5.4-m3 scales to 32 cores

The newly released MySQL 5.5 beta version MySQL 5.5.4-m3
has a large number of significant performance improvements.
These improvements makes it possible for MySQL to scale
well even on 32-core servers. The graph below shows how
MySQL 5.5.4-m3 scales from 12 cores to 32 cores using a
single thread per core. The benchmark used here is
dbStress. dbStress uses a number of tables which spreads
the impact of mutexes and improves scalability.



The graph below shows a similar scalability analysis on a
smaller server where the benchmark used was Sysbench RW.
The red line shows the scalability of MySQL 5.1.45, the
green line shows scalability of MySQL 5.5.3-m3 and the
blue line shows MySQL 5.5.4-m3. So this graph shows that
even with a single table in Sysbench RW we are able to
scale very well to 16 cores. The graph also displays how
our work on scaling MySQL since the release of MySQL 5.1
as GA in december 2008 is paying off in a significant
manner. So MySQL is following very well in the development
of new multi-core CPUs. The major performance enhancement
in MySQL 5.5.3-m3 is the use of the InnoDB plugin with
its inclusion of Google patches and other significant
enhancements to InnoDB. MySQL 5.5.4-m3 contains a large
number of new scalability enhancements that will be
explained more about on this blog.