MySQL Replication Lag Troubleshooting: 7-Step Diagnostic Guide for 5.7/8.0

1. Problem Background: What Does Lag Mean?

Replication lag impact depends on business context: reporting replicas tolerate minutes; user-facing reads after writes cannot; failover candidates risk data loss. First define what lag means for your business before tuning.

1.1 Real Replication Path

A write travels four segments: (1) primary commits → writes binlog; (2) dump thread sends binlog over network; (3) replica I/O thread writes relay log; (4) applier (coordinator + workers) executes transactions. Each segment requires different diagnostics.

1.2 Three Lag Metrics

Event time lag – Seconds_Behind_Master (8.0) / Seconds_Behind_Source (8.4): time since last applied transaction's original commit.

Transaction count lag – difference between Retrieved_Gtid_Set and Executed_Gtid_Set.

Heartbeat lag – pt-heartbeat measures replica's data freshness from user perspective.

These often diverge; relying on one causes misdiagnosis.

2. Scope & Prerequisites

Applies to classic async GTID replication (MySQL 5.7, 8.0.26–8.0.4x, 8.4 LTS). Requires REPLICATION CLIENT, SELECT on performance_schema, PROCESS privileges. Tools: Percona Toolkit ( pt-heartbeat, pt-table-checksum), mysqlbinlog. Not for Group Replication, NDB, or cloud-managed variants with restricted commands.

3. Core Knowledge: Replication Anatomy

3.1 Transaction Lifecycle

Primary: InnoDB commit → binlog cache → binlog file (fsync per sync_binlog) → dump thread. Replica: I/O thread → relay log → coordinator distributes to workers (8.0 parallel) → workers commit → update Executed_Gtid_Set. Two hard constraints: a transaction cannot be parallelized internally; commit order can be broken unless replica_preserve_commit_order=ON.

3.2 Applier Stage Observability (8.0+)

Query performance_schema.replication_applier_status_by_coordinator for LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP, START_APPLY_TIMESTAMP, END_APPLY_TIMESTAMP. Compute lag_seconds (now – original commit) and apply_seconds (end – start). Healthy: lag ~0.4s, apply ~0.003s. Worker-level status via replication_applier_status_by_worker.

3.3 Five Pitfalls of Seconds_Behind_Master

Idle applier shows 0 even if I/O thread stalled.

Network slowness masks lag (applier catches up to slow receiver).

Clock skew corrupts value.

Parallel replay uses Exec_Source_Log_Pos which may lag behind actual commits.

Field name changes across versions (8.0: Seconds_Behind_Master; 8.4: Seconds_Behind_Source) break monitoring.

3.4 GTID Set Difference

Use GTID_SUBTRACT or Python script to compute missing transactions between primary's gtid_executed and replica's Executed_Gtid_Set. Rising transaction count with zero seconds lag indicates network/I/O issue.

3.5 pt-heartbeat for User-Perspective Lag

Primary updates heartbeat table every second; replica reads it. Deploy as systemd service. Lag = NOW(6) - ts. Immune to clock skew and idle-applier zero.

3.6 8.0.26+ Commit Timestamps

GTID events carry original_commit_timestamp (primary), immediate_commit_timestamp (direct upstream), and applier start/end timestamps. Enables precise lag measurement without clock dependency.

4. Seven-Step Diagnostic Method

Freeze scene – snapshot SHOW REPLICA STATUS, load, time.

Thread health – check Replica_IO_Running, Replica_SQL_Running, Last_IO_Error, Last_SQL_Error. If I/O down → network; if SQL down → applier error.

Receive vs. replay – compare Source_Log_File vs Relay_Source_Log_File. Source ahead → replay bottleneck; both same but lag high → single heavy transaction; source behind → network/I/O.

Quantify three metrics – seconds lag, GTID gap, heartbeat lag. Cross-reference patterns (see article for table).

Replica workload – SHOW PROCESSLIST, innodb_trx for long queries/transactions holding locks.

Applier current transaction – worker apply_seconds, relay log analysis via mysqlbinlog.

Resource & I/O – iostat -x (await >50ms, %util~100% = disk bottleneck), vmstat (si/so = swapping), SHOW ENGINE INNODB STATUS (checkpoint lag, buffer pool hit rate).

Root cause must be supported by at least two independent evidence types (status, resource, data).

5. Version Differences & Recommended Config

Variable names changed at 8.0.22 ( slave_* → replica_*). Default replica_parallel_workers evolved from 0 (5.7) to 4 (8.0.27+). replica_parallel_type=LOGICAL_CLOCK required for replica_preserve_commit_order=ON. DATABASE type deprecated 8.0.29, removed 8.4. Recommended baseline for 8.0.27+/8.4: replica_parallel_workers=8, LOGICAL_CLOCK, preserve_commit_order=ON, replica_net_timeout=60, relay_log_recovery=ON. Use binlog_format=ROW, binlog_row_image=MINIMAL.

6. Root Cause 1: Large Transactions & DDL

Parallelism works across transactions, not within. A 5M-row UPDATE or ALTER TABLE blocks one worker entirely. Detect via primary's innodb_trx (rows_modified), replica's worker apply_seconds, relay log row-event count (>100k = heavy), pt-query-digest on binlog. Mitigate: split batch jobs (5k rows, 20ms sleep), use gh-ost / pt-online-schema-change for DDL with --throttle-control-replicas and --max-lag-millis.

7. Root Cause 2: Parallel Replication Misconfiguration

Common errors: workers=0/1 (no parallelism), DATABASE type with preserve_commit_order=ON (startup error), excessive workers (64) when bottleneck is I/O or write-set conflicts. Tune by binary search: measure GTID advance rate per 10s at different worker counts. Check primary's sys.schema_table_lock_waits for hotspot tables limiting parallelism.

8. Root Cause 3: Replica Hardware & I/O Bottlenecks

Common: undersized replica (8 cores, HDD vs primary 32 cores, NVMe). Compare innodb_buffer_pool_size, innodb_io_capacity (set to 4000/8000 for NVMe), innodb_flush_neighbors=0 (SSD), innodb_log_buffer_size (increase if log buffer space near full). Heavy reporting queries on replica evict buffer pool pages, contend I/O/CPU. Solution: dedicate separate replica for analytics.

9. Root Cause 4: Lock Waits & Long Transactions

Replica long-running SELECT ... FOR UPDATE or idle transaction blocks applier via row locks. Diagnose with sys.innodb_lock_waits (blocking query, wait_age). Kill offending connection ( KILL then KILL CONNECTION if needed). Long-term: app connection pool max-lifetime, idle-timeout; DB innodb_idle_transaction_session_timeout=300; replica innodb_lock_wait_timeout=15 (with auto-recovery).

10. Root Cause 5: Tables Without Primary Keys / Missing Indexes

ROW format requires primary/unique key for fast row lookup; otherwise full table scan per row. Find via sys.tables_with_no_primary_key (8.0) or information_schema join (5.7). Also find high-update tables with low index count via sys.schema_table_statistics. Fix: add primary key (use pt-online-schema-change with unique key first), add covering indexes for frequent UPDATE WHERE columns. Use ALGORITHM=INPLACE, LOCK=NONE.

11. Root Cause 6: Network & I/O Thread

Check Replica_IO_State (healthy: "Waiting for source to send event"). Measure binlog generation rate (bytes/sec) vs network bandwidth. If >70% utilization, replication stalls. Tune replica_net_timeout (default 60s, increase to 300s for transient blips). Semi-sync timeout interacts: primary falls back to async after timeout, causing lag spike illusion.

12. Root Cause 7: Semi-Sync Replication & Primary Stalls

Semi-sync waits for replica ACK (default 10s, 1 replica). Rpl_semi_sync_source_no_tx growing = transactions falling back to async (data loss risk). Semi-sync reduces replica lag but adds primary write latency. Trade-off: safety vs. performance. Adjust rpl_semi_sync_source_timeout (1s) and wait_for_replica_count=2 with monitoring.

13. Toolbox: Commands & Interpretation

mysqlrpl --check-slave-hosts

– topology health. pt-replication-check – data consistency (needs replcheck DB). pt-table-checksum + pt-table-sync --dry-run – row-level diff repair. mysqlbinlog --base64-output=DECODE-ROWS -v – inspect relay/binlog for DDL, large transactions. SHOW BINARY LOGS / SHOW REPLICAS – verify replica read positions before purging binlogs (keep 3 days + 2x peak volume).

14. Monitoring & Alerting

Three layers: (1)

mysqld_exporter

slave_status

metrics (version-aware naming), (2) pt-heartbeat collector (primary metric), (3) replication_applier_status_by_worker for apply latency. Prometheus rules: heartbeat lag >5m warning, >30m critical; seconds lag >10m; IO/SQL thread down; SQL error; relay log >10GB; heartbeat missing. Grafana panels: heartbeat trend, seconds lag (8.0/8.4 compatible), per-replica comparison, relay log growth. Supplemental Python exporter for GTID gap, apply seconds, IO running.

15. Three Real-World Cases

Case 1: Midnight Batch Delete → 40min Lag

Single 4.8M-row UPDATE (archival) took 90s on primary, 41s apply on one worker. Fix: wait for replay (lag decreasing) or skip with sql_replica_skip_counter (only if data non-critical). Long-term: batch script with 5k rows/20ms sleep, monitoring for single-transaction row count.

Case 2: Stale Reads Despite Zero Seconds_Behind_Master

ProxySQL misrouted reads to primary; replica had 55min reporting transaction holding 120k row locks. Kill long transaction, fix ProxySQL hostgroup, isolate reporting to dedicated replica.

Case 3: 1-Second Jitter After 5.7→8.0.36 Upgrade

Seconds_Behind_Master

oscillated 0/1s; heartbeat stable 0.1s, GTID gap 0. Bug #115811: mixed-version timestamp semantics. Chose to keep workers=8, alert on heartbeat only, add for: 5m to suppress jitter alerts.

16. Remediation Trade-offs

Actions with scope, condition, risk, recommendation:

Split large transactions – Root, App/script change, None, High

Add PK to keyless tables – Root, pt-osc / downtime, DDL lock, High

Increase parallel workers – Mitigate, Restart SQL thread, Applier pause, Med-High

gh-ost for DDL – Mitigate, Extra tool, Shadow table space, High

Upgrade replica hardware – Root, Procurement, None, High

Replica flush_log_at_trx_commit=2 – Mitigate, Restart mysqld, 1s data loss on crash, Med

Read routing by lag – Arch, ProxySQL config, Stale reads, Med

Dedicated reporting replica – Arch, New instance, Cost, High

Semi-sync – Mitigate, Both sides, Primary latency, Per biz

sql_replica_skip_counter – Stopgap, Extreme, Permanent inconsistency, Low

Three quick tricks: kill long replica queries, set relay_log_info_repository=TABLE (faster restart), disable replica binlog ( skip-log-bin) only if not cascade source and no PITR needed.

17. Validation, Canary & Rollback

Four evidences: (1) heartbeat lag curve peak reduced below threshold; (2) GTID diff zero; (3) read-consistency script (write→read→verify) shows 0% mismatch; (4) average/max apply_seconds decreased. Canary: change one non-production replica, observe 24h (CPU, I/O, apply latency, consistency), then roll out one-by-one (4h each). Persist to my.cnf after validation. Rollback table with commands, time, data risk.

18. Production Checklist & Summary

Daily 5-min Patrol

Check thread status, heartbeat lag, applier lag, long transactions (>60s), relay log space, disk usage, super_read_only=ON.

Config Baselines

Primary: ROW, MINIMAL, GTID, sync_binlog=1, flush_log_at_trx_commit=1, buffer pool 60-70% RAM, io_capacity=4000. Replica: read_only=super_read_only=ON, workers=8, LOGICAL_CLOCK, preserve_commit_order=ON, relay_log_recovery=ON, same I/O settings.

Top 10 Pitfalls

Trusting only Seconds_Behind_Master.

Mixing version-specific variable names.

Expecting parallelism to fix large transactions.

Skipping errors without data repair.

Setting replica flush_log_at_trx_commit=2 ignoring failover role.

Purging binlogs before replicas consumed them.

Running heavy reports on replica.

Adding indexes without checking long transactions.

Clock drift between primary/replica.

Dynamic SET GLOBAL without persisting to config.

Architecture Shift Signals

Workers >32 no improvement → I/O or dependency bound.

Primary write volume exceeds single replica replay capacity.

Zero-data-loss failover required → semi-sync + strict gate or Group Replication.

Read-your-writes needed → route to primary via middleware.

Root cause in app batch jobs → data access layer governance.

Final action list: (1) Deploy pt-heartbeat if missing; (2) Set alert thresholds from your baseline; (3) Record Source_Log_File vs Relay_Source_Log_File gap; (4) Scan for keyless large tables.