Tuesday, April 13, 2010

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


Lars Johansson said...

Hej Mikael.
I've read a lot a about the performance improvments in 5.5. But I do not understand if this is only for InnoDB. We use myIsam, can we expect performance improvments with 5.5? No article I read is clear about this.


Mark Callaghan said...

I don't see how LOCK_alarm was removed. It is still in the 5.5.4-m3 code I downloaded and thr_alarm() is still called by my_real_read.

Mark Callaghan said...

I looked at the preprocessed output and thr_alarm() is not used. So it works as you describe in your slides.