Background

MySQL-8.0 has made many improvements on InnoDB performance, one of the most important improvements is the improvement of redo log. MySQL-8.0 has redesigned Redo's write and flush process, so on MySQL-8.0, the write performance has been greatly improved. But the performance improved only when Binlog is off. When Binlog is on, the performance improvement is not obvious. For detailed performance comparison, you can visit the Dimitri blog. His article 《MySQL Performance: 8.0 RW & Binlog impact 》Compared the performance of binlog on and off.

Binlog is the foundation of MySQL's high availability and backup. In most scenarios, Binlog needs to be enabled. Therefore, AliSQL team has made a lot of effort on optimizing binlog performance. Hoping that when binlog is on, it will also have a good performance improvement.

IO Bottleneck In Commit Process

As shown in the figure above, there are two write IO operations during the transaction commit process. The first is redo's write operation, which persists the prepare state of the transaction. The second is the binlog write operation, which persists the binlog events of the transaction. This is a carefully designed process. The 2 write IO operations guarantees the data consistency between binlog and engine. But in the process of committing a transaction, the write IO is a relatively slow process, especially for network storage. Two IO write operations have a great impact on the performance of transaction commits.

So is it possible to remove one IO but not lose data consistency between binlog and engine? The answer is yes, and there are solutions for removing either Binlog Sync or Redo Sync.

Binlog In Redo

We finally chose to remove binlog sync. In this solution, binlog needs to be written to InnoDB redo log. Therefore it is called "Binlog In Redo". .

Design

• When committing a transaction, its binlog events is written into both redo and binlog file. But only redo log is flushed to storage. The Binlog file is synchronized to storage periodically by a separate thread. Therefore, one IO is reduced during the transaction commit process.
• Binlog events in binlog file may be lost when the host shutdown unexpected. During restart, the recovery process will copy the lost binlog events from redo log into binlog fie.
• This design keeps data consistency between binlog and engine while one write IO is reduced. The performance is improved and the latency becomes smaller. Since binlog is synchronized by a separate thread, it reduces the fsync call times of binlog significantly.

Performance

Test Environment

RDS Specifications: 32Core, 64G Ram, ESSD storage.
Test tool: sysbench

oltp_update_non_index

oltp_insert

oltp_write_only

Both olpt_update_non_index and oltp_insert are single-statement transactions. oltp_write_only is multi-statement transaction, including 2 UPDATE, one DELETE, and one INSERT. oltp_update_non_index and oltp_insert have more transaction commits than oltp_write_only, so oltp_update_non_index and oltp_insert has more performance improvement than oltp_write_only.

Binlog Fsync times comparison

When enabling the binlog in redo feature, the number of Binlog fsync is much less.

conclusion

Binlog in redo feature reduces one IO without losing reliability. In the cases of lower than 256 threads, the feature improves performance and reduces latency significantly. For most user scenarios, the performance improvement is very good.

The feature has been release in RDS-8.0 20200430, welcome to try it.