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.

Error Identification