- 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.
- 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 areinnod_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.
- 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.
For example, if you have a table partitioned by the RANGE COLUMN type on a date and a username, a query that is looking for rows from a particular username (string) after a particular date will be optimized. Read more at http://dev.mysql.com/doc/refman/5.5/en/partitioning-columns.html.
- 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.
Interested in working with Sheeri? Schedule a tech call.