MySQL参数:innodb_flush_log_at_trx_commit 和 sync_binlog
innodb_flush_log_at_trx_commit和sync_binlog参数详解
“innodb_flush_log_at_trx_commit”和“sync_binlog”两个参数是控制MySQL磁盘写入策略以及数据安全性的关键参数。当两个参数为不同值时,在性能,安全角度下会产生不同的影响。
innodb_flush_log_at_trx_commi | sync_binlog | 描述 |
---|---|---|
1 | 1 | 这就是所谓的双1设置:这种配置适合数据安全性要求非常高,而且磁盘IO写能力足够支持业务,比如充值消费系统,银行业务 |
1 | `0 | 这种设置:保证了事务日志是全的,也就保证可以实例恢复,即前滚和回滚,适合数据安全性要求高,磁盘IO写能力不太富余 |
2 | 0/N(0<N<100) | 这种设置:适合数据安全性有要求,允许丢失一点事务日志 |
0 | 0 | 这种配置适合 :磁盘IO写能力有限,对数据安全要求较低,例如:日志性登记业务 |
1.innodb_flush_log_at_trx_commit
redo log 的刷盘策略
0:日志缓存区将每隔一秒写到日志文件中,并且将日志文件的数据刷新到磁盘上。该模式下在事务提交时不会主动触发写入磁盘的操作。
1:每次事务提交时MySQL都会把日志缓存区的数据写入日志文件中,并且刷新到磁盘中,该模式为系统默认。
2:每次事务提交时MySQL都会把日志缓存区的数据写入日志文件中,但是并不会同时刷新到磁盘上。该模式下,MySQL会每秒执行一次刷新磁盘操作。
说明:
当设置为0,该模式速度最快,但不太安全,mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失;
当设置为1,该模式是最安全的,但也是最慢的一种方式。在mysqld服务崩溃或者服务器主机宕机的情况下,日志缓存区只有可能丢失最多一个语句或者一个事务;
当设置为2,该模式速度较快,较取值为0情况下更安全,只有在操作系统崩溃或者系统断电的情况下,上一秒钟所有事务数据才可能丢失;
2.sync_binlog
binlog 的刷盘策略
0:log buffer 每秒写入日志文件 log file 并刷新 flush 到磁盘。这种情况下,mysql 的日志刷写操作和事务提交操作没有关系。因此 mysql 的性能是最好的时刻。不过不安全
1:每次事务提交时,log buffer 会被写入到日志文件并且还要刷写到磁盘上。由于每次事务都要提交到I/O设备,因此会慢一点,不过是最安全的。
2:0和1的中间效果,即每次的事务提交会写入 log buffer,而刷写到磁盘则是一秒进行一次。性能属于一般。
说明:
“innodb_flush_log_at_trx_commit”和“sync_binlog”两个参数设置为1的时候,安全性最高,写入性能最差。在mysqld服务崩溃或者服务器主机宕机的情况下,日志缓存区只有可能丢失最多一个语句或者一个事务。但是会导致频繁的磁盘写入操作,因此该模式也是最慢的一种方式。
当sync_binlog=N(N>1 ),innodb_flush_log_at_trx_commit=2时,在当前模式下MySQL的写操作才能达到最高性能,秒杀或者高并发场景下推荐使用innodb_flush_log_at_trx_commit=2 ,sync_binlog=N (N为500 或1000)。
innodb_flush_log_at_trx_commit和sync_binlog查看、修改
1.查看命令
show variables like ‘innodb_flush_log_at_trx_commit’;
show variables like ‘sync_binlog’;
2.修改命令
set global sync_binlog = 0;
set global innodb_flush_log_at_trx_commit = 0;