MySQL supports two primary modes of replication in its binary logs: statement or row based.
Statement Based Replication:
Row Based Replication:
For the longest time, MySQL replication has been single-threaded: only one statement is applied by the slaves at a time. Since the master applies more statements in parallel, replication can fall behind on the slaves fairly easily, under higher load. Even though the situation has improved (group commit), the slave replication speed is still a limiting factor for a lot of applications. Since row based replication achieves higher update rates on the slaves, it has been the only viable option for most performance sensitive applications.
Schema changes however are not easy to achieve with row based replication. Adding columns can be done offline, but removing or changing columns cannot easily be done (there are multiple ways to achieve this, but they all have limitations or performance implications, and are not that easy to setup).
Vitess helps by using statement based replication (therefore allowing complex schema changes), while at the same time simplifying the replication stream (so slaves can be fast), by rewriting Update statements.
Then, with statement based replication, it becomes easier to perform offline advanced schema changes, or large data updates. Vitess’s solution is called pivot.
We plan to also support row based replication in the future, and adapt our tools to provide the same features when possible.
Vitess rewrites ‘UPDATE’ SQL statements to always know what rows will be affected. For instance, this statement:
UPDATE <table> SET <set values> WHERE <clause>
Will be rewritten into:
SELECT <primary key columns> FROM <table> WHERE <clause> FOR UPDATE
UPDATE <table> SET <set values> WHERE <primary key columns> IN <result from previous SELECT> /* primary key values: … */
With this rewrite in effect, we know exactly which rows are affected, by primary key, and we also document them as a SQL comment.
The replication stream then doesn’t contain the expensive WHERE clauses, but only the UPDATE statements by primary key. In a sense, it is combining the best of row based and statement based replication: the slaves only do primary key based updates, but the replication stream is very friendly for schema changes.
Also, Vitess adds comments to the rewritten statements that identify the primary key affected by that statement. This allows us to produce an Update Stream (see section below).
Within YouTube, we also use a combination of statement based replication and backups to apply long-running schema changes without disrupting ongoing operations. See the pivot tutorial for a detailed example.
This operation, internally dubbed as pivot, works as follows:
With this process, the only guarantee we need is for the change (schema or data) to be backward compatible: the clients won’t know if they talk to a server that has applied the change yet or not. This is usually fairly easy to deal with:
Note the real change is only applied to one instance. We then rely on the backup / restore process to propagate the change. This is a very good improvement from letting the changes through the replication stream, where they are applied to all hosts, not just one. This is also a very good improvement over the industry practice of online schema change, which also must run on all hosts. Since Vitess’s backup / restore and reparent processes are very reliable (they need to be reliable on their own, independently of this process!), this does not add much more complexity to a running system.
However, the pivot operations are fairly involved, and may take a long time, so they need to be resilient and automated. We are in the process of streamlining them, with the goal of making them completely automated.
Since the replication stream also contains comments of which primary key is affected by a change, it is possible to look at the replication stream and know exactly what objects have changed. This Vitess feature is called Update Stream.
By subscribing to the Update Stream for a given shard, one can know what values change. This stream can be used to create a stream of data changes (export to an Apache Kafka for instance), or even invalidate an application layer cache.
Note: the Update Stream only reliably contains the primary key values of the rows that have changed, not the actual values for all columns. To get these values, it is necessary to re-query the database.
We have plans to make this Update Stream feature more consistent, very resilient, fast, and transparent to sharding.
If you tell Vitess to enforce semi-sync
(semisynchronous replication)
by passing the -enable_semi_sync flag to vttablets,
then the following will happen:
The master will only accept writes if it has at least one slave connected and sending semi-sync ACK. It will never fall back to asynchronous (not requiring ACKs) because of timeouts while waiting for ACK, nor because of having zero slaves connected.
This is important to prevent split brain (or alternate futures) in case of a network partition. If we can verify all slaves have stopped replicating, we know the old master is not accepting writes, even if we are unable to contact the old master itself.
Slaves of replica type will send semi-sync ACK. Slaves of rdonly type will not send ACK. This is because rdonly slaves are not eligible to be promoted to master, so we want to avoid the case where a rdonly slave is the single best candidate for election at the time of master failure.
These behaviors combine to give you the property that, in case of master failure, there is at least one other replica type slave that has every transaction that was ever reported to clients as having completed. You can then (manually, or with an automated tool like Orchestrator) pick the replica that is farthest ahead in GTID position and promote that to be the new master.
Thus, you can survive sudden master failure without losing any transactions that were reported to clients as completed. In MySQL 5.7+, this guarantee is strengthened slightly to preventing loss of any transactions that were ever committed on the original master, eliminating so-called phantom reads.
With regard to replication lag, note that this does not guarantee there is always at least one replica type slave from which queries will always return up-to-date results. Semi-sync guarantees that at least one slave has the transaction in its relay log, but it has not necessarily been applied yet. The only way to guarantee a fully up-to-date read is to send the request to the master.