MySQL 5.5 Update Features

Oracle, the parent company of MySQL through their Sun Microsystems purchase, has announced the release-ready update for MySQL 5.5. This release promises performance gains of up to 360% for read/write on Linux platforms and over 600% on Windows Servers. Read on for a list of the new MySQL features for both Windows and Linux in the GA release.

When MySQL was purchased from being a Sun Micro, open-source product, there were a lot of fears that MySQL release cycles would be stopped by Oracle. On December 15th, Oracle announced their first major release for MySQL: 5.5.

MySQL 5.5 was built heavily by the user community feedback on the release candidate and offers significant improvements in scalability and performance. Prior to this release, 5.4 was the last update released by Sun back in April, 2009. Suffice to say, this MySQL update release was long-overdue.

So, you’re probably wondering what’s in this release that’s so special? Let’s break that down for you and you can start planning which features are going to benefit you the most.

Unix/Linux Performance
It seems that a lot of the features of MySQL 5.5 are around InnoDB performance.  In fact, InnoDB is now the default engine in MySQL 5.5’s GA release.  What this means is that from the initial install and without configuration, foreign key constraints and transaction-level sql are now available.

MySQL did this because now, with performance enhancements including those from Percona, they InnoDB implementation now has a 300%+ improvement in read/writes.  With this, the read/write locks that would happen at the lower levels in the mutex are now gone.  This is, in part, because the connection pool limit of 1024 is also now gone.

Also offered in this GA release, MySQL has added in what is called a performance_schema.  By using this, you are able to drill down into the metrics of how queries are being executed and monitors every server interaction and event.  Not necessarily something to leave live in production for a day, but turning this on can give you valuable performance and scale information to improve your systems.  

Hooray for performance!  Almost worth the year’s wait.

A Quick Plug for Percona

We’ve worked with some of the folks at Percona closely in the past.  We use their builds, they are really smart folks…and they really understand the payoffs of micro-optimizations on high-traffic DBs.  Their software is 100% free…download Percona builds for MySQL and run some tests for yourself.

Better Replication

Chances are you have a mixed environment.  Using MySQL 5.5 you can now rely better on your replication including linux to linux and linux to Windows replication.  Replication machines now provide what is called a heartbeat to signal the master db that it is alive and online, and vice versa.  The result is reliable replication.

MySQL also introduced some enhancements to circular replication.  In cases where you have a multi-master setup, if you remove a master from an old version of MySQL, replication would fail.  This version introduces what looks like the DB equivalent of RAID hot-swapping of the Master, where replication doesn’t fail but gracefully falls over.

Semi-Synchronous Replication is another feature of MySQL’s better replication.  In this, rather than try to poll and make sure all slaves are caught up and replicated by the Master, the slaves themselves can interact and receive data updates from other slaves.  This is all normal, but now with MySQL 5.5 the replication can take a time-out without raising flags and also gracefully catch itself up from another slave in the command chain.

{mospagebreak title=MySQL 5.5 Windows Performance Improvements}

Windows Performance and Daily Use

Remember how you’d have to write a script in C# or PHP if you wanted to load a file of data and transform it into XML if you were going to deal with your MySQL data imports and exports?  No mas!  MySQL now natively loads in XML data through a nifty construct of XML that is simple and easy to use.

Date conversion from microseconds is insane for any developer, especially since local time on machines can be out of sync if not using NTP.  Also, not a problem anymore.  MySQL 5.5 has added in the to_seconds() function that will give you back epoch time to work with no matter how the data is stored, leaving it up to the application-layer to convert the date into something meaningful.

Windows Performance

OK we’ve covered the linux side of the house, but what about Windows MySQL users?  Well, you are going to love MySQL 5.5 now…they have a 500% improvement in performance on Windows.  Granted, performance before was pretty sad at times, but you are now on-par it seems with the linux flavor of MySQL.

When you look at these charts of performance of MySQL 5.1 compared to 5.5, there is no comparison.  The problems for Windows 2003 server appears to be the same throughout the MySQL product family, the mutex locks.  Now that it is solved, you will be able to enjoy all the perks of Open Source!

The MySQL 5.5 Windows build also touts a different way of processing Windows API calls natively now inside of MySQL’s I/O.  This removes the additional translation layers and resources associated.

Overall Impression

We recommend waiting to see if any patches come out in the next 2 weeks, but plan for a 2011 filled with MySQL 5.5 upgrades regardless of your system running on Linux or Windows.  The gains of this year-in-waiting release really outweigh any risks once you have given the release community some time to bang on the GA.  We’re happy that Oracle has kept it’s side of the bargain and continued development on MySQL; a critical DB platform on a large percentage of websites around the world.

Good Articles:

Sitepoint Blog: http://blogs.sitepoint.com/2010/12/16/whats-new-in-mysql-55/

InnoDB Blog: http://blogs.innodb.com/wp/2010/09/mysql-5-5-innodb-performance-improvements-on-windows/

Jeremy Zawodny: http://blog.zawodny.com/2010/04/14/mysql-5-5-4-is-very-exicting/

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye