The Binlog in Redo function synchronously writes binary logs to the redo log file when a transaction is committed. This reduces operations on disks and improves database performance.

Prerequisites

Your RDS instance runs MySQL 8.0 (with a kernel version of 20200430 or later).

Background information

To ensure data security in crucial MySQL business scenarios, the system stores both binary and redo logs when a transaction is committed. Both the following parameters must be set to 1: 
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 still wait for the two I/O operations to complete. This affects the efficiency of transaction processing, especially when standard or enhanced SSDs are used. The performance of I/O merging is based on the number of concurrent transactions that are committed at the same time. 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 committing transactions, AliSQL provides the Binlog in Redo function. You can enable the function 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 then 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 improves and the system response is faster. Also, the number of times that the binary log files are stored is reduced. This significantly relieves the pressure on the file system while increasing performance. This pressure can be resulted from the calls of the fsync functions that are triggered by file updates in real time. The fsync function synchronizes files to disks.

Binlog in Redo does not change the format of binary logs. Replication and third-party tools that are based on binary logs are not affected.

Parameters

  • persist_binlog_to_redo

    The switch that is used to enable or disable the Binlog in Redo function. This parameter is a global system variable. Valid values: on and off. The parameter change immediately takes effect. You do not need to restart your RDS instance.

    Note If you want to enable Binlog in Redo, 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 normal cases, the default value is recommended. The parameter change immediately takes effect. You do not need to restart your RDS instance.

Stress testing

  • Test environment
    • Application server: an Alibaba Cloud ECS instance
    • RDS instance type: 32 CPU cores, 64 GB of memory, and enhanced SSDs
    • RDS edition: 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 Binlog in Redo 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 Binlog in Redo 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 Binlog in Redo 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 Binlog in Redo is enabled, the number of times is significantly reduced.

      Binlog Fsync

Test conclusion

  • oltp_update_non_index and oltp_insert test single-statement transactions, and the transactions are committed on a frequent basis. oltp_write_only 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 oltp_update_non_index and oltp_insert is more notable than that in oltp_write_only.
  • When the number of concurrent transactions is less than 256, Binlog in Redo significantly improves database performance and reduces latency. In most scenarios, Binlog in Redo provides significant benefits.
  • Binlog in Redo significantly reduces the number of times that the fsync function is called for binary logs. This improves the performance of the file system.