innodb_flush_log_at_trx_commit

-- innodb_flush_log_at_trx_commit

Controls the balance between strict  ACID compliance for  commit operations and higher performance that is possible when commit-related I/O operations are rearranged and done in batches. You can achieve better performance by changing the default value but then you can lose transactions in a crash.

可以透過將批次提交事務IO的方式來提高資料庫效能。 你可以透過修改innodb_flush_log_at_trx_commit的預設值來達到這一目的，但是當資料庫crash 的時候可能會丟事務。

• The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit.

innodb_flush_log_at_trx_commit 引數的預設值是1。只要事務一提交，就會將會log buffer進行刷盤。

• With a setting of 0, logs are written and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.

innodb_flush_log_at_trx_commit 設定為0時， log buffer就會每秒鐘進行刷盤。  當機器出現當機時，沒有刷盤的事務會丟失，也就是會丟失1秒鐘的事務。

• With a setting of 2, logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.

innodb_flush_log_at_trx_commit 設定為2時， mysql 先呼叫flush 將log buffer 重新整理到磁碟記憶體對映，每隔1s 將os cache中的資料重新整理到磁碟。

• For settings 0 and 2, once-per-second flushing is not 100% guaranteed. Flushing may occur more frequently due to DDL changes and other internal  InnoDB activities that cause logs to be flushed independently of the  innodb_flush_log_at_trx_commit setting, and sometimes less frequently due to scheduling issues. If logs are flushed once per second, up to one second of transactions can be lost in a crash. If logs are flushed more or less frequently than once per second, the amount of transactions that can be lost varies accordingly.

對於將innodb_flush_log_at_trx_commit 設定為0或2兩個引數都是每隔1s 進行落盤操作，但這是無法保證資料的完整性的。  刷盤的操作也受DDL操作和一些內部操作的影響。  

• Log flushing frequency is controlled by  innodb_flush_log_at_timeout, which allows you to set log flushing frequency to  N seconds (where  N is  1 ... 2700, with a default value of 1). However, any unexpected  mysqld process exit can erase up to  N seconds of transactions.

刷盤操作的頻率受innodb_flush_log_at_timeout 引數的控制。innodb_flush_log_at_timeout 的預設值為1， 可以將其設定為1 - 2700中的任意值。  

• DDL changes and other internal  InnoDB activities flush the log independently of the  innodb_flush_log_at_trx_commit setting.

• InnoDB  crash recovery works regardless of the  innodb_flush_log_at_trx_commit setting. Transactions are either applied entirely or erased entirely.

For durability and consistency in a replication setup that uses InnoDB with transactions:

For information on the combination of settings on a replica that is most resilient to unexpected halts, see  Section 16.3.2, “Handling an Unexpected Halt of a Replica”.

Many operating systems and some disk hardware fool the flush-to-disk operation. They may tell  mysqld that the flush has taken place, even though it has not. In this case, the durability of transactions is not guaranteed even with the recommended settings, and in the worst case, a power outage can corrupt InnoDB data. Using a battery-backed disk cache in the SCSI disk controller or in the disk itself speeds up file flushes, and makes the operation safer. You can also try to disable the caching of disk writes in hardware caches.

參考文件：

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit