✏️ Explanatory Question
Level: Very Hard — A real production emergency; stale replicas serve outdated data and break read-after-write expectations.
Seconds_Behind_Master climbs to 10,800 (3 hours). Users update their profile, refresh, and see the old data (because the read hit the lagging replica). What causes replication lag, and how do you fix it?
-- Check how far behind the replica is
SHOW REPLICA STATUS\G
-- Key fields:
-- Seconds_Behind_Master: 10800 <- 3 hours behind
-- Replica_IO_Running: Yes <- receiving binlog OK
-- Replica_SQL_Running: Yes <- applying, but too slowly
-- Retrieved_Gtid_Set vs Executed_Gtid_Set -> gap = backlog| Cause | Why It Lags | Fix |
|---|---|---|
| Single-threaded apply | Replica applies changes on 1 thread while the source writes in parallel | Enable parallel replication workers |
| Long/huge transactions | A 5M-row UPDATE replicates as one big serial op | Batch large writes into small chunks |
| Missing index on replica | Row-based updates do full scans to find rows | Ensure identical indexes; use PKs |
| Weak replica hardware | Slower disk/CPU than the source | Match or exceed source specs |
| Hot single-row updates | Serialized on the replica | Redesign the write hotspot |
-- On the replica: allow multiple apply threads
STOP REPLICA SQL_THREAD;
SET GLOBAL replica_parallel_workers = 8; -- e.g., 8 threads
SET GLOBAL replica_parallel_type = 'LOGICAL_CLOCK'; -- parallelize by commit group
SET GLOBAL replica_preserve_commit_order = ON; -- keep commit order safe
START REPLICA SQL_THREAD;
-- Also set binlog_transaction_dependency_tracking = WRITESET on the SOURCE
-- so more transactions can be applied in parallel-- BAD on source: one 5M-row delete replicates as one serial op -> lag spike
DELETE FROM logs WHERE created_at < '2025-01-01';
-- GOOD: chunked deletes keep each replicated transaction small
DELETE FROM logs WHERE created_at < '2025-01-01' ORDER BY id LIMIT 5000;
-- repeat in a loop; the replica keeps up between small batchesSeconds_Behind_Master exceeds a threshold.WAIT_FOR_EXECUTED_GTID_SET() to block a read until the replica has caught up to the write.-- After writing on the source, capture the GTID, then on the replica:
SELECT WAIT_FOR_EXECUTED_GTID_SET('the-write-gtid', 2); -- wait up to 2s
-- Returns when the replica has applied that exact write -> safe to read it there
SELECT * FROM users WHERE id = 42;Seconds_Behind_Master: It can read 0 then jump, or show 0 while a huge transaction is mid-apply. For accurate monitoring, compare GTID sets (Retrieved vs Executed) or use a heartbeat table with timestamps written by the source.
LOGICAL_CLOCK can't parallelize transactions that conflict on the same rows. Fix the write hotspot (sharded counters, append-only design) so transactions become independent and parallelizable.