Troubleshooting binary log replication lag for Aurora MySQL

This section provides guidance on binary log replication lag for Aurora MySQL configured as a binary log replica. It covers replication architecture, parallel replication, dependency tracking, monitoring, and configuration best practices.

Binary log replication replicates data between MySQL-compatible databases. The binary log replication covered in this section is asynchronous: the source does not wait for replicas to confirm application of changes before committing transactions. This does not apply to semi-synchronous replication. In semi-synchronous replication, the source waits for at least one replica to acknowledge receipt of the transaction before committing. For example, Multi-AZ DB clusters for Amazon RDS for MySQL use semi-synchronous replication, which is out of scope for this section. The replica maintains a persistent connection to the source via the I/O thread to continuously stream binary log events. This section applies to all binlog replication topologies where Aurora MySQL is the replica. The source can be another Aurora MySQL cluster, Amazon RDS for MySQL, on-premises MySQL, or MySQL on Amazon EC2. This section also applies when Aurora MySQL is the source replicating to any MySQL-compatible target.

Experiencing a lag spike right now? Skip the background material and start with Identifying the replication lag bottleneck to determine whether the I/O thread or the SQL thread is the bottleneck, then follow the link for that scenario: Troubleshooting I/O thread lag or Troubleshooting SQL thread lag.

MySQL replication architecture

MySQL replication is implemented through specialized threads:

The replication process works as follows:

Identifying the replication lag bottleneck

Replication lag can occur in two areas: the I/O thread or the SQL thread. The first step is to determine which component is lagging.

Source_Log_File:       mysql-bin.000045
Read_Source_Log_Pos:   524288000
Relay_Source_Log_File: mysql-bin.000045
Exec_Source_Log_Pos:   524200000

File:     mysql-bin.000045
Position: 1073741824

Troubleshooting I/O thread lag

The I/O thread is responsible for fetching binary log events from the source and writing them to the relay log. Common causes and resolutions:

Optimization strategies for I/O thread lag

Troubleshooting SQL thread lag

The SQL thread is the bottleneck when the I/O thread is caught up with the source but Seconds_Behind_Source is growing. To quantify the lag, monitor over a 15-minute window. Compare the binlog generation rate on the source (the Position delta from SHOW MASTER STATUS) with the SQL thread apply rate on the replica (the Exec_Source_Log_Pos delta).

Common causes and resolutions:

SELECT t.table_schema, t.table_name
FROM information_schema.tables t
  LEFT JOIN information_schema.table_constraints tc
    ON t.table_schema = tc.table_schema
    AND t.table_name = tc.table_name
    AND tc.constraint_type = 'PRIMARY KEY'
WHERE tc.constraint_type IS NULL
  AND t.table_type = 'BASE TABLE'
  AND t.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
ORDER BY 1;

SELECT trx_id, trx_started, trx_rows_modified, trx_query
FROM information_schema.innodb_trx
WHERE trx_rows_modified > 10000
ORDER BY trx_started;

SELECT database_name, table_name,
    MIN(last_update) AS oldest_stat_update,
    DATEDIFF(NOW(), MIN(last_update)) AS stats_age_in_days
FROM mysql.innodb_index_stats
WHERE database_name NOT IN ('mysql', 'sys')
GROUP BY database_name, table_name
HAVING stats_age_in_days > 7
ORDER BY stats_age_in_days DESC;

Reduce apply workload with replica-side replication filters

Aurora MySQL version 3.01.0 and higher supports replica-side replication filters. Use these to skip irrelevant databases or tables during apply, reducing SQL thread workload. Replica-side filters are applied by the SQL thread – the I/O thread still fetches all binary log events from the source, so these filters reduce apply work only, not network transfer. Unlike source-side filters (which operate at the database level only), replica-side filters support table-level granularity using replicate-do-table and replicate-ignore-table. For more information, see Configuring replication filters with Aurora MySQL.

Multi-threaded replication (MTR)

With MTR, the replica applies independent transactions in parallel. It cannot split a single large transaction into parallel parts. The degree of parallelism is limited by the dependency information written by the source.

When MTR is enabled (replica_parallel_workers >= 2), the SQL applier is split into a coordinator thread and multiple worker threads. The coordinator reads transactions from the relay log. It examines the logical timestamps (last_committed and sequence_number) that the source embeds in each transaction. It then assigns independent transactions to available worker threads for parallel execution. Two transactions are independent if they do not share data dependencies – that is, if they modify different rows. The source determines these dependencies at commit time using the configured dependency tracking method and records them in the binary log. The replica cannot increase parallelism beyond what the source permits.

MTR is the recommended default

We recommend running replicas with MTR enabled. MTR is enabled by default (replica_parallel_workers=4) in MySQL 8.0.27 and higher, including the Aurora MySQL 3 versions based on those releases. On earlier versions (Aurora MySQL 2.12.1 and higher, or versions based on MySQL releases before 8.0.27), enable it explicitly by setting replica_parallel_workers to a value of 2 or more. Keeping MTR enabled costs little when parallelism is unavailable and lets the replica take advantage of it whenever the workload allows.

MTR does not reduce lag in the following cases:

Dependency tracking on the source

The source determines which transactions can be applied in parallel using the binlog_transaction_dependency_tracking parameter. Recommended value: WRITESET.

Parallel type (replica_parallel_type)

The replica_parallel_type parameter determines how transactions are distributed among worker threads. There are two options:

MTR configuration

After changing parallel worker settings, restart replication:

CALL mysql.rds_stop_replication;
CALL mysql.rds_start_replication;

Aurora-specific replication optimizations

Aurora MySQL provides the following features to improve binary log replication performance. The following table summarizes the availability, default state, and how to enable each feature.

Monitoring parallel replication

Use the following methods to monitor replication performance and identify bottlenecks in parallel apply:

Performance Schema

Key tables for monitoring MTR:

To evaluate how evenly work is distributed across worker threads, use the following query:

SELECT
  a.channel_name,
  rw1.thread_id AS replica_worker_thread_id,
  ts1.count_star AS number_of_trx_executed,
  ROUND((ts1.count_star / sum_count_star) * 100, 2) AS percent_of_trx_executed_by_worker
FROM (
  SELECT rw.channel_name, SUM(ts.count_star) AS sum_count_star
  FROM performance_schema.events_transactions_summary_by_thread_by_event_name AS ts
    JOIN performance_schema.replication_applier_status_by_worker AS rw
      ON ts.thread_id = rw.thread_id
  GROUP BY rw.channel_name
) AS a
JOIN performance_schema.replication_applier_status_by_worker AS rw1
  ON a.channel_name = rw1.channel_name
JOIN performance_schema.events_transactions_summary_by_thread_by_event_name AS ts1
  ON rw1.thread_id = ts1.thread_id;

If one or two workers handle the majority of transactions, it indicates high transaction dependencies limiting parallelism. Consider switching to WRITESET dependency tracking on the source.

If the I/O thread and SQL thread positions are close to each other but Seconds_Behind_Source is still growing, the coordinator thread itself might be the bottleneck. Check performance_schema.replication_applier_status_by_coordinator for buffering delays. A coordinator bottleneck typically indicates heavy transaction dependencies or an overloaded single coordinator thread that cannot distribute events fast enough.

Error log multi-threaded replica statistics

When log_error_verbosity=3 (the default), Aurora MySQL writes multi-threaded replica statistics to the MySQL error log periodically. You can view these entries by:

To locate the multi-threaded replica statistics entries in the error log, search for the string shown in the following example. The MySQL error log uses legacy terminology in this internal string; this documentation uses the preferred term "replica" throughout.

The following is an example error log entry:

2026-05-10 14:54:28.045308 [Note] [MY-010559] [Repl] Multi-threaded slave statistics for channel '': seconds elapsed = 120; events assigned = 7215200; worker queues filled over overrun level = 0; waited due a Worker queue full = 0; waited due the total size = 0; waited at clock conflicts = 1203644200 waited (count) when Workers occupied = 1022462 waited when Workers occupied = 62349203500

Key fields to analyze:

Worked examples: diagnosing parallel apply bottlenecks

The following examples show how to use the preceding monitoring sources to diagnose common parallel apply bottlenecks. Each example starts from the same symptom – Seconds_Behind_Source is steadily growing and you have already confirmed the SQL thread is the bottleneck (see Identifying the replication lag bottleneck) – and uses the multi-threaded replica statistics and Performance Schema to determine the corrective action.

Example 1: Workers are underprovisioned

In CloudWatch, the ReplicaLag metric climbs steadily from near zero to approximately 400 seconds over 30 minutes. CPU utilization on the replica stays around 55%, so the replica is not CPU-bound.

To confirm where the lag is, run SHOW REPLICA STATUS twice, 60 seconds apart. Read_Source_Log_Pos advances by approximately 1.2 GB while Exec_Source_Log_Pos advances by only approximately 300 MB. The I/O thread is keeping up with the source, but the SQL thread is falling behind – the SQL thread is the bottleneck.

MTR is enabled with replica_parallel_workers=4. Retrieve the most recent multi-threaded replica statistics from the error log (see Error log multi-threaded replica statistics):

2026-05-10 14:54:28.045308 [Note] [MY-010559] [Repl] Multi-threaded slave statistics for channel '': seconds elapsed = 123; events assigned = 9876544; worker queues filled over overrun level = 0; waited due a Worker queue full = 0; waited due the total size = 0; waited at clock conflicts = 8455000 waited (count) when Workers occupied = 2456789 waited when Workers occupied = 110293847000

Interpret the key fields:

Diagnosis: the coordinator consistently has more independent transactions ready to apply than it has workers to run them. Because clock-conflict waiting is negligible, parallelism is limited by worker count, not by dependencies.

Action: increase replica_parallel_workers to the replica's vCPU count (for example, 16 on a db.r6g.4xlarge) and restart replication:

CALL mysql.rds_stop_replication;
CALL mysql.rds_start_replication;

Verification: a later statistics line shows the wait has nearly disappeared, and ReplicaLag returns to under 5 seconds:

2026-05-10 15:02:14.882201 [Note] [MY-010559] [Repl] Multi-threaded slave statistics for channel '': seconds elapsed = 120; events assigned = 31840552; worker queues filled over overrun level = 0; waited due a Worker queue full = 0; waited due the total size = 0; waited at clock conflicts = 9120000 waited (count) when Workers occupied = 41233 waited when Workers occupied = 5980411000

"waited when Workers occupied" dropped from approximately 110 seconds to approximately 6 seconds, and throughput (events assigned) more than tripled. Stop increasing workers once CPU utilization approaches 80% or the wait stops decreasing.

Example 2: COMMIT_ORDER dependency tracking serializes a low-concurrency workload

The source is an Amazon RDS for MySQL instance running an OLTP workload with only 4–8 concurrently committing application threads. The replica is a db.r6g.4xlarge with replica_parallel_workers=16 and replica_parallel_type=LOGICAL_CLOCK. Despite 16 workers, ReplicaLag holds steady around 600 seconds and replica CPU utilization is only about 20% – the workers look idle.

First, check how evenly transactions are distributed across workers using the worker-distribution query from Performance Schema:

+--------------+--------------------------+------------------------+-----------------------------------+
| channel_name | replica_worker_thread_id | number_of_trx_executed | percent_of_trx_executed_by_worker |
+--------------+--------------------------+------------------------+-----------------------------------+
|              |                       45 |                1903556 |                             96.41 |
|              |                       46 |                  23104 |                              1.17 |
|              |                       47 |                  18995 |                              0.96 |
|              |                       48 |                  16720 |                              0.85 |
.
.
+--------------+--------------------------+------------------------+-----------------------------------+

The preceding output shows the first 4 of the 16 worker threads. One worker is applying over 96% of transactions while the other 15 are nearly idle (each of the remaining workers executed less than 0.05%). Apply is effectively serial.

Next, confirm this with the multi-threaded replica statistics in the error log:

2026-05-10 15:22:11.110432 [Note] [MY-010559] [Repl] Multi-threaded slave statistics for channel '': seconds elapsed = 120; events assigned = 2014500; worker queues filled over overrun level = 0; waited due a Worker queue full = 0; waited due the total size = 0; waited at clock conflicts = 98712340000 waited (count) when Workers occupied = 50122 waited when Workers occupied = 1209847000

This points to a dependency problem, not a capacity problem. Check the dependency tracking method on the source:

SELECT @@global.binlog_transaction_dependency_tracking;

+-------------------------------------------------+
| @@global.binlog_transaction_dependency_tracking |
+-------------------------------------------------+
| COMMIT_ORDER                                    |
+-------------------------------------------------+

Root cause: with COMMIT_ORDER, the source decides which transactions can run in parallel based on which transactions committed together in the same binary log group commit. In this low-concurrency workload only a handful of threads commit at a time, so group commits are tiny. As a result, the source stamps nearly every transaction with a last_committed value equal to the previous transaction's sequence_number, marking it as dependent on the one before it. The replica must then apply them one after another – even though they modify completely unrelated rows – which is why 15 of the 16 workers sit idle.

Action: switch the source to row-level dependency tracking, which is independent of commit timing. Set it dynamically and persist it in the cluster (or DB) parameter group:

SET GLOBAL binlog_transaction_dependency_tracking = WRITESET;

WRITESET computes a hash of the rows each transaction modifies, so transactions touching different rows receive independent timestamps and can be applied in parallel regardless of when they committed. Ensure all tables have primary keys, because WRITESET produces empty writesets for tables without them (see Troubleshooting SQL thread lag). On MySQL 8.4, WRITESET is the default and this parameter no longer exists.

Verification: after the change, the worker-distribution query shows work spread evenly across all workers (showing the first 4 of 16; each worker now handles roughly 6% of transactions):

+--------------+--------------------------+------------------------+-----------------------------------+
| channel_name | replica_worker_thread_id | number_of_trx_executed | percent_of_trx_executed_by_worker |
+--------------+--------------------------+------------------------+-----------------------------------+
|              |                       45 |                 132540 |                              6.30 |
|              |                       46 |                 125610 |                              5.97 |
|              |                       47 |                 129774 |                              6.17 |
|              |                       48 |                 122901 |                              5.84 |
.
.
+--------------+--------------------------+------------------------+-----------------------------------+

and the error-log statistics show clock conflicts collapsing while ReplicaLag falls to near zero:

2026-05-10 15:41:55.204713 [Note] [MY-010559] [Repl] Multi-threaded slave statistics for channel '': seconds elapsed = 120; events assigned = 19874100; worker queues filled over overrun level = 0; waited due a Worker queue full = 0; waited due the total size = 0; waited at clock conflicts = 412300000 waited (count) when Workers occupied = 88122 waited when Workers occupied = 7019847000

"waited at clock conflicts" dropped from approximately 99 seconds to approximately 0.4 seconds, throughput rose roughly tenfold, and the bottleneck shifted from dependencies to worker capacity – at which point you can tune worker count as in Example 1.

Example 3: A missing primary key forces full table scans on the replica

ReplicaLag spikes during a nightly batch job that runs large UPDATE and DELETE statements, then recovers afterward. During the spike, replica CPU is moderate but one worker appears stuck.

On the replica, run SHOW PROCESSLIST and look at the replication worker threads. One worker stays in the Updating state on the same statement for many seconds at a time:

+----+-------------+------+---------+----------+------+
| Id | User        | db   | Command | State    | Time |
+----+-------------+------+---------+----------+------+
| 12 | system user | app  | Connect | Updating |   38 |
+----+-------------+------+---------+----------+------+

Check which replicated tables lack a primary key using the detection query from Troubleshooting SQL thread lag:

+--------------+----------------+
| table_schema | table_name     |
+--------------+----------------+
| app          | events_archive |
+--------------+----------------+

Root cause: with ROW-based binary logging, each row in an UPDATE or DELETE event must be located on the replica before it can be applied. If the table has no primary key or unique NOT NULL key, the replica performs a full table scan for every affected row. On a multi-million-row table, a batch operation turns into millions of full scans, and the worker applying it stalls – blocking apply progress and driving up lag.

Action: add a primary key to the table. If you cannot define an explicit key immediately, enable Generated Invisible Primary Keys (GIPK) by setting the sql_generate_invisible_primary_key parameter to ON (available in Aurora MySQL 3 versions based on MySQL 8.0.30 and later), so new tables receive an automatic primary key (see Troubleshooting SQL thread lag).

Verification: after adding the primary key, the worker no longer lingers in Updating, the SQL thread apply rate recovers, and ReplicaLag returns to baseline during the next batch window.

Example 4: A single large transaction cannot be parallelized

ReplicaLag jumps sharply at a predictable time each day, holds for several minutes, then drops back to near zero. WRITESET dependency tracking is enabled, replica_parallel_workers=16, and all tables have primary keys, so the earlier examples do not apply.

During the spike, the worker-distribution query shows one worker busy and the rest idle, but unlike Example 2 the error log shows neither high clock conflicts nor worker-occupied waits:

2026-05-10 02:13:40.551922 [Note] [MY-010559] [Repl] Multi-threaded slave statistics for channel '': seconds elapsed = 95; events assigned = 4120000; worker queues filled over overrun level = 0; waited due a Worker queue full = 0; waited due the total size = 0; waited at clock conflicts = 1530000 waited (count) when Workers occupied = 980 waited when Workers occupied = 210440000

Both dependency waits and worker-occupied waits are low, yet only one worker is active. This rules out both the dependency bottleneck (Example 2) and the worker-capacity bottleneck (Example 1).

Inspect the busy worker in performance_schema.replication_applier_status_by_worker, or run SHOW PROCESSLIST. A single worker is applying one transaction for the entire duration of the spike. On the source, an application job runs one large statement – for example, DELETE FROM orders WHERE created_at < '2025-01-01' affecting several million rows – as a single transaction.

Root cause: MTR parallelizes across independent transactions; it cannot split a single transaction across workers. A large transaction is applied by exactly one worker, so adding workers, switching to WRITESET, or adding primary keys does not help. For more information, see Multi-threaded replication (MTR).

Action: break the large operation into smaller batches on the source (for example, delete in chunks of a few thousand rows per transaction, with a brief pause between batches). Smaller transactions commit independently, so the replica can spread them across workers. Schedule bulk jobs during low-traffic windows where possible.

Verification: after batching the job, the daily ReplicaLag spike flattens, and the worker-distribution query shows the batch spread across multiple workers instead of one.

Monitoring best practices

Best practices for minimizing replication lag

The following recommendations summarize the key actions for minimizing replication lag. For detailed guidance on each topic, follow the cross-reference links.