Why is my MySQL replica lagging behind the primary?

A user saves their profile, the page reloads, and the old data is still there. The write went to the primary; the read came from a replica that's 40 seconds behind. Or a lag alert fires every night at 2 a.m. and clears itself by 4.

Why does a MySQL replica fall behind, and how do you make it keep up?

How replication applies changes

The source writes every committed transaction to its binary log. On the replica, one thread (the receiver, or I/O thread) copies binlog events into the local relay log, and the applier (SQL thread or worker threads) executes them.

Lag almost always comes from the applier. The source may commit transactions from 200 connections in parallel, and the replica has to replay that work. Historically it did so on a single thread.

Measure it properly

SHOW REPLICA STATUS\G     -- MySQL 8.0.22+ (SHOW SLAVE STATUS on older versions)

Key fields:

• Seconds_Behind_Source: lag estimate. It's based on event timestamps, so it can read 0 while the receiver itself is behind, and it jumps around during long transactions.
• Replica_IO_Running / Replica_SQL_Running: both must be Yes.
• Retrieved_Gtid_Set vs Executed_Gtid_Set: received vs applied.

For accurate lag, write a timestamp row on the source every second and compare it on the replica (pt-heartbeat does this). In 8.0 you can also look at per-worker detail in performance_schema.replication_applier_status_by_worker.

Cause #1: single-threaded apply

Turn on multi-threaded replication. It's on by default from 8.0.27, but check older configs:

# replica
replica_parallel_workers      = 8          # 0/1 = single-threaded
replica_parallel_type         = LOGICAL_CLOCK
replica_preserve_commit_order = ON

# source (MySQL 8.0; 8.4 always uses WRITESET)
binlog_transaction_dependency_tracking = WRITESET

With WRITESET, the source records which transactions touched disjoint rows, so the replica can apply them in parallel even if they committed at different times. That's usually the biggest single improvement.

Cause #2: tables without a primary key

This is the most damaging cause and people rarely suspect it. With row-based replication (the default), an UPDATE or DELETE of 10,000 rows is replicated as 10,000 row events. On the replica, each event has to find its row. With a primary key that's an index lookup. Without one, it's a full table scan per row. A single DELETE that took 2 seconds on the source can take hours to apply.

SELECT t.table_schema, t.table_name
FROM information_schema.tables t
LEFT JOIN information_schema.table_constraints c
  ON c.table_schema = t.table_schema AND c.table_name = t.table_name
 AND c.constraint_type = 'PRIMARY KEY'
WHERE t.table_type = 'BASE TABLE' AND c.constraint_name IS NULL
  AND t.table_schema NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema');

Add primary keys everywhere. MySQL 8.0.30+ can add an invisible one for you (sql_generate_invisible_primary_key), and sql_require_primary_key blocks new tables without one.

Cause #3: huge transactions

A 5-million-row DELETE is one transaction. The replica can't start it until the source has committed it, and it can't parallelize it. That's the 2 a.m. spike. Batch it:

DELETE FROM events WHERE created_at < NOW() - INTERVAL 90 DAY LIMIT 10000;
-- repeat, with a short sleep between batches

Long DDL is similar: an ALTER TABLE that rebuilds a table for 30 minutes on the source then blocks the replica's applier for another 30. Use ALGORITHM=INSTANT where supported, or an online schema change tool (gh-ost, pt-online-schema-change) that copies in small batches.

Cause #4: the replica is weaker or busier

Replicas often get smaller instances, slower disks, or heavy reporting queries that compete for I/O and lock rows the applier needs. Give the replica comparable I/O. If it can be rebuilt from the source, you can relax durability on the replica only:

innodb_flush_log_at_trx_commit = 2
sync_binlog                    = 0

Don't do this on a replica you plan to promote without understanding the risk to crash safety.

Living with lag in the application

Some lag is unavoidable with asynchronous replication, so design for it:

• Read your own writes from the primary for a short window after a user writes (a cookie or session flag).
• Route reads with a max staleness: skip replicas whose lag is above a threshold (ProxySQL and MySQL Router can do this).
• For read-after-write on a replica, wait for the GTID: SELECT WAIT_FOR_EXECUTED_GTID_SET('<gtid>', 1);

Checklist

1. Confirm both threads are running and measure lag with a heartbeat.
2. Enable parallel apply with LOGICAL_CLOCK and WRITESET.
3. Put a primary key on every table.
4. Batch large DML and use online DDL.
5. Design reads so the app tolerates a few seconds of lag.