Fix Replication Lag Over Threshold Fast

Quick Answer

Run STOP SLAVE; START SLAVE; on the replica — but first, check SHOW PROCESSLIST; and kill any long-running SELECT that's blocking the relay log.

What's Actually Happening

Replication lag isn't always a network issue. More often than not, it's a single long-running query on the replica that's holding up the SQL thread. The I/O thread keeps downloading events from the master, but the SQL thread can't apply them fast enough. The lag value you see is the difference between where the I/O thread is and where the SQL thread is. If it's over your threshold (commonly 10 or 30 seconds), you get paged.

I've seen this on MySQL 5.7 and 8.0, also on MariaDB 10.3+. The culprit is almost always a SELECT with a missing index, a table scan on a huge table, or a lock contention from a long write transaction. Rarely is it the master being slow — the master can churn out binlogs faster than the replica can apply them.

Fix Steps

Check the lag:
```
SHOW SLAVE STATUS\G
```
Look at Seconds_Behind_Master. If it's growing, move to step 2.
Identify the blocking query:
```
SHOW FULL PROCESSLIST;
```
Find the query with State: Updating or System lock that's been running more than 10 seconds. Note its Id.
Kill it:
```
KILL <thread_id>;
```
This stops the query. The SQL thread will then pick up the next events. Lag should drop within seconds.
If lag persists, restart the SQL thread:
```
STOP SLAVE SQL_THREAD;
START SLAVE SQL_THREAD;
```
This clears any stuck state. Wait 10 seconds, then check Seconds_Behind_Master again.
Still lagging? Restart both threads:
```
STOP SLAVE;
START SLAVE;
```
This resets the I/O and SQL threads. It'll re-read the relay log from disk. Usually fixes transient corruption or a stuck state.

If That Doesn't Work

Check Disk I/O

Run iostat -x 1 on the replica. If %util is near 100%, you've got a disk bottleneck. Consider moving to SSDs or increasing innodb_io_capacity to 2000. But honestly, disk I/O lag is rare — only if you're on spinning rust or a cheap cloud instance.

Network Latency

Sometimes the master's network is trash. Check SHOW SLAVE STATUS for Master_Log_File and Read_Master_Log_Pos. If these aren't advancing, the I/O thread can't fetch binlogs. That's a network or master-side issue. Run ping and traceroute between hosts.

Replica Too Slow to Apply

If you have a massive write load on the master and the replica is undersized, you need more CPU or RAM. But don't jump to that first — 90% of my cases were a single bad query.

Prevention

Set slave_net_timeout to 10 seconds in my.cnf. This makes the replica notice a dead master quicker. Also, monitor long-running queries on the replica with pt-query-digest or slow_query_log. Add indexes to slow queries before they cause lag. Regular ANALYZE TABLE on heavily updated tables helps the optimizer pick better plans.

One more thing — don't set a low threshold like 1 second unless you enjoy getting paged every hour. 10 seconds is sane. 30 seconds is fine for batch workloads.