MySQL and MySQL Community information

Top Features in MySQL 5.6.2 Development Milestone Release

Guest Author
Great news was announced today at the Collaborate 11 conference today;  the first development milestone release of MySQL 5.6.2 is now available to the MySQL Community!  This is significant because 5.6.2 builds on the momentum generated by the performance, scalability, InnoDB and Replication improvements that rolled out in version 5.5 just last Dec.  It also marks somewhat of a paradigm shift in how the MySQL Engineering team delivers new, defined sets of stable, development complete features (development "milestones") and cutting edge, currently under development, delivered early and often features via http://labs.mysql.com/.  Both options provide a means for Community users to begin using new versions and, more importantly, new features of MySQL very early on ensuring the final product has undergone the highest degree of testing, validation and hardening before we deem it production ready.

Some of the top features found in the 5.6.2 development milestone include:

Better Performance - Improved Optimizer

The MySQL Optimizer team has done a great job implementing improvements that make query execution more efficient in 5.6.2.  These include:

Index Condition Pushdown (ICP) - ICP enables MySQL to push indexed part of WHERE condition down to the storage engine layer.  This reduces the number of accesses the storage engine has to do against the base table(s) being queries and the number of accesses the MySQL server has to do against the storage engine.

Multi-Range Read (MRR) - MRR enables data rows to be accessed sequentially rather than in random order, based on index tuples. MRR also enables batch processing of requests for key access for operations that require access to data rows through index tuples, such as range index scans and equi-joins that use an index for the join attribute.

File sort optimization - Read intensive operations often have to do "SELECT ... ORDER BY non_index_column LIMIT X". This feature speeds up the sort when X * <Row Size> is smaller than sort_buff_size.

An even Better InnoDB

Calvin Sun and the InnoDB team have been doing some amazing things with InnoDB in 5.6.2.  A quick summary:

Persistent Optimizer Stats - Provides consistency and accuracy of InnoDB statistics, specifically during re-starts of the MySQL server.  Improves startup and recovery times as optimizer stats and access paths for InnoDB indexes and table data do not need to be re-calculated, re-loaded.

•    Metrics table - Provides a generic and more comprehensive resource and performance monitoring framework for InnoDB.
•    System Tables - Expose InnoDB internal data dictionary for data file recovery and other new features (e.g. transportable tablespaces).
•    Buffer Pool Information table - Captures, displays buffer pool page information for DBA and developers.

Multi-threaded Purge - InnoDB now has multiple purge threads and separate flush thread which make purge and flushing operations more efficient/effective.

As an added bonus, these are also available in Labs.mysql.com

InnoDB now provides "NotonlySQL" access to your InnoDB data via new New Memcached API to InnoDB tables/data
Benefit: Provides NoSQL option for direct access to InnoDB table data.  You can read all about it in Calvin's InnoDB blog.

And you can grab the current development binary/source by:

Go to http://labs.mysql.com/
Select "mysql-5.6-labs-innodb-memcached" from drop down menu
Binary: mysql-5.6-labs-innodb-memcached-linux-x86_64.tar.gz
Source: mysql-5.6-labs-innodb-memcached.tar.gz

Better Replication

Lars Thalmann, Mats Kindahl and the entire Replication team have listened to users and customers and implemented some of the most enabling features to date, some are listed here, more are still in development available on Labs:

Crash-Safe Slaves - Extends the robustness and ease-of-use of MySQL replication by making the slaves crash-safe when using transactional storage engines such as InnoDB. The slave can automatically recover from a failure and resume replicating DML updates, without the DBA having to access the master.info and relaylog.info files to manually roll back replication to the last successfully committed transaction, or to skip transactions.  As a result, data integrity is enhanced and DBAs can be free to concentrate on more strategic data management activities.

Replication Event Checksums - Ensures the integrity of data being replicated to a slave by detecting data corruption and returning an error, preventing the slave itself from becoming corrupt.  Checksums are implemented in the binary and relay logs as well as to individual replication events, allowing errors to be detected whether they are caused by memory, disk or network failures, or by the database itself.  Checksum checking can be implemented on a per-slave basis, giving maximum flexibility in how and where it is deployed. 

Optimized Row-Based Replication - By only replicating partial 'before' and 'after' images for INSERT, UPDATE and DELETE events where primary keys and/or explicit columns were set in the SQL statement, performance can be increased while binary log disk space, network resources and server memory footprint are reduced.

Time-Delayed Replication - The user can define a time delay for events to be replicated from a master to each slave, defined in millisecond increments up to a maximum of 68 years! Time-Delayed Replication affords 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. Time-Delayed Replication can also be useful in testing application behavior by emulating any instances of replication lag.

Time-Delayed Replication is implemented at the per-slave level (via holding execution of the SQL_THREAD), so a user could configure multiple slaves to apply replication events immediately, and another slave to apply only after a delay of 5 minutes, therefore providing deployment flexibility.

Informational Log Events - Enhances auditing and debugging when using Row-Based Replication by writing the original query to the binary log, which is then replicated with its associated row-based event to the slave.

Remote Binlog Back-up - Enhances operational efficiency by using the replication channel to create real-time back-ups from the binary log.  By adding a "raw" flag, the binlog is written out to remote back-up servers, without having a MySQL database instance translating it into SQL statements, and without the DBA needing SSH access to each master server.

Server UUIDs - Enhances auto-discovery and analysis of replication topologies when using the MySQL Enterprise Monitor by automatically generating a Universally Unique Identifier (UUID) for each server. This is especially useful in large and highly dynamic replication environments, making auto-discovery more reliable and simplifying systems management.

And these replication enhancements are now available in Labs.mysql.com

Multi-Threaded Slaves - Replication performance is improved by using multiple execution threads to apply replication events to slave servers. The multi-threaded slave splits work between worker threads based on the database name, allowing updates to be applied in parallel rather than sequentially. As a result, replication throughput is increased and latency is reduced which minimizes the risk of replication lag, enabling slaves to serve the freshest updates to the application.

The multi-threaded slave functionality is available now as part of the snapshot-next-mr-wl5563-labs build which can be downloaded from labs.mysql.com.  Keep your eye on drop down list on this page as we are working to add menu items and files names that will simplify identifying and downloading binaries and files for specific features (like we did with InnoDB + Memcached API noted above).

Better Instrumentation via Improved PERFORMANCE_SCHEMA

The PERFORMANCE_SCHEMA that was introduced in 5.5 has been improved in 5.6.2 to provide instrumentation and metrics on:

Table I/O - Enables DBA to understand/analyze and tune how an application generates table access related I/O load on the MySQL database server.
Table Locks - Enables a DBA to identify "hot tables" and other object-related bottlenecks caused by the data access patterns of an application.

Session/User Level Load stats -Enables DBA to monitor the load generated from a specific user or application by selectively instrumenting specific end user/application connections. This is valuable to narrow down the monitoring data collected: a) by thread or session, b) by connections originating from a given user account, c) by connections originating from a given machine, d) by connections originating from a given user account on a given machine.  Added bonus:  Reducing the amount of instrumented connections also reduces the runtime overhead of the instrumentation in production.

Global Performance Metric Summaries - Globally aggregates the data collected by the PERFORMANCE_SCHEMA by thread / by object / by instrumentation point.  Data is stored in a relational table and accessible via queries.  Enables DBA monitoring scripts and applications.

Table/Index I/O Summary - Aggregates Table I/O data collected by the PERFORMANCE_SCHEMA, by index, by table. Aggregation shows which tables / index are "hot" and most used by an application, and helps to identify the application data access patterns.   Data is stored in a relational table and accessible via queries. 

Table Lock Wait Summary - Aggregates Table lock data collected by the PERFORMANCE_SCHEMA by table.  This aggregation shows which "hot" tables are often locked by an application, and helps to identify the application bottlenecks caused by table locking.  Data is stored in a relational table and accessible via queries.

You can learn all about what's new in the PERFORMANCE_SCHEMA in Mark Leith's excellent blog

Better Partitioning

Partitioning now provides options for:

Explicit partition selection - which enables users to only name/use specific sets of partitions, i.e. in queries, explicit pruning.  This speeds up query performance by automatically pruning non-relevant partitions from data lookup operations.

Keep you eye for future 5.6.x development milestone releases, as each will introduce more new, development complete features that are ready for your testing, development, extensions, feedback.  Also, watch http://labs.mysql.com for features that still under development, but ready for our "release early, release often" adopters. 

As always, thanks for your continued support of MySQL!


Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.