Concurrent Commit Revisited
By user12610824 on Apr 21, 2009
Today Sun announced the availability of MySQL 5.4, which contains a number of performance and scalability enhancements to the base MySQL 5.1 code. For the full run down on all of the enhancements, and the resulting scalability improvements, check out these blogs from Mikael Ronstrom and Allan Packer. You can also read Neelakanth Nadgir's blog, where he describes some performance fixes that were tried but rejected. As he says, sometimes you can learn a lot from the things that don't work out!
This release contains a range of improvements, both from the community (especially Google) and from work that has taken place in the MySQL performance team. As you can guess, we aren't stopping here, and there are a number of additional enhancements in the pipeline for future releases. Among those is a bug fix that is near and dear to my heart, and you can read some of the background in my blog post from last November. In essence, if you enable binlog logging, you currently lose concurrent commits in InnoDB, and this results in a loss of scalability. After languishing for far too long, an enhanced version of the fix I developed for this, and submitted to InnoDB in August 2008, is being tested for inclusion in a future release. The fix relates to a very small change in the use of the prepare_commit_mutex lock, which controls the order of commits to the binary log and the InnoDB logs, for the benefit of InnoDB hot backups (ibbackup). This small change results in a big improvement in scalability, as I demonstrated in my previous blog.
Based on a review by some of the top MySQL development engineers, including Mikael Ronstrom, Sergei Golubchik, and Mats Kindahl, the current plan is to introduce a new MySQL configuration option to enable variations on my fix. The new option, innodb_support_ibbackup, will accept values of 0, 1, or 2. These values will mean:
- innodb_support_ibbackup=0 - no prepare_commit_mutex is ever used, which is unsafe for ibbackup but otherwise is ok and gives the best scalability.
- innodb_support_ibbackup=1 - use my optimization of prepare_commit_mutex, which is safe for ibbackup, and dramatically reduces the performance impact.
- innodb_support_ibbackup=2 - use the old behavior, which is safe for ibbackup, but breaks group commit and causes a huge scalability impact.
- The default value is 1, which is the optimized use of the lock and will give good out of the box performance while still supporting ibbackup. If you know you don't use ibbackup, and don't have some other custom, in house requirement that unrelated commits in the binlog appear in the same order as commits in the InnoDB logs, you can improve scalability further by setting innodb_support_ibbackup to 0.
There are plenty of other performance and scalability enhancements that are already available in the new MySQL 5.4 release, so I urge you to check out the blogs by Mikael Ronstrom and Allan Packer for the full story. This is just one example of the additional improvements that are still in the pipeline!