Mysql Innodb performance tuning - innodb_flush_log_at_trx_commit

The official definition of innodb_flush_log_at_trx_commit is:

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 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.

Typically, most of Mysql experts ask us to set the value to 1, so we won't lose any data when system is crashed. But after reading more articles, I found the value "0" might be more suitable for me. The bad condition is I will lose 1 second data, but I will get lot of performance improvement. I don't care about the 1 second data losing if the select and insert actions can be faster than before. If you set the value to 0, please remember to monitor innodb_log_buffer_size. The default value is 1M, you might need to increase the buffer size when more data comes into buffer.

mysql> show status like 'innodb_log%';


+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 0 |
| Innodb_log_writes | 1 |
+---------------------------+-------+

If the Innodb_log_waits is close to Innodb_log_writes, you should increase innodb_log_buffer_size, for most time, 8M is big enough to handle all normal conditions.