MySQL Replication Lag: How to Diagnose and Fix It
When a replica falls behind the source, Seconds_Behind_Source climbs and reads go stale. The cause is usually one of four things. Here's how to tell which — and fix it.
Step 1 — Measure the lag
SHOW REPLICA STATUS\G(On older versions, SHOW SLAVE STATUS\G.) Check three fields: Replica_IO_Running and Replica_SQL_Running should both be Yes, and Seconds_Behind_Source is your lag. If the IO thread is fine but lag grows, the apply side is the bottleneck.
Step 2 — Identify the cause
- Single-threaded apply — by default the SQL thread replays serially. A write-heavy source easily outpaces one apply thread.
- Long / large transactions — one big
UPDATEor bulk load blocks everything behind it. Check the source's binlog andSHOW PROCESSLISTon the replica. - Missing indexes on the replica — row-based events do primary-key/row lookups; a table without a proper key turns each change into a full scan.
- Disk / IO — a slower replica disk or saturated IO can't keep up with the relay log.
Step 3 — The fixes
- Enable parallel replication: set
replica_parallel_workers(>0) andreplica_parallel_type = LOGICAL_CLOCKso independent transactions apply concurrently. - Add the missing keys so row-based apply uses an index, not a scan.
- Break up big writes on the source into smaller batches.
- Match hardware — give the replica disk/IO comparable to the source.
After changes, watch Seconds_Behind_Source trend back toward zero.
How Tech Matrix solves this in ~60 seconds
Replication lag has a dozen causes and the slow part is correlating replica status with what's actually running. Tech Matrix reads SHOW REPLICA STATUS, the running threads, and the schema, then tells you whether it's single-threaded apply, a long transaction, missing indexes, or disk — grounded in your MySQL version and config.
Frequently asked questions
Run 'SHOW REPLICA STATUS\G' (or 'SHOW SLAVE STATUS\G') and read Seconds_Behind_Source, with Replica_IO_Running and Replica_SQL_Running both Yes.
Usually single-threaded apply on a write-heavy source, a long/large transaction, a missing index causing full scans on row-based events, or slow replica disk.
Enable parallel replication (replica_parallel_workers with LOGICAL_CLOCK), add missing indexes, split large transactions, and ensure the replica's storage matches the source.