MySQL Replication Options and their Challenges

Previously we discussed replication for MySQL systems.  In this post we will discuss various MySQL replication options.

For MySQL, replication is the primary go-to strategy for high availability (HA) and, to some extent, scale. By providing additional copies of the primary database, a MySQL system using replication can withstand the loss of the master database by promoting the slave to be the new master, and updating the application endpoints. In addition, replication can provide scale by providing additional read-only copies of the database for the application to leverage. This offloads application read requests from the master, allowing the master to focus more on writes.

This makes replication seem like a magic bullet. However, each of the different MySQL replication options, MySQL native and otherwise,  come with their associated caveats, namely the following:

  • What level of HA do the various MySQL replication options provide?
  • What level of scale do the various MySQL replication options provide?

And the big question:

  • How much latency do each of the MySQL replication options create?

In other words, if the application reads from a replication slave or secondary master, what’s the level of consistency between data on the primary master, and data on the replication slave or secondary master? As a practical example of how data consistency matters, imagine you’ve set up your e-commerce site catalog to be on one MySQL server, and your checkouts to be on a separate server (this is actually done with Magento 2). Correspondingly, there can be latency between the two servers due to replication lag. This means, that item descriptions or attribute dependencies that get updated on the catalog server might not be immediately reflected in active sessions on the checkout server, or the inventory server. This can affect what items are actually available-to-promise, and create disparities with inventory. This will affect checkouts, either causing delays in checkout, or potential mis-orders, which have to be handled by customer service.

Five Main Replication Options Used with MySQL Workloads

  • MySQL Asynchronous Replication is the default replication used by many deployments. Asynchronous means the master will COMMIT regardless if the slave has processed the binlog entry. Thus, there will always be some number of transactions which have COMMIT on master but aren’t yet aren’t represented on the slave. The result is a trade of slave latency for throughput. HA and read scale are provided as long as some level of data consistency between master and slave is tolerable to the application. In other words, if/when the master goes offline, the slave that is promoted to master will likely have some transactions missing. Similarly, as in the e-commerce example above, applications can leverage read slaves for read scale if the applications can tolerate some degree of data inconsistency.
  • MySQL Semi-Synchronous Replication provides more guarantees of high availability, in exchange for lesser performance. Semi-synchronous means the master will block after COMMIT until at least one semi-sync slave confirms it’s received the transaction’s details, and flushed its relay log to disk. That means there’s always a durable copy of the master’s transaction information available on the slave, but doesn’t guarantee that it has been processed yet. Thus, there may be some number (less than asynchronous replication) of transactions which have COMMIT on master aren’t yet represented on the slave. The result is a higher level of HA. As long as the slave is allowed to complete the relay log before being promoted to master, there will be no transactions lost. However, this represents some level of delay before the newly promoted master can resume the production workload.
  • Certification Replication (Codership’s Galera) creates a multi-master topology, with each master able to accept write and read transactions. When any master processes a transaction, transaction details are transmitted to all cluster nodes as a certificate. Next, all the other cluster nodes must confirm that the certificate is valid. The local master commits before application ACK, and the other masters COMMIT in the background. The result is strong consistency between nodes as long as only a single master accepts writes. This also results in full HA, as each master contains a full, consistent copy of the data. However, if writes are accepted to more than a single master, performance can be significantly impacted, to the extent Codership recommends applications write to a single master. Correspondingly, write scale with certification replication works best as a consolidated workload, i.e., multiple applications writing to a single master each, rather than a single application scaling out writes to multiple masters.
  • MySQL Group Replication is very similar to certification replication. It’s a multi-master topology, with each master able to accept write and read transactions. When any master processes a transaction, the transaction information is transmitted to all cluster nodes. A quorum of other nodes must confirm the transaction is valid. Then the local master commits before application ACK, while the other masters COMMIT in background. The result is strong consistency for the clustered masters in quorum as long as only a single master accepts writes. HA is strong for all nodes in quorum. And again similar to certification replication, MySQL group replication provides read scale similar to read slaves, but does not scale out writes.
  • ClustrixDB Data Replication (Synchronous) is very different than the other MySQL replication options. To be very clear, ClustrixDB does not use MySQL Replication between its nodes.  ClustrixDB is MySQL compatible, clustered relational scale-out database. Every ClustrixDB node is in identical, global transactional state at all times. All nodes (at least two) containing data associated with the transaction are durably updated before application receives ACK. The result is strong consistency (including durable WAL-flush) for all cluster nodes in quorum per transaction. HA is strongly consistent at all times, and number of total data copies is configurable via nResilience. And due to automatic fine-grained data distribution and query fanout, ClustrixDB linearly scales out both write and read transactions with each node added to the cluster.

Summary of MySQL Replication Options

In summary, ClustrixDB uses synchronous communication to ensure all participating nodes actually complete and durably log the local transaction(s), before the application is informed of the COMMIT. There is no possibility of skew between transactional state between the nodes.

In comparison, MySQL replication is usually an asynchronous process by which a slave RDBMS consumes the changes made on the master RDBMS. This results in skew between the master and slave. Even with Galera’s “certification-based replication,” there is still skew between master and the slave. Replication is designed to provide multiple full copies of the RDBMS for fault tolerance, with the trade off of some skew.

ClustrixDB provides synchronous fault tolerance at the transaction level. This strong consistency underlies our cross-node transactional ACID guarantees.

In short, when compared with other MySQL replication options, ClustrixDB provides strongly consistent HA, with multiple copies of the data distributed across all the nodes in the cluster. This strong consistency comes with both write and read scale, linearly scaling out with each additional node added to the cluster.