MySQL replication allows data changes made on one server to be copied to another server. Applications commonly use replication to support read scaling, reporting, high availability, and disaster recovery.
However, replication does not always happen immediately. A transaction committed on the primary server may take time to become visible on a replica. When this delay grows, users can see outdated information, reports may return inconsistent results, and applications that depend on replica reads can behave unexpectedly.
Replication lag is not always caused by a slow network or an underpowered replica. A large transaction, a slow query, a busy storage device, limited replication workers, or an inefficient table schema can delay the application of changes. In some cases, the replica is receiving changes quickly but cannot apply them at the same rate.
The first step is to identify where the delay occurs. You need to determine whether MySQL is waiting to receive events from the source, whether the replica has received events but has not applied them, or whether a particular transaction is taking too long to execute.
This article explains how to diagnose MySQL replication lag, find queries and transactions that delay replication, and apply practical fixes without compromising data consistency.
Understand How MySQL Replication Works
In a typical MySQL replication setup, the source server processes transactions and records changes in its binary log, commonly called the binlog. The replica receives these changes and applies them to its own data.
The replication process has several important stages:
- A transaction executes and commits on the source.
- MySQL records the transaction in the source's binary log.
- The replica's receiver thread retrieves the binary log events.
- The events are stored in the replica's relay log.
- The replica's applier thread or threads execute the changes locally.
The exact implementation and available monitoring fields depend on the MySQL version and replication configuration.
Replication lag can develop at different points in this process.
If the replica cannot retrieve events quickly enough, the problem may involve connectivity, source load, or receiver throughput. If the relay log continues to grow while the receiver is keeping up, the replica may be struggling to apply changes.
This distinction matters because increasing network capacity will not fix a replica that is spending too much time applying transactions.
Check Replication Status First
Begin by checking the replica's replication status. On MySQL versions that support the command, run:
SHOW REPLICA STATUS\G
The command returns information about replication connections, execution progress, errors, and lag estimates.
Some important fields include:
- Replica_IO_Running: Indicates whether the receiver thread is running.
- Replica_SQL_Running: Indicates whether the applier thread is running in configurations that use the traditional terminology.
- Seconds_Behind_Source: An estimate of replication delay, when available.
- Last_IO_Error: Describes the most recent receiver-related error.
- Last_SQL_Error: Describes the most recent applier-related error.
- Relay_Log_Space: Shows the space occupied by relay logs.
- Retrieved_Gtid_Set and Executed_Gtid_Set: Provide GTID progress information when GTID-based replication is enabled.
Field names and output vary by MySQL version and replication configuration. Older releases use terminology such as SHOW SLAVE STATUS and Slave_IO_Running. Use the syntax supported by your installed version.
Understand Seconds_Behind_Source
Seconds_Behind_Source is useful, but it should not be treated as a complete measure of replication health.
It is an estimate based on replication progress and event timing. Depending on the replication state, connection status, and version, it can be zero, nonzero, or unavailable even when the underlying situation requires investigation.
For example, a value of zero does not necessarily prove that a replica is fully caught up in every operational sense. A stopped receiver, a disconnected replica, or other replication conditions may make a single number misleading.
Always check the receiver and applier states, error fields, and replication progress together.
If either replication thread is stopped, investigate the reported error before attempting to restart it blindly.
Determine Whether the Replica Is Receiving or Applying Changes Slowly
One of the most useful diagnostic steps is separating event retrieval from event application.
Consider a replica where the receiver continues to download events, but the relay logs keep growing. This suggests that changes are arriving faster than the replica can apply them.
In contrast, if the receiver is disconnected or cannot retrieve new events, the delay may originate in the replication connection or source-side availability.
Use the following checklist:
| Observation | Possible explanation |
|---|
| Receiver thread is stopped | Network failure, authentication problem, source connectivity issue, or replication error |
| Applier thread is stopped | SQL error, duplicate-key conflict, missing row, or another event-application failure |
| Relay log space keeps increasing | The replica may be receiving events faster than it applies them |
| Replica lag rises during bulk imports | Large transactions or a high write rate may exceed replica apply capacity |
| Lag appears during reporting workloads | Read queries may compete with replication for CPU, memory, or storage |
| Lag persists despite low CPU usage | Storage latency, locks, dependencies, or replication configuration may be limiting progress |
These are diagnostic clues, not definitive conclusions. Confirm the cause with transaction details, performance metrics, and replication progress.
Find Long-Running Queries and Transactions
A long-running transaction on the source can produce a large amount of work for the replica to apply. Long-running activity on the replica can also compete with the applier for resources or hold locks that delay other operations.
Start by inspecting active sessions:
SELECT
ID,
USER,
HOST,
DB,
COMMAND,
TIME,
STATE,
INFO
FROM information_schema.PROCESSLIST
WHERE COMMAND <> 'Sleep'
ORDER BY TIME DESC;
This query helps identify sessions that have been active for a long time. The TIME field indicates how long the thread has been in its current state; it does not always represent the total execution time of a query across all stages.
The INFO field may be NULL when the thread is not executing a visible statement.
For more detailed investigation, inspect the Performance Schema, which provides statement and wait-event information when the relevant instrumentation is enabled.
Check for large transactions
A transaction that updates hundreds of thousands of rows may take a long time to execute on the replica. Even if the source commits the transaction quickly enough for the application's needs, the replica still has to apply the recorded changes.
Large transactions can also increase the time before their effects become visible on the replica, depending on the replication and transaction-application behavior.
Review recent bulk operations, schema migrations, batch updates, and scheduled jobs on the source.
For operations that can safely be split, smaller batches may reduce the duration of individual transactions and improve operational predictability. However, batching can change atomicity and application semantics, so do not split a transaction without considering its correctness requirements.
Investigate the Slow Query Log
The MySQL slow query log records queries that exceed the configured long_query_time threshold and satisfy the relevant logging conditions. It can help identify inefficient queries, including statements that scan many rows or perform expensive updates.
Check the current configuration:
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_output';
If the slow query log is disabled, consider enabling it according to your operational procedures. Logging can add overhead and generate substantial output on a busy server, so choose thresholds and retention settings carefully.
The slow query log is particularly useful on the replica when the applier executes SQL statements that are logged as slow queries in your configuration. It can also reveal expensive reporting queries that compete with replication.
However, do not assume that every replication delay will appear in the slow query log. Replication can be delayed by scheduling, dependency conflicts, storage waits, large transactions, or other factors that do not correspond neatly to one slow statement.
Examine the query execution plan
When an inefficient query is identified, use EXPLAIN to examine its execution plan.
For example:
EXPLAIN
SELECT
order_id,
customer_id,
status
FROM orders
WHERE customer_id = 1042
AND status = 'Pending';
If the query examines far more rows than expected, an appropriate index may reduce the work.
A possible index for this query is:
CREATE INDEX idx_orders_customer_status
ON orders (customer_id, status);
This is only an example. Before creating an index, inspect the existing indexes, table size, selectivity of the columns, and the query workload. A composite index may be appropriate when the query filters by both columns, but the best index depends on how the application accesses the table.
Creating indexes also consumes storage and adds work to writes. Adding indexes on the source can introduce additional replication work because the replica must maintain the same index structure.
Test index changes on representative data and monitor their effect on both query latency and replication lag.
Check Replication Worker Activity
MySQL supports parallel replication in compatible configurations. Instead of applying all transactions through a single worker, the replica can use multiple workers to execute eligible transactions concurrently.
Parallel replication may improve throughput, but it cannot make every workload scale linearly. Dependencies between transactions can limit concurrency, and extra workers can increase contention for CPU, storage, and locks.
Inspect replication-related configuration:
SHOW VARIABLES LIKE 'replica_parallel_workers';
SHOW VARIABLES LIKE 'replica_parallel_type';
SHOW VARIABLES LIKE 'replica_preserve_commit_order';
The available variables and accepted values depend on the MySQL version.
You can also inspect replication worker information through Performance Schema tables such as replication_applier_status_by_worker, where supported.
For example:
SELECT
CHANNEL_NAME,
WORKER_ID,
SERVICE_STATE,
LAST_ERROR_NUMBER,
LAST_ERROR_MESSAGE
FROM performance_schema.replication_applier_status_by_worker;
This query can help identify stopped workers and errors. Additional tables may provide worker timing and transaction details, depending on the version and instrumentation.
If workers report errors, resolve the underlying transaction or replication issue first. Increasing the worker count will not fix an invalid event or a duplicate-key conflict.
If workers are healthy but replication consistently falls behind, evaluate whether parallelism is suitable for the workload. Change worker settings gradually and measure CPU utilization, storage latency, apply throughput, and consistency requirements.
Look for Lock Contention and Resource Pressure
The replica can fall behind even when individual SQL statements are reasonably efficient. Reporting queries, maintenance jobs, and replication workers may compete for the same resources.
Check database locks
A long-running read transaction or a conflicting operation can interfere with other work, depending on the storage engine and transaction pattern.
Use Performance Schema and InnoDB status information to identify lock waits and transaction activity.
For a quick overview of InnoDB's current state, run:
SHOW ENGINE INNODB STATUS\G
Review the transaction section, lock waits, deadlocks, and other relevant information. The output is a snapshot, so capture it during the period when lag is rising.
Avoid killing sessions based only on their runtime. First determine whether they are blocking important work and whether terminating them could roll back a large transaction or disrupt users.
Check CPU and storage
Monitor CPU utilization, disk latency, disk throughput, and memory pressure on both the source and replica.
If the replica's storage is saturated, increasing the number of applier workers may make the problem worse by creating more concurrent I/O.
If CPU utilization is consistently high while storage remains healthy, query efficiency or insufficient parallelism may be worth investigating.
If both CPU and storage utilization are low, inspect replication errors, lock waits, transaction dependencies, and connection health rather than assuming that the server needs more hardware.
Understand GTIDs and Replication Progress
Global Transaction Identifiers (GTIDs) provide a way to track transactions across a replication topology.
When GTID-based replication is enabled, the retrieved and executed GTID sets can help determine whether the replica has received transactions that it has not yet applied.
For example, SHOW REPLICA STATUS may report Retrieved_Gtid_Set and Executed_Gtid_Set.
The retrieved set represents transactions received by the replica, while the executed set represents transactions that have been applied or otherwise recorded as executed in the relevant GTID context.
Comparing these sets can help identify an application backlog, but GTID sets are not simple queue-length counters. They represent transaction identities and may contain multiple intervals. Interpret them alongside replication status and worker metrics.
Do not manually modify GTID state to make the replica appear caught up. Incorrect GTID manipulation can cause transactions to be skipped or replication consistency to be compromised.
Common Mistakes When Fixing Replication Lag
Increasing hardware without finding the bottleneck
A larger instance may help when CPU, memory, or storage capacity is insufficient, but it will not resolve every replication problem. Identify the resource or operation that limits progress first.
Restarting replication without investigating errors
If the applier has stopped because of a duplicate-key conflict or another SQL error, restarting it without resolving the cause may simply reproduce the failure.
Capture the error details and determine why the source event cannot be applied.
Assuming a zero lag value means everything is healthy
Always check connection state, applier state, errors, and replication progress. A single lag metric is not sufficient to establish health.
Adding indexes without measuring their impact
Indexes can speed up reads and some updates, but they increase storage use and maintenance work. Test index changes and measure the effect on the complete workload.
Increasing parallel workers indiscriminately
More workers can improve throughput for eligible transactions, but they may also increase contention. Tune parallel replication based on actual worker activity and resource utilization.
Best Practices for Production MySQL Replication
Monitor replication lag alongside receiver and applier health, relay log growth, worker errors, CPU utilization, and storage latency. Alert on sustained lag and stopped replication threads, not only on a single threshold.
Use the slow query log and Performance Schema to identify expensive statements. Review large transactions and batch jobs that create bursts of replication work.
Keep source and replica configurations appropriate for their roles. A replica that serves heavy reporting queries may need workload isolation, resource tuning, or a separate reporting architecture to avoid competing with replication.
Test schema changes, index changes, and replication configuration adjustments before deploying them broadly. Confirm that the replica catches up under realistic write volume and that the application can tolerate the resulting consistency behavior.
Finally, define what replication lag means for the application. Some reporting screens can tolerate a few seconds of delay, while financial operations or read-after-write workflows may require reads from the primary or another mechanism that guarantees the required consistency.
Advantages and Disadvantages of Replication Tuning
Advantages
- Helps reduce delays between source commits and replica visibility.
- Improves the reliability of read scaling and reporting workloads.
- Makes replication failures and bottlenecks easier to diagnose.
- Can improve throughput through query optimization and suitable parallel replication.
- Provides clearer operational signals for capacity planning.
Disadvantages
- Parallel replication and configuration tuning can increase resource contention.
- Additional indexes consume storage and add write-maintenance overhead.
- Slow query logging and detailed instrumentation can create overhead.
- Replication does not automatically provide immediate consistency for replica reads.
- Some transactions cannot be applied concurrently because of dependencies.
Summary
MySQL replication lag occurs when changes are not received or applied quickly enough to keep a replica current. The first step is to inspect SHOW REPLICA STATUS and determine whether the receiver or applier is stopped, whether relay logs are accumulating, and whether replication errors are present.
Next, investigate long-running transactions, slow queries, inefficient indexes, replication worker activity, lock contention, and storage performance. Use the slow query log, EXPLAIN, Performance Schema, and InnoDB status information to gather evidence before changing configuration.
Tune parallel replication and indexes only when measurements show that they address the actual bottleneck. Monitor the effect of each change and preserve the application's consistency requirements.
A healthy replication setup is not simply one that reports low lag at a particular moment. It is one that can process the expected workload reliably, recover from interruptions, and keep replica data sufficiently current for the applications that depend on it.
Join the conversation! Your thoughts help the community grow.