Replication lag is the most misdiagnosed symptom in MySQL operations. "The replica is behind" has at least five distinct causes with five different fixes, and applying the wrong one — usually "add more parallel workers" — burns days. Here is the diagnostic sequence we use, in order.
First: measure lag properly
Seconds_Behind_Source (née Seconds_Behind_Master) is a rough proxy: it compares the replica's clock against the timestamp in the event being applied, reads zero while the SQL thread is idle-but-starved, and flaps to NULL on I/O thread issues. For real measurement use heartbeat-based lag — pt-heartbeat, or on 8.0 the replication_applier_status_by_worker Performance Schema tables, which show per-worker applied transaction timestamps. Aurora and RDS expose their own lag metrics; trust those over SHOW REPLICA STATUS arithmetic.
Also distinguish steady-state lag (replica always slightly behind, growing at peak) from episodic lag (fine all day, then a cliff at 2 a.m.). They have disjoint cause lists.
Cause 1: single-threaded apply on a multi-threaded workload
The classic. Your primary applies writes with dozens of concurrent threads; a default-configured replica applies them with one. If the replica's apply thread sits at 100% of one core while the host is otherwise idle, this is it.
Fix: multi-threaded replication with dependency tracking.
replica_parallel_workers = 8
replica_parallel_type = LOGICAL_CLOCK
replica_preserve_commit_order = ON
On 8.0.27+, binlog_transaction_dependency_tracking = WRITESET on the source is the big lever: it lets transactions touching disjoint rows apply in parallel even from a low-concurrency source. We have seen WRITESET take a replica from hours behind to seconds with no other change.
Cause 2: one giant transaction
Replication applies transactions serially within their dependencies, and a single 20-million-row UPDATE or DELETE becomes one binlog event group the replica must chew through alone — while everything behind it waits. Episodic lag that coincides with batch jobs is almost always this.
Fix: chunk the batch (1,000–10,000 rows per transaction, sleeping between chunks), or use the online-schema-change tooling's pattern for it. This is an application fix; no replica tuning survives a single huge transaction.
Cause 3: missing primary keys with row-based replication
With binlog_format = ROW, applying a row event on a table without a primary key forces the replica to locate each row by scan — a full table scan per modified row in the worst case. One PK-less table can be the whole story of a lagging replica.
Find them:
SELECT tables.table_schema, tables.table_name
FROM information_schema.tables
LEFT JOIN information_schema.table_constraints tc
ON tc.table_schema = tables.table_schema
AND tc.table_name = tables.table_name
AND tc.constraint_type = 'PRIMARY KEY'
WHERE tc.constraint_type IS NULL
AND tables.table_schema NOT IN ('mysql','sys','performance_schema','information_schema')
AND tables.table_type = 'BASE TABLE';
Fix: give every table a primary key (an invisible auto-increment column is fine on 8.0.23+, or set sql_generate_invisible_primary_key). This also unblocks Group Replication and most managed-platform migrations, which require PKs anyway.
Cause 4: the replica is simply slower or busier
Replicas serving heavy read traffic (reports, backups, analytics) contend with the apply threads for I/O and buffer pool. Cheaper instance classes for replicas are a false economy once lag matters. Checks: apply-thread waits in Performance Schema, disk latency during lag windows, buffer-pool hit rate versus the primary. Durability settings also matter — many shops run replicas with sync_binlog = 0 and innodb_flush_log_at_trx_commit = 2, accepting relaxed durability on a rebuildable replica in exchange for apply throughput. Make that an explicit decision, not an inherited one.
Cause 5: semi-sync and network effects
With semi-synchronous replication, a slow or distant replica pushes latency back onto the primary's commits — the symptom shows up as slow writes on the primary, not as lag. Check Rpl_semi_sync_source_tx_avg_wait_time, and never put a semi-sync acker on the far side of a high-latency link. For cross-region topologies, chain a local intermediate or accept async and design read paths for staleness.
Cause 6: schema changes and long-running reads on the replica
Two replica-side stalls masquerade as lag. A copy-based ALTER TABLE replicated from the primary runs for its full duration on every replica, blocking everything behind it — which is exactly why large-table schema changes belong to external tooling rather than plain DDL. And a long analytical query holding a consistent snapshot can block the apply thread behind a metadata lock. If lag correlates with a nightly report, look there before touching replication settings at all.
The order matters
Work the list top down: parallelism config, then transaction sizing, then PK audit, then replica capacity, then topology. Each step has a cheap, decisive test, and in our experience the first three cover the large majority of lagging replicas — long before "bigger instance" needs to enter the conversation.