As the world's most popular open source database, MySQL's speed, reliability, and ease of use have made user-generated content and social media available to the masses. In the 18 months since the Oracle acquisition of MySQL, Oracle continues to develop MySQL internally and integrate community patches. Under Oracle's stewardship, MySQL released its first new major version in 2 years.
MySQL 5.5 was released as "GA," or generally available, on Dec. 10, 2010. There are detailed public change logs for each minor release version. Since the GA release there have been an additional eight minor releases as of the time of this article. Oracle is indeed fulfilling the promises it made to the MySQL community when it acquired Sun.
Organizations using MySQL will want to use the new features - semi-synchronous replication, PERFORMANCE_SCHEMA, and the audit plug-in interface. Additionally, partitioning and the InnoDB storage engine are much improved.
Semi-Synchronous Replication
The most unexpected new feature in MySQL 5.5 is semi-synchronous replication. Before now, replication in MySQL has been asynchronous. The slave is configured to read from the master in a "pull" model, so the master has little awareness of its slaves. MySQL Cluster uses a completely synchronous model, which blocks a COMMIT's return until all nodes in the node group have processed the change.
Semi-synchronous replication is the best of both worlds. Slaves can register with a master server and the master can be configured to wait for some of the slaves to process the change until it returns a successful COMMIT. This ensures that the changes made on the master are also made on one or more slaves, without waiting for all the slaves to process the change.
PERFORMANCE_SCHEMA
The PERFORMANCE_SCHEMA, like the INFORMATION_SCHEMA, provides system tables with metadata. As the name suggests, the metadata contains information about MySQL's performance. There are about 20 tables in the PERFORMANCE_SCHEMA database, which can be queried like any table in a database. Statistics are collected when the performance_schema system variable is set to on; by default the variable is off, as there is overhead to gathering information for the PERFORMANCE_SCHEMA database.
Tables include information about current events, historical events, files, mutexes, timers, read/write locks and threads. The metadata provided by the PERFORMANCE_SCHEMA database is expected to grow with future versions. The PERFORMANCE_SCHEMA adds much-needed instrumentation to MySQL.
The Audit Plug-In Interface
This feature is perhaps the most exciting and disappointing at the same time. The audit plug-in interface's existence is thrilling; MySQL notably lacks in audit capabilities. However, there is no actual audit plug-in yet. Having an interface means that engineers, from Oracle or the community, can develop audit plug-ins. But it has been more than half a year since the GA release and there are no audit plug-ins released by either the community or by Oracle.
Improvements
Partitioning, new in MySQL 5.1, is much improved in MySQL 5.5. Multiple fields can now be used for RANGE and LIST partitioning keys, with the expected partition pruning query optimizations. For any partitioned table, index cardinality is now calculated by sampling the largest partition, instead of the first partition, resulting in better query plans and faster query executions. More optimizations for partition pruning have been released so that even more queries only touch the partitions they need. The addition of ALTER TABLE...TRUNCATE PARTITION syntax allows for fast truncation of a partition without having to drop and re-create the partition.
The InnoDB storage engine is vastly improved in MySQL 5.5. The faster and better InnoDB plug-in is the default handler for InnoDB; it was previously available but was not the default. Similarly, the InnoDB storage engine itself is the default storage engine in MySQL 5.5, instead of MyISAM. Many community patches by excellent engineers at companies like Facebook and Google have been integrated into this version.
The most improved performance comes with more multicore support. The number of threads for InnoDB reading and writing are configurable and by default are more than in previous versions. Options to separate out the InnoDB purge thread and increase the maximum IOPS contribute to finer performance tuning. InnoDB crash recovery is faster by requiring fewer steps in the recovery process.
Next Steps
MySQL 5.5 is freely available and can be downloaded immediately. Get involved - the MySQL User Council (sponsored by IOUG-Independent Oracle User Group - www.ioug.org) serves as a resource for the MySQL community, a guide for development of education, and a direct advocacy channel for feedback to Oracle.
About the author:
Sheeri K. Cabral works for PalominoDB, Inc., a remote database management firm, and is the author of the MySQL Administrator's Bible and co-hosts OurSQL: The MySQL Database Community Podcast.
Cabral is a pioneer member of the MySQL User Council (sponsored by IOUG - Independent Oracle User Group - www.ioug.org). The purpose of the council is to solicit feedback and build recommendations based on community input regarding educational curriculum development, community-building and networking practices, and trending information for the MySQL community. The council serves as a resource for the MySQL community, a guide for development of year round education and as a direct advocacy channel for feedback to Oracle.