Semi-Synchronous Replication: Monitoring Lag Systematically

1. The difference between ACK wait time and actual replication lag

ACK wait time is the span a source waits for at least one replica's acknowledgment before reporting the commit back to the client. That time is usually very short, often in the low single-digit millisecond range, because the replica only has to confirm that the event landed in its own relay log, not that it has already been applied. A low value for this wait time therefore says little about the actual state of the database on the replica.

Actual replication lag, on the other hand, measures how far the apply process on the replica lags behind what is already sitting in the relay log. These two figures can diverge widely: a replica can acknowledge every transaction within a few milliseconds and still take minutes to actually apply it, whenever the SQL thread gets slowed down by complex statements or locking conflicts.

2. How the semi-synchronous ACK actually works

Semi-synchronous replication is implemented through the rpl_semi_sync_source plugin on the source and rpl_semi_sync_replica on the replica. Once the source commits a transaction and has sent the corresponding binlog events to at least one replica, it waits for an ACK signal before confirming the commit to the calling client. The replica sends that ACK as soon as the received event has been written to the local relay log, not applied.

This exact design decision makes semi-sync noticeably faster than fully synchronous replication, as implemented by Galera or Group Replication in certification mode, since there is no need to wait for the full apply step. The price for that is that a source failure right after the ACK, but before the actual apply on the replica, can theoretically leave a replica holding the transaction in its relay log but not yet in its tables.

-- On the source
INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
SET PERSIST rpl_semi_sync_source_enabled = 1;

-- On the replica
INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
SET PERSIST rpl_semi_sync_replica_enabled = 1;
-- Then run START REPLICA IO_THREAD again on the replica

3. Why a low ACK time does not mean the replica is caught up

The apply process on the replica has historically run as a single SQL thread per channel, applying events strictly sequentially. Even with parallel replication enabled through replica_parallel_workers, parallelism stays limited by dependencies between transactions touching the same row or the same schema, so a single slow operation, such as a large batch update or a missing index, noticeably delays the entire apply progress.

A replica can therefore acknowledge every incoming transaction within milliseconds and still build up an apply backlog of several minutes, once the SQL thread gets blocked by resource-intensive operations. Anyone monitoring only semi-sync ACK metrics sees a seemingly perfectly healthy system in that case, while read queries against that replica already return stale data.

4. Relevant status variables for semi-sync itself

On the source, Rpl_semi_sync_source_yes_tx and Rpl_semi_sync_source_no_tx report how many transactions were acknowledged successfully or not in time, respectively, the latter being a hint that a fallback to asynchronous replication occurred. Rpl_semi_sync_source_avg_net_wait_time shows the average network wait time for the ACK, a value that should stay nearly constant on stable network connections.

A sudden rise in Rpl_semi_sync_source_no_tx almost always points at network problems or overloaded replicas regularly missing the ACK window. Since that value is counted cumulatively since the last server start, deriving the delta over a fixed time window is far more meaningful for a dashboard than the raw counter value.

SHOW STATUS LIKE 'Rpl_semi_sync_source_yes_tx';
SHOW STATUS LIKE 'Rpl_semi_sync_source_no_tx';
SHOW STATUS LIKE 'Rpl_semi_sync_source_avg_net_wait_time';
SHOW STATUS LIKE 'Rpl_semi_sync_source_status';

5. Performance schema tables for monitoring the actual apply lag

For the actual backlog, performance_schema.replication_applier_status_by_worker provides the timestamp of the most recently applied transaction per apply worker, from which a precise difference against the current time can be computed. This table is noticeably more meaningful than the classic Seconds_Behind_Source from SHOW REPLICA STATUS, which is known to return imprecise or even NULL values under parallel replication and after connection drops.

In addition, replication_connection_status shows the time gap between the moment a transaction committed on the source and the moment it was received on the replica, through the LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP and LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP columns. Combining both tables cleanly separates network lag from apply lag.

SELECT WORKER_ID,
       TIMESTAMPDIFF(SECOND, APPLYING_TRANSACTION_START_APPLY_TIMESTAMP, NOW())
         AS apply_lag_seconds
FROM performance_schema.replication_applier_status_by_worker
WHERE SERVICE_STATE = 'ON';

6. Measuring real lag with a heartbeat table

For an application-level lag measurement independent of the replication protocol itself, the heartbeat pattern, known from tools like pt-heartbeat, has proven effective: a small table on the source gets updated at a fixed interval with a current timestamp, and that update replicates like any other transaction. On the replica, the difference between the stored timestamp and the local server time gets computed, giving the actual end-to-end lag including apply time, independent of internal replication metrics.

This approach has the advantage of running through the same apply pipeline as any real application transaction, and therefore measures more realistically than a pure metadata query. For Magento environments with read replicas for reporting or search, this heartbeat table is worth running alongside the internal metrics, because it directly reflects the data freshness that matters to end users.

-- Run on the source every 2 seconds via cron/event
UPDATE heartbeat SET ts = NOW(6) WHERE id = 1;

-- Evaluated on the replica
SELECT TIMESTAMPDIFF(MICROSECOND, ts, NOW(6)) / 1000000 AS lag_seconds
FROM heartbeat WHERE id = 1;

7. Alerting thresholds for production operation

For most Magento-adjacent setups with read replicas, a tiered alerting scheme has proven effective: a warning starting around five seconds of apply lag, which is mostly informational and points at a brief load spike, and a critical alert starting around thirty seconds, at which point read queries against that replica should be actively rerouted for time-sensitive use cases such as stock displays or order status. These specific numbers are not a law of nature, they need to be adjusted to the actual write load and error tolerance of each application.

More important than a single fixed threshold is trend analysis: lag that keeps growing continuously across several measurement intervals signals a structural problem, such as a permanently overloaded replica, while a brief spike after a large batch import is usually uncritical and resolves itself. Alerting rules should therefore react to trend and duration, not exclusively to a single point-in-time value.

8. The fallback mechanism when the ACK timeout is exceeded

If no replica responds within the time span configured through rpl_semi_sync_source_timeout, ten seconds by default, the source automatically falls back to asynchronous replication so as not to block application write access indefinitely. That fallback becomes visible through Rpl_semi_sync_source_status, which then switches from ON to OFF, while semi-sync itself stays enabled and automatically returns to synchronous mode as soon as the next acknowledgment succeeds in time.

For monitoring, it is crucial to watch that status change itself, not just the lag values, because a permanent fallback into asynchronous mode means the semi-sync guarantee effectively no longer applies, even though the configuration still claims semi-sync. An alert on every switch of Rpl_semi_sync_source_status to OFF reliably catches exactly this silent loss of guarantee.

9. A practical example: a compact monitoring query set for dashboards

A production-ready dashboard typically combines three layers: the semi-sync status values for the ACK guarantee itself, the performance schema query for apply lag per worker, and the heartbeat table for the end-to-end view from the application's perspective. All three values can be queried regularly with little effort through a simple cron script and forwarded to a monitoring system like Prometheus via the mysqld exporter.

For alerting rules, it pays to consider all three values together rather than in isolation: high apply lag with a simultaneously normal semi-sync status points at a performance problem in the apply thread, while a fallback status with low apply lag points more toward a network problem between source and replica. This combination allows a noticeably more precise diagnosis than any single metric on its own.

10. Summary

11. FAQ: Semi-Sync Lag: Key Facts at a Glance