Binlog parallel flush

Prerequisites

• The instance runs one of the following database versions:

  • MySQL 8.4

  • MySQL 8.0 with a minor engine version of 20230930 or later.

    Note

    On the Basic Information page, look for the Upgrade Minor Engine Version button in the Configuration Information section. If this button is visible, you can click it to view the current version. If not, your instance is on the latest version. For more information, see Upgrade Minor Engine Version.

• The sync_binlog parameter for the instance is not set to 1.

Background

In MySQL, every transaction must write to the binary log during the commit stage. This is a serial process: a transaction can write to the binary log only after the preceding transaction finishes, as shown in the preceding figure.

This process is also time-consuming. Before writing to the binary log, all events stored in the binary log cache must be parsed, checksums and log positions must be populated, and a GTID event must be generated. Only then can these events be written to the binary log file. This serial and time-consuming process creates a significant bottleneck for instance write performance. To address this bottleneck, AliSQL includes the Binlog Parallel Flush optimization.

Optimization details

Binary log buffer

AliSQL enhances the standard logic by introducing a binary log buffer. After positions are allocated, multiple threads can write binary log events to the binary log buffer in parallel. A backend thread then writes the contents of the binary log buffer to the binary log file. This design allows previously serial steps—such as parsing, populating checksum and log position values, and generating GTID events—to execute in parallel. This significantly alleviates the performance bottleneck when writing binary logs during transaction commits.

Parallel group commit

In MySQL, transactions write to the binary log and redo log in groups during the commit stage. This technique, known as group commit, merges I/O operations to improve performance. This optimization retains the group commit concept and integrates it with the Binlog Parallel Flush feature, as illustrated in the following figure.

With the Binlog Parallel Flush optimization, each transaction serially allocates a GTID and space in the binary log buffer. Subsequently, multiple groups can write to the binary log buffer in parallel. After the redo log is persisted and the backend thread finishes writing to the binary log, the system commits the entire group of transactions.

Binary log persistence

With the Binlog Parallel Flush optimization, a backend thread periodically persists the binary log file. By default, this occurs once per second.

Parameters

loose_binlog_parallel_flush

This global system variable enables or disables the Binlog Parallel Flush feature. Valid values: on or off. Changes to this parameter take effect immediately without requiring an instance restart.

Performance impact

Test environment

The test compared performance across four different ApsaraDB RDS for MySQL instance specifications, as detailed in the following table.

Parameter settings

The test instance uses a high-performance parameter template with two performance-related parameters set to sync_binlog = 1000 and innodB_flush_log_at_trx_commit = 2.

Test script

The performance test used the oltp_update_non_index script from SysBench. The test dataset consisted of 100 tables, each with 100,000 rows.

Test results

The test results are shown in the following figure. Under high-concurrency workloads, the Binlog Parallel Flush feature delivers a significant performance improvement compared to the standard MySQL implementation. The peak performance gain is between 10% and 30%.