How to Minimize Replication Lag in MySQL: Practical Optimization Tips

Replication delay in MySQL is an unavoidable issue, but it can be minimized through careful optimization.

To optimize the replication process, we first examine its three main stages.

There are 3 critical time points:

1. Time for the primary server to write to the binary log

For example, a large transaction updating 30,000 rows may take three minutes; the binary log is written only after the transaction commits. Splitting such a transaction into smaller ones (e.g., 3,000 rows each) allows each sub‑transaction to be replicated sooner.

The goal is to accelerate log writing by keeping transactions short and frequent.

2. Binary log transmission time

Steps 2 and 3 in the diagram represent log transmission, which includes network transfer and disk write. In a typical LAN environment, network latency is negligible, and logs are written sequentially, so disk I/O is not a bottleneck.

The main optimization here is to reduce the amount of data transferred. Evaluate which databases truly need replication; unnecessary databases can be excluded using the binlog_ignore_db parameter.

3. Time for the slave server to replay SQL

By default, a single SQL thread replays the logs serially. MySQL 5.7 supports multi‑threaded replication, which can significantly speed up replay if enabled.

Refer to the earlier article on configuring multi‑threaded replication in MySQL 5.7 for detailed steps.