In order to control redo log Write strategy of ,InnoDB Provides innodb_flush_log_at_trx_commit Parameters , It has three possible values ：
Set to 0 When , It means that every time a transaction is submitted, it just puts redo log Remain redo log buffer in ; Call... Every second flush + fsync Brush to disk
Set to 1 When , Indicates that each time a transaction is committed, it will redo log Persist directly to disk ;
Set to 2 When , It means that every time a transaction is submitted, it just puts redo log writes page cache. Once a second flush( Brush to disk ) fuck do
InnoDB There is a background thread , every other 1 second , It will redo log buffer Log in , call write Write to the file system page cache, And then call fsync Persist to disk
write, It refers to writing logs to the file system page cache, It doesn't persist data to disk , So it's faster .
fsync, The operation of persisting data to disk . In general , We think fsync It's the disk IOPS
write and fsync The timing of , By parameters sync_binlog The control of the ：
sync_binlog=0 When , Indicates that every time a transaction is committed, only write, No fsync; The default value is It's up to the operating system fsync
sync_binlog=1 When , Indicates that every time a transaction is committed fsync;
sync_binlog=N(N>1) When , Indicates every transaction submitted write, But cumulatively N After a business fsync.
In the presence of IO In the bottleneck scene , take sync_binlog Set to a larger value , Can improve performance . In the actual business scenario , Considering the controllability of the amount of lost logs , It is generally not recommended to set this parameter to 0, It is more common to set it to 100~1000 Some value in .
however , take sync_binlog Set to N, The corresponding risk is ： If the host reboots abnormally , Will lose recent N A business binlog journal .