MySQL Replication Lag on a VPS: Reading SHOW REPLICA STATUS and Tuning Parallel Workers

Seconds_Behind_Source is a rough guide, not a measurement. Here is how to tell whether a MySQL replica is really behind, why, and what to change on the VPS.

To find out whether a MySQL replica on a VPS is really behind, check that both replication threads are running, then compare the source commit time of the last applied transaction with the time the replica finished applying it, using the Performance Schema. Seconds_Behind_Source is useful as a first look, but MySQL's own documentation lists several cases where it is misleading. Once you know the lag is real, the usual fixes are primary keys on every table, parallel apply workers, and enough disk and memory on the replica.

This guide assumes replication is already running. For the initial GTID setup, see our earlier post on MySQL replication on a VPS: GTID setup, binary logs and failover.

Step 1: Check that both threads are running

SHOW REPLICA STATUS\G

Two fields come first. Replica_IO_Running is the receiver thread that pulls events from the source: Yes means running and connected, Connecting means running but not connected, No means stopped. Replica_SQL_Running is the applier thread. If either is not Yes, you do not have lag, you have a stopped replica, and the error fields tell you why.

Step 2: Read the fields that matter

FieldWhat it tells you
Last_IO_Errno / Last_IO_ErrorThe most recent error that stopped the receiver, with a timestamp in Last_IO_Error_Timestamp. Usually network, credentials or a purged binary log on the source.
Last_SQL_Errno / Last_SQL_ErrorThe most recent error that stopped the applier, such as a duplicate key or missing row. On a multithreaded replica, check every worker as well (step 3).
Retrieved_Gtid_SetEvery transaction received into the relay logs.
Executed_Gtid_SetEvery transaction applied and written to this server's binary log. Transactions in the retrieved set but not here are received but not yet applied.
Relay_Log_SpaceTotal size of the relay log files. If it keeps growing, the applier is falling behind the receiver.
Seconds_Behind_SourceThe replica's clock minus the source timestamp of the event being processed. See the caveats below.

Why Seconds_Behind_Source misleads

  • It shows 0 whenever no event is being processed, so a replica whose receiver is slow can look current while it is well behind.
  • It is NULL when the applier is not running, or when the applier has used up the relay log and the receiver is stopped.
  • Clock changes, such as NTP corrections on either server, reduce its accuracy.
  • In a chain where an intermediate server also takes direct writes, the value can fluctuate.
  • On a multithreaded replica it is based on Exec_Source_Log_Pos, so it may not reflect the most recently committed transaction.

Step 3: Measure the real delay per worker

The Performance Schema table replication_applier_status_by_worker records, for each applier worker, when the last applied transaction was committed on the original source and when this worker finished applying it:

SELECT WORKER_ID, SERVICE_STATE, LAST_ERROR_NUMBER,
       LAST_APPLIED_TRANSACTION,
       TIMESTAMPDIFF(SECOND,
         LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP,
         LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP) AS lag_seconds
FROM performance_schema.replication_applier_status_by_worker;

The original commit timestamp is set by the source where the transaction first ran, so it is the same on every replica in a chain. The end-apply timestamp is recorded locally. The difference is your replication delay plus any clock skew, so keep both servers on NTP. The APPLYING_TRANSACTION_* columns show the transaction a worker is applying right now; if its start-apply time is old, that one transaction is the bottleneck. These timestamps are zero when the Performance Schema is disabled.

For the receiver side, replication_connection_status holds the equivalent queueing information, and replication_applier_status_by_coordinator covers the coordinator thread on a multithreaded replica.

Step 4: Fix the common causes

Missing primary keys

The MySQL 8.4 manual warns that tables without primary keys hurt replica performance more when more than one parallel worker is used. Row-based events on such tables can force a scan per row on the replica. Add primary keys before you tune anything else.

Parallel apply settings

In MySQL 8.4, replica_parallel_workers defaults to 4, so a fresh replica already applies in parallel. Setting it to 0 is deprecated; use 1 if you want a single worker. replica_preserve_commit_order defaults to ON, keeping commits in the source's order, and replica_parallel_type defaults to LOGICAL_CLOCK and is itself deprecated. Changes to the worker count take effect at the next START REPLICA:

STOP REPLICA;
SET PERSIST replica_parallel_workers = 8;
START REPLICA;

More workers help only up to a point, and only if the source's transactions can actually run in parallel. Raise the count in steps and re-measure with the query above.

Very large transactions

replica_pending_jobs_size_max (default 128M) is a soft limit on memory for queued events. A transaction larger than that waits until all worker queues are empty, and everything behind it waits too. Break bulk updates and deletes into smaller batches on the source.

The VPS itself

A replica applies every write the source makes, so it needs disk and memory comparable to the source, not a smaller leftover plan. If Relay_Log_Space grows while workers are busy, look at disk wait and buffer pool hit rate on the replica before blaming replication.

A routine that catches lag early

  • Alert when either thread is not running, or when a worker's LAST_ERROR_NUMBER is not 0.
  • Graph the per-worker delay query every minute rather than Seconds_Behind_Source.
  • Watch Relay_Log_Space for steady growth.
  • Before any failover, confirm the replica's Executed_Gtid_Set contains the source's transactions instead of trusting a 0.

StreamData offers database hosting for MySQL, PostgreSQL and Redis on KVM VPS plans from 1 GB to 128 GB RAM, and on dedicated servers for larger primaries. Placing the replica on a different node from the source gives you a copy that survives a site problem; test the path between nodes the same way you test it for users. Backups remain your responsibility unless a written order includes them, as the company policies set out, and a replica is not a backup.


Sources