mariadb-errors-replication-lag

name	mariadb-errors-replication-lag
description	Use when a replica is reporting Seconds_Behind_Master > 0 and growing, replication appears stuck, slave_parallel_threads is not helping, or you need to measure actual replication lag. Prevents the common mistake of trusting Seconds_Behind_Master as a real-time metric, leaving a single-threaded applier on a legacy setup, long master-transactions creating cascading lag, or binlog_row_image=MINIMAL causing replay or fix-on-replica failures. Covers measuring lag accurately with pt-heartbeat (NEVER with Seconds_Behind_Master alone), common root causes (single-threaded applier, long master transactions, replica IO bottleneck, large binlog events, GTID position mismatch with gtid_strict_mode), fixing with parallel-applier modes and threads, GTID position checks, and semi-sync timeout misunderstanding. Keywords: replication lag, Seconds_Behind_Master, replica behind, slave lag, pt-heartbeat, slave_parallel_threads, slave_parallel_mode optimistic, binlog_row_image MINIMAL, GTID position mismatch, IO bottleneck on replica, my replica is falling behind, replication broken, slave SQL thread, slave IO thread, replication keeps failing, gtid_strict_mode, rpl_semi_sync_master_timeout, semi-sync fallback to async, SHOW ALL SLAVES STATUS, slave_domain_parallel_threads, slave_parallel_max_queued, binlog event too large, long master transaction blocks applier, why is my replica slow
license	MIT
compatibility	Designed for Claude Code. Requires MariaDB 10.6-LTS, 10.11-LTS, 11.x, 12.x.
metadata	{"author":"OpenAEC-Foundation","version":"1.0"}

MariaDB Replication Lag

How to measure, diagnose, and fix replication lag on a MariaDB replica. Covers the unreliability of Seconds_Behind_Master, parallel-applier tuning, long-master-transaction root causes, GTID position checks, and the semi-sync-to-async fallback that looks like a lag spike but is not.

Quick Reference

• Seconds_Behind_Master from SHOW SLAVE STATUS is UNRELIABLE for real-time lag : it updates only between binlog events and reports 0 while the applier is mid-replay of a large transaction. NEVER use it alone for alerting. ALWAYS pair with pt-heartbeat (Percona Toolkit) for true lag.
• slave_parallel_threads default is 0 (single-threaded applier). On any modern workload, set to 4-16 based on disk-IO benchmark. The value is dynamic but requires STOP SLAVE ; SET GLOBAL ... ; START SLAVE.
• slave_parallel_mode default is optimistic from 10.5.1+ (was conservative until 10.5.0). NEVER set to none in production unless you are reproducing a determinism bug ; that forces single-thread applier even when slave_parallel_threads is non-zero.
• Long master transactions (single UPDATE touching millions of rows, batch DELETE without LIMIT) produce one huge binlog event that the applier MUST process serially. ALWAYS batch large DML on the master into chunks of 1000-10000 rows.
• binlog_row_image=MINIMAL reduces binlog volume but RECORDS only the primary key in the before-image. Fix-on-replica row reconstruction and point-in-time recovery to a specific row state both BREAK with MINIMAL. NEVER set MINIMAL on PITR-critical or multi-master fix-able instances.
• gtid_strict_mode=ON REFUSES any out-of-sequence GTID. A failed-over or partially-re-cloned replica with a stale gtid_slave_pos will stop with ER_GTID_STRICT_OUT_OF_ORDER and silently grow lag until reset.
• Semi-sync fallback to async (when rpl_semi_sync_master_timeout is reached) appears as a lag spike on monitoring graphs but is NOT a real applier-side lag. ALWAYS check Rpl_semi_sync_master_status (ON vs OFF) before treating a spike as applier-thread slowness.
• Replica disk slower than master = permanent lag. NEVER pair a HDD replica with an SSD master for an OLTP workload ; the applier IO cannot catch up.