Why does the replica lag when parallel replication is on?

Because MySQL assigns a whole transaction to one applier worker and never splits one across workers. Measured on a MySQL 8.4.11 source and replica in containers on 2026-09-20, with the shipped default of four workers, worker 1 held a single 400,000 row insert from 18:11:18.299512 until somewhere between 18:11:22.716 and 18:11:24.905, the two samples bracketing the moment it finished, while the APPLYING_TRANSACTION column on the other three was empty in every sample.

How do I see whether the workers are actually working?

Query performance_schema.replication_applier_status_by_worker and read APPLYING_TRANSACTION, not LAST_APPLIED_TRANSACTION. The MySQL 8.4 manual defines APPLYING_TRANSACTION as the GTID the worker is currently applying, so it is empty exactly when the worker has nothing in hand. LAST_APPLIED_TRANSACTION standing still only means no new transaction finished between your samples, which looks the same whether the worker is idle or part-way through a long one.

Does raising replica_parallel_workers fix lag?

It depends on the stream, and the difference is large either way. On a MySQL 8.4.11 source and replica in containers on 2026-09-20, a backlog of 20,000 independent single-row transactions took 68.440, 72.959 and 75.158 seconds at four workers against 139.572, 198.640 and 139.279 at one, three runs of each interleaved. Two further four-worker runs against a different control read 59.517 and 65.040. The same setting bought nothing for a single large transaction, and nothing when the destination table had no primary key.

Is Seconds_Behind_Source reliable?

It is a coarse signal with two documented limits. The MySQL 8.4 manual says it is 0 when no event is currently being processed, so it drops to zero the sample after a large transaction commits; measured on a MySQL 8.4.11 replica in a container on 2026-09-20 it read 0, 0, 0, 7, 9, 11, 0, 0 across one such apply. The manual also says that on a multithreaded replica it is based on Exec_Source_Log_Pos and may not reflect the most recently committed transaction. Use it to notice lag and the per-worker table to explain it.

What does replica_preserve_commit_order cost?

It makes the replica commit in the same order as the source, which is what keeps a read on the replica consistent with a read on the source. The cost is that a worker that finishes early waits for the ones in front of it, so one slow transaction holds up the ones behind it even when they were applied in parallel.

Why is MySQL replication lagging with parallel replication enabled?

Last updated Sep 20, 2026

View as Markdown

Quick answer: MySQL gives a whole transaction to one applier worker and never splits one across workers, so a single large transaction leaves the rest idle. A table with no unique key does the same thing for a different reason: the source has no key values to compare, so it falls back to commit order, and transactions committed one after another look dependent. Read APPLYING_TRANSACTION in the per-worker table to tell which you have.

What is the problem?

replica_parallel_workers is set, replica_parallel_type is LOGICAL_CLOCK, and the replica is still seconds or minutes behind. Raising the worker count helps only when the stream contains transactions that can run independently.

A worker applies a whole transaction. It cannot apply half of one, and two workers cannot share one, because the transaction has to commit atomically on the replica just as it did on the source. The elapsed apply time of your largest transaction is therefore a serial stretch of any catch-up that includes it, and no worker count shortens that stretch. It is not a limit on the whole stream: transactions before and after it can still be applied in parallel.

Raising the worker count is useful when the stream has independent transactions and useless when it does not. The per-worker table shows which case you have.

What it looks like, measured

A MySQL 8.4.11 source and replica in containers on one host, 2026-09-20, with the replica left at its shipped defaults of replica_parallel_workers=4, replica_parallel_type=LOGICAL_CLOCK and replica_preserve_commit_order=ON, all three read back from the running server. One statement on the source inserts 400,000 rows in a single transaction.

Seconds_Behind_Source on that replica, one SHOW REPLICA STATUS every two seconds through the apply, MySQL 8.4.11, 2026-09-20:

0  0  0  7  9  11  0  0

It reads 0 for the three samples before the transaction reaches the replica, climbs while the transaction is being applied, and is back to 0 on the first sample after the commit, with nothing in between. That is the documented behaviour: “When no event is currently being processed on the replica, this value is 0.” (SHOW REPLICA STATUS, MySQL 8.4 manual) A transaction becomes visible on the replica only when it commits, so this field has no partial progress to report while the worker is part-way through one.

Over the same window, on the same server and day, sampled every two seconds, all four workers:

worker	`LAST_APPLIED_TRANSACTION`	`APPLYING_TRANSACTION` while the source was ahead	`SERVICE_STATE`
1	`...:46`, then `...:47`	`...:47`, first attempt stamped 18:11:18.299512	ON
2	empty	empty	ON
3	empty	empty	ON
4	empty	empty	ON

Worker 1 held the 400,000 row transaction from 18:11:18.299512 until somewhere between 18:11:22.716 and 18:11:24.905, the two samples that bracket the moment it finished. The other three held nothing at either column in any sample.

The second column identifies an idle worker; the first one does not. LAST_APPLIED_TRANSACTION is “the GTID of the last transaction applied by this worker”, so a value that does not move only means no new transaction finished between two samples. A worker part-way through a long transaction looks the same. APPLYING_TRANSACTION is “the GTID of the transaction this worker is currently applying”, and it is empty when the worker has nothing in hand. (replication_applier_status_by_worker, MySQL 8.4 manual)

SERVICE_STATE will not tell you either. The manual defines ON on that table as “thread exists and is active or idle”, so four workers reading ON means four threads have started. A health check that stops there sees four healthy workers whatever they are doing.

How do I confirm it on my replica?

Start with the coarse number to confirm that there is lag:

SHOW REPLICA STATUS\G

Then check the per-worker table, which explains the lag. Sample it twice, a few seconds apart:

SELECT WORKER_ID,
       SERVICE_STATE,
       LAST_APPLIED_TRANSACTION,
       APPLYING_TRANSACTION
FROM performance_schema.replication_applier_status_by_worker
ORDER BY WORKER_ID;

Read APPLYING_TRANSACTION first. That column separates a busy worker from an idle one:

what the samples show	what it means	next check
one worker holds a GTID for many samples, the rest empty	one long transaction is being applied and cannot be divided	the size of that GTID’s transaction on the source
every worker empty, lag growing	the applier is not the bottleneck; look at the receiver	`SHOW REPLICA STATUS` for receiver state and relay log backlog
workers hold GTIDs but only ever one at a time	the stream has no parallelism the replica can use	whether the tables being written carry a unique key, below
every worker holds a GTID at once and lag still grows	the replica genuinely cannot keep up	its disk, and whether a row event is scanning a table
workers hold GTIDs in bursts with gaps	possibly `replica_preserve_commit_order` making finished workers wait, and possibly source group-commit shape or lock waits; it is not diagnostic on its own	`LAST_ERROR_MESSAGE` on the worker rows, then lock waits

Read LAST_APPLIED_TRANSACTION instead and the first two rows collapse into each other: a frozen value and an empty one look the same in that column.

The coordinator has its own row. It tells you whether the applier is starved or backed up:

SELECT SERVICE_STATE,
       PROCESSING_TRANSACTION,
       LAST_ERROR_MESSAGE
FROM performance_schema.replication_applier_status_by_coordinator;

How much do more workers actually buy?

Enough to measure, and nothing in the case above. Same MySQL 8.4.11 pair, 2026-09-20: 20,000 single-row inserts, each its own transaction, each to a distinct primary key, into an empty table. The replica’s applier is stopped while the whole backlog is built on the source, then started and timed until it has executed every GTID the source has, so the source is not part of the timed interval. The runs were interleaved four, one, four, one, four, one, because a machine that drifts between two blocks of trials will introduce a difference.

`replica_parallel_workers`	seconds to apply the same 20,000 transaction backlog
4	68.440, 72.959, 75.158
1	139.572, 198.640, 139.279

Those are the three four-worker runs interleaved with the three one-worker runs; two further four-worker runs, interleaved with a different control, appear in the next section. No run at four workers is within a minute of any run at one on that MySQL 8.4.11 pair of containers, 2026-09-20. So “more workers does nothing” is false for this stream, and it remains true of the single 400,000 row transaction measured above, where three of the four workers held nothing at any sample. Both facts are from the same replica on the same day. The transaction shape, not the setting, determines the result.

The prerequisite nobody mentions: the tables need a key

The parallelism above does not work with every schema. Repeat exactly the same backlog, same MySQL 8.4.11 pair, same 2026-09-20, same four workers, changing one thing: whether the destination table has a primary key.

destination table	1 worker	4 workers
`(id bigint PRIMARY KEY, pad char(100))`	139.572, 198.640, 139.279	68.440, 72.959, 75.158, 59.517, 65.040
`(id bigint, pad char(100))`	145.526, 148.819	145.645, 143.317

Each cell contains the times from separate runs of the same 20,000 transaction backlog on that MySQL 8.4.11 pair of containers, 2026-09-20, in seconds. The keyed 59.517 and 65.040 were the runs interleaved with the keyless ones, so the pair being compared ran minutes apart rather than hours. Without the key, four workers buy nothing: 143.317 and 145.645 seconds against 145.526 and 148.819 at a single worker.

A keyless table is not slow to write. At one worker on that MySQL 8.4.11 pair of containers, 2026-09-20, the keyless runs of 145.526 and 148.819 seconds sit within ten seconds of the keyed median of 139.572, whose own three runs were 139.572, 198.640 and 139.279. The 198.640 shows how far this host drifts, not what the key costs. The missing part is parallelism.

You can watch it happen. A 6,000 transaction backlog with replication_applier_status_by_worker sampled every 50 ms on that MySQL 8.4.11 pair of containers, 2026-09-20, counting the workers holding a non-empty APPLYING_TRANSACTION in each sample:

destination table	samples	workers busy at once
primary key	136	four in 131 samples, three in 1, two in 1, none in 3
no key	326	one in 310 samples, none in 16, never more than one

The keyed row provides the comparison for the keyless row. The same sampler at the same interval resolved four simultaneous workers on one table, so “never more than one” on the other is an absence it would have detected if it were there.

The reason is in the next section: the source works out what is independent from the rows each transaction touched, and it identifies rows by unique key values. With no key there is nothing to compare, and the source falls back to commit order for that transaction, so a stream committed one after another looks dependent throughout.

Why the source decides how parallel the replica can be

The replica does not work out which transactions are independent. The source writes that information into the binary log, and the replica follows it.

On MySQL 8.4 this is writeset based, and there is no setting for it: “In MySQL 8.4 (and later), when multithreaded replicas are in use, the source mysqld uses always writesets to generate dependency information for the binary log; this has the same effect as setting binlog_transaction_dependency_tracking to WRITESET in previous versions of MySQL.” (What Is New in MySQL 8.4, MySQL 8.4 manual) Two transactions that touched different rows can then be applied at the same time even though they committed one after the other.

The rule is not “different tables”, although that is the mental model people carry away. The comparison is over rows, identified by unique key values. Two writes to the same table with different keys can run together, which is what the 20,000 single-row inserts above are, all into one table. Two writes to different tables can still be held apart by the transaction they are in.

The variable that used to control this, binlog_transaction_dependency_tracking, no longer exists on 8.4; reading it on a running 8.4.11 server returns nothing. The related variable that remains is binlog_transaction_dependency_history_size, 25000 on that same 8.4.11 server and 25000 as the documented default, which “sets an upper limit on the number of row hashes which are kept in memory and used for looking up the transaction that last modified a given row. Once this number of hashes has been reached, the history is purged.” (Binary log options, MySQL 8.4 manual) A dependency older than the retained history cannot be found, so the source uses the safe answer: less parallelism rather than an incorrect one.

On 8.0 the setting still exists and its default is the slow one

On a running 8.0.46 server binlog_transaction_dependency_tracking reads COMMIT_ORDER, which matches the documented default, with WRITESET and WRITESET_SESSION the other two values. (Binary log options, MySQL 8.0 manual) Check this before concluding that parallel replication does not work on 8.0. Same backlog as above, 20,000 single-row inserts each its own transaction into a keyed table, on an 8.0.46 source and replica pair in containers, 2026-09-20, four workers throughout, changing only the source’s tracking mode, interleaved:

`binlog_transaction_dependency_tracking`	seconds to apply the same 20,000 transaction backlog
`WRITESET`	79.059, 110.464
`COMMIT_ORDER`	192.307, 208.029

On 8.0, check that setting first, and treat those results as measurements rather than a guarantee. On 8.4 it is already writeset behaviour. The remaining questions are the shape of your transactions and whether the tables they write carry a key.

What actually fixes it

Split the large transaction. This fixes the case above, and it requires a change on the source. A backfill that inserts a million rows in one statement becomes a loop over primary key ranges that commits each batch. The replica then has a hundred transactions it can schedule instead of one it cannot.

It has a cost, and the cost is not performance. A million row backfill split into a hundred commits is no longer all-or-nothing: it can stop half done, so it needs to be resumable and safe to run twice, and intermediate states are visible to anything reading. Batch by key range rather than by LIMIT so a retry lands on the same rows. If the operation cannot tolerate being half applied, a smaller batch will not help; do the work somewhere other than the replicated path.

Give every table a key. Measured above, without unique key values to compare the source falls back to commit order and cannot see that two transactions committed one after another are independent, so a keyless table loses the parallelism: 143.317 and 145.645 seconds at four workers against 145.526 and 148.819 at one, for the same 20,000 transaction backlog on that MySQL 8.4.11 pair of containers, 2026-09-20. The cost is invisible from the source, which committed the same transactions either way.

Look at durability on the replica. sync_binlog=1 and innodb_flush_log_at_trx_commit=1 are the safe defaults and they cost an fsync per commit. On a replica whose only job is to be a read copy, relaxing them is a legitimate trade and a decision about how much you are willing to lose in a crash.

Leave replica_preserve_commit_order on unless you have decided that reads on the replica may see transactions in a different order from the source. It is on by default on 8.4, and turning it off to chase lag trades correctness for throughput.

Raise replica_parallel_workers when the table shows workers idle with a backlog behind them. Measured on that MySQL 8.4.11 pair of containers, 2026-09-20, the same 20,000 transaction backlog applied in 59.517 to 75.158 seconds over five runs at four workers against 139.279 to 198.640 over three runs at one, a median of 68.440 against a median of 139.572, and it did nothing for a single large transaction or for writes into a keyless table. It is the setting people change first; change it when the per-worker table says the workers have nothing to do.

Why does AI-generated code cause this?

The first generated pattern is the bulk write. Asked to backfill a column or import a dataset, the natural output is one statement that does all of it, because that is correct, atomic and efficient on the source. Nothing in the request mentions a replica, and the cost lands on a machine the code never refers to.

The second is the migration wrapped in an explicit transaction for safety. That is good practice on the source, and it produces the shape that cannot be applied in parallel.

How do I stop it coming back?

Put a transaction size limit in your migration review. A number, such as ten thousand rows per commit, makes the limit checkable rather than a matter of judgment.

Alert on APPLYING_TRANSACTION, not only on Seconds_Behind_Source. One worker holding a GTID while three hold nothing is a condition you can detect, and it tells you what to do. The seconds number does not: it reads 0 whenever no event is being processed, and on a multithreaded replica “this value is based on Exec_Source_Log_Pos, and so may not reflect the position of the most recently committed transaction.” (SHOW REPLICA STATUS, MySQL 8.4 manual) If the question you have is how stale a read can be, use a heartbeat row written on the source and read on the replica, not this field.

Require a primary key on every table in review. It is a practical protection against replica-only slowness, it is what dependency tracking compares, and neither cost is visible from the source.

Test the backfill against a replica before it runs. Lag depends on the statement and the topology together.

FAQ

Why does the replica lag when parallel replication is on?
Because MySQL assigns a whole transaction to one applier worker and never splits one across workers. Measured on a MySQL 8.4.11 source and replica in containers on 2026-09-20, with the shipped default of four workers, worker 1 held a single 400,000 row insert from 18:11:18.299512 until somewhere between 18:11:22.716 and 18:11:24.905, the two samples bracketing the moment it finished, while the APPLYING_TRANSACTION column on the other three was empty in every sample.
How do I see whether the workers are actually working?
Query performance_schema.replication_applier_status_by_worker and read APPLYING_TRANSACTION, not LAST_APPLIED_TRANSACTION. The MySQL 8.4 manual defines APPLYING_TRANSACTION as the GTID the worker is currently applying, so it is empty exactly when the worker has nothing in hand. LAST_APPLIED_TRANSACTION standing still only means no new transaction finished between your samples, which looks the same whether the worker is idle or part-way through a long one.
Does raising replica_parallel_workers fix lag?
It depends on the stream, and the difference is large either way. On a MySQL 8.4.11 source and replica in containers on 2026-09-20, a backlog of 20,000 independent single-row transactions took 68.440, 72.959 and 75.158 seconds at four workers against 139.572, 198.640 and 139.279 at one, three runs of each interleaved. Two further four-worker runs against a different control read 59.517 and 65.040. The same setting bought nothing for a single large transaction, and nothing when the destination table had no primary key.
Is Seconds_Behind_Source reliable?
It is a coarse signal with two documented limits. The MySQL 8.4 manual says it is 0 when no event is currently being processed, so it drops to zero the sample after a large transaction commits; measured on a MySQL 8.4.11 replica in a container on 2026-09-20 it read 0, 0, 0, 7, 9, 11, 0, 0 across one such apply. The manual also says that on a multithreaded replica it is based on Exec_Source_Log_Pos and may not reflect the most recently committed transaction. Use it to notice lag and the per-worker table to explain it.
What does replica_preserve_commit_order cost?
It makes the replica commit in the same order as the source, which is what keeps a read on the replica consistent with a read on the source. The cost is that a worker that finishes early waits for the ones in front of it, so one slow transaction holds up the ones behind it even when they were applied in parallel.

Previous
Public key retrieval Next
Empty slow query log