innodb_flusn_log_at_trx_commit和rsync_binlog参数详解

5645re

innodb_flush_log_at_trx_commit参数解析

innodb_flush_log_at_trx_commit=0|1|2   

#"0"表示每次事务提交都写入log buffer，但是每1秒将log buffer中所有更新写入log file，并将log file刷入磁盘（同时进行）。每次事务提交不会主动触发磁盘写入，只每秒定时刷入。

#"1"表示每次事务提交时，mysql都会把log buffer的数据写入log file，并flush data将更新的数据写入磁盘（安全性最高，效率最低，每次都要调用磁盘读写，等事务刷入完成后执行下一条，通常导致cpu使用不高，IO请求等待）（系统默认模式）

#"2"表示每次事务更新都会把log buffer的数据写入log file，但是每1秒执行一次flush data（将数据刷入磁盘）。

注意：  由于进程调度策略问题,这个“每秒执行一次 flush(刷到磁盘)操作”并不是保证100%的“每秒”

"0"速度最快，效率最高，充分利用cpu资源，不用等待IO，安全性最低（mysqld进程的崩溃，就会导致前一秒的所有事务丢失，存储在log buffer缓存中的数据丢失）

"2"既有速度，又相对安全，只有在操作系统崩溃或者crash断电情况下，上一秒的所有事务才可能丢失

"1"最安全的模式，但也是效率最低的。在操作系统崩溃或者断电crash，只可能丢失一个语句或则事务

1 2 3 4 5 6 7

下面我以一个简洁的方式解释一下innodb_flush_log_at_trx_commit在不同取值时的含义： innodb_flush_log_at_trx_commit=0 （延迟写、实时刷）：log_buffer --每隔1秒--> log_file --实时-----> disk innodb_flush_log_at_trx_commit=1 （实时写、实时刷）：log_buffer --实时-----> log_file --实时-----> disk innodb_flush_log_at_trx_commit=2 （实时写、延迟刷）：log_buffer --实时-----> log_file --每隔1秒--> disk 1、取值为0时，log buffer数据每隔1秒写入到日志文件（log file)，并且把log file刷新到磁盘.该模式下，事务提交时，不会主动触发写入磁盘的操作; 2、取值为1时，在每次事务提交时，MySQL会把log buffer的数据写入到日志文件（log file)，并且把log file刷新到磁盘; 3、取值为2时，在每次事务提交时，MySQL会把log buffer的数据写入到日志文件(log file）.但是刷新到磁盘的操作不会同时进行.该模式下，MySQL会每秒执行一次log file刷新到磁盘的操作.

------------------------------------------------------------------------------------------

rsync_binlog参数解析

rsync_binlog = 0|N

This makes MySQL synchronize the binary log’s contents to disk each time it commits a transaction

sync_binlog是MySQL的二进制日志（binlog）同步到磁盘的频率.
sync_binlog的默认值是0，此时binlog同步到磁盘中依赖于操作系统来刷新binlog到磁盘；
sync_binlog = N（N>0），MySQL每写N次binlog时，会使用fdatasync()函数将binlog同步到磁盘.

innodb_flush_log_at_trx_commit和rsync_binlog两个参数是控制mysql磁盘写入策略及数据安全性的关键参数，当两个参数都设置问1时，性能最差，推荐设置：

innodb_flush_log_at_trx_commit = 2;

rsync_binlog = 500 或者 1000;

两个参数设置不同值时的性能测试结果：

测试场景1
  innodb_flush_log_at_trx_commit=2
  sync_binlog=1000
测试场景2
  innodb_flush_log_at_trx_commit=1
  sync_binlog=1000
测试场景3
  innodb_flush_log_at_trx_commit=1
  sync_binlog=1
测试场景4
  innodb_flush_log_at_trx_commit=1
  sync_binlog=1000
测试场景5
  innodb_flush_log_at_trx_commit=2
  sync_binlog=1000

场景	TPS
场景1	41000
场景2	33000
场景3	26000
场景4	33000

由此可见，当两个参数设置为双1的时候，写入性能最差，sync_binlog=N (N>1) innodb_flush_log_at_trx_commit=2 时，(在当前模式下)MySQL的写操作才能达到最高性能。