RDS for MySQL optimizes parallel replication to eliminate replication lag during batch data processing. This feature is especially useful when you perform batch deletion, organization, or import operations during off-peak hours.
Applicability
Your database instance must meet one of the following version requirements:
-
RDS for MySQL 8.4
-
RDS for MySQL 8.0 with a minor engine version of 20251130 or later
Enablement
To enable this feature, set the following global parameter on the primary instance or a read-only instance.
-
Parameter name:
loose_optimize_replica_parallelism -
Value:
ON -
Effective: The setting takes effect immediately. No instance restart is required.
Background
Many applications perform data operations such as deletion, organization, and import during off-peak hours in small, high-concurrency batches. This approach is fast and allows you to adjust batch size and concurrency to control database impact.
During batch data processing, two main types of transactions coexist on the instance: medium-sized transactions from batch jobs, which typically process thousands of rows per transaction, and small transactions from regular business operations. Even during off-peak hours, some regular business traffic persists. These two transaction types are interleaved in the binlog file. If multiple small transactions that modify the same row are sandwiched between two medium-sized transactions, the two medium-sized transactions cannot execute concurrently.
As shown in the figure above, Trx2 and Trx5 are medium-sized transactions from a batch process. Trx3 and Trx4 are small transactions from regular business operations that modify the same row. When the SQL thread dispatches these transactions, Trx2 and Trx3 are dispatched normally. However, Trx4 must wait for Trx3 and all preceding transactions to commit before the SQL thread can dispatch it. This delays the dispatch of Trx5 until Trx2 commits, preventing Trx2 and Trx5 from being applied in parallel.
As a result, replica concurrency is very low during batch data processing. At times, only a single worker thread is active, which effectively reduces replication to single-threaded performance and causes significant lag.
Results
The following test uses batch data deletion to demonstrate the improvement. A single-threaded sysbench write-only script simulated small background transactions, while eight concurrent threads deleted data in 5,000-row batches. The figures above show the replication lag on a read-only instance. Before the feature was enabled, replica throughput was low and replication lag grew steadily. After the feature was enabled, throughput increased significantly and replication lag decreased until the replica caught up.
How it works
With this optimization, small conflicting transactions that appear between medium-sized ones wait in their worker threads for dependencies to commit, rather than waiting in the SQL thread. This prevents the SQL thread from blocking the dispatch of other transactions, so non-conflicting medium-sized transactions can execute in parallel.