AliSQL optimizes the Binlog Parallel Flush feature during the commit phase of binary logs to improve write performance. If you enable the feature after the optimization, the write performance of your ApsaraDB RDS instance is significantly improved.
Prerequisites
Your RDS instance runs MySQL 8.0.
Your RDS instance runs a minor engine version of 20230930 or later.
NoteTo view the minor engine version of your RDS instance, you can log on to the ApsaraDB RDS console and go to the Basic Information page. In the Configuration Information section, check whether the Upgrade Minor Engine Version button is displayed. If the button is displayed, you can click the button to view and update the minor engine version of your RDS instance. If the button is not displayed, your RDS instance runs the latest minor engine version. For more information, see Update the minor engine version.
The sync_binlog parameter of your RDS instance is not set to 1.
Background information
In MySQL, the system writes data to binary logs each time a transaction is committed. The writes are performed in sequence. The system writes data to the binary logs of one transaction after another.
The write process is time-consuming. Before the system writes data to binary logs, the system must parse all events stored in the binary log cache, assign values to the Checksum and log_pos parameters, and then generate global transaction identifier (GTID) events. The write process poses a significant bottleneck to the write performance of the RDS instance. To address the bottleneck, AliSQL optimizes the Binlog Parallel Flush feature.
Optimization details
Binary log buffer
AliSQL introduces a binary log buffer to implement the optimization. After multiple threads are allocated, the system can write binary log events to the binary log buffer in a parallel manner. Then, backend threads write the data in the binary log buffer to binary log files. This way, the system can perform the operations that must be completed in sequence in a parallel manner. The operations include assigning values to the Checksum and log_pos parameters and generating GTID events. This significantly improves the write performance when events are written to binary log files.
Group commit
In MySQL, the system writes data to binary log files and redo log files of different groups of transactions during the transaction commit. This helps merge I/O operations to the greatest extent and improve performance. After the optimization, the idea of group commit is retained. The following figure shows the Binlog Parallel Flush feature with group commit.
After the optimization, the system allocates GTIDs and binary log buffers for transactions one after another. Then, the system writes the binary log events of the group of transactions to the binary log buffers. After the redo log files are stored and the backend thread writes the data in the binary log buffers to binary log files, the group of transactions can be committed.
Persistence of binary logs
After the optimization, binary log files are persisted by a backend thread at regular intervals. By default, a binary log file is persisted once per second.
Parameter description
loose_binlog_parallel_flush
This parameter specifies whether to enable the Binlog Parallel Flush feature. This parameter is a global system variable. Valid values: on and off. If you change the value of this parameter, the change immediately takes effect. You do not need to restart your RDS instance.
Optimization effect
Test environment
The following table describes the feature optimization effect on ApsaraDB RDS for MySQLRDS instances that use different specifications.
Service | Version | CPU and memory | Storage type | Storage capacity |
ApsaraDB RDS for MySQL | MySQL 8.0 (minor engine version: 20230930) | 16 cores, 32 GB | ESSD PL1 | 1,000 GB |
16 cores, 32 GB | SSD | 1,000 GB | ||
64 cores, 128 GB | ESSD PL1 | 1,000 GB | ||
64 cores, 128 GB | SSD | 1,000 GB |
Parameter configuration
The RDS instance in the test uses a high-performance parameter template. In the template, the following performance-related parameter configuration is used: sync_binlog = 1000 and innodB_flush_log_at_trx_commit = 2.
Test script
The oltp_update_non_index script of Sysbench is used for the performance test. The test data contains 100 tables, and each table contains 100,000 rows.
Test results
The following figure shows the test results. Compared with native MySQL Normal Flush, Binlog Parallel Flush significantly improves the write performance of an RDS instance. The maximum improvement can reach 10% to 30%.
