Seconds Behind Master Keeps Climbing? Check Slave Threads First

You're watching SHOW SLAVE STATUS\G and that Seconds_Behind_Master number just keeps climbing. It's frustrating because nothing else seems broken. The slave is running, the master is fine, but lag grows like a slow leak. Here's the real fix.

The One Fix That Works in 80% of Cases

Stop the slave, enable parallel replication, restart. That's it. MySQL 5.7+ and MariaDB 10.0+ support multi-threaded replication on the slave. By default, they run with a single SQL thread, which is your bottleneck.

-- On the slave:
STOP SLAVE;
SET GLOBAL slave_parallel_workers = 4;
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';  -- MySQL 5.7+
START SLAVE;

Watch Seconds_Behind_Master drop like a stone. If your slave has enough CPU cores (4+), this is often the entire fix. The reason: one thread can't keep up with the master's writes, especially under heavy write load. Parallel workers let the slave apply transactions concurrently when they're safe to apply — i.e., they don't conflict on row locks.

Why This Works

What's actually happening here is a queue problem. The slave I/O thread fetches relay logs from the master just fine — SHOW SLAVE STATUS will show Slave_IO_Running: Yes and Master_Log_File advancing. But the SQL thread replaying those logs is stuck in a single-threaded loop: read one event, apply it, commit, repeat. If the master is doing 10,000 writes per second, the slave's single thread can maybe do 2,000. The backlog grows.

slave_parallel_type = 'LOGICAL_CLOCK' is the key. It tells MySQL to group commits from the master that happened at the same logical time (same clock tick) and apply them in parallel on the slave. They're safe to apply concurrently because the master already resolved any row conflicts within that group. MariaDB uses slave_parallel_threads without the LOGICAL_CLOCK distinction — it just makes groups based on database or table, which works but is less fine-grained.

When Parallel Threads Aren't Enough

If you set parallel workers and lag still grows, the bottleneck has moved to disk I/O. Check SHOW SLAVE STATUS\G again. Look at Relay_Log_Space — if it's in the gigabytes, your SQL thread can't write relay logs to disk fast enough. Here's the fix for that:

-- On the slave (requires restart):
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
SET GLOBAL sync_binlog = 0;

This is safe on a slave because you don't need crash-safe durability on the replica — if it crashes, you can re-clone from the master. innodb_flush_log_at_trx_commit = 2 flushes InnoDB logs every second instead of on every commit. sync_binlog = 0 lets the OS buffer binary log writes. Combined, they cut disk write frequency by orders of magnitude.

I've seen a slave with spinning disks (7200 RPM) go from 30,000 seconds behind to zero in 10 minutes after these two settings. The tradeoff: if the slave crashes, you might lose those last second of transactions. But you can always rebuild from the master. Acceptable for most setups.

Less Common Variants

1. Network Lag on the I/O Thread

Sometimes the I/O thread itself falls behind. Check Master_Log_File vs Relay_Master_Log_File. If they're not matching, the slave can't fetch fast enough. This is rare with 1 Gbps links, but common over VPN or cross-region connections. The fix: compress binlog traffic with slave_compressed_protocol = 1, or move the slave closer to the master.

2. Long-Running Queries on the Slave

If the slave runs reporting queries that lock tables (e.g., SELECT ... FOR UPDATE or ALTER TABLE), they block the SQL thread. Check SHOW PROCESSLIST on the slave for queries with State: Updating or State: System lock that last longer than a second. Kill them or schedule them during low-write periods. The SQL thread won't skip them — it waits.

3. MyISAM Tables on the Slave

If your master writes to MyISAM tables, the slave's SQL thread serializes all writes to those tables. MyISAM has table-level locking, so parallel threads can't help. The fix: convert those tables to InnoDB on both sides. ALTER TABLE t ENGINE=InnoDB;. Do it on the master first, then let replication update the slave.

Prevention

1. Set parallel replication at setup. Don't wait for lag. Default slave_parallel_workers = 4 from day one on any slave with 4+ cores.
2. Use SSD on slaves. If you're budget-constrained, at least put the relay log directory on fast storage. You can move it with relay_log_index and relay_log config.
3. Monitor Seconds_Behind_Master with alerting. Set a threshold at 60 seconds. If it crosses, investigate before it snowballs. The lag grows non-linearly under sustained write load — it can go from 10 seconds to 10,000 seconds in an hour.
4. Test your failover regularly. A slave that's 2 hours behind is useless for high-availability failover. You'll lose that much data. Run STOP SLAVE; RESET SLAVE ALL; CHANGE MASTER TO ... to re-clone it before it gets too far behind.

The bottom line: seconds behind master increasing is nearly always a single-threaded bottleneck. You fix it with parallel workers, then tune disk if needed. Don't waste time tweaking buffer pools or query caches — those won't help the SQL thread go faster.