ApsaraDB RDS for MySQL optimizes the lock-wait logic between I/O, SQL, and worker threads to eliminate replication latency caused by high-concurrency small transactions during peak hours.
Overview
Replication latency
Replication latency: When the primary instance applies transactions at a higher concurrency than the replica instance, the replica falls behind, causing replication latency. Even if you increase the number of worker threads or enable the Writeset feature, the replica may still be unable to keep up because its I/O or SQL thread reaches a throughput bottleneck.
I/O and SQL thread throughput bottlenecks: MySQL multi-threaded replication uses three types of threads: the I/O thread, the SQL thread, and multiple worker threads. The I/O thread retrieves binlog events from the primary instance. The SQL thread distributes these binlog events to the worker threads. The worker threads then apply the transactions in parallel.
The coordination between the I/O and SQL threads, and between the SQL and worker threads, relies on lock-protected wait and notify operations. Each binlog event requires one such lock-and-notify cycle. A single transaction consists of multiple binlog events. For example, in the common sysbench write_only test script, one transaction involves updating two rows, inserting one row, and deleting one row, which generates 11 binlog events. Under high concurrency, these locks frequently become a performance bottleneck, limiting the throughput of the I/O and SQL threads.
When the I/O or SQL thread hits a throughput bottleneck, worker threads do not receive transactions to apply promptly. If the apply concurrency of the worker threads on the replica instance is lower than the workload concurrency on the primary instance, replication latency occurs.
Small transaction batching optimization
AliSQL bundles the multiple binlog events of a small transaction into a single lock-and-notify cycle for the I/O and SQL threads. This significantly reduces the number of lock operations and increases the throughput of both threads.
Prerequisites
To use this optimization, your instance must meet the following requirement:
-
DB engine version: Your instance must run ApsaraDB RDS for MySQL 8.0 with a minor engine version of 20260228 or later. If your instance does not meet this requirement, you can Upgrade minor engine version or Upgrade DB engine version.
Procedure
You can enable this feature by configuring a global parameter on your primary or read-only instances:
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 Parameter settings.
-
On the Modifiable parameters tab, search for the
loose_replica_package_transaction_limit_sizeparameter and configure its value.-
Parameter description: A value of 0 disables this feature. When the value is greater than 0, transactions with a binlog size smaller than this value are eligible for this optimization.
-
Valid values: 0 to 1 MB.
-
Recommended value: 1 MB.
-
-
Click OK, and then click Submit parameters. In the dialog box, select an Effective period. The parameter change takes effect immediately and does not require an instance restart.
Optimization results
The following results were obtained by running the sysbench write_only script on the primary instance with 128 concurrent threads (80,000 TPS) over a 60-second peak write workload:
-
Before optimization: Replication latency on the replica instance continuously increased, and throughput was very low.
-
After optimization: The replica instance experienced no replication latency, and its throughput nearly doubled.
