MySQL Replication Lag: Why Your Replica Returns Stale Data

The save button that lied

A ticket came in last month that I could not reproduce. Customer edits a product price in the admin panel, hits save, the page reloads, and the old price is still there. Refresh once more and it is correct. Two of us tried, both saw it intermittently, neither could trigger it on demand.

It was plain MySQL replication lag. The write hit the source, the admin panel's read hit a replica, and for a few hundred milliseconds the replica had not caught up. Nothing was corrupt. The user was reading the past, and the app did not care.

Almost every metric you reach for first says the database is fine, so here is my order.

SHOW SLAVE STATUS does not run any more

Out of muscle memory, I typed:

SHOW SLAVE STATUS\G

Syntax error. Those boxes had been upgraded to MySQL 8.4, where the master/slave vocabulary was removed, not deprecated. What still runs:

SHOW REPLICA STATUS\G
-- the column is Seconds_Behind_Source now

I had written up the 8.0 to 8.4 upgrade notes after hitting this during the migration. What I had not thought through is monitoring. A cron check that greps SHOW SLAVE STATUS output now gets a syntax error, the wrapper swallows stderr, and the alert channel goes quiet. Quiet looks exactly like healthy. Mine had been dead six days.

A number that reads zero while the replica is stale

With the right command, the output said Seconds_Behind_Source: 0, at the exact moment the admin panel was serving stale rows.

That column is not how far behind the source you are. It is the gap between the applier thread and the receiver thread inside the replica. When the applier is the slow part, you get real numbers. When the receiver itself is behind, because the link is slow, the applier has nothing to be late on and the number sits at zero. The manual spells it out: it measures the distance between the SQL thread and the I/O thread. A health check for one stage, wearing the name of an end to end metric.

Use a heartbeat instead

The number I trust is a row stamped on the source and read back on the replica. Percona's pt-heartbeat does this properly, but the short version works on managed instances too.

CREATE TABLE heartbeat (
  id TINYINT PRIMARY KEY,
  ts DATETIME(6) NOT NULL
) ENGINE=InnoDB;

INSERT INTO heartbeat (id, ts) VALUES (1, NOW(6));

Stamp it every second from cron on the source, then read it on the replica:

-- source
UPDATE heartbeat SET ts = NOW(6) WHERE id = 1;

-- replica
SELECT TIMESTAMPDIFF(SECOND, ts, NOW(6)) AS lag_seconds FROM heartbeat;

The timestamp comes from the source and travels the same path as your data, so this keeps working when the applier stalls or replication breaks. Two conditions: both hosts need NTP, since clock drift reads as fake lag. And run the updater from system cron, not the MySQL event scheduler, which shares a fate with the server it is watching.

Which stage owns the lag

Where does the lag actually live? MySQL 8 keeps commit and apply timestamps per applier worker:

SELECT worker_id,
  TIMESTAMPDIFF(SECOND, last_applied_transaction_original_commit_timestamp,
                last_applied_transaction_end_apply_timestamp) AS apply_seconds,
  TIMESTAMPDIFF(SECOND, applying_transaction_original_commit_timestamp, NOW(6)) AS txn_age
FROM performance_schema.replication_applier_status_by_worker
ORDER BY worker_id;

Small apply_seconds with a big heartbeat lag means the receiver is the bottleneck, so look at network throughput and binlog generation rate. One worker with a huge txn_age while its siblings idle means a single transaction is hogging the applier. All of them climbing together means the replica cannot keep up with the write rate, and you have a capacity problem.

One large UPDATE beats every tuning knob

A maintenance job did it: somebody ran a single UPDATE over about 1.8 million rows to backfill a column. One transaction, nothing to parallelize, so the replica applied it as a unit while everything else queued behind it. The admin panel served stale prices for eleven minutes.

Batch writes get chunked now, no exceptions:

DELIMITER $$
CREATE PROCEDURE backfill_orders()
BEGIN
  DECLARE done INT DEFAULT 1;
  WHILE done > 0 DO
    UPDATE orders SET archived = 1
     WHERE created_at < '2025-01-01' AND archived = 0
     ORDER BY id
     LIMIT 5000;
    SET done = ROW_COUNT();
    DO SLEEP(0.05);
  END WHILE;
END$$
DELIMITER ;

Five thousand rows per transaction plus a breather, and the replica never has more than a second of work waiting. DELIMITER is only needed in the mysql client. Same rule for migrations, since a long ALTER replays as one blocking event.

Most tuning advice for the parallel applier is out of date. Since 8.0.27 the replica is multi threaded by default: four workers, LOGICAL_CLOCK scheduling, commit order preserved. If the source runs 8.0 with the stock binlog_transaction_dependency_tracking=COMMIT_ORDER, raising workers to sixteen buys almost nothing, because the binlog only marks transactions as independent when their commit windows overlapped. Switch the source to WRITESET first. On 8.4 the variable is gone and write set tracking is always on.

The part that actually fixed the stale reads

Chunking killed the eleven minute stalls. It did not close the two hundred millisecond window the ticket was about. That one needs an application fix, and the honest version is: after a user writes, do not read it back from a replica until you know the write landed.

-- source, same connection, right after the commit
SELECT @@global.gtid_executed;

-- replica, before the dependent read
SELECT WAIT_FOR_EXECUTED_GTID_SET('3e11fa47-71ca-11e1-9e33-c80aa9429562:1-184203', 1);

Returns 0 when the set has been applied, 1 on timeout, NULL on error. Position based setups have SOURCE_POS_WAIT('mysql-bin.000418', 918273, 1), which returns the event count waited or -1 on timeout. Both replace the MASTER_* names.

In the admin panel we did something cruder: for thirty seconds after a write, that user's session reads from the source. Sticky routing off a cookie. Ugly, cheap, and it worked.

One thing I would not lean on: semisynchronous replication. The ack happens when a replica receives the event, not when it applies it, so a row can be acknowledged and still invisible to a read.

The queries from this post are in my Snippet Ark under "replication". If your lag hunt turns out to be a query problem instead, the slow query workflow covers that half.