Context
A MySQL replica performs two separate jobs: it receives transaction records from its source and applies them locally. A delay can originate in either job. Increasing worker counts cannot repair a broken network connection, and faster networking cannot make an expensive transaction finish sooner. Start by identifying which boundary is falling behind.
Prerequisites and scope
This procedure targets self-managed MySQL 8.4 asynchronous replication. Obtain permission to inspect replication status and Performance Schema, the channel name, the source identity, and a recent workload timeline. Keep application consistency requirements nearby: an analytics report and a confirmation page may tolerate different freshness. The observations below are read-only; changing replication settings needs a separate tested change plan.
Follow the transaction path
- Capture
SHOW REPLICA STATUStwice with timestamps. Record receiver and SQL thread states, the last connection and applier errors, source log positions, and relay log space. On a server with several channels, inspect the intended channel rather than combining unrelated streams. An old error message needs correlation with the current thread state. - Compare received and executed progress over the same interval. A receiver that makes no progress while the source advances suggests connectivity, source availability, authentication, or bandwidth trouble. A growing queue behind a progressing receiver points toward local apply work. Treat
Seconds_Behind_Sourceas supporting evidence: its calculation can hide transport delay and is not a complete freshness measurement. - Inspect the coordinator and worker status tables in Performance Schema. Look for a stopped worker, repeated transient errors, or one long transaction occupying a worker while others wait. Correlate the interval with disk latency, CPU saturation, backups, and reporting queries on the replica. Avoid collecting unrestricted SQL text into an incident ticket if it contains business data.
- Choose a correction at the demonstrated boundary. Repair certificate or network failures for receiving problems; review local resource competition or transaction size for applying problems. Test concurrency changes against the actual transaction dependency pattern. Do not skip a failed transaction merely to make a lag dashboard look healthy.
Diagnostic guide
| Observation | Likely investigation | Next evidence |
|---|---|---|
| Receiver disconnected | Source path or credentials | Connection error and source logs |
| Receiver advances, execution stalls | Local apply constraint | Worker state and disk latency |
| One worker remains busy | Large transaction or dependency | Transaction history and batch schedule |
| Delay reads zero, data is old | Incomplete metric interpretation | Source progress and freshness probe |
Illustrative scenario
Suppose a warehouse import commits a very large transaction while staff run reports on the replica. Receiving continues, relay logs accumulate, and one worker stays occupied. The team first postpones the optional report and confirms that apply progress resumes. It then tests smaller import batches in a representative environment. This example demonstrates an investigation path; it does not predict a particular throughput improvement.
Verify the correction
Repeat the same timestamped observations under comparable demand. Confirm that both threads remain healthy, the receive-to-execute gap contracts, and a harmless application freshness check becomes current. Keep the replica out of workflows requiring fresh reads until their acceptance condition passes. If the source continues writing, a moving position is expected; compare rates and transaction identity rather than demanding that every counter stop changing.
Operating procedure
Monitor receiver availability, applier errors, backlog trends, free disk space, and a workload-specific freshness signal together. Route alerts to the replication owner with channel and source identity included. After every import redesign, reporting change, or replica resize, review a peak workload interval. Record the cause, the correction, and the evidence that distinguished receiving delay from applying delay.
Explore Clouvenix database administration for implementation and operational handover.