All Products
Search
Document Center

ApsaraDB RDS:Use the Binlog in Redo feature

Last Updated:Oct 27, 2023

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.

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.

    Note

    If 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 setting sync_binlog = 1 automatically becomes invalid.

  • 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_update_non_index-QPSoltp_update_non_index-laterncy

    • 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_insert-QPSoltp_insert-latency

    • 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.

      oltp_write_only-QPSoltp_write_only-latency

    • 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.

      Binlog Fsync

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.