MySQL: Alive and Kicking

Despite some of the concerns floating around the MySQL Conference this week, there's some good news coming out of the event. The MySQL developers are returned to a "release early, release often" schedule and the pending 5.4 release has a number of features worth keeping an eye on.

The MySQL development team stated it will return to a “release early, release often” schedule; and the team announced that MySQL 5.4, a maintenance release of the shipping production software, will include a variety of optimizations to boost the performance of the database software on everything from vanilla gear to high-end, 64-processor machines.

Previously, iterations of the MySQL server software were offered on a timetable, with each scheduled release including only those features and repairs considered production-ready at the time. More recently, updates have been less predictable, because the team postponed each revision until a requisite set of features were implemented in entirety. While “release early, release often” is a considerable onus on the developers, it is nonetheless the prevalent model for open source software. Moreover, frequent maintenance releases deliver valuable, often essential, new code quickly and constantly. Hence, MySQL DBAs should be pleased with the new mandate to field features more promptly.

MySQL 5.4, now available for preview on Linux and Solaris systems and soon to be obtainable for all platforms, is a case-in-point: MySQL 5.4 includes some much-needed performance bumps and comes just a scant five months after the release of MySQL 5.1. (MySQL 5.1 is the current production-quality software. MySQL 6.0, the next major release, is in alpha, or very early testing.) As the release notes state, “The principal emphasis [for MySQL 5.4] is to improve scalability on multi-core CPUs. MySQL 5.4 takes advantage of features of SMP systems and tries to eliminate bottlenecks in MySQL architecture that hinder full use of multiple cores.”

There are three notable tune-ups in MySQL 5.4. Two are patches, courtesy of Google, to increase throughput on multi-processor machines. The third, implemented by MySQL staff, hastens subqueries and joins.

Google Goodness

Google is a longtime user of MySQL, and its developers made a number of local changes to the database to speed it up on multi-core machines. (MySQL supports many different storage engines. InnoDB is one of the most mature of the engines and offers transactions and referential integrity.) Specifically, the patches (SMP Performance, InnoDB Async I/O, and InnoDB I/O Tuning) affect InnoDB, especially locking, memory management, and thread concurrency.

The InnoDB storage engine can now utilize more than four cores. 16-core x86 systems are supported, as are 64- or more concurrent multi-threaded (CMT) servers. Better yet, no SQL code changes are required.

Some of the acceleration must also be attributed to changes to the InnoDB I/O subsystem to make more effective use of available I/O capacity. And beyond enhancements, the InnoDB I/O subsystem is now configurable. You can change the number of input and output threads and regulate the I/O rate. For example, if your system uses solid-state disks, you can increase the I/O rate to capitalize on fast transfers.

Google also contributed two patches to improve the status display of the InnoDB engine. This output shows the condition of the I/O threads.

Subqueries are notoriously slow in MySQL 5.1 and older versions, so the changes in MySQL 5.4 are particularly welcome.

Schumacher provides this example.

SELECT COUNT(l_orderkey) FROM lineitem
WHERE l_linenumber=1 AND
l_orderkey IN
(SELECT o_orderkey FROM orders
WHERE o_totalprice > 1000 AND
o_custkey IN
(SELECT c_custkey FROM customer
WHERE c_address LIKE 'Le%'));

Here, the result of each parenthetical query, or subquery, forms a kind of ephemeral table for another query. This query asks, “Show me all orders of more than $1,000, with one item, placed by customers whose address begins ‘Le’.”

According to Schumacher, MySQL 5.1 performs this query in a glacial 720 seconds, while MySQL 5.4 runs the same query in 1.8 seconds with just 2 percent of the reads required previously. According to Schumacher, certain subquery operations can accelerate 99 percent.

Simple join operations have also been optimized. Here’s another example, one that would seemingly be quite common in an application.

SELECT COUNT(*) FROM part, lineitem
WHERE l_partkey=p_partkey AND p_retailprice>2050
AND l_discount>0.04;

Run on the same data set, MySQL 5.1 required 255 seconds; MySQL 5.4 took just 44 seconds. Again, this performance gain is seamless. Just install the new version of the database software and relaunch your application.

Mikael Ronstrom, the architect of MySQL 5.4, also reports general speed gains of 30-40 pecent, and a 200 percent increase on a certain benchmark.

Keep On Queryin’

MySQL 5.4 has a number of other improvements. It’s easier to build an efficient server executable from source code; some of the performance enhancements made to MySQL 6.0 have been backported to the release; and the team tweaked the code to leverage the capabilities of individual operating systems. For instance, you can now use DTrace with MySQL 5.4 on Solaris. The team also revised the default configuration parameters, so MySQL 5.4 should work better “out of the box.”

Refer to the release notes for the complete list and to learn how to tune the multi-processor settings for your hardware.