A Quick Look at MySQL 5.4

Although MySQL 5.1 was released in December of 2008, Sun Microsystems isn't wasting any time moving forward with a number of new and exciting enhancements for its next release, MySQL 5.4. The first item of note is that the MySQL Server will be returning to a release early/often paradigm. So, instead of waiting on a full set of previously agreed upon features to be ready, the goal will now be to adhere to time-controlled releases that include all features that are ready by an agreed-upon beta date, with any features not fully 'baked' simply rolling to the next release.

This being the case, MySQL 5.4 won't include some large new features like the Falcon transactional storage engine and the new backup utility, but it will include a number of very desirable enhancements that help your database-driven systems scale better and run faster in many cases. Let's take a look at what's included...

Scalability Improvements

One of the primary fixes in MySQL 5.4 is that the InnoDB storage engine can now address more than 4 CPU's/cores; now those using MySQL and InnoDB can see performance increases all the way up to 16-way x86 servers and 64-way CMT servers and beyond. Naturally, this helps the MySQL Server scale much better under large application workloads. One of the nice aspects of this change is that the gains are transparent; in other words, there is nothing you have to do from a programming or configuration standpoint to obtain the benefit.

Depending on the type of benchmark or test used, preliminary internal tests from MySQL/Sun show anywhere from modest to very dramatic performance gains from the scalability fixes in 5.4 as compared to the current MySQL 5.1 GA. For example, results of an EAStress2004 benchmark (a subset of the SPECJAppserver2004 benchmark, which models a typical web application and involves a significant number of read/update/insert and delete on the database) showed MySQL 5.4 running 59% faster than MySQL 5.1:

The internal tests show MySQL 5.4 scaling better on 16 way x86 servers, improving server utilization, handling more concurrent users, and providing 60% better price/performance. And on Sun CMT configurations, benchmarks show MySQL 5.4 throughput increasing 71% from 32 to 64 vCPUs.

We also see some impressive improvements in MySQL 5.4 performance from the sysbench benchmark.

Be sure to look for all of the engineering blogs (e.g. Mikael Ronstrom's at http://mikaelronstrom.blogspot.com/2009/04/mysql-54-acknowledgements.html, several at Allan Packer's, which is: http://blogs.sun.com/allanp/, etc.) for more technical details and evidence on the various benchmarks that have been run with MySQL 5.4.

A final thing to note about the scalability fixes is that they are community contributed - Google's Mark Callaghan developed the InnoDB fixes and delivered them to MySQL engineering who then implemented them for MySQL 5.4. Also note that there are investigations currently underway to determine the viability of backporting these fixes to an upcoming release of MySQL 5.1.

Subquery Optimizations and Join Improvements

Surveys done in the MySQL Community as well as for MySQL Enterprise customers show that subquery optimizations continues to be one of the most requested items. Understanding this need, the MySQL Optimizer team has delivered a number of enhancements that boost the performance of subqueries under a number of various use cases. As an example, below is a DBT3 query run on MySQL 5.1 as well as MySQL 5.4, with the reduction in response time for MySQL 5.4 being quite dramatic:

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%'));

As with the InnoDB scalability fixes, the subquery optimizations are transparent and should not require any coding modifications to enable the performance benefit. Note that unlike the InnoDB scalability fixes, the optimizer changes for subqueries apply to all storage engines and not just InnoDB.

MySQL 5.4 also offers a new join algorithm, termed batched key access. Batched key access operations attempt to utilize main memory to speed up the execution time of multi-way joins, especially for engines such as MySQL Cluster because the number of round-trips between the Server and cluster nodes is minimized. Here's an example of some of the internal gains seen from this enhancement:

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

Improved Stored Procedure Management

MySQL 5.4 offers better stored code programming and management in the area of error handling. SIGNAL and RESIGNAL are now implemented for use in stored procedures, triggers, and functions, with the new additions providing better control over how to deal with errors that may result in the process flow/logic in a stored code object.

An example of using the new features might be the following:

CREATE PROCEDURE p (divisor INT)
BEGIN
  DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012';
  IF divisor = 0 THEN
    SIGNAL divide_by_zero;
  END IF;
END;

Information Schema Additions

There are a couple of new object enhancements included in the MySQL 5.4 information schema. The first is a new object – PARAMETERS – that details the parameters used for stored code objects; it looks like the following:

mysql> desc parameters;
+--------------------------+--------------+------+
| Field                    | Type         | Null |
+--------------------------+--------------+------+
| SPECIFIC_CATALOG         | varchar(512) | NO   |
| SPECIFIC_SCHEMA          | varchar(64)  | NO   |
| SPECIFIC_NAME            | varchar(64)  | NO   |
| ORDINAL_POSITION         | int(21)      | NO   |
| PARAMETER_MODE           | varchar(5)   | YES  |
| PARAMETER_NAME           | varchar(64)  | YES  |
| DATA_TYPE                | varchar(64)  | NO   |
| CHARACTER_MAXIMUM_LENGTH | int(21)      | YES  |
| CHARACTER_OCTET_LENGTH   | int(21)      | YES  |
| NUMERIC_PRECISION        | int(21)      | YES  |
| NUMERIC_SCALE            | int(21)      | YES  |
| CHARACTER_SET_NAME       | varchar(64)  | YES  |
| COLLATION_NAME           | varchar(64)  | YES  |
| DTD_IDENTIFIER           | longtext     | NO   |
| ROUTINE_TYPE             | varchar(9)   | NO   |
+--------------------------+--------------+------+
15 rows in set (0.00 sec)

The other changes are enhancements made to the ROUTINES object so that it contains the object's return data type information:

mysql> desc routines;
+--------------------------+---------------+------+
| Field                    | Type          | Null |
+--------------------------+---------------+------+
| SPECIFIC_NAME            | varchar(64)   | NO   |
| ROUTINE_CATALOG          | varchar(512)  | NO   |
| ROUTINE_SCHEMA           | varchar(64)   | NO   |
| ROUTINE_NAME             | varchar(64)   | NO   |
| ROUTINE_TYPE             | varchar(9)    | NO   |
| DATA_TYPE                | varchar(64)   | NO   |
| CHARACTER_MAXIMUM_LENGTH | int(21)       | YES  |
| CHARACTER_OCTET_LENGTH   | int(21)       | YES  |
| NUMERIC_PRECISION        | int(21)       | YES  |
| NUMERIC_SCALE            | int(21)       | YES  |
| CHARACTER_SET_NAME       | varchar(64)   | YES  |
| COLLATION_NAME           | varchar(64)   | YES  |
| DTD_IDENTIFIER           | longtext      | YES  |
| ROUTINE_BODY             | varchar(8)    | NO   |
| ROUTINE_DEFINITION       | longtext      | YES  |
| EXTERNAL_NAME            | varchar(64)   | YES  |
| EXTERNAL_LANGUAGE        | varchar(64)   | YES  |
| PARAMETER_STYLE          | varchar(8)    | NO   |
| IS_DETERMINISTIC         | varchar(3)    | NO   |
| SQL_DATA_ACCESS          | varchar(64)   | NO   |
| SQL_PATH                 | varchar(64)   | YES  |
| SECURITY_TYPE            | varchar(7)    | NO   |
| CREATED                  | datetime      | NO   |
| LAST_ALTERED             | datetime      | NO   |
| SQL_MODE                 | varchar(8192) | NO   |
| ROUTINE_COMMENT          | varchar(64)   | NO   |
| DEFINER                  | varchar(77)   | NO   |
| CHARACTER_SET_CLIENT     | varchar(32)   | NO   |
| COLLATION_CONNECTION     | varchar(32)   | NO   |
| DATABASE_COLLATION       | varchar(32)   | NO   |
+--------------------------+---------------+------+
30 rows in set (0.00 sec)

OUT Parameter Support in Prepared Statements

In prior versions of MySQL, you could not have output parameters in any prepared statements, but in MySQL 5.4 you now can, as this simple example shows:

mysql> CREATE PROCEDURE p1(OUT v1 INT, OUT v2 CHAR(32))
    ->   BEGIN
    ->     SELECT 1;
    ->     SET v1= 10;
    ->     SET v2= 100;
    ->   END
    -> /
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE s1 FROM 'CALL p1(?, ?)';
    -> /
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> delimiter ;
mysql> EXECUTE s1 USING @u1, @u2;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

Conclusion

There are other enhancements in MySQL 5.4 as well such as the introduction of DTrace support for MySQL on the Solaris platform, and a replication heartbeat, which helps ensure that master/slave topologies are up and running. All in all, the 5.4 release focuses quite a lot on transparent performance improvements and extra diagnostic and error handling capabilities, which are things normally always welcomed by developers and DBAs.

You can now download the MySQL 5.4 preview addition at: http://www.mysql.com/5.4. When you download MySQL 5.4, keep two important things in mind. First, the preview release of MySQL 5.4 only includes the InnoDB scalability fixes (addressing more CPU's/cores). Second, it is limited to the Solaris and Linux 64-bit platforms at this time. The other features mentioned in this article and additional platform support will be appearing very shortly, so be looking for upcoming announcements soon.

But if you have a >4 CPU 64-bit Solaris/Linux server, are using InnoDB, and want to see what impact MySQL 5.4 will have on your current applications, then please download the preview release now and let us know what you see. And as always, thanks for your support of MySQL and Sun!