This week marks two major milestones for MySQL and our growing community of users.
While not exhaustive, this article covers the key 5.6 features - some of which have already been previewed in earlier DMRs, and some of which are new. At a glance, 5.6 is simply a better MySQL with across the board improvements that hit every functional area of the database kernel, bringing benefits whether you deploy on-premise or in the cloud, including:
Each of these functional areas, and the key new improvements, are covered below. There are links to technical development blogs along the way so you can drill into implementation details and practical how to's to quickly get started with the features that catch your eye.
In MySQL 5.6 performance has been improved to provide better linear performance and scale on systems supporting multi-processors and high CPU thread concurrency. Internal benchmarks for SysBench Read/Write and Read Only workloads show a marked improvement in scale over the most current version of MySQL 5.5. The following shows that MySQL 5.6 provides "up and to the right" linear read/write transactions per second (TPS) scale on systems that support upwards of 48 concurrent CPU threads.
Read only TPS scale on similar systems is also improved as demonstrated here:
These MySQL 5.6 performance/scale improvements are the result of the aggregate features described below that enable version 5.6 to fully exploit the advanced multi-threaded processing power of today's x86-based commodity-off-the-shelf hardware. For context, let's take a look at the 5.6 feature set and how it breaks down across the MySQL internally tiered components.
Providing an ACID compliant transactional-based feature set, InnoDB is by far the most widely used MySQL storage engine and has been the default table type since MySQL 5.5 was released in Dec, 2010. MySQL 5.6 improves InnoDB efficiency and concurrency by removing legacy thread contention and mutex locking within the InnoDB kernel. New 5.6 optimizations and features also expand InnoDB into more use cases so developers can simplify applications by standardizing more databases on a single storage engine. The key new features include:
The net result of these improvements is reflected in the SysBench read/write benchmarks shown here for Linux:
For Linux, MySQL 5.6 shows up to a 150% improvement in TPS throughput over MySQL 5.5, while similar tests run on Windows 2008 reveal a 47% performance gain.
New optimizations have been made for read only transactions that greatly improve the performance and concurrency of web-based lookups and report-generating applications. These optimizations are enabled by default when autocommit is on, or can be atomically controlled using the new
START_TRANSACTION_READ_ONLY syntax. SysBench read only results on Linux show:
For Linux, MySQL 5.6 shows up to a 230% improvement in read only TPS throughput over MySQL 5.5, and similar tests run on Windows 2008 show a 65% performance gain.
You can download the SysBench benchmark tool for use with your own MySQL deployments and use cases.ö>
You can also get in depth MySQL 5.6 performance and feature specific benchmarks by following Mikael Ronstrom's blog and Dimitri Kravtchuk's blog. Both share the test cases and configurations they use to arrive at the conclusions drawn above.
Also, as delivered in previous 5.6 DMRs, InnoDB is optimized for use with Solid State Drives as it now supports smaller 4k and 8 k page sizes, portable .ibd files and separate tablespaces for the InnoDB Undo Log.
For a deep technical dive into the implementation, how to enable/disable where applicable and the use case specific improvements you can expect with each of these new features follow the MySQL InnoDB Engineering team blog.
The MySQL Optimizer engineering team demonstrates its leadership in innovation by re-factoring the 5.6 optimizer for better efficiency and maintainability and an improved feature set for better query execution times and diagnostics. They key 5.6 optimizer features include:
SELECT col1, ... FROM t1 ... ORDER BY name LIMIT 10;
Results have shown up to a 4x improvement in query execution times which helps improve overall user experience and response times.
For a deep technical dive into the implementation, how to enable/disable where applicable and the use case specific performance improvements you can expect with each of these new features, check out the MySQL Optimizer Engineering team blog.
Developers of today's web, cloud, social and mobile services are challenged with providing dynamic, interactive content within the best possible user response time. Applications must be designed and developed with the shortest possible path to the most commonly referenced data (show me "my profile") while also providing users with the ability to easily perform advanced requests when needed (show me all Fords that are for sale, by owner, in my area for less than $10,000). With this in mind, InnoDB now provides simple, key-value lookup of InnoDB data via the familiar Memcached API. Implemented via a new Memcached daemon plug-in to mysqld, the new Memcached protocol is mapped directly to the native InnoDB API and enables developers to bypass the expense of query parsing and go directly to InnoDB data for lookups and transactional compliant updates. With MySQL 5.6, developers can now commingle the "best of both worlds" with fast key-value look up operations and complex SQL queries to meet user and application specific requirements.
You can learn more about the details and how to get started in the InnoDB Integration with memcached section of the MySQL Documentation.
Replication remains the most widely used MySQL feature and the 5.6 RC includes new replication features designed to enable next generation web, cloud, social and mobile services with self-healing replication topologies and high performance master and slaves. The key features include:
For a rundown of the details, use cases and related benchmarks of all of these features check out Mat Keep's Developer Zone article, MySQL 5.6 Replication - Enabling the Next Generation of Web & Cloud Services.
The MySQL Performance Schema was introduced in MySQL 5.5 and is designed to provide point in time metrics for key performance indicators. The MySQL 5.6 RC improves on the 5.5 implementation in answer to common DBA/developer problems. New instrumentations include:
The 5.6 Performance Schema is also enabled by default with optimized settings that provide the above with less than 5% overhead so using the Performance Schema on a production server is no longer an issue. In addition, new atomic levels of instrumentation provided by the Performance Schema enable the capture of granular levels of cloud resource consumption by users, hosts, accounts, applications, etc. for billing and chargeback.
MySQL Engineering has several champions behind the 5.6 Performance Schema, and many have published excellent blogs that you can reference for technical and practical details. To get started see Mark Leith's blog and Marc Alff's blog.
The MySQL Performance Schema chapter in the MySQL Documentation is also an excellent source for all that is available and that can be done with the 5.6 Performance Schema.
MySQL 5.6 also introduces a major overhaul to how passwords are internally handled and encrypted. The new options and features include:
The MySQL Security Guide in the MySQL 5.6 Documentation provides details on these security specific enhancements and to all of the general MySQL 5.6 security related options.
MySQL 5.5 has been called the best release of MySQL ever. MySQL 5.6 builds on this by providing across the board improvements in performance, scalability, transactional throughput, availability and performance related instrumentation all designed to keep pace with requirements of the most demanding web, cloud and embedded use cases. The feature complete 5.6 Release Candidate is available for immediate download and evaluation from MySQL Developer Zone.
As stated earlier, this article introduces many of the key features in MySQL 5.6. For a compete picture, visit the What Is New in MySQL 5.6 section of the MySQL Documentation.
As always, thanks for reading, and thanks for your continued support of MySQL!