The Binlog in Redo feature allows the system to synchronously write binary logs into the redo log file when transactions are committed. This reduces operations on disks and increases database performance.
Prerequisites
Your RDS instance runs MySQL 8.0 with a minor engine version of 20200430 or later.
Background information
To ensure data security in crucial MySQL business scenarios, the system stores both binary logs and redo logs when a transaction is committed. In this case, the following parameter settings must be configured:
sync_binlog = 1;
innodb_flush_log_at_trx_commit = 1;Each time a transaction is committed, the system performs two I/O operations. One is to write the binary logs to disks, and the other is to write the redo logs to disks. Although Group Commit is enabled for binary logs, the system must wait for the two I/O operations to complete. This affects the efficiency of transaction processing, especially when cloud disks are used. The performance of I/O merging varies based on the number of concurrent transactions that are committed. When the number of concurrent transactions is small, the performance is low. For example, when a small number of write transactions are committed, the system response is slow.
To increase the efficiency of transaction commitment, AliSQL provides the Binlog in Redo feature. You can enable the feature by setting the persist_binlog_to_redo parameter to on. When a transaction is committed, the system synchronously writes binary logs to the redo log file and stores only the redo log file to disks. This reduces I/O consumption. The binary log files are asynchronously stored to disks by using a separate thread at regular intervals. If a restart operation is triggered upon an exception, the system uses the binary logs in the redo log file to complement the binary log files. In this way, the database performance is improved and the system response is faster. In addition, the number of times that the binary log files are stored is reduced. This significantly relieves the pressure on the file system and increases performance. The pressure is resulted from the calls of the fsync function that are triggered by file updates in real time.
The Binlog in Redo feature does not change the format of binary logs. Replication and third-party tools that require binary logs are not affected.
Usage notes
If you enable the Binlog in Redo feature for your RDS instance that uses local disks and want to restore the data of the RDS instance to a self-managed database by using physical backup files, you must use the XtraBackup tool that is provided by ApsaraDB RDS. For more information, see Tool preparation.
Parameters
persist_binlog_to_redo
This parameter specifies whether to enable the Binlog in Redo 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.
NoteIf you want to enable the Binlog in Redo feature, you only need to set the
persist_binlog_to_redo parameter to on. You do not need to modify the settings of other parameters. The settingsync_binlog = 1automatically takes effect.sync_binlog_interval
The interval at which binary logs are asynchronously stored. This parameter is a global system variable. It takes effect only when the
persist_binlog_to_redo parameter is set to on. Default value: 50. Unit: milliseconds (ms). In most cases, the default value is recommended. If you change the value of this parameter, the change immediately takes effect. You do not need to restart your RDS instance.
Stress testing
Test environment
Application server: an Alibaba Cloud Elastic Compute Service (ECS) instance
RDS instance specifications: 32 CPU cores, 64 GB of memory, and enhanced SSDs (ESSDs)
RDS edition: RDS High-availability Edition with asynchronous data replication
Test cases
Sysbench provides the following test cases:
oltp_update_non_index
oltp_insert
oltp_write_only
Test results
oltp_update_non_index
After the Binlog in Redo feature is enabled, the queries per second (QPS) significantly increases and the latency is low when the number of concurrent queries is small.
oltp_insert
After the Binlog in Redo feature is enabled, the QPS significantly increases and the latency is low when the number of concurrent queries is small.
oltp_write_only
After the Binlog in Redo feature is enabled, the QPS slightly increases and the latency is low when the number of concurrent queries is small.
Number of times that the fsync function is called for binary logs
After the Binlog in Redo feature is enabled, the number of calls is significantly reduced.
Test conclusion
The oltp_update_non_index and oltp_insert test cases test single-statement transactions, and the transactions are committed on a frequent basis. The oltp_write_only test case tests multi-statement transactions, and the transactions are committed on a less frequent basis. This type of transaction contains two UPDATE statements, one DELETE statement, and one INSERT statement. Performance improvement in the oltp_update_non_index and oltp_insert test cases is more notable than that in the oltp_write_only test case.
When the number of concurrent transactions is less than 256, the Binlog in Redo feature significantly improves database performance and reduces latency. In most scenarios, the Binlog in Redo feature provides significant benefits.
The Binlog in Redo feature significantly reduces the number of times that the fsync function is called for binary logs. This improves the performance of the file system.