innodb_flush_log_at_trx_commit 參數對效能的影響有多大

innodb_flush_log_at_trx_commit 

 innodb_flush_log_at_trx_commit

Command-Line Format	--innodb_flush_log_at_trx_commit[=#]
Option-File Format	innodb_flush_log_at_trx_commit
Option Sets Variable	Yes, innodb_flush_log_at_trx_commit
Variable Name	innodb_flush_log_at_trx_commit
Variable Scope	Global
Dynamic Variable	Yes
	Permitted Values
	Type	enumeration
	Default	1
	Valid Values	0 1 2

If the value of innodb_flush_log_at_trx_commit is 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit. When the value is 1 (the default), the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. When the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues.

先貼一段MysqlDOC上的介紹，該值有三個0，1，2 。

為0就是最簡單的，每秒把Logbuffer的內容寫到記錄檔，並且Flash到磁碟。

為1就是Commit的時候Flush到磁碟，這個是最安全的, 照理也是效能最差的。

為2就是每次Commit就從Logbuffer刷到記錄檔，並且每秒從往磁碟刷。

之前看到很多文檔和部落格都介紹說innodb_flush_log_at_trx_commit 這個參數對Innodb的效能來說是非常關鍵的，用腳想想也知道設成0 肯定比1要好的多，但是現在的機器都有BBU，在這種情況下，到底效能真的有提升麼，於是就簡單的測試了一下。

測試資料：45G。

BufferPool：8G，並發線程數為100。

測試是OLTP混合模式，類比微薄這種訪問請求，測試主要關注TPS表現。

結果，看起來都差不多，調為1的話效能略差。