Analysis of MySQL Master‑Slave Replication Delay and Mitigation Strategies

The author documents a real‑world incident that occurred on the eve of a major JD.com promotion, where an online service raised an alarm due to a null‑pointer exception caused by an empty database query. Initial investigation pointed to master‑slave replication delay, which lasted a few seconds but recurred multiple times over subsequent months, worsening on the first day of the 11.11 campaign.

Further diagnosis revealed that the slave’s SQL thread was processing a large number of delete statements on a table without an index, while the master already had an appropriate index. This mismatch caused the slave to fall behind, resulting in noticeable replication lag.

The article then outlines the fundamentals of MySQL master‑slave replication, which involves three threads:

Master (binlog dump thread) : writes data‑change events to the binary log and notifies the slave.

Slave I/O thread : connects to the master, requests binlog data, and stores it in the relay log.

Slave SQL thread : reads the relay log and re‑executes the events to keep the slave in sync with the master.

The replication process is illustrated by an accompanying diagram (image omitted for brevity).

Combining the replication theory with the observed symptoms, the root cause is identified as the slave’s SQL thread executing slow statements sequentially; any poorly performing operation inflates replication latency.

To mitigate the issue, the team added the missing index on the slave, which immediately reduced the lag and made the impact on the online service negligible.

Additional recommendations for lowering master‑slave delay include:

Introduce a caching layer (e.g., Memcached or Redis) between the application and MySQL to reduce read pressure.

Deploy higher‑performance hardware for the slave.

Set sync_binlog=0 on the slave.

Use the --log‑slave‑updates option to prevent the slave from writing received updates to its own binary log.

Disable binary logging on the slave when it is not needed.

JD.com’s architecture typically employs one master and eight slaves; high replication lag directly affects read‑heavy services across all business lines. By addressing indexing and employing the above strategies, the system’s stability and performance can be significantly improved.