MySQL — Replication Lag: Seconds_Behind_Master Keeps Growing

Diagnose growing MySQL replication lag with SHOW REPLICA STATUS and performance_schema, separate IO from SQL thread stalls, and fix the bottleneck side.

intermediatedebuggingmysqlreplicationperformance

Symptoms

Seconds_Behind_Master climbs from zero into minutes or hours and never catches up during business hours
Reads against replicas return stale data: writes visible on the primary are missing for minutes
Replica_IO_Running and Replica_SQL_Running are both Yes — the threads are healthy but lagging

Possible causes

Single-threaded applier cannot keep up with parallel writes on the primary (binlog group commit vs one SQL thread)
Large transactions on the primary stall the replica applier for their whole duration
Replica disk IO saturation under mixed read traffic plus apply load
Primary writes come in bursts (nightly jobs) that outpace steady-state apply rate

Diagnosis steps

Read the replica status in full

mysql -e "SHOW REPLICA STATUS\G" | grep -E "Running|Behind|Master_Log|Read_Master|Exec_Master|Seconds|GTID|Retrieved|Executed"

Interpretation

Compare Retrieved versus Executed positions: both advancing together means apply-bound; Retrieved far ahead means the SQL thread is the bottleneck.

Confirm the applier thread state

mysql -e "SHOW PROCESSLIST;" 2>/dev/null | grep -i "system user"

Interpretation

The system-user applier row shows what it is doing: Reading event from the log (network side) versus applying (execution side).

Check replica disk pressure

mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_data_pending%';" && iostat -x 2 3

Interpretation

Pending IO or a saturated disk during apply windows means IO contention, not applier parallelism.

Root cause

Replication lag is a throughput mismatch: the primary applies writes with full parallelism while the replica applies them with limited applier parallelism or saturated IO. When sustained write rate exceeds apply rate, the executed position falls behind and Seconds_Behind_Master grows without bound.

Fix

Enable and tune parallel replication (replica_parallel_workers, LOGICAL_CLOCK or writeset-based scheduling), break large transactions into bounded batches on the writer, move heavy replica reads away or scale IO, and alert on lag trend rather than absolute spikes.

Risk note

Raising parallelism helps only with schedulable workloads; DDL and foreign keys serialize the applier regardless. Fix write shapes too.

Verify the fix

Under the same write load, lag peaks and returns to zero within a bounded time; the trend alert stays quiet for a week of business hours.

Symptom walkthrough

The one-line status hides a two-sided story:

mysql> SHOW REPLICA STATUS\G
     Replica_IO_Running: Yes
    Replica_SQL_Running: Yes
Seconds_Behind_Master: 1873
     Retrieved_Gtid_Set: 3e11...:1-98421132
     Executed_Gtid_Set:  3e11...:1-98214355

Retrieved minus Executed is over two hundred thousand transactions sitting in the relay log — the IO thread is fine; the applier is the bottleneck. Both threads show Yes, which is why health checks pass while users see stale data. When Retrieved is barely ahead of Executed instead, the network or the primary is slow to deliver.

Fix in practice

Enable parallel apply with the modern scheduler:

# my.cnf on the replica
replica_parallel_workers = 8
replica_parallel_type = LOGICAL_CLOCK
binlog_transaction_dependency_tracking = WRITESET

Fix the write shape on the primary — the applier can only replay what it gets:

# one 200k-row transaction stalls apply for its whole duration;
# bounded batches let the parallel applier interleave:
for batch in chunked(rows, size=5000):
    conn.begin()
    upsert(conn, batch)
    conn.commit()

And protect the replica IO budget: move analytical reads to another replica or a delayed follower, and check InnoDB pending IO during apply windows before blaming parallelism.