MySQL Binlog--事务日志和BINLOG落盘参数对磁盘IO的影响

本文涉及的产品
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: MySQL Binlog--事务日志和BINLOG落盘参数对磁盘IO的影响

mysql主从延迟,最近项目中遇到一个mysql 主从同步延迟非常严重的问题。

修改双0,立马下来。

 

innodb_flush_log_at_trx_commit这个参数关系到事务日志落地,

默认参数1 每一次事务提交或事务外的指令都需要把日志写入硬盘,io开销大

参数0 每隔一秒把log buffer刷到文件系统中去,并且调用文件系统的“flush”操作将缓存刷新到磁盘上去。这样的话,可能丢失1秒的事务数据。

参数 2 在每次事务提交的时候会把log buffer刷到文件系统中去,但是每隔一秒调用文件系统的“flush”操作将缓存刷新到磁盘上去。


这里解释下,系统文件和磁盘介质之间也有缓存,这里的0 是提交日志之后,每隔1秒将logbuffer刷到系统文件中,并马上执行落盘操作,写到到物理介质

2呢是刷到系统文件系统之后,每隔1秒调用flush,写到物理介质,并不是马上


两者的区别是,0:当mysql挂了之后,可能会损失前一秒的事务信息

2:当mysql挂了之后,如果系统文件系统没挂,不会有事务丢失

 

参数说明

innodb_flush_log_at_trx_commit和sync_binlog 两个参数是控制MySQL 磁盘写入策略以及数据安全性的关键参数,不同参数设置对磁盘IO影响不同。

 

参数innodb_flush_log_at_trx_commit


innodb_flush_log_at_trx_commit=0:每秒一次将Log Buffer中数据写入到Log File中,并且Flush到磁盘。事务提交不会主动触发写磁盘操作。
innodb_flush_log_at_trx_commit=1:每次事务提交时将Log Buffer数据写入到Log File中,并且Flush到磁盘。
innodb_flush_log_at_trx_commit=2:每次事务提交时将Log Buffer数据写入到Log File中,但不立即Flush到磁盘,MySQL会每秒一次刷新到磁盘。
由于进程调度问题,每条一次操作不能保证每一秒都执行一次。
当innodb_flush_log_at_trx_commit=0时,最近一秒的事务日志存在MySQL的Log Buffer中,无论时MySQL实例停止还是MySQL服务器宕机,都会导致最近一秒的事务日志丢失。
当innodb_flush_log_at_trx_commit=1时,最近一秒的事务日志存在操作系统的文件缓存中,MySQL实例停止不会导致事务日志丢失,但MySQL服务器宕机会导致最近一秒事务日志丢失。
上述的一秒一次刷新,取决于参数innodb_flush_log_at_timeout默认值为1,DDL或其他InnoDB内部操作并不受参数innodb_flush_log_at_trx_commit的限制。


图片来源于:https://www.h3399.cn/201809/614170.html

 

参数sync_binlog:

sync_binlog=0:每次事务提交后,将Binlog Cache中的数据写入到Binlog文件,但不立即刷新到磁盘。由文件系统(file system)决定何时刷新到磁盘中。
sync_binlog=N:每N次事务提交后,将Binlog Cache中的数据写入到Binlog文件,调用fdatasync()函数将数据刷新到磁盘中。
当sync_binlog=0(默认设置)时,不会执行强制刷盘指令,性能最好同时风险最高。
当sync_binlog=N时,当MySQL服务器宕机后,会导致最近N个事务的BINLOG丢失。

 

BINLOG和REDO/UNDO LOG的区别


1、处理层次不同,REDO/UNDO LOG由Innodb存储引擎处理,而BINLOG由MySQL 服务层处理。
2、记录内容不同,REDO/UNDO LOG记录的数据页的修改情况,REDO LOG采用物理日志+逻辑日志的方式存储,UNDO LOG采用逻辑日志方式存储,用于保证数据一致性;而BINLOG日志记录的事务操作的内容,用于主从复制。
3、记录时机不同,REDO/UNDO LOG在事务的执行过程中不断生成和写入,而BINLOG在事务最终COMMIT前写入。
4、涉及到数据更新的SELECT操作会被记录到BINLOG中(基于语句格式复制)。
5、BINLOG刷新到磁盘的行为由参数sync_binlog决定,而REDO LOG写入磁盘的行为受参数innodb_flush_log_at_trx_commit的影响。
6、可以通过会话级参数SQL_LOG_BIN来设置某事务不生成BINLOG,但不能通过参数控制是否生成事务日志。


 

某台从库存在严重的复制延迟,调整参数后性能变化:

1、主库(MySQL 5.5版本)凌晨2点开始清理历史数据。
2、从库(MySQL 5.7版本)开启多线程复制,但由于从库做多源复制且单线程复制(主库MySQL 5.5),磁盘使用率飙升至80%,出现复制延迟。
3、从库在08:35调整参数为双0,CPU使用率从3%提升至7%,磁盘使用率从75%降至8%,磁盘写次数从8500下降至2200,复制延迟开始下降。
4、从库在08:51复制无延迟,积压时间被消费完,CPU使用率恢复至1%,磁盘使用率从8%降至1%,磁盘写次数从2200下降至300。
5、从库在09:01调整参数为双1,CPU使用率无变化,磁盘使用率从1%升至8%,磁盘写次数从300升职2200。

 

IOPS测试

创建测试表:


## 创建测试表
CREATE TABLE `tb003` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `C1` int(11) DEFAULT NULL,
  `DT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


创建测试脚本:

#!/bin/bash
for i in $(seq 1 10000)  
do   
/export/servers/mysql/bin/mysql --host='127.0.0.1' --port=3358 --user='root' --password="root_psw" -e 'insert into db001.tb003(c1)values(0);' 1>/dev/nul 2>&1
done

运行测试脚本发现,每秒执行230个左右事务,使用iostat -dxk 1查看发现服务器每秒写1380,事务提交次数和每秒IO写次数的比约为1:6。

 

https://www.cnblogs.com/gaogao67/p/11023837.html


相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
13天前
|
存储 安全 关系型数据库
Mysql 的binlog日志的优缺点
MySQL的binlog(二进制日志)是一个记录数据库更改的日志文件,它包含了所有对数据库执行的更改操作,如INSERT、UPDATE和DELETE等。binlog的主要目的是复制和恢复。以下是binlog日志的优缺点: ### 优点: 1. **数据恢复**:当数据库出现意外故障或数据丢失时,可以利用binlog进行点恢复(point-in-time recovery),将数据恢复到某一特定时间点。 2. **主从复制**:binlog是实现MySQL主从复制功能的核心组件。主服务器将binlog中的事件发送到从服务器,从服务器再重放这些事件,从而实现数据的同步。 3. **审计**:b
|
22天前
|
SQL 关系型数据库 MySQL
mysql的binlog恢复数据
mysql的binlog恢复数据
24 0
|
23天前
|
SQL 关系型数据库 MySQL
MySQL数据库,可以使用二进制日志(binary log)进行时间点恢复
对于MySQL数据库,可以使用二进制日志(binary log)进行时间点恢复。二进制日志是MySQL中记录所有数据库更改操作的日志文件。要进行时间点恢复,您需要执行以下步骤: 1. 确保MySQL配置文件中启用了二进制日志功能。在配置文件(通常是my.cnf或my.ini)中找到以下行,并确保没有被注释掉: Copy code log_bin = /path/to/binary/log/file 2. 在需要进行恢复的时间点之前创建一个数据库备份。这将作为恢复的基准。 3. 找到您要恢复到的时间点的二进制日志文件和位置。可以通过执行以下命令来查看当前的二进制日志文件和位
|
2月前
|
存储 SQL 安全
浅谈MySQL Binlog
浅谈MySQL Binlog
45 0
|
2月前
|
缓存 运维 Linux
Linux系统调优详解(十二)——IO调优之磁盘测速
Linux系统调优详解(十二)——IO调优之磁盘测速
55 1
|
11天前
|
Linux
Linux操作系统调优相关工具(三)查看IO运行状态相关工具 查看哪个磁盘或分区最繁忙?
Linux操作系统调优相关工具(三)查看IO运行状态相关工具 查看哪个磁盘或分区最繁忙?
21 0
|
2月前
|
运维 Linux
Linux系统调优详解(五)——磁盘IO状态查看命令
Linux系统调优详解(五)——磁盘IO状态查看命令
49 5
|
2天前
|
Java 数据处理 索引
|
1月前
|
SQL 缓存 关系型数据库
MySQL的万字总结(缓存,索引,Explain,事务,redo日志等)
MySQL的万字总结(缓存,索引,Explain,事务,redo日志等)
65 0
|
2月前
|
存储 监控 关系型数据库
ELK架构监控MySQL慢日志
ELK架构监控MySQL慢日志