MySQL 5.5 is GA!
by SHEERI CABRAL Dec 15, 2010
Today it was announced that MySQL 5.5 is GA. This is very exciting news; we have not had a GA announcement since MySQL 5.1 two years ago. There are tons of new features, mostly performance enhancements, that I am looking forward to. Here is why I am excited about MySQL 5.5:
- First and foremost: the fact that it exists in a GA release. [Note: a MySQL/Sun/Oracle timeline follows. To skip this and go straight to the actual features in MySQL 5.5 I am excited about, click here.] Sun bought MySQL in January 2008, and said it was not going to change anything in the first year. MySQL 5.1 was released in November of 2008, so presumably that was on target with what MySQL AB would have done had they not been bought out by Sun.
A few months later, in April 2009, a "preview" version of MySQL 5.4 was announced. Simultaneously, it was announced that Oracle was buying Sun (and thus, was acquiring MySQL). This acquisition did not actually happen until January 2010, however, so all of the development in 2009 was still under Sun.
Then in April 2010, a year after the MySQL 5.4 announcement, we were again teased, this time with an announcement of a MySQL 5.5 beta version. We had heard this song and dance just a year ago, and all we knew at that point was that we had absolutely no confidence in what the version number of the next GA release would actually be. The official word was "MySQL 5.5" but I was feeling "I'll believe it when I see it".
So I am particularly happy to see Oracle doing good work in the MySQL sphere. As much as Oracle has a lot to learn about the open source road and has some tough times ahead of them in that regards, I am glad that at least, for now, Oracle is getting MySQL right.
- Much better scalability on multi-core CPUs (aka SMP systems), especially with InnoDB. The manual page with the details is at http://dev.mysql.com/doc/refman/5.5/en/smp-improvements.html
- Better InnoDB I/O usage - One of the biggest advantages of the Percona build is getting more background threads. In MySQL 5.1 and earlier, there is one background thread each for reading and writing. In MySQL 5.5, you can configure how many you want. The default is 4 of each, the manual says they are numeric but does not show a range of what the permitted values are (though I believe the range is 1-64, based on the Percona build documentation). The variable names are innod_read_io_threads and innodb_write_io_threads; they are global and static, so changes require a reboot.
- innodb_io_capacity allows you to change the number of iops (i/o per second). Previously the value was compiled in at 100 iops. In MySQL 5.5 it defaults to 200 but it can be changed. The manual shows the minimum value is 100, but does not have a max value (the Percona build documentation shows a max value of 999999999). Global and dynamic.
- The default storage engine for new tables is InnoDB rather than MyISAM.
- There is support for an interface for an optional semisynchronous replication plugin. Semisynchronous replication means that a master will not consider a commit completed until at least one slave shows that it has logged the transaction.
- The performance schema, a new feature for monitoring MySQL Server execution at a low level. The performance_schema is off by default and is a global static variable, meaning it needs a restart to be turned on. The manual states that the performance_schema is low overhead, though I am curious to see exactly what the overhead is.
What excites me about the performance schema is that up until now almost all of the internal monitoring for MySQL is counters showing very basic information. The exceptions to this (for me) are some of the numbers in SHOW ENGINE INNODB STATUS and Jeremy Cole's* SHOW PROFILE/S syntax. The performance schema has crazy levels of detail similar to SHOW PROFILE/S, but on a global server level. This is definitely going to challenge me and help me be a better DBA!
- Partitioning is much improved. This is not surprising since partitioning was new in 5.1, and not as feature-rich as I would have liked it to be (although even in the 5.1 tree it has improved greatly). Specifically they have added two new partitioning types RANGE COLUMNS and LIST COLUMNS. These enable optimized partitioning of ranges and lists based on dates, datetimes, and strings. The partitioning key for ranges/lists can be based on up to 16 fields, and there is partition pruning optimization when the different columns in the partitioning key are compared to constants.
- Pluggable MySQL Authentication - MySQL 5.5 supports plugin development for external authentication. I would be much more excited about this if there were actually plugins developed for authentication via LDAP, Kerberos, PAM and Active Directory. I am fairly sure that given the strength of the MySQL community plugins will be developed fairly quickly, though.
- Proxy users allow one person to have the permissions of another. This is important to be able to grant permissions to externally authorized users. Part of me thinks that the MySQL ecosystem will come up with clever hacks using this new feature.
One note: Lots of deprecated variables have been removed, so check your error logs if MySQL won’t start up after upgrade. For example, table_type has been removed in favor of storage_engine. Similarly the TYPE option for CREATE/ALTER table will not work any more, which might mean your internal scripts might not work. LOAD TABLE....FROM MASTER is gone (it was MyISAM only anyway) and BACKUP/RESTORE TABLE is gone (very very old).
The old, non-secure “put the replication parameters in the my.cnf” days are gone because --master-host, -user, -password, -port etc are removed after being deprecated for a while.
Some tools also have removed deprecated features, including mysqlbinlog, mysqldump and mysqld_multi.
All in all I am extremely excited about this release. I wonder if a lot of the improved performance is adding in the features implemented in the Percona build; I also wonder if Percona is going to come out with a MySQL 5.5 build.
* Apparently, Jeremy Cole is now a MySQL Database Architect for Twitter (starting Nov 15 2010).