# Why is MySQL replication lagging with parallel replication enabled? > **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](https://dev.mysql.com/doc/refman/8.4/en/show-replica-status.html)) 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](https://dev.mysql.com/doc/refman/8.4/en/performance-schema-replication-applier-status-by-worker-table.html)) `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: ```sql SHOW REPLICA STATUS\G ``` Then check the per-worker table, which explains the lag. Sample it twice, a few seconds apart: ```sql 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: ```sql 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](https://dev.mysql.com/doc/refman/8.4/en/mysql-nutshell.html)) 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](https://dev.mysql.com/doc/refman/8.4/en/replication-options-binary-log.html)) 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](https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html)) 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](https://dev.mysql.com/doc/refman/8.4/en/show-replica-status.html)) 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. ## How DBGorilla helps Lag is a symptom whose cause is a statement, and the gap between the two is what makes this hard. DBGorilla connects read-only and gives your AI coding agent the workload the server actually ran, with average duration, invocations and rows per query fingerprint, plus connection and activity snapshots sampled continuously rather than read once. That is what turns "the replica was behind at four o'clock" into the statement that was running. Engine coverage is listed on the product pages. It surfaces and explains; it does not write to your database. [Get started free →](https://app.dbgorilla.com/signup) ## Related - [How to find slow queries in MySQL](https://www.dbgorilla.com/learn/mysql/how-to-find-slow-queries-in-mysql/) - [Why is the slow query log empty while users see latency?](https://www.dbgorilla.com/learn/mysql/why-is-the-slow-query-log-empty-while-users-see-latency/)