Binlog In Redo-阿里云开发者社区

开发者社区> 数据库> 正文

Binlog In Redo

简介: Introduce the feature which persists binlog into redo on RDS-8.0

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

commit-process.png

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

design.png

  • 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

update-qps.png
update-latency.png

oltp_insert

insert-qps.png
insert-latency.png

oltp_write_only

write-only-qps.png
write-only-latency.png

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

fsync.png

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.

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章