mysql落库参数多少时间刷盘_MySQL Binlog--事务日志和BINLOG落盘参数对磁盘IO的影响...

参数说明

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。

MySQL的undo/redo日志和binlog日志，以及2PC

发现自己的知识点有点散,今天就把它们连接起来,好好总结一下. 一.undo log.redo log.binlog的定义和对比   定义和作用                       所在架构层级 ...

mysql开启登录日志和sql日志 排查错误

首先看是否开启了日志 show global variables like '%general%'; set global general_log = on; // 打开 set global gen ...

MySQL 调优基础(四) Linux 磁盘IO

1. IO处理过程 磁盘IO经常会成为系统的一个瓶颈,特别是对于运行数据库的系统而言.数据从磁盘读取到内存,在到CPU缓存和寄存器,然后进行处理,最后写回磁盘,中间要经过很多的过程,下图是一个以wri ...

【MySQL】binlog_format以及binlog事务记录分析

MySQL官方对于binlog_format参数的说明: http://dev.mysql.com/doc/refman/5.5/en/binary-log-setting.html binlog_f ...

MySQL crash-safe replication(3): MySQL的Crash Safe和Binlog的关系

2016-12-23 17:29 宋利兵 作者:宋利兵 来源:MySQL代码研究(mysqlcode) 0.导读 本文重点介绍了InnoDB的crash safe和binlog之间的关系,以及2阶段提 ...

解说mysql之binlog日志以及利用binlog日志恢复数据

众所周知,binlog日志对于mysql数据库来说是十分重要的.在数据丢失的紧急情况下,我们往往会想到用binlog日志功能进行数据恢复(定时全备份+binlog日志恢复增量数据部分),化险为夷! 废 ...

MySQL和Mariadb二进制日志binlog详解

Mariadb/mysql提供了4中不同的日志,分别是错误日志(error.log).普通日志(general log).慢日志(slow log)以及二进制日志(binlog).错误日志记录了系统启 ...

MYSQL数据库日志和mysqlbinlog相关

mysql有4种不同的日志,分别是二进制日志,查询日志,慢查询日志和错误日志,这些日记记录着数据库工作的方方面面,可以帮助我们了解数据库的不同方面的踪迹,下面介绍二进制日志的作用和使用方法. 1.二进 ...

在MySQL中使用init-connect与binlog来实现用户操作追踪记录

在MySQL中使用init-connect与binlog来实现用户操作追踪记录 分类: MySQL 前言: 测试环境莫名其妙有几条重要数据被删除了,由于在binlog里面只看到是公用账号删除的,无法查 ...

随机推荐

Android基础：startActivityForResult 和 onActivityResult 问题

项目中用到弹出Acitivity来获得用户输入 所以用到 onActivityResult()方法接受用户输入 奇怪问题 startActivityForResult() 后直接调用 onActivi ...

word - 如何让 图片任意移动

选中图片, 设置图片的自动换行  为四周环绕型

WebApp远程调试工具

针对存在内嵌在客户端的webview项目中,在开发过程中我们希望能像PC上一下直接进行调试,提高我们平时的开发效率,这时候我们可以使用webApp远程调试工具-weinre. 安装: 本工具需要nod ...

【EF学习笔记03】----------使用原生Sql语句

在EF中使用原生SQL,首先要创建上下文对象 using (var db = new Entities()) { //数据操作 } 新增 string sql = "insert into ...

UDP广域网，局域网通信-原理分析，穿透技术

一.UDP局域网通信. 这个比较简单,关于局域网中的2台或者更多的计算机之间的UDP通信,网络上一大把,直接复制粘贴就可以使用,原理也非常简单.所以,本文不做详细介绍. 二.UDP广域通信(包括路由器 ...

Z-Stack内部API 小结

Z-Stack是TI推出的全功能ZigBee协议栈,通过了ZigBee联盟的兼容性平台测试,包含如下几个组件. 1. HAL,硬件抽象层 2. OSAL,操作系统抽象层 3. ZigBee Stack ...

Java反射结合JDBC写的一个通用DAO

以前写反射只是用在了与设计模式的结合上,并没有考虑到反射可以与DAO结合.也是一个偶然的机会,被正在上培训的老师点到这个问题,才考虑到这个可能性,于是上网参考各种代码,然后自己动手开发了一个通用DAO ...

浅尝key-value数据库(一)——一览NoSQL

浅尝key-value数据库(一)——一览NoSQL 最近由于一个项目的关系,研究了一下key-value数据库这个最近很火的概念.本系列从项目需求的角度分析并测试了几个key-value数据库的性能 ...

Linux系统目录

[root@localhost ~]# ls /bin dev home lost+found misc opt root selinux sys usrboot etc lib media net ...

CSRF(Cross Site Request Forgery, 跨站域请求伪造)

CSRF(Cross Site Request Forgery, 跨站域请求伪造) CSRF 背景与介绍 CSRF(Cross Site Request Forgery, 跨站域请求伪造)是一种网络的 ...