MySQL 5.6 Replication – New Early Access Features
By Mat Keep on Aug 01, 2011
At OSCON 2011 last week, Oracle delivered more early access (labs) features for MySQL 5.6 replication. These features are focused on better integration, performance and data integrity:
- The Binlog API: empowering the community to seamlessly integrate MySQL with other applications and data stores;
- Binlog Group Commit and Enhanced Multi-Threaded Slaves: continuing to deliver major improvements to replication performance;
- Durable Slave Reads: further enhancing data integrity.
These new features build on the significant replication enhancements announced as part of the MySQL 5.6.2 Development Milestone Release back in April.
We are always listening to our customers and community. And, based on their needs and input, the MySQL engineering team continues to take replication to the next level.
This new functionality is available for evaluation now and can be downloaded today from http://labs.mysql.com/
Highlights for each of the new capabilities are discussed below with links to blogs written by the MySQL engineers, describing implementation and how to get started in evaluating them.
We value your feedback and therefore encourage you to share your input and experiences through the comments sections of this and the blogs referenced below.
The Binlog API empowers the MySQL community to seamlessly integrate MySQL with both new and legacy applications and data stores.
Data volumes around the world now growing at the rate of 40% per annum, driving users to implement more integrated data management technologies to capture, integrate and analyze the trillions of bytes coming daily from web applications, social networking, mobile broadband networks, embedded sensors, etc.
The Binlog API enables developers to reduce the complexity of integration by standardizing their SQL data management operations on MySQL, while replicating data to other applications within their data management infrastructure.
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.
The Binlog API can be evaluated with the MySQL 5.6.2 Development Milestone Release as well as the current GA 5.5 release.
You can download the code as part of MySQL Server Snapshot: mysql-5.6-labs-binary-log-api from http://labs.mysql.com
To demonstrate the possibilities of the Binlog API, an example application for replicating changes from MySQL Server to Apache Solr (a full text search server) has been developed. The example is available with the source download on Launchpad
Enhanced Multi-Threaded Slaves
Multi-threaded slaves were previewed as part of a 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.
On-going testing and feedback from the community has enabled Oracle to release an enhanced implementation of multi-threaded slaves including:
- code refactoring;
- bug fixes;
- crash-recovery processes;
- new slave management options - now, when a user issues a STOP SLAVE command, the operation waits until all threads across each database have applied their updates before stopping, ensuring slave consistency. Slaves can still be stopped immediately by killing the SQL thread on the slave;
- improved support for statement-based replication by enhancing the handling of temporary tables;
- simplified configuration through the renaming, reworking and reduction in parameters, without affecting overall tuning ability.
The enhancements delivered as part of this labs release enables the community to extend evaluation and prototyping of new applications using a more robust and feature-complete implementation of multi-threaded slaves.
You can download the code as part of MySQL Server Snapshot: mysql-5.6-labs-multi-threaded-slave from http://labs.mysql.com
Binlog Group Commit
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 – providing two options:
1. Configure the number of transactions that should be grouped together before commit
2. Define the time interval, with millisecond granularity, before the binary log is persisted.
Checksums are used to ensure all events have been written to the binary log.
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.
You can download the code as part of MySQL Server Snapshot: mysql-5.6-labs-binlog-group-commit from http://labs.mysql.com
Durable Slave Reads
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.
This new flexibility means users can configure for performance or read-durability, depending on the requirements of their application.
So in summary, the enhancements delivered by these latest early access features deliver new capabilities and flexibility that benefits almost all users of MySQL replication.
You can evaluate them today by downloading the snapshot from http://labs.mysql.com/
Let us know what you think of these enhancements directly in comments for each blog. We look forward to working with the community to perfect these new features.