MySQL 5.5.4 is Very Exciting

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.

About Jeremy Zawodny

I'm a software engineer and pilot. I work at craigslist by day, hacking on various bits of back-end software and data systems. As a pilot, I fly Glastar N97BM and high performance gliders in the northern California and Nevada area. I'm also the original author of "High Performance MySQL" published by O'Reilly Media. I still speak at conferences and user groups on occasion.
This entry was posted in programming. Bookmark the permalink.

29 Responses to MySQL 5.5.4 is Very Exciting

  1. Michael Niehaus says:

    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 :-)

  2. Pingback: Les perfs de la 5.5 (et autres features) MySql’2010 - Teorem's

  3. John Russell says:

    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.

  4. Jared Kuolt says:

    “I, for one, cannot wait to see what this stuff does for us.”

    Keep us abreast, good sir!

  5. Miles Egan says:

    I think you mean “exciting”.

  6. Tim says:

    I’m confused, the lastest version is 5.1.

    http://www.mysql.com/downloads/

    When was MySQL 5.5 released?

  7. James Day says:

    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.

  8. Tim says:

    @James,

    So when will MySQL 5.4 become general availability?

  9. James Day says:

    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.

  10. Tim says:

    @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

  11. Pingback: Daily Developer Links 2010-04-14

  12. Paul Dlug says:

    How does this stack up against MariaDB? As I understand it they’ve incorporated some similar patches a while ago.

  13. Paul: I haven’t seen or done any MariaDB benchmarks yet. That would definitely be interesting–especially since they’re using the Percona version.

  14. Pingback: MySQL 5.5.4 looks awesome. « SmugMug's Don MacAskill

  15. Pingback: Top Posts — WordPress.com

  16. Pingback: Venu Anuganti Blog » MySQL 5.5 – A Community Winner

  17. Pingback: tecosystems » YourSQL, MySQL, and NoSQL: The MySQL Conference Report

  18. Mark Callaghan says:

    I am thrilled too. This is way better than I expected. They did a great job and I am starting to test it now.

  19. Pingback: Stephen O’Grady: YourSQL, MySQL, and NoSQL: The MySQL Conference Report | Weez.com

  20. Pingback: MySQL 5.5.4 is Very Exciting « Jeremy Zawodny's blog | Source code bank

  21. Pingback: Notes: Mysql @ Facebook « leskowsky.net

  22. Adrian says:

    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 :)

  23. Joel Hanger says:

    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!

  24. Pingback: MySQL 5.5.4-m3 in Production « Jeremy Zawodny's blog

  25. Pingback: sql examples | MySQL 5.5 is now in RC, and will be GA soon!

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

  27. Pingback: Notes: Mysql @ Facebook | Leskowsky.net

  28. domain says:

    Thanks for finally writing about >MySQL 5.5.4 is Very Exciting | Jeremy Zawodny’s blog <Loved it!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s