innodb_flush_log_at_trx_commit 和 sync_binlog 参数解析

这两个参数和MySQL的一致性以及性能相关，默认配置大多数情况下不是最优的。一般来说，互联网线上系统的配置：

innodb_flush_log_at_trx_commit —— 0

sync_binlog —— 1000

一、innodb_flush_log_at_trx_commit 事务提交刷盘时机

如果我们想要提交一个事务了，会根据一定的策略把 redo 日志从 redo log buffer 刷入到磁盘文件里去。通过 innodb_flush_log_at_trx_commit 来配置的：

值为0 : 提交事务的时候，不立即把 redo log buffer 数据刷入磁盘文件，而是依靠 InnoDB 的主线程每秒执行一次刷新到磁盘。此时可能你提交事务了，结果 mysql 宕机了，然后此时内存里的数据全部丢失。

值为1 : 提交事务的时候，必须把 redo log 从内存刷入磁盘文件，只要事务提交成功，那么 redo log 就必然在磁盘里了。注意，因为操作系统的“延迟写”特性，此时的刷入只是写到了操作系统的缓冲区中，因此执行同步操作才能保证一定持久化到了硬盘中。

值为2 : 提交事务的时候，把 redo 日志写入磁盘文件对应的 os cache 缓存里去，而不是直接进入磁盘文件，可能 1 秒后才会把 os cache 里的数据写入到磁盘文件里去。

可以看到，只有1才能真正地保证事务的持久性，但是由于刷新操作 fsync() 是阻塞的，直到完成后才返回，写磁盘速度很慢，因此 MySQL 的性能会明显地下降。

如果不在乎事务丢失，0和2能获得更高的性能。

# 查询
select @@innodb_flush_log_at_trx_commit;

二、sync_binlog 控制着二进制日志写入磁盘的过程

该参数的有效值为0 、1、N：

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

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

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

将这个参数设为1以上的数值会提高数据库的性能，但同时会伴随数据丢失的风险。

二进制日志文件涉及到数据的恢复，以及想在主从之间获得最大的一致性，那么应该将该参数设置为1，但同时也会造成一定的性能损耗。

默认，sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。

对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

所以很多MySQL DBA设置的sync_binlog并不是最安全的1，而是100或者是0。这样牺牲一定的一致性，可以获得更高的并发和性能。