2010-12-16

A quick look at MySQL 5.5 GA

Congratulations to Oracle and the MySQL team for getting 5.5 out the door!

I first have to acknowledge that I have not been able to follow the MySQL 5.5 development as closely as I would have wanted during the last 2 years as most of the planning of MySQL 6.0, 5.4 and 5.5 has happened behind closed doors, without insight for the community. The commits have been open, until recently, but it's not easy to follow what is happening just based on the commits. I am sure that I am missing below some of the important features in 5.5 and forgetting to acknowledge some of the people that have done great work on 5.5.

That said, the 5.5 release contains some things that a lot of heavy users of MySQL have waited a long time for. Here follows some of the main things that I know about.

InnoDB improvements:
  • The new version 1.1 of the already improved InnoDB plugin is now the default (and only) InnoDB (in 5.1 the InnoDB plugin version 1.0.x was an optional engine).
  • Multiple buffer pools and rollback segments, splitting of log_sys and flush_list mutex, and other improvements that together give a major contribution to the improved scalability and performance of 5.5.
  • Thanks to the InnoDB team for continuing to do impressive work!
Adding DDL locking.
  • In earlier MySQL versions LOCK_open, which protected table opening (among other things), was one of the main things that stopped MySQL from scaling on many CPU's, especially when many tables were used. By introducing proper DDL locking this issue is now mostly fixed.
  • A big thanks to Konstantin Osipov and Dimitry Lenev for the hard work they have done in getting this to work.
Reducing scalability bottlenecks in the core server, such as LOCK_open, LOCK_alarm, LOCK_thread_count, Query cache and binlog mutexes and so on.
  • Thanks to Mikael Ronström for driving this!
A big speed improvement for multi-cpu on windows.
  • This was actually the result of a minor bug fix for how mutex and condition variables were used on Windows. By using native Vista primitives instead of homegrown ones, a big speedup was achieved.
  • Thanks to Vladislav Vaintroub for getting this fixed!
Optional Semi-synchronous replication (the server will wait for at least one slave to catch up before continuing) and replication heart beat are good improvements for those that use replication.
  • Thanks to Google and Mark Callaghan's team for providing the initial patches for this.
The performance schema gives a lot of more insight in which mutex and IO calls are the bottleneck for your queries.
  • Thanks to Marc Alff for driving and developing this.
Pluggable authentication
  • Done by MIT student R.J.Silk and Sergei Golubchik for MariaDB 5.2. Donated to Oracle by Monty Program Ab.
And of course a big thanks to everyone else who has been involved in this and been fixing bugs in 5.1 and later.

Some good links for more information about the improvements in 5.5:
- New In MySQL 5.5 Performance_scale Unleashed Presentation
-
Introduction to MySQL 5.5
- MySQL 5.5: What's New in Replication

It's now almost exactly 2 years since the MySQL 5.1 GA release, so what's the verdict of 5.5?

The two things that impress me are the work on the InnoDB storage engine and the DLL locking. Most of the other new features are minor (technically) adjustments even if the results, like with the windows speed improvement, can be impressive. As a technical person I have a hard time getting impressed by something that could be done in a few hours and could easily have been done in 5.1.

What is worrying me the most is the things that are not in 5.5. Some if the things that were supposed to be in the next MySQL release are:

Pool of threads (instead of one thread per connection like it is in MySQL 5.1).
  • As far as I know, this feature has been moved to appear in 5.6 enterprise and will not be part of the community server.
An open source, free backup utility and backup API that would work for all storage engines. This has probably been the most awaited feature of MySQL ever!
  • This project was discontinued by Oracle the same day the feature was deemed ready to be pushed into 5.5.
In 5.5 some internal subsystems, like safemalloc, a portable memory checker, were removed.

Another worry I have is how long Oracle will be able to continue development of MySQL while important core developers continue to leave. Of the above, Konstantin Osipov and Vladislav Vaintroub have already left Oracle and there are not many old-timers left.

When it comes to MariaDB we are continuing to work making MariaDB 5.3 stable. MariaDB 5.1, 5.2 and 5.3 use the InnoDB-plugin compatible XtraDB storage engine, which already has many of the speed improvements of the new InnoDB plugin, but not yet the windows speed improvement.

We have already merged MariaDB 5.3 with MySQL 5.5 and we will release the MariaDB 5.5 tree shortly. It has taken some time as there are a lot of things done in MariaDB 5.3 that conflict with new code in MySQL 5.5 and we also have to add back some of the features removed in MySQL 5.5.

The next big decision is to decide which version we should release next as a stable release; MariaDB 5.3 or MariaDB 5.5.

The problem is that it's very hard to evaluate how stable 5.5 really is as that 5.5 has not been tested by the public enough and some of the bugs in the public bug system, like the critical Bug #33082, can't be accessed. The bug database shows 294 open bugs for MySQL 5.1 and 150 for MySQL 5.5. I haven't however had time to evaluate all public bugs, so I don't know how severe they really are. A lot depends also how many bugs will be filed during the next few weeks after the GA release.

Note: I corrected the above sentence after getting confirmation that the MySQL team has not yet switched to Oracle's internal bug system. This is scheduled to happen first in the spring. Thanks for helping me get the facts right!

The good news is that many of the bugs that I originally reported for the 5.1 GA release have been fixed. However there are still things that could have been better:
And there are still bugs that were supposed to be fixed before the 5.1 GA which are still not fixed:
So our plan is to continue to fix the last issues in MariaDB 5.3 and at the same time keep MariaDB 5.5 up to date. Early next year when it's time to decide which one to release as stable, we will have more information about the stability of MySQL 5.5. Let's all hope for the best!

6 comments:

Anonymous said...

Monty, the following:

"all new bugs found internally at Oracle by the MySQL team are filed in their internal bug system, not in the public one. A lot of the bugs in the public bug system, like the critical Bug #33094, can't be accessed."

is not true. bugs.mysql.com is still used for all new bug reports and, while there are private bug reports there, bug #33094 (http://bugs.mysql.com/bug.php?id=33094) is public and closed for ages, and http://bugs.mysql.com/bug.php?id=58970 it points to in your blog post is a bug reported (and verified) today, public one. Please, let's remain correct in details.

Anonymous said...

Thank you for correction. http://bugs.mysql.com/bug.php?id=33082 is, indeed, private.

Monty said...

openxs: Thanks for the comments.
- The bug number for the private bug was a type, now fixed.
- I have updated the blog when it come to internal bugs database. Looks like I got wrongly informed about that :(

As a separate questions, do you happen to know when Oracle will put out the latest 5.5 as a bzr tree ?
Without access to the code comments it's hard to judge the quality of the code...

LenZ said...

The MySQL 5.5 Bazaar tree has been on Launchpad for quite some time: https://code.launchpad.net/~mysql/mysql-server/mysql-5.5
We now merged back the changes that were staged in the release build tree. Ongoing bug fixing activity currently takes place in the "bugteam" tree, but will switch to the main 5.5 tree in the near future:
https://code.launchpad.net/~mysql/mysql-server/mysql-5.5-bugteam

Anonymous said...

Monty said:
"Another worry I have is how long Oracle will be able to continue development of MySQL while important core developers continue to leave. Of the above, Konstantin Osipov and Vladislav Vaintroub have already left Oracle and there are not many old-timers left."

Whilst I don't doubt the huge contributions of the old-timers, which is undeniable!! Is there not also potential to bring new ideas with new, smart developers, perhaps take mySQL in new and interesting directions? No matter how smart any of us think we are, there is always someone smarter than us!

Mark Callaghan said...

Very talented people have either left MySQL/Oracle for MPAB or left altogether. I regret that. MySQL has also added talented/experienced developers but they seem to be very quiet. The OReilly conference is a great place to send some of them so they can meet other developers.