How to Diagnose and Fix MySQL Master‑Slave Replication Lag

What is replication lag?

Replication lag is the time difference between the master writing a binlog entry and the replica replaying that entry, causing temporary data inconsistency between master and replica.

Why does lag occur?

Replication mechanism

MySQL replication relies on the binary log (binlog) that records all changes. The process is asynchronous:

Master writes binlog : update/insert/delete statements are recorded.

Master sends binlog : a dump thread streams the binlog to replicas.

Replica writes relay log : the I/O thread fetches the binlog and stores it in a relay log.

Replica replays : the SQL thread reads the relay log and executes the statements.

Causes of lag

Replication is single‑threaded on the replica; the SQL thread must execute DDL/DML whose I/O is random and costly.

Lock contention from other queries on the replica can block the SQL thread.

A long‑running DDL (e.g., 10 minutes) blocks the SQL thread, causing all subsequent statements to wait.

High master TPS combined with many DDL statements exceeds the capacity of a single replica SQL thread.

Large queries on the replica can create lock waits during the relay‑log replay stage.

Scenarios that lead to lag

Replica hardware : weaker hardware than the master reduces replay speed.

Replica load : high read/write load on the replica increases contention.

Too many replicas : excessive number of replicas can amplify replication traffic; a typical range is 3‑5 replicas.

Large transactions : a transaction that runs for minutes delays the replica for the same duration; large DELETE batches or massive DDL on big tables are common culprits.

Network latency : limited bandwidth (e.g., 20 M) slows binlog transfer; upgrading to higher bandwidth (e.g., 100 M) reduces delay.

Old MySQL version : versions that support only single‑threaded replication cannot keep up with high‑concurrency workloads.

Practical solutions

Upgrade replica hardware to match or exceed the master.

Split large transactions into smaller batches to avoid long‑running replay.

Deploy multiple replicas (one‑master‑many‑slaves) to distribute read load.

Improve network bandwidth to reduce binlog transfer time.

Upgrade to a newer MySQL version that supports multi‑threaded binlog replication.

Business‑level considerations:

Use caching (e.g., Redis) to serve reads while writes are still propagated to the database; note the potential consistency challenges.

Read from the master when necessary (e.g., order payment) to guarantee freshness, accepting the added load on the master.

Configuration tweaks can further reduce replica lag:

sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

These settings give the master strong durability. On replicas, durability can be relaxed to improve speed, for example:

sync_binlog = 0   // or disable binlog
innodb_flush_log_at_trx_commit = 0

Architectural suggestion: use several replicas to share read traffic, and designate one replica solely for backup with relaxed durability settings (e.g., sync_binlog = 0), thereby boosting query performance on the remaining replicas.