MySQL: Alive and Kicking

The Seventh Annual MySQL Users Conference is being held this week in Santa Clara, CA. While much of the conference banter has focused on the prospects of the popular, open source database—the state of MySQL 6.0 and Oracle’s proposed acquisition of Sun Microsystems, the stewards of MySQL—two other announcements hold great promise for the immediate future.

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.

According to Sun, the EAStress2004 benchmark, which models a typical web application and involves a significant number of create, read, update, and delete (CRUD) operations, showed MySQL 5.4 running 59% faster than MySQL 5.1. On Sun CMT configurations, benchmarks show MySQL 5.4 throughput increasing 71 percent, from 32 to 64 vCPUs.

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.

--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
  reads 0 writes 0 requests 0 io secs 0.000000 io msecs/request 0.000000
  max_io_wait 0.000000
I/O thread 1 state: waiting for i/o request (log thread)
  reads 0 writes 3 requests 3 io secs 0.006906 io msecs/request 2.302000
  max_io_wait 6.861000
I/O thread 2 state: waiting for i/o request (read thread)
  reads 33 writes 0 requests 2 io secs 0.005925 io msecs/request 2.962500
  max_io_wait 5.895000
...
Summary of background IO slot status: 0 issued, 0 done, 0 claimed, sleep set 0

Robin Schumacher, MySQL’s Director of Product Management, claims, “MySQL 5.4 improves server utilization, handles more concurrent users, and provides 60 percent better price/performance.”

Join Me, Won’t You?

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.

And buckle up. MySQL 5.4 aims to burn rubber. Or is it silicon?