Yesterday at the MySQL Conference, I spent time in a few sessions discussing the performance enhancements in the MySQL 5.5.3 and 5.5.4 milestone releases. What I saw made me very, very happy. In fact, the timing couldn’t be better. We’re just starting a migration to some new database hardware at work, going from some dual processor, dual core AMD boxes with truly horrible hardware RAID and 32GB RAM setups to machines with 8 and 16 CPU cores, 72GB RAM, and blindingly fast Fusion-io solid state disks (SSDs).
It seems that in the MySQL 5.5.4 release, several performance bottlenecks that really affected scalability beyond 4 cores have been either eliminated or seriously mitigated. Some of the changes were in MySQL itself, while others are InnoDB specific.
- Multiple Rollback Segments mean the 1,024 concurrent transaction limit goes away and concurrent transactions will have less mutex (lock) contention.
- Removing MySQL’s LOCK_open mutex (lock) and replacing it with a Metadata Locking (MDL) Framework eliminates a very large bottleneck when going beyond 4 CPU cores in even read-only benchmarks.
- InnoDB Recovery is WAY FASTER in this release. This is the result of some small but important changes to the algorithms used when dealing with the redo log and replaying transactions. At least one O(n*n) operation has become O(n log n) and other optimizations have contributed to an order of magnitude improvement in recovery time.
- There is now a DELETE buffer inside of InnoDB that should help with workloads that involve some large DELETE runs. I’m optimistic that this may apply to a particularly hairy problem we run into with slave lag during such runs.
- Split Buffer Pools allow you to squeeze another 30% or so out of InnoDB in workloads that would otherwise bottleneck on the kernel mutex (lock) inside of InnoDB. This means you can tell InnoDB to create N buffer pools instead of a single monolithic one and it will then use a simple hash function to spread pages across the buffer pools. In the future that simple hash function may be something that DBAs can tune. Imagine being able to have a buffer pool per table, or at least being able to isolate certain tables into their own buffer pools. This is roughly similar to having multiple key caches in the MyISAM world.
- The InnoDB Performance Schema provides a lot more visibility about what’s going on inside of InnoDB.
- There are numerous improvements to MySQL replication durability that make it less likely to encounter problems when a slave crashes and comes back online.
There is a full list of changes to MySQL 5.5 available on-line. I haven’t yet found documentation for the full changes in the InnoDB plugin 1.1 but I’m sure that will appear soon (or someone will correct me).
The benchmarks presented that compared MySQL 5.5.4 with 5.1 show substantial improvements in a variety of workloads. And given how many shops are still running MySQL 5.0.xx in production (including us), that means there really is A LOT to look forward too–especially on newer hardware.
I, for one, cannot wait to see what this stuff does for us.
Thanks to the MySQL and InnoDB teams for their continued hard work and dedication to making MySQL faster as hardware evolves.