Common Causes and Fix Steps for MySQL Master‑Slave Replication Lag

Problem Background

MySQL master‑slave replication is a core high‑availability solution for read/write separation, backups, and disaster recovery. In production, replication lag is a frequent issue, ranging from a few seconds to several hours, causing data inconsistency, false alerts, business logic errors, and risky failovers.

Applicable Scenarios

MySQL 5.7 / 8.0 master‑slave environments

Asynchronous or semi‑synchronous replication

Single master‑single slave, one‑master‑multiple‑slaves, or cascaded replication

Physical machines, VMs, containers, or cloud RDS

GTID mode and traditional binlog position mode

Core Knowledge

Replication Threads

Binlog Dump Thread (master) : reads the binary log and sends it to the slave.

IO Thread (slave) : receives the binlog and writes it to the relay log.

SQL Thread (slave) : reads the relay log and executes the SQL statements.

Lag usually appears in the slave IO or SQL thread.

Key Metrics

Seconds_Behind_Master

: time difference between the last executed SQL on the slave and the current time. Slave_SQL_Running_State: current state of the SQL thread. Exec_Master_Log_Pos vs Read_Master_Log_Pos: progress gap between the SQL and IO threads. Relay_Log_Space: size of the relay log on the slave.

Overall Troubleshooting Flow

Detect lag → SHOW SLAVE STATUS →
If Seconds_Behind_Master > 5s, determine whether IO or SQL thread is slow →
If IO is slow: check network, master Binlog Dump thread, slave disk space →
If SQL is slow: check parallel replication, resource usage, lock waits, large transactions on master, heavy read queries on slave →
Apply the appropriate fix → Verify that lag decreases → Record root cause and solution.

Step‑by‑Step Procedure

Step 1: Confirm Replication Lag

SHOW SLAVE STATUS\G   -- MySQL 5.7
SHOW REPLICA STATUS\G   -- MySQL 8.0

Key fields to check: Slave_IO_Running and Slave_SQL_Running should be Yes. Seconds_Behind_Master > 5 indicates lag.

If either thread is No, the thread has stopped and must be restarted.

Step 2: Identify the Slow Component

SELECT Read_Master_Log_Pos, Exec_Master_Log_Pos, Relay_Log_Space
FROM performance_schema.replication_applier_status_by_worker;

Large gap between Read_Master_Log_Pos and Exec_Master_Log_Pos → SQL thread is slow.

Rapid growth of Relay_Log_Space while Read_Master_Log_Pos advances slowly → IO thread fast, SQL thread cannot keep up.

Slow increase of Read_Master_Log_Pos → IO thread slow, possibly network or master‑side issue.

Step 3: Check Master Write Load

SHOW GLOBAL STATUS LIKE 'Com_insert';
SHOW GLOBAL STATUS LIKE 'Com_update';
SHOW GLOBAL STATUS LIKE 'Com_delete';
SHOW GLOBAL STATUS LIKE 'Questions';

Record the values, wait 10 seconds, compute per‑second rate. High write traffic indicates the master may be generating binlog faster than the slave can consume.

Step 4: Examine Slave SQL Thread Execution

SHOW PROCESSLIST;

Find the system user threads and inspect the State column. Common states include:

Reading event from the relay log

Waiting for dependent transaction to commit (parallel replication)

Waiting for table metadata lock

Waiting for table level lock

Updating

If a thread stays in one state for a long time, that component is the bottleneck.

Step 5: Monitor Slave Resource Usage

# CPU usage per thread
 top -H -p $(pgrep mysqld)

# Memory usage
 free -h

# Disk I/O
 iostat -x 1

# Network traffic
 iftop -i eth0

High %CPU for the SQL thread, high %util or await for the disk, or saturated network indicate resource constraints.

Step 6: Detect Lock Waits

SELECT * FROM information_schema.INNODB_LOCK_WAITS;
SELECT * FROM performance_schema.data_lock_waits;

Long‑running lock waits often stem from user queries that hold locks.

Step 7: Look for Large Transactions on the Slave

SELECT * FROM information_schema.INNODB_TRX\G;
SELECT trx_mysql_thread_id FROM information_schema.INNODB_TRX WHERE trx_started < DATE_SUB(NOW(), INTERVAL 30 SECOND);
KILL <thread_id>;

Killing a blocking transaction can release the SQL thread, but verify with the application team before doing so.

Step 8: Verify Parallel Replication Settings

# MySQL 5.7
SHOW VARIABLES LIKE 'slave_parallel_type';
SHOW VARIABLES LIKE 'slave_parallel_workers';
# MySQL 8.0
SHOW VARIABLES LIKE 'replica_parallel_type';
SHOW VARIABLES LIKE 'replica_parallel_workers';

# Enable parallel replication (example for 4 workers)
STOP SLAVE;
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 4;
START SLAVE;

If slave_parallel_workers (or replica_parallel_workers) is 0, the slave runs single‑threaded replication.

Step 9: Test Network Latency

# Simple ping test
 ping -c 10 <master_ip>
# Capture packets for deeper analysis
 tcpdump -i eth0 host <master_ip> and port 3306 -w /tmp/mysql_repl.pcap
 wireshark /tmp/mysql_repl.pcap

High round‑trip time or packet loss points to network‑related lag.

Step 10: Check Master Binlog Format

SHOW VARIABLES LIKE 'binlog_format';

STATEMENT

: small binlog size but may cause inconsistency. ROW: large binlog size, safe for consistency, but can increase slave processing time. MIXED: automatic choice.

If the master uses ROW and runs massive batch DML, the slave may fall behind.

Common Root Causes (10)

Cause 1 – Master Write Load Too High

High TPS on the master, rapid binlog generation.

Both IO and SQL threads on the slave cannot keep up.

Solution: Optimize master SQL, shard tables, increase slave parallel workers, upgrade slave hardware.

Cause 2 – Slave Runs Single‑Threaded Replication

slave_parallel_workers = 0

(or replica_parallel_workers = 0).

SQL thread CPU usage high while master load is moderate.

Solution: Enable parallel replication, e.g.

STOP SLAVE;
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 4;
START SLAVE;

Recommended workers: 2‑4 for 2‑CPU, 4‑8 for 4‑CPU, 8‑16 for 8‑CPU machines.

Cause 3 – Slave Disk I/O Bottleneck

iostat

shows %util near 100 % or high await.

SQL thread slow.

Solution: Upgrade to SSD, tune MySQL parameters:

SET GLOBAL innodb_flush_log_at_trx_commit = 2;
SET GLOBAL sync_binlog = 0;

(Note: these settings reduce durability and should be used only on the slave.)

Cause 4 – Long Transactions or Lock Waits on Slave

SQL thread blocked by metadata or table locks.

Long‑running transactions in information_schema.INNODB_TRX.

Solution: Identify blocking transaction ID and kill it if safe:

SELECT * FROM information_schema.INNODB_TRX WHERE trx_started < DATE_SUB(NOW(), INTERVAL 30 SECOND);
KILL <thread_id>;

Risk: aborts user query; coordinate with the application team.

Cause 5 – Large Transactions on Master

Massive INSERT/UPDATE/DELETE on master.

Slave Seconds_Behind_Master spikes.

Solution: Split large transactions into smaller batches, use tools like pt-online-schema-change for DDL, pt-archiver for archival.

Cause 6 – ROW Binlog with Bulk Operations

Binlog format ROW with large batch updates/deletes.

Huge binlog files, both IO and SQL threads lag.

Solution: Short‑term – wait for catch‑up. Long‑term – reduce batch size, consider MIXED format, upgrade slave hardware.

Cause 7 – Network Latency or Packet Loss

High ping latency or packet loss.

Slave IO thread receives data slowly.

Solution: Check network devices, contact ISP or cloud provider, use dedicated lines or VPN, consider semi‑synchronous replication.

Cause 8 – Slave Hardware Under‑Provisioned

CPU, memory, or disk on the slave lower than the master.

Resource usage stays high, lag persists.

Solution: Upgrade slave hardware to match or exceed master specifications.

Cause 9 – Slave Serves Heavy Read Queries

High SELECT load on the slave, CPU and I/O pressure.

Solution: Limit read concurrency, optimize slow queries, add more slaves, use a read‑write splitting proxy.

Cause 10 – Sub‑optimal Slave Configuration

innodb_buffer_pool_size

set too small, frequent swapping.

Disk I/O pressure high.

Solution: Increase innodb_buffer_pool_size to 50‑70 % of physical RAM, e.g.

SET GLOBAL innodb_buffer_pool_size = 8589934592;  -- 8 GB

MySQL 8.0 supports dynamic adjustment; MySQL 5.7 requires restart.

Configuration Examples

Enable Parallel Replication on the Slave

# /etc/my.cnf (MySQL 5.7)
[mysqld]
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 8
slave_preserve_commit_order = ON

# MySQL 8.0
replica_parallel_type = LOGICAL_CLOCK
replica_parallel_workers = 8
replica_preserve_commit_order = ON

Restart MySQL or set dynamically:

STOP SLAVE;
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 8;
SET GLOBAL slave_preserve_commit_order = ON;
START SLAVE;

Adjust Disk Write Parameters (Slave Only)

SET GLOBAL innodb_flush_log_at_trx_commit = 2;
SET GLOBAL sync_binlog = 0;

Persist in /etc/my.cnf:

[mysqld]
innodb_flush_log_at_trx_commit = 2
sync_binlog = 0

Adjust InnoDB Buffer Pool

SET GLOBAL innodb_buffer_pool_size = 8589934592;  -- 8 GB

Persist in /etc/my.cnf:

[mysqld]
innodb_buffer_pool_size = 8G

Log and Metric Observation

Check Slave Replication Status

SHOW SLAVE STATUS\G

Seconds_Behind_Master

– lag in seconds. Slave_IO_Running / Slave_SQL_Running – thread status. Exec_Master_Log_Pos vs Read_Master_Log_Pos – execution progress. Relay_Log_Space – relay log size. Slave_SQL_Running_State – current SQL thread state.

Monitor Master Binlog Generation

ls -lh /var/lib/mysql/mysql-bin.* | tail -10

Monitor Slave Relay Log Size

du -sh /var/lib/mysql/relay-bin.*

Resource Monitoring Commands

# CPU per thread
 top -H -p $(pgrep mysqld)
# Memory
 free -h
# Disk I/O
 iostat -x 1
# Network
 iftop -i eth0

Lock Wait Inspection

SELECT * FROM information_schema.INNODB_LOCK_WAITS;
SELECT * FROM performance_schema.data_lock_waits;

Prometheus + Grafana Monitoring

# prometheus scrape config (yaml)
- job_name: 'mysql'
  static_configs:
  - targets: ['slave-host:9104']
  relabel_configs:
  - source_labels: ['__address__']
    target_label: instance

In Grafana, plot mysql_slave_status_seconds_behind_master and set an alert when it exceeds 30 seconds for 5 minutes.

Verification Methods

Run SHOW SLAVE STATUS\G every few seconds and watch Seconds_Behind_Master decrease.

Confirm multiple system user threads appear in SHOW PROCESSLIST after enabling parallel replication.

Use iostat -x 1 10 to verify that %util and await drop.

Run SELECT * FROM information_schema.INNODB_LOCK_WAITS; result should be empty after lock‑wait resolution.

Validate data consistency with pt-table-checksum and, if needed, fix with pt-table-sync.

Rollback Plans

Revert Parallel Replication

STOP SLAVE;
SET GLOBAL slave_parallel_workers = 0;
START SLAVE;

Restore Disk Write Parameters

SET GLOBAL innodb_flush_log_at_trx_commit = 1;
SET GLOBAL sync_binlog = 1;

Reset Buffer Pool Size

SET GLOBAL innodb_buffer_pool_size = <original_value>;

Rebuild Slave (if data inconsistency persists)

On the master, take a full backup:

mysqldump --single-transaction --master-data=2 --all-databases > backup.sql

Copy the dump to the slave and import:

scp backup.sql user@slave-host:/tmp/
mysql < /tmp/backup.sql

Extract binlog coordinates from the dump (search for CHANGE MASTER TO).

Configure replication on the slave with MASTER_AUTO_POSITION = 1 (GTID) or the extracted coordinates.

CHANGE MASTER TO MASTER_HOST='<master_ip>', MASTER_USER='repl', MASTER_PASSWORD='<password>', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=123456;
START SLAVE;

Verify replication status.

Production‑Grade Best Practices

Backup /etc/my.cnf before any parameter change.

Increase parallel workers gradually (2‑4 at a time) and monitor impact.

Apply disk‑write tuning only on slaves; never on the master.

Schedule regular pt-table-checksum runs (weekly or monthly).

Monitor lag with Prometheus/Grafana and set alerts.

Use semi‑synchronous replication to reduce data loss risk.

Prefer GTID mode for easier failover and consistency.

Keep the slave dedicated to replication; limit read‑heavy workloads.

Before any master‑slave switchover, ensure Seconds_Behind_Master = 0.

Summary

Replication lag is a multi‑factor problem that requires systematic diagnosis: verify the lag metric, pinpoint the slow thread, examine master write pressure, check slave resources, look for lock waits or large transactions, enable parallel replication, and address network or hardware bottlenecks. The most common root causes are excessive master writes, single‑threaded slaves, disk I/O limits, lock contention, large transactions, ROW binlog bulk operations, network latency, under‑provisioned slave hardware, heavy read traffic on the slave, and sub‑optimal configuration. Remedies include optimizing master SQL, scaling out or upgrading the slave, tuning InnoDB and binlog parameters, enabling parallel replication, using semi‑synchronous replication, and regularly verifying data consistency. Proper monitoring, cautious parameter changes, and documented rollback procedures ensure stable, low‑latency replication in production environments.