Featured image of post MySQL Replication, Part 1: How It Works, and Why Synchronous Still Lags

MySQL Replication, Part 1: How It Works, and Why Synchronous Still Lags

MySQL replication sends the source's binary log to replicas, which write it to a relay log and apply it with their own threads. A read sent to a replica right after a commit may not find the row. Semi-synchronous replication makes each commit wait for one replica, and Group Replication, the synchronous option, makes it wait for the group to agree. A GTID names each transaction on every server it reaches. Can a copy that is synchronous still be behind?

(updated )
TL;DR
Part 1 of a four-part series on MySQL replication. It covers how a source’s binary log becomes rows on a replica (log formats, the receiver and applier threads, the settings around them, GTIDs), then semi-synchronous replication and Group Replication, and whether a synchronous copy can still be behind. Part 2 covers what causes lag, Part 3 what breaks a replica, and Part 4 failover; every number comes from runs on MySQL 8.4.13.

Replication exists because every database server goes down eventually: a disk fails, a kernel panics, a data center loses power, or the server simply needs patching. With a single server, the application is down until it comes back, and if the disk is gone, the data exists only in backups until someone restores them. Replication keeps the same data on more than one server, so that when one goes down another takes over and the application carries on, ideally noticing nothing more than a few failed queries and a reconnect. That is high availability, and it comes in two broad shapes: a cluster whose members agree on every write before it counts, or one server that sends its changes to others that can be promoted when it fails. MySQL has both. This post starts with the second, the classic source and replica setup that most MySQL installations run, adds semi-synchronous replication to it, and ends with Group Replication, the basis of InnoDB Cluster, which is the first kind.

Here is how it works, in outline. One server, the source (older MySQL versions called it the master), takes every write and records each change in a log; the others, the replicas, fetch that log and replay it against their own copy. When the source fails, a replica is promoted to take its place, and tools such as Orchestrator automate that part: they watch the source, pick a replica, promote it and point the application at it. Because the replicas hold the data anyway, they can also answer reads, while writes still go to one server.

The weak spot is “ideally noticing nothing”. The replicas are always a little behind: a row read from a replica the moment its insert committed was missing all 6,000 times I tried it, even with both servers on the same machine, and arrived about 2 milliseconds later. How far behind depends on settings this post goes through one at a time, and what that gap costs when the source dies is Part 4.

The binary log: what a commit leaves behind

A transaction is a group of statements the database treats as one unit, and COMMIT promises that its changes survive a crash. MySQL keeps that promise with two logs. InnoDB, the storage engine, writes each change to its redo log, which it replays after a crash. The server itself writes the whole transaction to the binary log, a file of every change in commit order, and that is the log replication ships. At the defaults (innodb_flush_log_at_trx_commit = 1 and sync_binlog = 1) both are flushed to disk before COMMIT returns. Durable means durable on this server’s disk, though: if the machine is gone for good, so is every commit that existed only there, and copying commits elsewhere before anyone needs them is the job replication does.

Here is what one small transaction looks like in the binary log, decoded with mysqlbinlog -vv --base64-output=DECODE-ROWS (MySQL 8.4.13, output trimmed):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
#261117  1:44:08 server id 1  end_log_pos 277  GTID  last_committed=0  sequence_number=1  rbr_only=yes  transaction_length=343
SET @@SESSION.GTID_NEXT= '534f6c00-e228-11f1-9e8a-66177ab163d8:9'/*!*/;
BEGIN
#261117  1:44:08 server id 1  end_log_pos 422  Table_map: `lab`.`orders` mapped to number 85
#261117  1:44:08 server id 1  end_log_pos 510  Update_rows: table id 85 flags: STMT_END_F
### UPDATE `lab`.`orders`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='new' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
###   @3='leave at door' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='shipped' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
###   @3='leave at door' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
#261117  1:44:08 server id 1  end_log_pos 541  Xid = 35
COMMIT/*!*/;

The statement was UPDATE orders SET status = 'shipped' WHERE id = 1, and the statement is not what got logged. With binlog_format = ROW, the default, the log holds the row as it was and as it became, every column of both, because binlog_row_image = FULL is also the default. A replica applying this event does not run the WHERE; it finds the row by its values (by its primary key, when the table has one; Part 2 measures what happens when it does not) and overwrites it. Three more fields matter later: the GTID, which names this transaction everywhere it goes, and last_committed and sequence_number, which tell a replica which transactions it may apply at the same time.

The format decides how much gets shipped. One UPDATE that changed every row of a 1,000,000-row table wrote this much to the binary log, and the replica showed the change this long after COMMIT returned:

SettingBinary log writtenReplica showed it after (median of 3)
ROW, binlog_row_image = FULL (default)142.6 MB3.7 s
ROW, binlog_row_image = MINIMAL10.0 MB3.1 s
ROW, FULL, binlog_transaction_compression = ON3.4 MB5.2 s
STATEMENT307 bytes2.9 s

MySQL 8.4.13, one source and one replica on the same host, 1 CPU each. Replica times varied by up to 2 s between runs (ranges: 3.4–5.2, 3.0–4.2, 3.5–8.1, 2.7–3.0 s).

STATEMENT ships the SQL text and the replica runs it again, which is tiny on the wire and only as correct as the statement is deterministic; MySQL warns about statements it considers unsafe (LIMIT without ORDER BY, UUID()), and MIXED switches those to row events. MINIMAL logs only the primary key in the before image and only the changed columns in the after image, which is most of the size of FULL saved, at the price of a log that no longer shows what a row looked like before a bad update. Compression cut the log by a factor of 41 here and cost CPU on both ends, which on one core each showed up as slower and noisier apply. For network-bound replicas in another region it is often a good trade; on the same rack it mostly is not.

The binary log is also a disk-space setting. Files rotate at max_binlog_size (1 GB) and are deleted after binlog_expire_logs_seconds, 2,592,000 seconds or 30 days by default. A replica that is stopped for longer than that, or a source that purged early to free disk, leaves the replica asking for history that no longer exists, which is one of the breakages in Part 3.

Two threads, a relay log, and the settings around them

The tests in this series ran in Docker on one laptop. Each server got 1 CPU and 900 MB of memory, a source had two replicas with GTIDs on, the Group Replication tests used a group of three, and everything ran at MySQL 8.4.13 defaults except where a test says otherwise. On the same host a round trip between two servers took 0.1 ms. To stand in for servers further apart I added network delay with tc netem; the Docker VM could not add less than about 3 ms in each direction, so the tables quote the round trip I measured (22 ms), not the delay I asked for.

A replica runs two kinds of threads. The I/O thread (renamed the receiver in 8.0.26) connects to the source, asks for binary log events from where it left off, and writes them to a local file, the relay log. The SQL thread (now the applier) reads the relay log and applies each transaction as ordinary row changes. Since 8.0.27 the applier is a coordinator that hands transactions to parallel workers, four by default (replica_parallel_workers = 4), and replica_preserve_commit_order = ON makes them commit in the source’s order even when they run side by side, so a reader on the replica never sees transaction 10 without transaction 9.

Which transactions may run side by side is what last_committed and sequence_number in the binary log encode. MySQL 8.4 decides it by writeset: the source hashes the keys each transaction touched, and two transactions that touched no common row may be applied in parallel even if they committed one after the other from the same session. Before 8.4 the default was commit order, where only transactions that committed in the same group on the source could overlap, and the standard tuning was binlog_group_commit_sync_delay on the source: wait a few hundred microseconds before each binary log flush so more transactions share it, giving replicas wider groups to parallelize. 8.4 removed the choice (binlog_transaction_dependency_tracking is gone; writesets are always used), and in my runs the delay no longer helped the replica apply faster. It slowed the source instead, which shrank the backlog because the writes arrived more slowly; Part 2 has those numbers, and they cut both ways.

The two halves run independently, so a replica can have received a transaction and not applied it, and most of what follows lives in that gap. SHOW REPLICA STATUS shows both sides:

1
2
3
4
5
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
        Seconds_Behind_Source: 0
           Retrieved_Gtid_Set: ca550d64-e03e-11f1-9a5e-42ce690cb075:1-7
            Executed_Gtid_Set: ca550d64-e03e-11f1-9a5e-42ce690cb075:1-7

The replicas here ran with read_only on, which is meant to keep anyone from writing to them. It stopped the application’s user and let root straight through:

The replicas here ran with read_only on, which is meant to keep anyone from writing to them. It stopped the application’s user and let root straight through:

1
2
3
4
5
6
7
8
-- MySQL 8.4.13 replica: read_only = 1, super_read_only = 0
-- as an application user
UPDATE lab.sbtest1 SET k = k WHERE id = 1;
(1290, 'The MySQL server is running with the --read-only option so it cannot execute this statement')
-- as root: the same UPDATE succeeded, with no error and no warning

-- after SET GLOBAL super_read_only = ON, as root
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --super-read-only option so it cannot execute this statement

read_only exempts accounts with administrative privileges, and super_read_only is the setting that covers them too. That matters more on MySQL than on a byte-for-byte copy, because a replica applying row changes is a separate server with its own tables: handy for an extra reporting index or a rolling upgrade, and able to drift from the source after a write it should never have taken.

The time between a commit on the source and the moment a replica’s queries can see it is called replication lag. Here is what it did to a page that reads a row right after writing it. One connection inserted a row on the source and, as soon as COMMIT returned, a second connection looked for it on a replica:

Round trip between serversReads that missed the new rowRow visible on the replica after (median / p99)
Same host (0.1 ms)6,000 of 6,0002.4 ms / 5.8 ms
22 ms1,000 of 1,00013.6 ms / 31.1 ms

Same host: three runs of 2,000; p99 is the median of the three runs’ p99.

A couple of milliseconds of lag is invisible to a person and certain to a program, whose next query arrives sooner than that. The usual fix is to send a session’s reads to the source for a short while after it writes (read-your-writes routing).

That gap grows with the shape of the writes: many small commits, transactions too large to start applying before they end, tables without a primary key, schema changes, and whatever else the replica is busy with. Part 2 measures each of those.

A replica is not a backup
Everything the source does reaches the replicas within milliseconds, mistakes included. A DELETE without a WHERE or a dropped table is replayed as faithfully as an insert, which in the tests above took about 2 ms. Replicas protect against losing a machine; protecting against a bad statement takes backups you can restore to a point in time, which the backups post covers.

GTIDs: how a replica knows where it is

Before GTIDs, a replica tracked its place as a file name and a byte offset in the source’s binary log, binlog.000042 at position 1,987,123. That works until the source changes: the new source’s files have different names and offsets, and working out where each remaining replica should resume was a manual, error-prone step in every failover.

A GTID, a global transaction identifier, names each transaction instead. It is the UUID of the server where the transaction first committed plus a sequence number, 534f6c00-e228-11f1-9e8a-66177ab163d8:9 in the event above, and it travels with the transaction to every server that applies it. Each server keeps the set of GTIDs it has executed, written as ranges:

1
2
3
4
5
6
-- source
  @@server_uuid: 534f6c00-e228-11f1-9e8a-66177ab163d8
@@gtid_executed: 534f6c00-e228-11f1-9e8a-66177ab163d8:1-918760
-- replica: a different server_uuid, the same executed set
  @@server_uuid: 5357ee2a-e228-11f1-8409-fad5c7423278
@@gtid_executed: 534f6c00-e228-11f1-9e8a-66177ab163d8:1-918760

With auto-positioning, a replica connects with its executed set, and the source sends every transaction not in it, from whatever files hold them:

1
2
3
CHANGE REPLICATION SOURCE TO SOURCE_HOST = 'rl-myp', SOURCE_USER = 'repl',
  SOURCE_AUTO_POSITION = 1;
START REPLICA;

That is what makes a failover tractable. Pointing the remaining replicas at a newly promoted source is the same statement with a different host, and choosing which replica to promote is a set comparison: GTID_SUBSET(a, b) and GTID_SUBTRACT(a, b) tell which server has more. gtid_purged is the part of the executed set whose binary logs have already been deleted; a replica that needs anything in it cannot be served and has to be rebuilt.

Two caveats. gtid_mode is OFF by default even in 8.4 (enforce_gtid_consistency likewise), so a server set up from defaults is still on file and position until someone turns it on, which can be done online in steps. And a set comparison is only as good as the sets: a transaction committed directly on a replica gets the replica’s own UUID, and that errant GTID is how a stray write turns into a failed failover months later, which is Part 3.

Semi-synchronous: waiting for one replica to receive it

The lag above comes from a choice about when COMMIT returns. In asynchronous replication, the default, the source acknowledges a commit as soon as its own disk has it, and replicas fetch the change afterwards. Semi-synchronous replication makes the commit wait until at least one replica says it has the transaction. It ships as a pair of plugins, installed and enabled on both sides:

1
2
3
4
5
6
-- on the source
INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
SET GLOBAL rpl_semi_sync_source_enabled = 1;
-- on each replica, then restart its receiver thread
INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
SET GLOBAL rpl_semi_sync_replica_enabled = 1;

“Has the transaction” means received, not applied: “The replica acknowledges receipt of a transaction’s events only after the events have been written to its relay log and flushed to disk” (manual). With the default wait point, AFTER_SYNC, the source waits for that acknowledgement after writing the transaction to its binary log and before committing it in InnoDB.

Diagram of a commit travelling from a MySQL source to a replica. The source writes the transaction to its binary log and flushes it, which is where asynchronous replication returns to the client. The replica receives it: semi-synchronous replication returns once one replica has written it to its relay log, and Group Replication at its default consistency once a majority of the group has agreed on it. The replica then applies it so queries can see it, which only Group Replication with consistency AFTER waits for.

So semi-sync protects a committed transaction against losing the source, for as long as a replica is there to acknowledge it, and does nothing for reads. The tests show the read side plainly: with semi-sync on the same host, a read on the replica right after the commit missed the new row 2,000 times out of 2,000, because the replica had it in its relay log and had not applied it yet. At a 22 ms round trip it missed 22 of 1,000, only because waiting for the round trip gave the applier time to catch up.

The protection also switches itself off. If no replica acknowledges within rpl_semi_sync_source_timeout, which is 10 seconds by default, “the source reverts to asynchronous replication” (manual), and it returns to semi-sync on its own once a replica catches up. With the only semi-sync replica down, a commit stalls for up to ten seconds and then writes carry on with no second copy, which is the exact window in which losing the source loses data. Nothing fails and no client sees an error. The sign is Rpl_semi_sync_source_status reading 0 and Rpl_semi_sync_source_no_tx climbing. I did not run this case; it is the documented behaviour. A setup that needs the guarantee to hold sets the timeout high enough that the source blocks instead, and accepts that losing the replica then stops writes.

What the wait costs depends on distance. On the same host it did not register: a commit took 1.6 ms either way. At a 22 ms round trip, every commit paid the round trip:

Setting, 22 ms round tripCommit time (median / p99)Replica reads that missed the new rowCommits per second, 16 sessions
Async1.6 ms / 12.6 ms1,000 of 1,0002,220 to 3,010
Semi-sync27.2 ms / 34.2 ms22 of 1,000about 600

Commits per second: three 10-second runs of 16 sessions each inserting one row per transaction.

A session that commits one row at a time can do about 37 commits a second at 27 ms each, however fast the server is, so with 16 sessions, throughput here fell from 2,220 to 3,010 commits a second to about 600, and only more sessions in parallel win it back. That is why semi-synchronous replicas usually sit in the same region, often in a different availability zone a millisecond or two away, while replicas in other regions stay asynchronous. And semi-sync’s promise has an expiry date, 10 seconds by default, after which it quietly reverts to asynchronous replication; Part 4 measures what that costs in a failover.

Synchronous: Group Replication, and whether it can lag

Group Replication replaces the source and its replicas with a group of servers, three in these tests, that agree on every write. In the default single-primary mode one member takes writes and the others are secondaries. When a transaction is ready to commit, the primary broadcasts the rows it changed to the group, and “read/write transactions commit only after they have been approved by the group” (manual): a majority of members, two of three here, agree on the transaction’s place in the order and check it for conflicts with other transactions (certification). Only then does COMMIT return. A member cut off from the majority cannot commit anything. That is the sense in which it is synchronous, and it is why it is usually read as “every member has the change when COMMIT returns”.

It does not mean that. The group agrees on the order of transactions, not on having applied them. Each secondary queues the agreed transaction in its relay log and applies it afterwards with its own applier threads, the same way an asynchronous replica does, and the manual says so plainly: “Group Replication is an eventual consistency system, meaning that as soon as the incoming traffic slows down or stops, all group members have the same data content.”

So yes, synchronous replication can lag. At the 8.4 default, group_replication_consistency = BEFORE_ON_PRIMARY_FAILOVER (which changes only what a newly elected primary does and otherwise behaves like EVENTUAL), a read on a secondary right after the commit missed the new row 4,042 times out of 6,000 on the same host, and 953 of 1,000 at a 22 ms round trip. Under a 20-second burst from 16 sessions, which left an asynchronous replica 44,300 transactions behind (Part 2 follows that run second by second), a secondary fell 19,192 transactions behind and caught up 26 seconds after the writes stopped. Flow control, which slows the primary down once a member’s queue passes group_replication_flow_control_applier_threshold (25,000 transactions by default), never stepped in.

Line chart of transactions not yet applied during and after a 20-second write burst. The asynchronous replica peaks at 44,300 and catches up at 87 seconds, at 2,909 commits a second. A Group Replication secondary at the default consistency peaks at 19,192 and catches up at 46 seconds, at 1,695 commits a second. With consistency AFTER, the backlog never exceeds 6, at 502 commits a second.

The setting that closes the gap is group_replication_consistency = AFTER, under which “any subsequent transaction reads the written value or a more recent value on any group member” (manual): the commit waits until every member has applied the change. With it, 0 of 6,000 reads missed. It is also the costliest setting measured here, because each commit now waits for the slowest member to apply, not only for the network:

SettingCommit, same host (median)Commit, 22 ms round trip (median / p99)Replica reads that missed the new rowWrite burst, commits per second
Async1.6 ms1.6 ms / 12.6 ms6,000 of 6,0002,909
Semi-sync1.6 ms27.2 ms / 34.2 ms2,000 of 2,000 (same host)not measured
Group Replication, default2.1 to 2.5 ms51.5 ms / 68.8 ms4,042 of 6,0001,695
Group Replication, AFTER2.3 to 2.6 ms76.2 ms / 102.5 ms0 of 6,000502

Same host unless noted. Write burst: 16 sessions inserting for 20 seconds, same host.

Bar chart of median commit time at a 22 ms round trip: async 1.6 ms, semi-synchronous 27.2 ms, Group Replication at default consistency 51.5 ms, Group Replication with consistency AFTER 76.2 ms. Replica reads that missed the new row: 1,000 of 1,000, 22 of 1,000, 953 of 1,000 and 0 of 1,000.

At a 22 ms round trip, Group Replication’s default commit took about twice as long as semi-sync’s, close to two round trips, and still left 95% of immediate reads on a secondary stale. AFTER also exists per session, so the transactions that must be visible everywhere can pay for it while the rest do not, and BEFORE puts the wait on the reading side instead.

What to watch

Replicas, semi-synchronous or not, and Group Replication secondaries all add read capacity and no write capacity, because every one of them applies every write. The PostgreSQL replication post measured that on PostgreSQL (three servers, 3.2 times the reads, every write on the primary); my MySQL run of the same comparison was limited by its single load-generating container, so its figure is left out. When the writes outgrow one server, the next step is splitting the rows across several sources, called sharding, which gets a post of its own.

For lag, Seconds_Behind_Source is accurate about the age of what the applier is working on and says nothing about how long the backlog will take, which is the number a failover or a read-routing decision needs. Comparing Retrieved_Gtid_Set with Executed_Gtid_Set shows what has arrived and not been applied, and performance_schema.replication_applier_status_by_worker reports each applier’s last applied transaction with its original commit time on the source. On a semi-synchronous source, Rpl_semi_sync_source_status is the one to page on, since it is the only sign that the setting has quietly turned itself off. In Group Replication, COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE in performance_schema.replication_group_member_stats is each member’s backlog, the number that reached 19,192 above. And a replica that falls behind with a quiet source usually has a cause on its own side; the stalled replica post walks through finding one.

QuestionAsyncSemi-syncGroup Replication, defaultGroup Replication, AFTERSource
Read right after a write sees it on another member?No (6,000 of 6,000 missed)No (2,000 of 2,000 missed)Usually not (4,042 of 6,000 missed)Yes (0 of 6,000 missed)measured
Commit time at a 22 ms round trip (median)1.6 ms27.2 ms51.5 ms76.2 msmeasured
Can the copy fall behind under load?Yes, 44,300 transactionsYes (it waits for receipt only)Yes, 19,192 transactionsNo (6)measured, semi-sync reasoned
More write capacityNoNoNoNoreasoned
SELECT insights FROM experience WHERE downtime = 0; -- Ruslan Tolkachev