MariaDB Replica Lag: Diagnostic Guide for Async Primary-Replica

Problem: stale reads from a MariaDB replica

Your application reads from a MariaDB replica and returns rows that were already updated on the primary. SHOW REPLICA STATUS reports Seconds_Behind_Master greater than zero, and the number keeps climbing.

Useful takeaway: replica lag almost always means the replica is fetching primary events faster than it can apply them, or one of the replication threads has stopped. Check thread states and log positions before changing buffers, disks, or replication topology.

This guide assumes a standard asynchronous primary-replica topology. It does not apply to Galera Cluster or multi-primary synchronous replication. MariaDB releases have used both SHOW SLAVE STATUS and SHOW REPLICA STATUS; use the statement your version supports.

Recognizable condition and likely causes

Ordered diagnostic checks

Check 1: Read replica status on the replica

Run on the MariaDB replica:

mysql -h <replica_host> -P 3306 -u <replication_admin> -p -e "SHOW REPLICA STATUS\G"

• Where: replica server, mysql client
• Permissions: REPLICATION CLIENT
• Expected check: Replica_IO_Running and Replica_SQL_Running both show Yes
• Risk: read-only diagnostic; no state change

Check 2: Compare fetched and applied positions

If both threads are running, determine whether events are fetched but not applied:

# Non-GTID topology
Read_Master_Log_Pos: 452318
Exec_Master_Log_Pos: 449022

# Gap means 3,296 bytes of primary binary log have been received but not applied

# GTID topology
Retrieved_Gtid_Set: 1-100
Executed_Gtid_Set: 1-92

• Where: replica server
• Expected check: A growing gap between retrieved/fetched and executed/applied positions indicates applier-side pressure, not a connection failure.
• Risk: none

Check 3: Inspect replication errors

From the same SHOW REPLICA STATUS output, read:

• Last_IO_Error: connection or binary log read failures on the replica IO thread
• Last_SQL_Error: failures while applying events on the replica SQL thread

Non-empty values explain why a thread stopped. Empty values with rising lag point to performance saturation rather than a hard error.

Check 4: Review the replica error log and process list

Check the replica error log, often /var/log/mariadb/mariadb.log or hostname.err in the data directory, around the time lag began. Also run:

mysql -h <replica_host> -u <replication_admin> -p -e "SHOW PROCESSLIST;"

Look for threads with User set to system user. A missing replication thread or a long-running replica query can confirm where apply work is stuck.

Check 5: Correlate system resources

During the lag window, check replica disk write latency, CPU utilization, and innodb_buffer_pool_size usage. High disk latency or buffer pool thrashing usually explains a SQL thread that is running but applying slowly. Compare with primary write throughput to distinguish replica saturation from a primary write spike.

Fixes tied to findings

START REPLICA IO_THREAD;

START REPLICA SQL_THREAD;

SET GLOBAL innodb_buffer_pool_size = <new_size>;

STOP REPLICA SQL_THREAD;
SET GLOBAL replica_skip_counter = 1;
START REPLICA SQL_THREAD;

Do not use the replica skip counter as a routine fix. Skipping transactions can silently diverge replica data from the primary. Validate affected tables with CHECKSUM TABLE before returning the replica to production traffic.

State-change note: STOP REPLICA and START REPLICA change replication state and are reversible by issuing START REPLICA. Skipping transactions is not safely reversible without reinitializing the replica.

Escalation criteria

• Lag continues growing unbounded after thread restarts and resource tuning
• Replica error log shows fatal binary log or GTID inconsistency
• The application read-after-write SLA is breached and cannot be met by tuning the replica

Verify the result

After applying a fix, run SHOW REPLICA STATUS again on the replica and confirm:

• Seconds_Behind_Master trends toward zero
• Exec_Master_Log_Pos or Executed_Gtid_Set closes the gap with the fetched position
• No new rows appear in Last_IO_Error or Last_SQL_Error

For a concrete data-integrity check, run the same statement on primary and replica:

-- Run on both primary and replica
CHECKSUM TABLE <database>.<table>;

If checksums differ, the replica is diverged and should be reinitialized from a fresh primary snapshot rather than returned to read traffic.

Limitations

• Assumes a standard asynchronous primary-replica topology, not Galera Cluster or multi-primary synchronous replication.
• MariaDB versions differ in replication thread terminology and GTID behavior; do not apply binary-log-position recovery steps to a GTID-configured topology without converting the procedure.
• Seconds_Behind_Master can be misleading under parallel replication because workers may apply transactions out of primary commit order.
• Asynchronous replicas are not automatic failover targets with zero recovery point objective unless an additional manager or synchronous cluster is configured.