欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL 重要参数 innodb_flush_log_at_trx_commit 和 sync_binlog

程序员文章站 2022-09-28 08:36:07
innodb_flush_log_at_trx_commit 该参数控制重做日志写入磁盘的过程。我们知道 InnoDB 使用“Write Ahead Log”策略来避免数据丢失问题,即依靠重做日志来保证数据能在丢失后进行恢复。因此,InnoDB 重做日志的持久化非常重要。这个参数的默认值为1 首先需 ......

innodb_flush_log_at_trx_commit
该参数控制重做日志写入磁盘的过程。我们知道 innodb 使用“write ahead log”策略来避免数据丢失问题,即依靠重做日志来保证数据能在丢失后进行恢复。因此,innodb 重做日志的持久化非常重要。这个参数的默认值为1

首先需要大致了解一下mysql日志操作步骤:
log_buff --》 mysql写 (write) --》 log_file --》 os刷新 (flush) --》 disk

innodb_flush_log_at_trx_commit 参数解释:
0(延迟写): log_buff -- 每隔1秒 --》 log_file — 实时 --》 disk
1(实时写,实时刷): log_buff — 实时 --》 log_file -- 实时 --》 disk
2(实时写,延迟刷): log_buff — 实时 --》 log_file -- 每隔1秒 --》 disk

该参数的有效值有 0、1、2:

0:事务提交时,不将重做日志缓冲写入磁盘,而是依靠 innodb 的主线程每秒执行一次刷新到磁盘。因此如果 mysql 发生宕机,那么就有可能丢失一部分事务。

1:事务提交时,会将重做日志缓冲写入磁盘,并且立即刷新(fsync())。注意,因为操作系统的“延迟写”特性,此时的刷入只是写到了操作系统的缓冲区中,因此执行同步操作才能保证一定持久化到了硬盘中。

2:事务提交时,会将重做日志缓冲写入磁盘,但是不会立即进行刷新操作,因此只是写到了操作系统的缓冲区。此时若操作系统发生宕机而没有即使的同步,也可能会丢失一部分数据。

可以看到,只有1才能真正地保证事务的持久性,但是由于刷新操作 fsync() 是阻塞的,直到完成后才返回,我们知道写磁盘的速度是很慢的,因此 mysql 的性能会明显地下降。如果不在乎事务丢失,,0和2能获得更高的性能。

sync_binlog
该参数控制着二进制日志写入磁盘的过程。

该参数的有效值为0 、1、n:

0:默认值。事务提交后,将二进制日志从缓冲写入磁盘,但是不进行刷新操作(fsync()),此时只是写入了操作系统缓冲,若操作系统宕机则会丢失部分二进制日志。

1:事务提交后,将二进制文件写入磁盘并立即执行刷新操作,相当于是同步写入磁盘,不经过操作系统的缓存。

n:每写n次操作系统缓冲就执行一次刷新操作。

将这个参数设为1以上的数值会提高数据库的性能,但同时会伴随数据丢失的风险。
二进制日志文件涉及到数据的恢复,以及想在主从之间获得最大的一致性,那么应该将该参数设置为1,但同时也会造成一定的性能损耗。