Docs

RDS for MySQL

Performance

Large transaction commit binlog optimization

This topic describes the large transaction commit binlog optimization feature provided by Volcano Engine RDS for MySQL.

Background

In MySQL, committing a large transaction often blocks write operations for the entire instance. This is because large transactions generate many binlog events, which are first stored in the current session's temporary cache and then copied to the binlog file upon commit. This process generates a large number of I/O operations and keeps the binlog locked, preventing other transactions from committing. Volcano Engine RDS for MySQL optimizes this situation. It first writes the binlog events generated by large transactions to a temporary file. Upon commit, instead of being copied, the temporary file is directly renamed to the official binlog file. This greatly improves the efficiency of large transaction commits and ensures that the submission of other transactions is not affected.

Supported version

MySQL 5.7 version, and the kernel version is MySQL 5.7.32_20250615 and above.

MySQL 8.0 version, and the kernel version is MySQL 8.0.32_20250615 and above.

MySQL 8.4, with a kernel minor version of MySQL 8.4.7_20260415 or later.

tip

You can view the kernel minor version of an instance by Viewing instance information. If your instance's kernel minor version is not supported, you can manually upgrade the instance kernel version.

Instructions

You can use the large transaction commit binlog optimization feature with the following parameters.

Parameter name	Type	Does it need instance restart to take effect?	Value range	Default value	Scope	Description
loose_enable_binlog_large_trans_opt	bool	No	[ON\|OFF]	OFF	Global	Specifies whether to enable the binlog optimization for large transaction commits.
loose_binlog_large_trans_threshold	integer	No	[20971520- 18446744073709551615]	268435456（256MB）	Global	Specifies the threshold for the size of a transaction's binlog event to be optimized as a large transaction. If the binlog event size of a transaction is larger than the value of this parameter, this transaction is treated as a large transaction and its binlog commit is optimized. The unit of this parameter is bytes.

Examples

On a MySQL 5.7 instance, run the following SQL to create a large table.

create table large_table1(id bigint not null auto_increment primary key, a varchar(10000));

insert into large_table1(a) select repeat('a', 10000);

// 不断 insert ... select ...

MySQL [test]> insert into large_table1(a) select a from large_table1;
Query OK, 65536 rows affected (8.439 sec)
Records: 65536 Duplicates: 0 Warnings: 0

// 继续 insert ... select ...

Confirm that the large transaction commit binlog optimization feature is disabled (the value of loose_enable_binlog_large_trans_opt must be OFF), and then run the following SQL and record the execution duration.

MySQL [test]> create table tmp like large_table1;
Query OK, 0 rows affected (0.006 sec)

MySQL [test]> begin;
Query OK, 0 rows affected (0.003 sec)

MySQL [test]> insert into tmp select * from large_table1 limit 672000;
Query OK, 672000 rows affected (2 min 0.550 sec)
Records: 672000 Duplicates: 0 Warnings: 0

MySQL [test]> commit;
Query OK, 0 rows affected (26.278 sec)

Before the transaction is committed, the binlog cache for the tmp table reaches 6.3 GiB; the transaction commit takes 26.278 seconds, during which other write transactions are completely blocked.

Modify the value of the loose_enable_binlog_large_trans_opt parameter to ON to enable the large transaction commit binlog optimization feature. Execute the SQL in step 2 again and record the execution duration.

MySQL [test]> create table tmp1 like large_table1;
Query OK, 0 rows affected (0.006 sec)

MySQL [test]> begin;
Query OK, 0 rows affected (0.003 sec)

MySQL [test]> insert into tmp1 select * from large_table1 limit 672000;
Query OK, 672000 rows affected (2 min 0.514 sec)
Records: 672000 Duplicates: 0 Warnings: 0

MySQL [test]> commit;
Query OK, 0 rows affected (0.008 sec)

After the binlog optimization for large transaction commits is enabled, the transaction commit time is 0.008 sec.

Reference information

The following data shows the commit times for transactions of different sizes on a 5.7 instance with the binlog optimization for large transaction commits enabled and disabled.

tip

The following data is for reference only and should not be considered as performance data for the binlog optimization for large transaction commits.

Transaction binlog size	Enable optimization	Disable optimization
800 MiB	0.138 sec	2.351 sec
1.6 GiB	0.130 sec	6.749 sec
3.2 GiB	0.097 sec	12.741 sec
6.4 GiB	0.165 sec	25.508 sec

Last updated: 2026.05.28 15:17:49