MySQL之innodb_flush_log_at_trx_commit和sync_binlog

innodb_flush_log_at_trx_commit和sync_binlog 两个参数是控制MySQL 磁盘写入策略以及数据安全性的关键参数,它们的配置对于mysql 性能有很大的影响

一、innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit：是 InnoDB 引擎特有的，ib_logfile的刷新方式（ ib_logfile：存放InnoDB引擎的事务日志信息，含redo log和undo log的信息）

取值：0/1/2

• innodb_flush_log_at_trx_commit=0，表示每秒将log buffer写入os chache并刷新到磁盘。尚未刷新日志的事务可能会在崩溃中丢失。也就是说一秒之前的日志都保存在日志缓冲区，也就是内存上，如果机器宕掉，可能丢失1秒的事务数据。

• innodb_flush_log_at_trx_commit=1，表示在每次事务提交的时候，都把log buffer刷到文件系统中(os cache)去，并且调用文件系统的“flush”操作将缓存刷新到磁盘上去。这样的话，数据库对IO的要求就非常高了，如果底层的硬件提供的IOPS比较差，那么MySQL数据库的并发很快就会由于硬件IO的问题而无法提升。要完全符合ACID，必须使用默认设置1。

• innodb_flush_log_at_trx_commit=2，表示在每次事务提交的时候会把log buffer刷到OS cache中去并每秒刷新一次到磁盘。如果只是MySQL数据库挂掉了，由于文件系统没有问题，那么对应的事务数据并没有丢失。只有在数据库所在的主机操作系统损坏或者突然掉电的情况下，数据库的事务数据可能丢失1秒之类的事务数据。这样的好处，减少了事务数据丢失的概率，而对底层硬件的IO要求也没有那么高(log buffer写到文件系统中，一般只是从log buffer的内存转移的文件系统的内存缓存中，对底层IO没有压力)。

• 对于设置0和2，不能100％保证每秒刷新一次。因为DDL更改和其他内部InnoDB活动会导致日志的刷新独立于innodb_flush_log_at_trx_commit 设置，刷新可能会更频繁地发生。如果日志每秒刷新一次，则在崩溃中最多会丢失一秒钟的事务。如果日志刷新频率大于或小于每秒一次，则可能丢失的事务量会相应变化。

• 日志刷新频率由来控制 innodb_flush_log_at_timeout，可让您将日志刷新频率设置为 N秒（其中 N为1 … 2700，默认值为1）。但是，任何意外的mysqld进程退出都可能擦除多达N几秒钟的事务。
  

二、sync_binlog

sync_binlog：是MySQL 的二进制日志（binary log）同步到磁盘的频率。

取值：0-N

• sync_binlog=0，禁用MySQL服务器将二进制日志同步到磁盘的功能。取而代之的是，MySQL服务器依靠操作系统不时地将二进制日志刷新到磁盘上，就像处理任何其他文件一样。此设置可提供最佳性能，但是在电源故障或操作系统崩溃的情况下，服务器可能提交了尚未同步到二进制日志的事务。

• sync_binlog=1，在提交事务之前启用二进制日志到磁盘的同步。这是最安全的设置，但是由于磁盘写入次数增加，可能会对性能产生负面影响。如果发生电源故障或操作系统崩溃，二进制日志中缺少的事务将仅处于准备状态。这允许自动恢复例程回滚事务，从而确保二进制日志中不会丢失任何事务。

• sync_binlog=n，其中N的值代表事务量：在N次事务之后，二进制日志将同步到磁盘 。在电源故障或操作系统崩溃的情况下，服务器可能提交了尚未刷新到二进制日志的事务。由于磁盘写入次数的增加，此设置可能会对性能产生负面影响。较高的值可以提高性能，但会增加数据丢失的风险。

三、推荐配置组合

innodb_flush_log_at_trx_commit	sync_binlog	描述
1	1	适合数据安全性要求非常高，而且磁盘写入能力足够支持业务
1	0	适合数据安全性要求高，磁盘写入能力支持业务不足，允许备库落后或无复制。
2	0/N(0<N<100)	适合数据安全性要求低，允许丢失一点事务日志，允许复制延迟。
0	0	磁盘写能力有限，无复制或允许复制延迟较长。

总结：

• innodb_flush_log_at_trx_commit和sync_binlog两个参数设置为1的时候，安全性最高，写入性能最差。在mysqld服务崩溃或者服务器主机宕机的情况下，日志缓存区只有可能丢失最多一个语句或者一个事务。但是会导致频繁的磁盘写入操作，因此该模式也是最慢的一种方式。

• 当sync_binlog=N(N>1 )，innodb_flush_log_at_trx_commit=2时，在当前模式下MySQL的写操作才能达到最高性能。

Reference

• https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit
• https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_binlog_error_action
• https://support.huaweicloud.com/bestpractice-rds/rds_02_0010.html