Skip navigation links

MySQL Newsletter - Subscribe Today!

MySQL 5.6 - (Early Access Features) - InnoDB and Replication!

Delicious Digg StumbleUpon Reddit Twitter Facebook

MySQL 5.5 has been widely acknowledged to be the best release in the history of MySQL. And, in April 2011, we raised the bar again with Development Milestone Release (DMR) and Early Access (Labs) releases of MySQL 5.6. John Russell did an excellent job of summarizing the features in the "What's New in 5.6" devzone article he posted in April.

At OSCON 2011, we are delivering more early access (labs) features for InnoDB and Replication features. These features are focused on better scalability, performance and flexibility. We look forward to community testing and feedback.

InnoDB Improvements

InnoDB is the default storage engine for MySQL. The following feature improvements are in direct response to community and customer feedback and requests.

InnoDB Full-Text Search:

InnoDB full-text search provides users with the ability to build full text indices and search for specific text-based content stored in InnoDB tables. This new functionality supports fast and accurate search on document content using natural language, Boolean, query expansion (wildcard) and proximity search options/algorithms. Learn more:

InnoDB REDO log files max size increased up to 2 TB

Increases the maximum size of InnoDB REDO log files from 4 GB to 2 TB. Greatly improves the performance of applications with write-heavy workloads or very long running transactions, where the REDO log file limit could be reached.

InnoDB UNDO logs to reside in their own tablespace

Currently InnoDB stores UNDO logs in the InnoDB system tablespace. This enhancement enables users to separate UNDO logs (and associated random IO activity) from the system tablespace (and more sequential IO activity) and to store them on their own tablespace. This also enables users to store UNDO log specific tablespace(s) on Flash or SSDs if desired. Learn more:

InnoDB Buffer Pool options for pre-loading/warming on re-start

New options allow for the preloading or "warming" of the InnoDB buffer pool by providing the capability of dumping and restoring the contents of InnoDB buffer pool(s) to/from disk automatically at shutdown or restart, or manually at any time. The dump process is fast and compact, with minimal storage overhead on the server, as it only stores a list of pages in the bufferpool rather than the contents of the pages. On restart, the restore process reloads page data but runs in the background so normal database operations start quicker and remain unaffected while the bufferpool is reloaded. Learn more:

InnoDB .ibd files improved auto-extension

Improves the auto-extension of .ibd files for tables created using the innodb_filte_per_table setting. This setting is used by many useful InnoDB features (compression, off-page storage for BLOBs and large prefix keys for indexes. This enhancement helps reduce the contention on the InnoDB file system mutex when these features are used in configurations with many tables. With this improvement multiple files can be extended in a background process with no contention/locking of other threads doing normal read/write operations. Learn more:

InnoDB Page Size from 4K to 64K

This feature is a prerequisite for the InnoDB specific optimizations for flash and SSD (coming in a future MySQL 5.6 Labs build). InnoDB now supports smaller 4k and 8k page sizes which result in better performance and longer life span for this type of media. This feature extends InnoDB to support 4K to 64K page sizes in addition to the current 16K default. Learn more:

The list above is not exhaustive, so for a complete rundown of all the new InnoDB features see Calvin Sun's InnoDB blog.

Replication Improvements

MySQL Replication is the most popular and widely used way to achieve scalability and high-availability. The following feature improvements are also in direct response to community and customer feedback and requests.

Binlog API

The Binlog API exposes a programmatic C++ interface to the binary log, implemented as a standalone library. Using the API, developers can read and parse binary log events both from existing binlog files as well as from running servers and replicate the changes into other data stores. In short, the new Binlog API enables developers to reduce the complexity of integration by standardizing their SQL data management operations on MySQL, while replicating data to other non-MySQL applications within their data management infrastructure.

To demonstrate the possibilities of the Binlog API, an example application for replicating changes from a MySQL database to Apache Solr (http://lucene.apache.org/solr/) (a full text search server) has been developed. Learn more:

Binlog Group Commit (completes InnoDB Group Commit implemented in MySQL 5.5)

Designed to improve the performance of MySQL replication, Group Commit applies updates to the binary log in parallel and then commits them as a group to the binlog on disk.

Users have complete control over the frequency of commits to disk:

  1. Configure the number of transactions that should be grouped together
  2. Define the time interval, with millisecond granularity, before the binary log is persisted.

The current Binlog Group Commit is a snapshot of a work-in-progress.  We have not benchmarked the implementation at this time, and we expect results from that exercise to influence final implementation decisions. This is a great opportunity for the community to evaluate the implementation and feedback to the MySQL development team. Learn more:

Durable Slave Reads

With this enhancement users now have the option to control when a slave reads the master's binary log. There are two options:

  • Read the binlog as soon as updates are applied to it (with the risk of the data being lost in the event of a master crash)
  • Read the binlog only once the updates have been committed to disk, making them read-durable and therefore not risking lost transactions, but meaning the slave will be behind the master.

Learn more:

Enhanced Multi-Threaded Slaves

Multi-threaded slaves were previewed as part of the first MySQL 5.6 Labs build in April 2011, delivering significant performance enhancements by allowing updates to be applied in parallel across databases, rather than sequentially. This "refreshed" Labs version reflects testing and feedback from early adopters. Learn more:

Next Steps:

MySQL Customers & Community have been central to identifying and prioritizing these new features, and now is your opportunity to provide further input into their on-going development. Please take the time to download and evaluate the early access (labs) builds and share your feedback with us - the good, the bad... and the downright ugly! This will help ensure MySQL 5.6 is the best release ever.

Thanks, as always, for supporting MySQL!