Binlog In Redo

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 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.

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
6月前
|
关系型数据库 MySQL 数据库
Mysql数据库redo log及binlog的写入
Mysql数据库redo log及binlog的写入
|
30天前
|
SQL 存储 关系型数据库
美团面试:binlog、redo log、undo log的底层原理是什么?它们分别实现ACID的哪个特性?
老架构师尼恩在其读者交流群中分享了关于 MySQL 中 redo log、undo log 和 binlog 的面试题及其答案。这些问题涵盖了事务的 ACID 特性、日志的一致性问题、SQL 语句的执行流程等。尼恩详细解释了这些日志的作用、所在架构层级、日志形式、缓存机制以及写文件方式等内容。他还提供了多个面试题的详细解答,帮助读者系统化地掌握这些知识点,提升面试表现。此外,尼恩还推荐了《尼恩Java面试宝典PDF》和其他技术圣经系列PDF,帮助读者进一步巩固知识,实现“offer自由”。
美团面试:binlog、redo log、undo log的底层原理是什么?它们分别实现ACID的哪个特性?
|
1月前
|
存储 SQL 关系型数据库
面试官:你能聊聊 binlog、undo log、redo log 吗?
本文详细解析了MySQL数据库中的三种日志:binlog、undo log和redo log。binlog用于记录数据库的所有表结构变更及数据修改,支持归档、主从复制和数据恢复;undo log用于事务回滚,确保事务的原子性和实现多版本控制;redo log则用于crash-safe,确保数据库异常重启后已提交记录不丢失。文章通过实例和图表,深入浅出地介绍了每种日志的特点、应用场景及其实现机制。适合数据库开发者和运维人员阅读。
78 2
|
25天前
|
存储 关系型数据库 MySQL
MySQL中的Redo Log、Undo Log和Binlog:深入解析
【10月更文挑战第21天】在数据库管理系统中,日志是保障数据一致性和完整性的关键机制。MySQL作为一种广泛使用的关系型数据库管理系统,提供了多种日志类型来满足不同的需求。本文将详细介绍MySQL中的Redo Log、Undo Log和Binlog,从背景、业务场景、功能、底层实现原理、使用措施等方面进行详细分析,并通过Java代码示例展示如何与这些日志进行交互。
49 0
|
存储 SQL 缓存
【MySQL】change buffer,buffer pool,redo log,bin log,undo log的作用
【MySQL】change buffer,buffer pool,redo log,bin log,undo log的作用
138 0
|
6月前
|
监控 安全 数据库
Binlog vs. Redo Log:数据库日志的较劲【高级】
Binlog vs. Redo Log:数据库日志的较劲【高级】
142 0
|
6月前
|
存储 缓存 关系型数据库
Binlog vs. Redo Log:数据库日志的较劲【基础】
Binlog vs. Redo Log:数据库日志的较劲【基础】
445 0
|
存储 SQL 关系型数据库
MySQL中 redo log、undo log、bin log的理解
MySQL中 redo log、undo log、bin log的理解
104 1
|
存储 缓存 关系型数据库
MySQL中的日志(redo log、undo log、binlog)
MySQL中的日志(redo log、undo log、binlog)
113 0
|
SQL 存储 关系型数据库
MySQL学习笔记-redo log 和 binlog&两阶段提交
MySQL学习笔记-redo log 和 binlog&两阶段提交
96 0