binlog 写入 binlog cache 》事务提交后写入binlog files(文件系统的pagecache)》fsync 到disk
策略sync_binlog
redo log 也是先写入redo log buffer,根据策略刷入磁盘
策略 innodb_flush_log_at_trx_commit
当不是双1 的时候 redo log prapare后持久化磁盘失败,但是binlog记录成功,这时候备库同步数据的时候就会出现主备不一致的情况,此时
innodb_support_xa就是为了保证数据的一致性,默认开启,根据XID来对比redo log 和binlog 中的事务完整性
innodb_flush_log_at_trx_commit 为1 sync_binlog为1
组提交,是为了减少频繁的IO,redo log 在buffer中,先去写binlog 然后在fsync(拖时间)binlog也是如此
每个线程一个binlog cache > 事务提交写入binlog files(文件系统page cache)> fsync 到disk
其实,binlog 的写入逻辑比较简单:事务执行过程中,先把日志写到 binlog cache,事务提交的时候,再把 binlog cache 写到 binlog 文件中。
一个事务的 binlog 是不能被拆开的,因此不论这个事务多大,也要确保一次性写入。这就涉及到了 binlog cache 的保存问题。
系统给 binlog cache 分配了一片内存,每个线程一个,参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小,就要暂存到磁盘。
事务提交的时候,执行器把 binlog cache 里的完整事务写入到 binlog 中,并清空 binlog cache。状态如图 所示。
可以看到,每个线程有自己binlog cache,但是共用同一份bin log文件。
write写的是文件系统的page cache
fsync是有文件系统的page cache写入到磁盘
write 和fsync的时机,是由参数sync_binlog控制的:
因此,在出现 IO 瓶颈的场景里,将 sync_binlog 设置成一个比较大的值,可以提升性能。在实际的业务场景中,考虑到丢失日志量的可控性,一般不建议将这个参数设成 0,比较常见的是将其设置为 100~1000 中的某个数值。
但是,将 sync_binlog 设置为 N,对应的风险是:如果主机发生异常重启,会丢失最近 N 个事务的 binlog 日志。
redo log 刷新策略对比
innodb_flush_log_at_trx_commit:
原文链接
https://blog.csdn.net/zbszhangbosen/article/details/9132833
感谢大佬的分享,转载如有不妥,请联系删除!
官方文档解释
“EnablesInnoDBsupport for two-phase commit in XA transactions, causing an extra disk flush for transaction preparation. This set-ting is the default. The XA mechanism is used internally and is essential for any server that has its binary log turned on and is accepting changes to its data from more than one thread. If you turn it off, transactions can be written to the binary log in a different order from the one in which the live database is committing them. This can produce different data when the binary log is replayed in disaster recovery or on a replication slave. Do not turn it off on a replication master server unless you have an unusual setup where only one thread is able to change data.”
翻译结果
" EnablesInnoDBsupport在XA事务中提交两阶段,导致额外的磁盘刷新事务准备。这种设置是默认的。XA机制在内部使用,对于打开二进制日志并接受来自多个线程的数据更改的任何服务器都是必需的。如果关闭它,事务可以以不同于动态数据库提交事务的顺序写入二进制日志。当在灾难恢复或复制从属服务器上重播二进制日志时,可能会产生不同的数据。不要在复制主服务器上关闭它,除非您有一个不寻常的设置,其中只有一个线程能够更改数据。
innodb_support_xa的作用是分两类:
首先我们需要明白为什么需要保持binlog与redo log之间数据一致性,这里分两个方面来解释
大致意思就是,在事务提交的两阶段,写了redo log 的prepare,没写binlog,异常重启后,mysql回滚这条事务的时候也会记录binlog,然后从库用binlog同步,导致数据不一致
解决方法–内部xa事务
mysql处理事务流程
1. prepare,然后将redo log 持久化到磁盘
2. 如果prapare成功,那么在继续将事务日志持久化到binlog
3. 如果前面成功,那么在redo log里面写上一个commit记录
那么假如在进行着三步时又任何一步失败,crash recovery是怎么进行的呢?
是否开启bin_log
show variables like 'log_bin';
log_bin = /var/lib/mysql/bin-log
log_bin_index = /var/lib/mysql/mysql-bin.index
expire_logs_days = 7
server_id = 0002
binlog_format = ROW
log_bin = /var/lib/mysql/bin-log
开启 Binlog 并写明存放日志的位置;默认使用的设置是“log-bin=mysql-bin”,这样日志是存放在默认的位置上的,一般是放在data目录中。
log_bin_index = /var/lib/mysql/mysql-bin.index
指定索引文件的位置,每个bin-log都会有对应的index文件
expire_logs_days = 7
删除超出这个变量保留期之前的全部日志被删除
server_id = 0002
指定一个集群内的mysql服务器ID,如果做数据库集群那么必须全局唯一,一般来说不推荐 指定 server_id 等于 1。
max_binlog_size = 1G
单个二进制文件的最大值,如果超过该值,会产生新的二级制文件,后缀名+1,并记录到.index文件,默认值1G
binlog_cache_size = 32K
mysql 会为每个线程开启一个binlog cache,超过该值的时候回记录到临时文件中,默认32K
binlog_cache_disk_use
SHOW GLOBAL STATUS 查看 ,该参数是使用临时文件的次数 二进志日志缓存的已经存在硬盘的条数
binlog_cache_use
二进制日志已缓存的条数(内存中)
Binlog_stmt_cache_disk_use
(非事务类)二进志日志缓存的已经存在硬盘的条数
Binlog_stmt_cache_use
(非事务类)二进制日志已缓存的条数(内存中) 非事务型的语句,都存在这儿,比如MYISAM引擎的表,插入记录就存在这儿
max_binlog_cache_size
最大能有多少事务cache在内存中
binlog_do_db和binlog_ingore_db
是一对控制对哪些数据库进行收集的选项。示例:
binlog_do_db=fujie
binlog_do_db=fujieace
show master logs;
事务开始 =====> redo log prapare(落盘) ====> binlog =====>redo log commit
redo log buffer 里面的内容,是不是每次都同步到磁盘?
不需要
如果事务执行期间 MySQL 发生异常重启,那这部分日志就丢了。由于事务并没有提交,所以这时日志丢了也不会有损失。
事务未提交的时候,redo log buffer日志有没有可能被持久化到磁盘?
会有
redo log的三种状态
日志写到 redo log buffer 是很快的,wirte 到 page cache 也差不多,但是持久化到磁盘的速度就慢多了。
为了控制 redo log 的写入策略,InnoDB 提供了 innodb_flush_log_at_trx_commit 参数,它有三种可能取值:
注意,事务执行中间过程的 redo log 也是直接写在 redo log buffer 中的,这些 redo log 也会被后台线程一起持久化到磁盘。也就是说,一个没有提交的事务的 redo log,也是可能已经持久化到磁盘的。
如果把 innodb_flush_log_at_trx_commit 设置成 1,那么 redo log 在 prepare 阶段就要持久化一次,因为有一个崩溃恢复逻辑是要依赖于 prepare 的 redo log,再加上 binlog 来恢复的。
每秒一次后台轮询刷盘,再加上崩溃恢复这个逻辑,InnoDB 就认为 redo log 在 commit 的时候就不需要 fsync 了,只会 write 到文件系统的 page cache 中就够了。
通常我们说 MySQL 的“双 1”配置,指的就是 sync_binlog 和 innodb_flush_log_at_trx_commit 都设置成 1。也就是说,一个事务完整提交前,需要等待两次刷盘,一次是 redo log(prepare 阶段),一次是 binlog。
这意味着我从 MySQL 看到的 TPS 是每秒两万的话,每秒就会写四万次磁盘。但是,我用工具测试出来,磁盘能力也就两万左右,怎么能实现两万的 TPS?
解释这个问题,就要用到组提交(group commit)机制了。
日志逻辑序列号
日志逻辑序列号(log sequence number,LSN)的概念。LSN 是单调递增的,用来对应 redo log 的一个个写入点。每次写入长度为 length 的 redo log, LSN 的值就会加上 length。
LSN也会写到InnoDB的数据页中,来确保数据页不会被多次执行重复的redo log。
如图 所示,是三个并发事务 (trx1, trx2, trx3) 在 prepare 阶段,都写完 redo log buffer,持久化到磁盘的过程,对应的 LSN 分别是 50、120 和 160。
所以,一次组提交里面,组员越多,节约磁盘 IOPS 的效果越好。但如果只有单线程压测,那就只能老老实实地一个事务对应一次持久化操作了。
在并发更新场景下,第一个事务写完 redo log buffer 以后,接下来这个 fsync 越晚调用,组员可能越多,节约 IOPS 的效果就越好。
为了让一次 fsync 带的组员更多,MySQL 有一个很有趣的优化:拖时间。在介绍两阶段提交的时候,我曾经给你画了一个图,现在我把它截过来。
写 binlog”当成一个动作。但实际上,写 binlog 是分成两步的:
MySQL 为了让组提交的效果更好,把 redo log 做 fsync 的时间拖到了步骤 1 之后。也就是说,上面的图变成了这样:
这么一来,binlog 也可以组提交了。在上图 中第 4 步把 binlog fsync 到磁盘时,如果有多个事务的 binlog 已经写完了,也是一起持久化的,这样也可以减少 IOPS 的消耗。
不过通常情况下第 3 步执行得会很快,所以 binlog 的 write 和 fsync 间的间隔时间短,导致能集合到一起持久化的 binlog 比较少,因此 binlog 的组提交的效果通常不如 redo log 的效果那么好。
如果你想提升 binlog 组提交的效果,可以通过设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 来实现。
这两个条件是或的关系,也就是说只要有一个满足条件就会调用 fsync。
所以,当 binlog_group_commit_sync_delay 设置为 0 的时候,
binlog_group_commit_sync_no_delay_count 也无效了。
之前有同学在评论区问到,WAL 机制是减少磁盘写,可是每次提交事务都要写 redo log 和 binlog,这磁盘读写次数也没变少呀?
现在你就能理解了,WAL 机制主要得益于两个方面:
我不建议你把 innodb_flush_log_at_trx_commit 设置成 0。因为把这个参数设置成 0,表示 redo log 只保存在内存中,这样的话 MySQL 本身异常重启也会丢数据,风险太大。而 redo log 写到文件系统的 page cache 的速度也是很快的,所以将这个参数设置成 2 跟设置成 0 其实性能差不多,但这样做 MySQL 异常重启时就不会丢数据了,相比之下风险会更小。