Why MySQL Replication Lags and How to Eliminate It

Background

A bulk‑delete operation caused noticeable master‑slave replication lag, prompting an investigation into the root causes of MySQL replication delay and how to mitigate it.

Typical Master‑Slave Topologies

When a single MySQL instance becomes a bottleneck, a read‑write split architecture is adopted: the primary (master) handles all writes, while one or more replicas (slaves) serve read traffic. Various deployment patterns exist, but the core principle remains the same – asynchronous propagation of write events from master to slaves.

Replication Mechanics

MySQL replication relies on two log files:

Binary log (binlog) on the master – records every data‑modifying statement in the order it is executed.

Relay log on each slave – a local copy of the master’s binlog received by the slave’s I/O thread.

The replication flow is:

The master writes events to the binlog.

A dedicated binlog‑dump thread streams new binlog entries to each connected slave.

On the slave, the I/O thread receives the stream and writes it to the relay log.

The SQL thread reads the relay log sequentially and replays the events on the slave’s data files.

Key diagnostic command: SHOW SLAVE STATUS\G Important fields include Seconds_Behind_Master, Exec_Master_Log_Pos, and Relay_Log_Space.

Root Causes of Replication Lag

Random (non‑sequential) replay : The master’s binlog is written sequentially, which is fast on disk. The slave’s SQL thread, however, applies changes as random writes, which are slower. If the SQL thread cannot keep up, the relay log grows and lag appears.

High write concurrency on the master : A burst of write statements generates many binlog events in a short period. Because the slave’s SQL thread is single‑threaded (pre‑5.6), it may become a bottleneck.

Lock waits on the slave : If a long‑running query or transaction holds locks on the replica, the SQL thread blocks until the lock is released, extending the delay.

Mitigation Strategies

1. Parallel Replication (MySQL 5.6+)

MySQL 5.6 introduced multi‑worker replication, allowing the SQL thread to be split into several workers that replay transactions concurrently.

Configuration example:

SET GLOBAL slave_parallel_workers = 4;  -- number of workers
SET GLOBAL binlog_transaction_dependency_tracking = WRITESET;  -- enable transaction dependency tracking
START SLAVE;

For GTID‑based replication, you can also set slave_parallel_type = LOGICAL_CLOCK to improve ordering guarantees.

2. Reduce Master Write Load (All Versions)

Throttle incoming write traffic using application‑level rate limiting.

Offload read‑heavy workloads to a cache layer such as Redis or Memcached.

Batch large DML operations (e.g., bulk deletes) and introduce short sleeps between batches to give the replica time to catch up.

3. Read from the Master for Latency‑Sensitive Queries

If a query requires up‑to‑the‑second data, direct it to the primary instead of a replica. This avoids stale reads caused by lag.

4. Tune InnoDB and Binlog Settings

Set innodb_flush_log_at_trx_commit = 2 on the replica to reduce disk‑sync overhead during replay.

Use sync_binlog = 1 on the master to guarantee binlog durability without excessive fsync latency.

5. Monitoring and Alerting

Regularly poll Seconds_Behind_Master and set alerts when it exceeds an acceptable threshold (e.g., 5 seconds). Tools such as pt-heartbeat can provide sub‑second latency measurements.

Summary

Replication uses the master’s binlog and the slave’s relay log; the master streams binlog entries, the slave’s I/O thread writes them to the relay log, and the SQL thread replays them.

Lag originates from random‑write replay, high write concurrency on the master, and lock‑wait situations on the slave.

Mitigation includes enabling parallel replication (MySQL 5.6+), throttling master writes, reading from the master for real‑time data, and tuning InnoDB/ binlog parameters.