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\GTwo 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
| Field | What it tells you |
|---|---|
Last_IO_Errno / Last_IO_Error | The 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_Error | The 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_Set | Every transaction received into the relay logs. |
Executed_Gtid_Set | Every 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_Space | Total size of the relay log files. If it keeps growing, the applier is falling behind the receiver. |
Seconds_Behind_Source | The replica's clock minus the source timestamp of the event being processed. See the caveats below. |
Why Seconds_Behind_Source misleads
- It shows
0whenever no event is being processed, so a replica whose receiver is slow can look current while it is well behind. - It is
NULLwhen 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_NUMBERis not 0. - Graph the per-worker delay query every minute rather than
Seconds_Behind_Source. - Watch
Relay_Log_Spacefor steady growth. - Before any failover, confirm the replica's
Executed_Gtid_Setcontains 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.