MySQL Replication Lag: Why Your Replica Falls Behind and How to Fix It

MySQL replication lag rarely announces itself. First a report shows yesterday's numbers. Then a user updates their profile and sees the old one on the next page. Then someone asks whether the replica is safe to fail over to, and nobody wants to answer. A lagging replica is not just a performance problem: it is stale reads today and lost transactions on the day you have to promote it. Here is how to find where the lag comes from and what actually fixes it.

Step one: which half of replication is slow?

A MySQL replica has two jobs. The receiver (I/O) thread downloads binary log events from the source and writes them to the relay log. The applier (SQL) thread, or a pool of worker threads, executes those events. Lag can come from either side, and the fixes are completely different. Start with:

SHOW REPLICA STATUS\G

The fields worth reading first:

• Replica_IO_Running and Replica_SQL_Running: both should say Yes. If one says No, you don't have lag, you have broken replication. Read Last_IO_Error and Last_SQL_Error.
• Retrieved_Gtid_Set vs Executed_Gtid_Set (with GTIDs): if the replica has received far more than it has executed, the applier is the bottleneck.
• Relay_Log_Space: a relay log that keeps growing also means events arrive faster than they are applied.

If the received position is close to the source and the relay log is small, but the replica is still behind, look at the receiver side: network bandwidth, latency between regions, or a source that is struggling to write and send its binary log.

Don't trust Seconds_Behind_Source blindly

Seconds_Behind_Source is the number everyone graphs, and it is often misleading. The MySQL documentation itself warns that a value of 0 usually means the replica has caught up, but not always. A broken network connection may go unnoticed until replica_net_timeout passes, so the replica happily reports 0 while receiving nothing. And when the receiver is queuing events with old timestamps, the value can jump back and forth between 0 and a large number.

For alerting, measure lag directly. The classic approach is a heartbeat: a job on the source updates a timestamp in a small table every second, and you compare it with the clock on the replica. Percona Toolkit's pt-heartbeat does exactly that. In MySQL 8 you can also read per-worker timestamps from Performance Schema:

SELECT WORKER_ID,
       LAST_APPLIED_TRANSACTION,
       TIMEDIFF(LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP,
                LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP) AS apply_delay
FROM performance_schema.replication_applier_status_by_worker;

Alert on both things: lag above your tolerance and replication threads that are not running. Lag belongs next to the other early-warning signals we describe in database metrics that predict incidents.

The usual suspects

1. One huge transaction

A DELETE of 20 million old rows takes, say, 15 minutes on the source. The replica gets the whole transaction only after the source commits it, and then spends its own 15 minutes or more applying it. Everything behind it waits. Lag graphs that look like a sawtooth after nightly jobs almost always have this cause.

Fix: break bulk changes into chunks of a few thousand rows with a short pause between them. Purges, backfills and data fixes should all be written this way. It is slower on paper and much faster for everyone else.

2. Tables without a primary key

With row-based replication, the replica must find each changed row. Without a primary key or a suitable index it may scan the table, and in the worst case it scans it once per changed row. One UPDATE touching 10,000 rows on the source can become 10,000 table scans on the replica. This is a long-known problem (MySQL bug #53375).

Fix: give every InnoDB table a primary key. Find the offenders with a query on information_schema, and turn on sql_require_primary_key so new ones can't appear. If you can't change the application, MySQL can add a generated invisible primary key: sql_generate_invisible_primary_key on the source for new tables, or REQUIRE_TABLE_PRIMARY_KEY_CHECK = GENERATE on the replica's channel (MySQL docs on GIPKs; Percona describes the invisible-column approach here).

3. The applier can't run in parallel

In MySQL 8.4 replicas are multithreaded by default: replica_parallel_workers is 4 and replica_preserve_commit_order is ON (replica options). Older configs often still have a single applier thread, so check what you actually run. More workers only help if transactions are independent. If every transaction updates the same counter row or the same hot table, they have to be applied one after another no matter how many workers you add. Watch whether workers are busy or mostly waiting before raising the number.

4. The replica is weaker than the source

"It's just a replica" is how replicas end up on half the CPU and slower disks. But a replica has to do all the writes the source does, plus whatever reads you send it. Give it comparable storage, the same buffer pool size and the same important InnoDB settings. Some teams relax durability on replicas (innodb_flush_log_at_trx_commit, sync_binlog) to speed up applying. That can help, but understand the trade-off: after a crash that replica may need rebuilding, and it is a poor failover candidate.

5. Things running on the replica

• Reporting queries and backups compete with the applier for I/O and CPU, and long transactions on the replica can hold back purge.
• Big DDL. An ALTER TABLE that ran online on the source is applied on the replica as one statement, and transactions behind it wait until it finishes. Online schema change tools that copy in chunks avoid that long stall.

6. The network

Cross-region replicas over a thin link fall behind during write bursts. Binary log transaction compression (binlog_transaction_compression, available since MySQL 8.0.20) can cut the volume considerably for row-based workloads at some CPU cost on both sides.

A quick diagnostic order

1. Both threads running? If not, read the errors before doing anything else.
2. Receiver or applier? Compare received and executed GTID sets and watch the relay log.
3. What is the applier doing right now? Look at SHOW PROCESSLIST and the Performance Schema applier tables. One long-running event points to a big transaction or a table scan.
4. Check the table it is stuck on for a primary key.
5. Compare replica hardware and config with the source.
6. Only then tune parallel workers.