Optimize replication latency for large transactions - ApsaraDB RDS

ApsaraDB RDS for MySQL uses logical replication, where binlog events are sent from the primary instance to a replica after a transaction commits. Large transactions can cause significant replication latency because the replica applies them only after the primary instance commits. The real-time application feature for large transactions allows the replica to start executing a large transaction while it is still running on the primary instance, nearly eliminating the latency and ensuring high availability.

How it works

Replication latency for large transactions (left diagram): In MySQL, the replica begins to apply a large transaction only after it is committed on the primary instance. The transaction's execution time on the replica becomes the replication latency, during which other committed transactions cannot be applied, delaying data updates.

Real-time application optimization for large transactions (right diagram): The large transaction executes on the primary instance and replica simultaneously. When the transaction is committed or rolled back on the primary instance, the same operation occurs on the replica.

Prerequisites

To use the real-time application feature for large transactions, your instance must meet the following requirement:

Database engine version: MySQL 8.0 with a minor engine version of 20260228 or later. If your instance does not meet this requirement, you can upgrade the minor engine version or upgrade the database engine version.

Procedure

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the left-side navigation pane, click Parameters.

On the Editable Parameters tab, search for and configure the following parameters:

Enable the real-time application feature for large transactions:
1. Set loose_binlog_realtime_apply_long_trx_enabled to ON.
2. Set loose_binlog_realtime_transmit_source_enabled to ON.
3. Set loose_binlog_realtime_transmit_replica_enabled to ON.

Other required parameters:

Parameter	Description	Recommended value
`loose_binlog_realtime_replica_long_transaction_limit_size`	The size threshold for triggering real-time replication. When binlog events generated by a transaction exceed this value, the feature is automatically activated.	64 MB
`loose_binlog_realtime_enable_log_messag`	Specifies whether to write real-time replication logs to the error log.	`ON`
loose_binlog_realtime_apply_workers	The number of Brr Worker threads for real-time replication.	8

Click OK, and then click Submit Parameters. In the dialog box that appears, choose when to apply the changes. Parameter changes take effect immediately without requiring an instance restart.

Optimization results

The following example demonstrates the effect of this feature on a data import transaction involving 5 million rows:

Before optimization: The transaction executes on the primary instance first. After it is committed, it is applied on the replica. The replication latency equals the transaction's execution time.
After optimization: The transaction executes on the primary instance and the replica simultaneously, eliminating replication latency.