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.
Jeremy Zawodny's blog 
Reading through your blog posting made me realize what I appreciate most in database systems: Ones that just work without me needing to know what’s going on behind the scenes 🙂
Pingback: Les perfs de la 5.5 (et autres features) MySql’2010 - Teorem's
The Plugin 1.1 manual is online here: http://dev.mysql.com/doc/innodb-plugin/1.1/en/index.html
Change log in the manual not updated just yet, but most of these changes are documented in Ch. 7 on Performance.
“I, for one, cannot wait to see what this stuff does for us.”
Keep us abreast, good sir!
I think you mean “exciting”.
I’m confused, the lastest version is 5.1.
http://www.mysql.com/downloads/
When was MySQL 5.5 released?
@Tim All non-GA versions are on http://dev.mysql.com/ like http://dev.mysql.com/downloads/mysql/5.5.4.html
Tim, that URL is only for versions released for general availability (full production use). See http://dev.mysql.com/ for betas and other pre-release versions. Check in a week or so if you don’t find them the first time you look at the downloads page, it’ll usually take a few days before items announced at the conference become available.
@James,
So when will MySQL 5.4 become general availability?
Tim, it won’t be declared GA until it’s looking good and reliable after a significant amount of beta usage to uncover problems that QA may have missed.
The date when it goes GA won’t be announced until it happens because that avoids annoying people who take estimated dates as guarantees and upsetting stock market regulators who get unhappy if things are announced to only some people.
Not really satisfying answers but that’s the best that can be said when the date depends on the results of testing and usage, which can’t be known in advance.
@James
I just found on MySQL that they expect a GA release of v5.5 in mid-2010.
http://planet.mysql.com/entry/?id=22648
Pingback: Daily Developer Links 2010-04-14
How does this stack up against MariaDB? As I understand it they’ve incorporated some similar patches a while ago.
Paul: I haven’t seen or done any MariaDB benchmarks yet. That would definitely be interesting–especially since they’re using the Percona version.
Pingback: MySQL 5.5.4 looks awesome. « SmugMug's Don MacAskill
Pingback: Top Posts — WordPress.com
Pingback: Venu Anuganti Blog » MySQL 5.5 – A Community Winner
Pingback: tecosystems » YourSQL, MySQL, and NoSQL: The MySQL Conference Report
I am thrilled too. This is way better than I expected. They did a great job and I am starting to test it now.
Pingback: Stephen O’Grady: YourSQL, MySQL, and NoSQL: The MySQL Conference Report | Weez.com
Pingback: MySQL 5.5.4 is Very Exciting « Jeremy Zawodny's blog | Source code bank
Pingback: Notes: Mysql @ Facebook « leskowsky.net
Great work from the MySQL team, in a moment when I for one was kind of nervous regarding its future in the long term. One thing that surprised me was the recent release of an SQL API for Berkeley DB, or to put it more clearly: An SQLite interface on top of a Berkeley DB engine.
It reminds me of the time when MySQL had BDB as an alternative transactional storage engine, along with InnoDB. Now, their only thing in common is Oracle 🙂
Great article. I’m hopefully migrating my free mysql services website: http://www.mysqlforfree.com to solid state, but that’s a little ways off. I can’t wait for the release of 5.5 and am starting pushing some development of features around it. I’m excited to see these improvements in MySQL! Keep up the great informative blogs!
Pingback: MySQL 5.5.4-m3 in Production « Jeremy Zawodny's blog
Pingback: sql examples | MySQL 5.5 is now in RC, and will be GA soon!
Similarly, the graph below shows the scalability of v.5.1, 5.5.3, and 5.5.4 using Sysbench RW. The major performance enhancement in v.5.5.3 is the inclusion of the InnoDB plugin with Google patches and other InnoDB performance enhancements. Nice work!
Pingback: Notes: Mysql @ Facebook | Leskowsky.net
Thanks for finally writing about >MySQL 5.5.4 is Very Exciting | Jeremy Zawodny’s blog <Loved it!