Skip navigation links

What's New in the MySQL 5.6 Release Candidate

This week marks two major milestones for MySQL and our growing community of users.

  • The first is the MySQL Connect conference in downtown San Francisco. Presented "By Oracle, with and for the MySQL Community" MySQL Connect provides an intimate, technical venue for MySQL users to meet the engineers developing and supporting the MySQL products as well as fellow users and community members. MySQL Connect represents a great opportunity to discuss the latest and greatest innovations and learn about the most advanced use cases with the world's most popular open source database.
  • The second is the much anticipated MySQL Connect keynote announcement of the now feature complete MySQL 5.6 Release Candidate ("RC"). The 5.6 RC is an aggregation of the previous 5.6 Development Milestone Releases ("DMR") and provides a true functional image of what we hope will soon be the generally available ("GA"), production-ready 5.6 product.

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:

  • Better Performance and Scalability
  • Improved InnoDB for better transactional throughput and developer agility
  • Improved Optimizer for better query execution times and diagnostics
  • New NoSQL Access to InnoDB via Memcached, enabling key-value operations and complex SQL queries across the same data set
  • Improved Replication for high performance, self-healing distributed deployments
  • Improved Performance Schema for better instrumentation
  • Improved Security for worry-free application deployments
  • And other Important Enhancements

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.

Better Performance and Scalability

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.

MySQL 5.6 - Read Write (Linux)

Read only TPS scale on similar systems is also improved as demonstrated here:

MySQL 5.6 - Read Only (Linux)

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.

Improved InnoDB

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:

  • Better Transactions Per Second Thoughput - New in the 5.6 RC, InnoDB has been re-factored to minimize legacy mutex contentions and bottlenecks, most notably these things have been done:
    • Split the kernel mutex
    • There is now a separate thread for flushing operations
    • Purge is now multi-threaded
    • Reduced buffer pool contention and
    • New adaptive hashing algorithm
    • Better query optimization, execution via persistent optimizer statistics that are collected at more regular, predictable intervals

The net result of these improvements is reflected in the SysBench read/write benchmarks shown here for Linux:

MySQL 5.6 vs. 5.5 - Read Write (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:

MySQL 5.6 vs. 5.5 - Read Only (Linux)

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.

  • Online Operations - Also new in the 5.6 RC, InnoDB now enables true online index and table operations via new CREATE INDEX and ALTER TABLE DDL syntax additions. Users can add indexes and perform standard table alterations while the database remains available for application updates.
  • InnoDB Full Text Search - Provided as a better alternative to MyISAM FTS, InnoDB now enables developers to build FULLTEXT indexes on InnoDB tables to represent text-based content and speed up application searches for words and phrases. InnoDB full-text search supports Natural language/Boolean modes, proximity search and relevance ranking.
  • Transportable Tablespaces - InnoDB .ibd files created in file-per-table model are now transportable between physical storage devices and database servers; when creating a table you can now designate a storage location for the .idb file outside of the MySQL data directory. This enables you to define "hot" or busy tables on an external SSD device that does not compete with application or database overhead. This new feature also enables you to easily export/import InnoDB tables between running MySQL servers without inconsistencies or mismatches caused by buffered data, in-progress transactions, etc.

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.

Improved Optimizer

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:

  • Subquery Optimizations - The optimizer now simplifies query development by optimizing subqueries using semi-joins and materialization, in some cases prior to execution. Developers no longer need to re-write existing subqueries into joins for the optimizer to assign the most efficient access path to data. Tests run using the DBT3 Query #13 show a marked improvement in execution times over previous versions.
  • File Sort Optimizations with Small Limit - For queries with ORDER BY and small LIMIT values, the optimizer now produces an ordered result set using a single table scan. These queries are common in web applications that display only a few rows from a large result set such as
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.

  • Index Condition Pushdown (ICP) - The optimizer now pushes WHERE conditions down to the storage engine for evaluation, table scan and return of result set to the MySQL server. Benchmarks have shown up to 15x improved execution times with ICP enabled.
  • Postpone Materialization of Views/Subqueries in FROM - The optimizer is now more efficient in handling subqueries in the FROM clause. Specifically, materialization of subqueries in the FROM is postponed until their contents are needed during execution. Additionally, the optimizer may add an index to derived tables during execution to speed up row retrieval.
  • Batched Key Access (BKA) and Multi-Range Read (MRR) - The optimizer now provides the storage engine with all primary keys in batches and enables the storage engine to access, order and return the data more efficiently. Together, BKA and MRR show up to 280x improvement in query execution times.
  • Better Optimizer Diagnostics - The optimizer now provides better diagnostics and debugging with
    • EXPLAIN for INSERT, UPDATE, and DELETE operations,
    • EXPLAIN plan output in JSON format with more precise optimizer metrics and better readability
    • Optimizer Traces for tracking the optimizer decision-making process.

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.

Flexible NoSQL Access to InnoDB

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.

Improved Replication, High Availability

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:

  • Global Transactions Identifiers (GTIDs) - Arguably the most requested replication feature, GTIDs enable replication transactional integrity to be tracked through a replication master/slave topology, providing a foundation for self-healing recovery, and enabling DBAs/developers to easily identify the most up to date slave in the event of a master failure. Built directly into the native replication stream, GTIDs eliminate the need for a third-party add- on to provide this same level of tracking intelligence.
  • New MySQL Replication HA utilities - A new set of Python Utilities are designed to leverage the new replication GTIDs to provide replication administration and monitoring with automatic fail-over in the event of a failed master, or switchover in the event of maintenance to the master. This too alleviates the need for additional third party High-Availability solutions, protecting web and cloud-based services against both planned and unplanned downtime without operator intervention.
  • Multi-threaded Slaves - Splits processing between worker threads based on schema, allowing updates to be applied in parallel, rather than sequentially. This delivers benefits to those workloads that isolate application data using databases - e.g. multi-tenant systems. Results have shown up to 5x in performance gains with multi-threading enabled. For more details and related performance benchmarks, check out Mat Keep's excellent blog, Benchmarking MySQL Replication with Multi-Threaded Slaves.
  • Binary Log Group Commit - New in the 5.6 RC, replication masters now group writes to the Binlog on disk, rather than committing them one at a time, significantly improving performance on the master side of the topology. Results have shown up to 4x in performance gains when enabled.
  • Optimized Row-based Replication - By only replicating those elements of the row image that have changed following INSERT, UPDATE and DELETE operations, replication throughput for both the master and slave(s) can be increased while binary log disk space, network resource and server memory footprint are all reduced.
  • Crash-Safe Slaves and Binlog - Slaves can automatically roll back replication to the last committed event before a crash, and resume replication without administrator intervention. Not only does this reduce operational overhead, it also eliminates the risk of data loss or corruption caused by the failure of a slave. Further, if a crash to the master causes corruption of the binary log, the server will automatically recover it to a position where it can be read correctly.
  • Replication Checksums - Ensure the integrity of data being replicated to a slave by detecting data corruption and returning an error, preventing the slave itself from becoming corrupt.
  • Time-delayed Replication - Provides protection against operational errors made on the master, for example accidently dropping tables, in which event the slave can be promoted to the new master in order to restore the database to its previous state. It also becomes possible to inspect the state of a database without reloading a back up.

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.

Improved Performance Schema

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:

  • Statements/Stages
    • What are my most resource intensive queries? Where do they spend time?
  • Table/Index I/O, Table Locks
    • Which application tables/indexes cause the most load or contention?
  • Users/Hosts/Accounts
    • Which application users, hosts, accounts are consuming the most resources?
  • Network I/O
    • What is the network load like? How long do sessions idle?
  • Summaries
    • Aggregated statistics grouped by statement, thread, user, host, account or object

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.

Improved Security

MySQL 5.6 also introduces a major overhaul to how passwords are internally handled and encrypted. The new options and features include:

  • Provide an alternative to password in master.info - MySQL 5.6 extends the replication START SLAVE command to enable DBAs to specify master user and password as part of the replication slave options and to authenticate the account used to connect to the master through an external authentication plugin (user defined or those provided under MySQL Enterprise Edition). With these options the user and password no longer need to be included in the master.info file.
  • Obfuscate passwords in general query log, slow query log, and binary log - Passwords in statements written to these logs are no longer recorded in plain text.
  • Providing password hashing with appropriate strength - Default password hashing for internal MySQL server authentication via PASSWORD() is now done using the SHA-256 password hashing algorithm using a random salt value.
  • Avoid passwords on the command line - MySQL 5.6 introduces a new "scrambled" option/config file (.mylogin.cnf) that can be used to securely store user passwords that are used for command line operations.
  • Change Password at next login - Developers and DBAs can now control when account passwords must be changed via a new password_expired flag in the mysql.user table.
  • Policy-based Password validations - Passwords can now be validated for appropriate strength, length, mixed case, special chars, and other user defined policies based on LOW, MEDIUM and STRONG designation settings. See the Password Validation Plugin section of the MySQL 5.6 Documentation for details and available configuration options.

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.

And Other Important Enhancements

  • New default configuration optimizations - MySQL 5.6 introduces changes to the server defaults that provide better out-of-the-box performance on today's server and system architectures. These new defaults are designed to minimize the upfront time spent on changing the most commonly updated variables and configuration options. See the Changes to Server Defaults section of the MySQL Documentation, for a complete rundown of what is changed, what parameters are auto sized based on environment, and what settings can be set and controlled at server start up.
  • Improved TIME/TIMESTAMP/DATETIME Data Types
    • TIME/TIMESTAMP/DATETIME - Now allow microsecond level precision.
    • TIMESTAMP/DATETIME - For any TIMESTAMP or DATETIME column in a table, you can now assign the current timestamp as the default value, the auto-update value, or both. In 5.5, this is true only for TIMESTAMP, and for only one TIMESTAMP column per table.
    • TIMESTAMP - Columns are now nullable by default. TIMESTAMP columns no longer get DEFAULT NOW() or ON UPDATE NOW() attributes automatically without them being explicitly specified and non-NULLable TIMESTAMP columns without explicit default value treated as having no default value. You can learn more here < http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html>.
  • Ipv6 improvements
    • MySQL 5.6 improves INET_ATON() to convert and store string-based IPv6 addresses as binary data for minimal space consumption.
    • MySQL 5.6 changes the default value for the bind-address option from "0.0.0.0" to "0::0" so the MySQL server accepts connections for all IPv4 and IPv6 addresses. You can learn more in the MySQL Documentation.
  • Improved Partitioning
    • Improved performance for tables with large number of partitions - MySQL 5.6 now performs and scales on highly partitioned systems, specifically for INSERT operations that span upwards of hundreds of partitions.
    • Import/export tables to/from partitioned tables - MySQL 5.6 enables users to exchange a table partition or subpartition with a table using the ALTER TABLE ... EXCHANGE PARTITION statement; existing rows in a partition or subpartition can be moved to a nonpartitioned table, and conversely, any existing rows a nonpartitioned table can be moved to an existing table partition or subpartition. You can learn more in the Exchanging Partitions and Subpartitions with Tables section of the MySQL Documentation.
    • Explicit partition selection - MySQL 5.6 supports explicit selection of partitions and subpartitions that are checked for rows matching a given WHERE condition. Similar to automatic partition pruning, the partitions to be checked are specified/controlled by the issuer of the statement, and is supported for both queries and a number of DML statements (SELECT, DELETE, INSERT, REPLACE, UPDATE, LOAD DATA, LOAD XML). You can learn more in the Partition Selection section of the MySQL Documentation.
  • Improved GIS: Precise spatial operations - MySQL 5.6 now provides geometric operations via precise object shapes that conform to the OpenGIS standard for testing the relationship between two geometric values. You can learn more by referencing the MySQL Documentation.

Conclusion

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.

Next Steps

As always, thanks for reading, and thanks for your continued support of MySQL!