Improved MySQL 8 Replication Lag Monitoring Using GTID Timestamps and Performance Schema

Traditional MySQL replication lag observation is incomplete: it cannot show the true master‑slave delay and relies on the second_behind_master metric, which only indicates replay delay and trend changes.

The community‑contributed heartbeat‑table method creates a table on the master that a heartbeat process updates every second; slaves calculate the time difference after syncing. This solution, while not requiring MySQL code changes, suffers from single‑point failure, coarse second‑level granularity, inability to reflect actual business transaction delay, and binlog pollution due to many heartbeat events.

MySQL 8 Replication Lag Observation Improvements

Workloads WL#7319 and WL#7374 enhance lag monitoring. WL#7319 adds original_commit_timestamp (microsecond timestamp of the transaction when it is written to the master binlog) and immediate_commit_timestamp (microsecond timestamp when the transaction is written to the slave’s relay log or applied binlog), enabling slaves to compute exact transaction lag.

original_commit_timestamp : timestamp of the transaction on the master (identical across nodes).

immediate_commit_timestamp : timestamp of the transaction on the slave (or relay node) when it is recorded in the binlog, representing the replay completion time.

WL#7374 introduces new Performance Schema tables that expose additional observation points:

replication_connection_status : records the state of the I/O thread.

replication_applier_status_by_coordinator : records the state of the coordinator thread used for parallel apply.

replication_applier_status_by_worker : records the state of the SQL worker threads.

replication_connection_status fields

LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP – original commit timestamp written to the relay log.

LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP – immediate commit timestamp written to the relay log.

LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP – start‑queue timestamp of the transaction in the relay log.

LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP – end‑queue timestamp of the transaction in the relay log.

QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP – original commit timestamp of the transaction currently being written.

QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP – immediate commit timestamp of the transaction currently being written.

QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP – start‑queue timestamp of the transaction currently being written.

replication_applier_status_by_coordinator fields

LAST_PROCESSED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP – original commit timestamp of the last scheduled transaction.

LAST_PROCESSED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP – immediate commit timestamp of the last scheduled transaction.

LAST_PROCESSED_TRANSACTION_START_BUFFER_TIMESTAMP – start‑buffer timestamp when the transaction was handed to a worker thread.

LAST_PROCESSED_TRANSACTION_END_BUFFER_TIMESTAMP – end‑buffer timestamp when the transaction left the worker buffer.

PROCESSING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP – original commit timestamp of the transaction currently being scheduled.

PROCESSING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP – immediate commit timestamp of the transaction currently being scheduled.

PROCESSING_TRANSACTION_START_BUFFER_TIMESTAMP – start‑buffer timestamp of the transaction currently being scheduled.

replication_applier_status_by_worker fields

LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP – original commit timestamp of the last applied transaction.

LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP – immediate commit timestamp of the last applied transaction.

LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP – start‑apply timestamp of the last applied transaction.

LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP – end‑apply timestamp of the last applied transaction.

APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP – original commit timestamp of the transaction currently being applied.

APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP – immediate commit timestamp of the transaction currently being applied.

APPLYING_TRANSACTION_START_APPLY_TIMESTAMP – start‑apply timestamp of the transaction currently being applied.

Observing Lag at Different Points

Position 1 – Full end‑to‑end delay from master (A) to slave (D):

SELECT LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP - LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP
FROM performance_schema.replication_applier_status_by_worker

Position 1 (via relay node C to D):

SELECT LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP - LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP
FROM performance_schema.replication_applier_status_by_worker

Position 2 – Delay between a transaction that has been scheduled and the start of its apply phase:

SELECT APPLYING_TRANSACTION_START_APPLY_TIMESTAMP - APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP
FROM performance_schema.replication_applier_status_by_worker

Position 3 – Delay of scheduled transactions waiting for apply when MTS is enabled:

SELECT LAST_PROCESSED_TRANSACTION_END_BUFFER_TIMESTAMP - LAST_PROCESSED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP
FROM performance_schema.replication_applier_status_by_coordinator

Position 4 – Delay of transactions that have reached the relay log but have not yet been scheduled (MTS enabled):

SELECT PROCESSING_TRANSACTION_START_BUFFER_TIMESTAMP - PROCESSING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP
FROM performance_schema.replication_applier_status_by_coordinator

Position 5 – Delay of transactions synchronized to the slave’s relay log:

SELECT LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP - LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP
FROM performance_schema.replication_connection_status

Position 6 – Network transmission delay of the currently syncing transaction:

SELECT QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP - QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP
FROM performance_schema.replication_connection_status

MySQL 8 fundamentally resolves the previous lack of transaction commit timestamps by embedding them in the binlog and exposing richer performance_schema views, allowing engineers to diagnose replication lag with far greater precision.