Tag Archive for MySQL 5.7

MySQL Replication – Multi-Threaded Slave just got a whole lot faster

MySQL Replication Logo
A new MySQL MySQL 5.7 Multi-threaded slave labs load has now been published on MySQL Labs. This represents a signifficant step in the process of speeding up the processing of replication events on the slave – letting it keep up with the master.

The original implementation of MTS made a simple assumption about what events could safely be applied in parallel (with no dependency on the ordering). That assumption was that different schemas had no dependencies on each other and so if you had 5 schemas then one event from each schema could be applied in parallel (this is described in MySQL 5.6 GA – Replication Enhancements). This approach delivered a much higher throughput on the slave but only if you were using multiple schemas – perfect for many deployments (such as multi-tenant systems) but if you only had a single database then it didn’t help.

Last year we released a new version which removed that dependency on multiple schemas – allowing events to be applied to the same schema in parallel. For this implementation the new assumption was that if a set of transactions were part of the same binlog group commit on the master then the locking on the master has already determined that they’re indepent of each other and so that same set of events can be applied in parallel on the slave.

The latest enhancement is to allow the user to tune how many transactions are likely to be in each of those BGC buckets. This is done at the expense of delaying commits on the master and can be controlled by specifying the longest time that the system should wait (binlog-group-commit-delay) and the maximum number of transactions that should be committed together (binlog-group-commit-count). Initial benchmark results are very possitive – a 6X throughput (if willing to tolerate a small impact to the latency on the master) or 3X with no impact to the master.

Multi-Threaded slave performance gains
This is a feature where the mileage will definitely vary depending on your application and how you tune these two new parameters and so I’d certainly recommend that you try it out for yourselves. As always we’re anxious to get your feedback – for example through comments on this post or on the MySQL Replication forum.

Webinar – What’s New in MySQL Replication

MySQL Replication logoOn Friday (22/11/2013), we hosted webinar covering the new replication features in the MySQL 5.7 DMR as well as in MySQL Labs. You can now view the webinar replay here.

It’s a very exciting time for MySQL Replication! MySQL 5.6 contains numerous new replication features and Oracle recently announced…

  • The second Development Milestone Release of MySQL 5.7, introducing yet again new replication features and enhancements including optimized multi-threaded slave, better consistency with semi-synchronous replication, and improved monitoring with new Performance Schema tables.
  • Early access to multi-source replication via labs.mysql.com

During the live webinar, attendees asked questions and the MySQL Replication engineers answered them; this Q&A is included here:

  • Can we have Master & Slave on different versions of MySQL? Like Master is with 5.1 & Slave with 5.6? In general, cross version replication to a next higher version on slave is supported unless the behaviour of SQL statements change in the different versions. For more details, please look at the documentation on MySQL Replication cross-version compatibility.
  • The MySQL Replication topology discussed here supports asynchronous replication. How is Synchronous replicaiton is possible with mysql? With the loss-less semi-synchronouse replication, you’re getting very close to synchronous replication in that when your transaction has committed, you can be sure that the change has been received by a slave and so is safe. If you want truly synchronous replication then you can use MySQL Cluster (the NDBCLUSTER storage engine); typically this synchronous replication would be within a data center but you can split the cluster if you have a real need. You can watch this demo video of MySQL Cluster.
  • When Slave says it is x seconds behind, what does that means? Does it means slave would require x seconds to recover and reach the master’s current position? When the slave SQL thread is actively processing updates, this field is the number of seconds that have elapsed since the timestamp of the most recent event on the master executed by that thread.
  • What is default setting for the rpl_semi_sync_master_wait_point setting. after_sync
  • What is the best mechanism in MySQL Community Edition for backing up InnoDB tables? mysqldump has –single-transaction option, which is mainly useful for InnoDB tables with REPEATABLE READ isolation level. In short, for Community Edition mysqldump is the best solution. But for Enterprise Edition, there is MySQL Enterprise Backup.
  • How can I verify that my slave DB is in sync with the master? Is there something similar to the archive log sequence number verification in Oracle. we know there are some paramerts from show slave status output. But is there any other way to verify the sync status using MySQL tables? In MySQL 5.6, if you set master_info_repository and relay_log_info_repository to TABLE the status information is put into the mysql.slave_relay_log_info and mysql.slave_master_info tables. And in MySQL 5.7 the status is put in performance_schema you can query the status using SQL.
  • Does multi-source replication require the use of GTIDs? No – you just need to make sure that GTIDs are either enabled on the slave and all masters or disabled on all of them.
  • If multi-source can work without GTIDs, how do you prevent a transaction executed on A which is replicated to B and C, from being replicated back again to C and B via the replication channel from B to C and C to B ? Without GTIDs, the transactions are re executed (in the above scenario) if the schema developer *doesn’t* take care of fixing the keys. If there is a conflict on a key, the Slave errors out. So, In short, MSR doesn’t involve itself with conflict detection and resolution. When using positions, the schema developer has to make sure that multisourced slave receives correct data to avoid reexecution of transactions.
  • In loss-less semi-sync, how can the transaction be written in the binary log of the master without the master committing the transaction? Does this stall the transaction on the master (until a commit is written to the binary log)? When using this feature, a commit has two phases (which is same as an XA transaction) – first there is a prepare phase and then the commit phase. The binary log is written after the prepare phase but before commit phase(internally, it is part of the commit phase but happens before the engine commit).
  • With parallel replication, is the binary logs order preserved on the slave (compared to the master), even if the transactions are applied in parallel? Asked in another way, can 2 slaves of the same master have different binlogs? No, the order will be different when multi-threaded slave feature is on.
  • Are you working with an easier way to create a replication setup – like automatically copying the master database to the slave (something like you had for MyISAM before)? Take a look at MySQL Utilities as there are lots of new tools in there 2 help setup, monitor and manage replication.
  • You guys have multiple blogs and it is a shame to not present this information in a common place. That’s been something that we’ve been discussing as well. Of course they get aggregated through planet.mysql.com and there is also a summary blog pointing to the others.
  • In Loss-Less Semi-Sync replication, what happens if the slave dies, before it receives the change in the relay. Is there a timeout which then commits and ack the data to the master? Yes, there is a timeout. After the timeout, it commits and falls back to normal asynchronous replication. The timeout is specified by the variable rpl_semi_sync_master_timeout. Currently you cannot turn off this behavior, but you can set the timeout very very high. For more information, see the configuration guide.

MySQL 5.7.2 DMR and Labs – new replication features

With today’s announcement of the second MySQL 5.7 Development Milestone Release and a new labs release it’s a very exciting time for MySQL Replication. MySQL 5.6 contained a lot of new content to make replication faster, easier to use and more reliable (Global Transaction Identifiers, Multi-Threaded Slaves, Binary Log Group Commit, Optimized Row Based Replication, Crash Safe Replication, Replication Event Checksums, Time Delayed Replication & Informational Logs) and now we want to improve things even further.

The new DMR has something for everyone.

With the improvements to Semi-Synchronous Replication, the application developer can be confident that when a transaction has been commited, the changes have been safely copied to one or more slaves and so whatever happens, that change will not be lost. Further, we now prevent other application threads seeing those changes until they’ve been received by the slave and so the application cannot start acting on the new data until it’s known to be safe. This is an important improvement in consistency which moves more of the onus from the application developer onto the database.

DBAs want replication to be fast – in particular for the slave(s) not to fall behind the master. MySQL 5.6 made some massive improvements in this area – both on the master and the slave. A number of users though were unable to exploit the Multi-Threaded Slave (MTS) feature as relied on the use of multiple schemas (databases) to get changes applied in parallel. In the new MySQL 5.7 DMR we’ve included a new option for MTS where changes can be applied in parallel – even within the same schema. A second performance feature improves throughput on the master – where the dump thread no longer needs to lock the binary log – refer to this engineering Blog on Dump Thread Enhancement in MySQL 5.7.2 for more details.

DBAs also want to monitor the status of replication and for years have relied on the SHOW SLAVE STATUS command. As replication has evolved, SHOW SLAVE STATUS has become less suitable – we needed a solution that could properly model the more sophisticated replication architectures (including GTIDs and MTS) now possible. The approach we’ve taken is to provide this information through the performance_schema.

Note that the earlier MySQL 5.7 DMR added non-blocking SHOW SLAVE STATUS, idempotent and –rewrite-db options for mysqlbinlog – these are still available in the new DMR.

The new features are described in a little more detail in the following sections (together with links to more technical content from the MySQL Engineeing team).

Loss-less Semi-Synchronous Replication

Intra-Schema Semi-Synchronous Replication

Intra-Schema Semi-Synchronous Replication

When using semi-synchronous replication in previous releases, the processing of the transaction on the master would wait for the slave’s acknowledgement after the change had been written to the storage engine but before the commit was acknowledged to the client. This satisfied the requirement that the application could be confident that if a transaction has been commited (and an acknowledgement received for the commit) then the update would not be lost. It did however leave a window where a user on another connection could read the new data from the master (because it has been written to the storage engine and the locks have been released) before the change had been received by the slave and safely stored in its relay log – that user could then start acting on that data but if the master failed at that point then that original update could still be lost and so the user would be acting on what is now inaccurate data.

This feature removes the above race condition by making the master wait for the update to be received by the slave before writing it to the storage engine and releasing the locks.

The functionality is enabled by default and shouldn’t have any negative impacts (for example on peformance) but it you’d like to disable it then you can do so using rpl_semi_sync_master_wait_point = AFTER_SYNC.

You can read more details on this in this Loss-less Semi-Synchronous Replication on MySQL 5.7.2 blog from the MySQL engineering team.

Intra-Schema Multi-Threaded Slaves

DBAs want replication to be fast – in particular for the slave(s) not to fall behind the master. If there is a short but heavy burst of writes on the master then the slave falls behind (and there is a risk of lost data if the master fails during this period) but if the write-rate is sustained then the slave would fall further and further behind indefinitely. The ongoing challenge has been that the master gets faster and faster as more cores and clients are added but applying these changes asynchronously on the slave is more complex as you need to maintain some form of ordering in order to always have a consistent data set.

The earliest solution to maintaining ordering/consistency was for the slave to apply all of the changes serially, in a single thread – this ensured that changes were applied in the same order as on the master and so guaranteed the slave always contained a consistent view. Unfortunately this meant that the slave applier thread could only exploit a single core which is very wasteful in modern systems.

MySQL 5.6 made some massive improvements for many use cases by allowing the slave to apply updates in parallel using multiple threads. The assumption made was that data was held in multiple schemas (databases) and that there were no dependencies between the data in those different schemas. In this way all of the updates for a schema would be applied in order by a single thread (ensuring consistency) but updates to other schemas could be handled by additional threads. This allowed the slave to work many times faster but was limited to those use cases that met the assumptions.

In the second MySQL 5.7 DMR we introduce a new option that enables the slave to safely apply updates in parallel – even when all of the data is held within a single schema and no assumptions can be made about the independence between any rows from any tables. To avoid conflicts/divergence from the master, the slave must ensure that any transactions that are applied in parallel don’t read or write any overlapping rows. The good news is that this grouping on non-overlapping transactions is already being figured out on the master as part of the binary log group commit functionality (introduced in MySQL 5.6) as row level locking means that overlapping transactions cannot be part of the same group commit.

MySQL 5.7 adds a logical clock/counter to the master which is used to tag transactions that are part of the same group commit’s prepare phase. The slave can then use that information – knowing that it is safe to apply all of the transactions with the same logical clock value in any order (and so can use multiple threads).

Activating the functionality is very straight-forward and the key step is to set slave-parallel-type = LOGICAL_CLOCK but see this blog from the MySQL engineering team: using the intra-schema MTS functionality for more detailed instructions. Note that there is another blog in the series – that blog provides a detailed view of how the intra-schema MTS is implemented.

Replication Performance Schema

With the ever increasing sophistication of MySQL Replication, presenting all of the information in SHOW SLAVE STATUS has become unwieldy with data for multiple slave applier threads, GTID sets etc. What would be much more convenient is if this information were presented in tables so that the user could get exactly the information they needed using SQL queries (plus of course we can lay out the data in an understandable (relational) way and can access the information from stored procedures). MySQL 5.7.2 does just this by adding MySQL Replication tables to the MySQL performance_schema.

This feature introduces 6 new performance_schema tables: replication_connection_configuration, replication_connection_status, replication_execute_configuration, replication_execute_status, replication_execute_status_by_coordinator, replication_execute_status_by_worker.

Shivji from the MySQL engineering team has written a great blog post on what’s in the new performance_schema tables and how to interpret the results.

Multi-Source Replication – LABS

Multi-Source Replication

Multi-Source Replication

MySQL replication is very flexible in the way that networks of masters and slaves that can be built up; a master can replicate to multiple slaves, a master can itself be a slave of another master, you can create a replication ring…. The one caveat to this has always been that a slave server can only have a single master (MySQL Cluster is an exception to this rule).

Why might you want to do this? There are a few use cases around consolidating data from multiple MySQL Servers into one:
– Where each of the masters is for a different shard (where the application is responsible for the sharding) and you want to be able to run reports over all of that data to produce a consolidated view
– You want to avoid the expense of dedicating a slave server to each master server
– A remote location may require less throughput and so a single server can service all of the traffic for all of the data – the ‘super-slave’ gives it a low-overhead, local database to access all of the data
– The ‘super-slave’ is used as a point where you can perform backups for all of the data

It’s possible to have a slave time-slice between multiple masters with a bit of scripting but that isn’t an ideal solution. You can also add an additional repliation layer (such as writing your own code to use the binary log API) but it would be much simpler if it were built into MySQL itself.

In this labs (i.e. for test only, not to be deployed!) release we allow a slave to simultaneously receive and process replication events from multiple masters – exactly what our users have been asking for.

What’s more, we’ve also ensured that this new functionality is compatible with the other enhancements that have been made to the MySQL Replication architecture – this includes loss-less semi-synchronus replication; and intra-schema multi-threaded slaves (as well as the existing per-database MTS). This means that you don’t have to choose between all of these tempting features – the keys to the candy store are yours!

It’s likely that not all of the masters will be the same, have the same maintenance schedules etc. and so it makes sense to be able to manage each of the relationships independently. In this labs release you can manage each master independently, including the relevant server variables but the same replication filters are currently applied to the replication events from all of the masters – we recognise that this isn’t ideal but this is cuurently an early access release and it’s something we intend to address in future versions.

At the moment there’s no limit to the number of masters that can replicate to a specific slave – in the final version we’re likely to apply a configurable limit.

Because this further complicates the information that would need to be included in SHOW SLAVE STATUS, most of the detailed information is instead presented in the Performance Schema.

It should be pointed out that there is no conflict detection or resolution built into this feature – it is the responsibility of the application to make sure that the masters are working on distinct data sets (or that they’re comfortable with the results of any conflicts).

For more technical details, refer to this engineering blog post on multi-source replication.


There’s a lot of exciting new content in the MySQL 5.7.2 DMR (download here) and the Mulit-Source Replication labs release (download here). The reason these features has been released is that we value early feedback from our community and customers – please try them out and let us know what we’re getting right and what needs to be enhanced!