MySQL

MySQL Replication Lag, How to Diagnose

Replication lag means the replica database is falling behind the source, applying changes later than they actually happened — diagnosing it correctly means checking a specific set of replica status fields rather than just observing that reads from the replica seem stale.

The Problem

Data read from a replica database doesn't reflect recent writes made to the source, and the delay seems to grow over time rather than staying constant or catching up.

Replication lag Source (writes) Replica behind by Xs binlog stream

Why It Happens

Replication lag accumulates when the replica can't apply changes as fast as the source generates them. Common causes:

  • Traditional MySQL replication applies changes on the replica using a single thread by default, so a source handling many concurrent writes across multiple connections can outpace a replica applying them one at a time sequentially
  • A single large, slow-running write on the source (a bulk update or a large delete) blocks the replication stream for that entire operation's duration before the replica can move on to subsequent changes
  • The replica server itself is under-provisioned relative to the source — less CPU, slower disk I/O, or less memory — making it inherently slower at applying the same volume of changes
  • Network latency or bandwidth constraints between source and replica slow the transfer of binary log data itself, independent of how fast the replica could apply changes once received

The Fix

First, check the replica's actual reported lag directly, rather than inferring it indirectly from stale-looking data:

SHOW REPLICA STATUS\G

(On older MySQL versions, this is SHOW SLAVE STATUS\G.) Look specifically at Seconds_Behind_Source (or Seconds_Behind_Master on older versions), which reports the replica's own measurement of how far behind it currently is.

Also check Replica_IO_Running and Replica_SQL_Running (both should show Yes) — if either shows No, replication has actually stopped entirely rather than just lagging, which is a different and more urgent problem requiring investigation of the specific error reported in the same status output.

If replication is running but consistently behind, and you're using default single-threaded replication, consider enabling multi-threaded replication, which allows the replica to apply changes from different databases (or, with row-based replication and appropriate settings, different transactions) in parallel rather than strictly sequentially:

SET GLOBAL replica_parallel_workers = 4;

The appropriate number of parallel workers depends on your specific workload and how well it parallelizes — changes affecting different, unrelated tables parallelize well, while changes that depend on strict ordering within the same table don't benefit as much from this setting.

Identify whether specific slow queries on the source are causing lag spikes by correlating slow query logs on the source with periods of increased replication lag on the replica:

SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 20;

A single very slow, large write on the source translates directly into a delay in the replica catching up, since that same operation has to be replayed there too, and can't be parallelized away if it's a single large statement rather than many smaller ones.

Confirm the replica's own hardware resources aren't simply under-provisioned relative to what it needs to keep pace with the source's write volume — monitoring the replica's CPU, disk I/O, and memory usage during periods of lag clarifies whether it's a genuine hardware bottleneck rather than a replication configuration issue.

Still Not Working?

If lag persists despite addressing parallelism and slow queries, consider whether your replication topology itself needs reconsidering for the actual write volume involved — for very high-throughput systems, alternatives like semi-synchronous replication (which has different tradeoffs around consistency and latency) or restructuring large batch operations on the source to run in smaller, more frequent chunks (rather than one large operation) can meaningfully reduce the peak lag spikes that a single large write otherwise causes, even if average lag under normal conditions is already acceptable.

You might also like