MySQL replication lag: finding the cause and fixing it

#1. Check the replica

SHOW REPLICA STATUS\G

If Retrieved_Gtid_Set is far ahead of Executed_Gtid_Set, the replica is applying slowly. If they're close but the source is far ahead, the problem is network or IO-thread speed.

Note

Seconds_Behind_Source can jump around or show 0 while a large transaction is still being applied. For an accurate measurement, use a heartbeat table (pt-heartbeat) or the timestamps in performance_schema.replication_applier_status_by_worker.

#2. What is the applier doing?

SELECT worker_id, service_state, last_error_message,
       applying_transaction,
       TIMEDIFF(NOW(6), applying_transaction_start_apply_timestamp) AS applying_for
FROM performance_schema.replication_applier_status_by_worker;

If one worker has been applying the same transaction for minutes, that transaction is the problem.

#Cause 1: the applier is effectively single-threaded

SHOW VARIABLES LIKE 'replica_parallel_workers';   -- 4 by default from 8.0.27
SHOW VARIABLES LIKE 'replica_preserve_commit_order';

STOP REPLICA SQL_THREAD;
SET GLOBAL replica_parallel_workers = 8;
START REPLICA SQL_THREAD;

Parallel apply depends on writeset-based dependency tracking on the source. It's the default in 8.0.x and the only mode in 8.4. Without it, transactions that could run in parallel still apply one after another.

#Cause 2: tables without a primary key

With row-based replication, the replica must find each changed row. With no primary key or unique key, it does a full table scan for every row. One DELETE of 100k rows becomes 100k table scans.

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, or turn on sql_generate_invisible_primary_key for new tables (8.0.30+).

#Cause 3: large transactions and DDL

• A single UPDATE touching millions of rows runs as one transaction on the replica, too. Split it into batches of 1,000–10,000 rows on the source.
• An ALTER TABLE that takes 2 hours on the source takes about 2 hours on the replica, and blocks the applier while it runs. Use ALGORITHM=INSTANT where possible, or online schema change tools (gh-ost, pt-online-schema-change).

#Cause 4: the replica is smaller or busier

Replicas often get smaller instances "because they're read-only", then get heavy reporting queries as well. Compare CPU, IOPS and buffer pool size with the source. On managed services, check the replica lag metric next to the replica's IOPS and CPU.

#When replication stops with an error

Last_SQL_Error: ... Error 'Duplicate entry '42' for key 'PRIMARY'' on query ...

Caution

Don't skip errors by habit (sql_replica_skip_counter, or injecting empty GTID transactions). Each skip leaves the replica silently different from the source. Find out why the row differs, usually a write made directly on the replica, and set super_read_only = ON on replicas to stop it happening again. If the data has drifted, rebuild the replica from a fresh clone (CLONE INSTANCE or a snapshot).