A day in the life of Danny D'Amours

MySQL 5.4 New Features – Scaling up

Has the Sun set on MySQL?

Has the sun set on MySQL? I don’t think so. Even after the buyout from Oracle, Sun’s MySQL is still looking up. With a new release coming down the pipes, there is little reason to shy away from MySQL.

After a controversial release of version 5.1 in December 2008, Sun’s (and soon to be Oracle’s) MySQLdatabase is quickly coming back with a preview version of MySQL 5.4. Part of the reason for the rapid successive releases is the shift to a new release schedule based on time instead of the previous feature based releases. Because of this shift, 5.4 won’t include any major new features such as the Falcon transactional storage engine but a more incremental set of features.

Sun making its mark

This is probably the first release in which we will see Sun’s influence over the databases’ evolution. Sun is interested in making their hardware (and OS) platform more appealing to run MySQL on and so emphasis on better support for Sun’s hardware and Solaris in particular should come through in 5.4 and future releases.

The major enhancements between 5.1 and 5.4 are scalability improvements. This new version adds the ability to address more than 4 CPU’s or cores (at least for the InnoDB engine). This allows MySQL to scale up much better on larger servers such as 16 way x86 boxes and up to 64 way on Sun’s new CMT servers.

There are other scalability improvements including the inclusion of Google’s SMP and IO patches, a new thread concurrency algorithm and more efficient read-write lock mechanism.

Optimizing queries

Subquery optimizations and improvements in how joins are performed have provide dramatic improvements in execution times for certain queries. A specific OSDL DBT-3 query was changed from a 12 minutes query requiring 9 million reads to a 1.8 second query with 153,000 reads. Obviously this is probably an extreme example but many subqueries will be sped up due to these changes.

Other changes which clearly show Sun’s influence on MySQL include the inclusion of DTrace probes on Solaris, porting of Google’s SMP patches to Solaris and “operating system specific optimizations”

So how does it perform?

Overall performance according to benchmark looks quite a bit improved including a 59% improvement in EAStress2004 benchmark and generally improved read only and read write benchmarks on both Linux and Solaris.

One of the nice things about 5.4 is that most of the improvements and enhancements don’t require and database changes or altered configurations.

Looking forward to final release

Related Posts:

April 30th, 2009 Posted by Danny D'Amours | Tech | no comments

No Comments

No comments yet.

Sorry, the comment form is closed at this time.

css.php