I turned innodb_flush_log_at_trx_commit = 2 and get a very fast write speed. But is it safe be used in production web site?
You can lose up to one second's worth of transactions. The default value is 1, which helps keep InnoDB ACID Compliant.
According to the MySQL Documentation on innodb_flush_log_at_trx_commit
Based on this, values other than 1 put InnoDB at risk of losing 1 second's worth of transactions, or a transaction commit's worth of data.
The documentation also says use
According to the MySQL Documentation on sync_binlog
Your safest choice is
If you do not mind possible data loss (up to 1 second's worth) then you can use either 0 or 2 at your own risk if the rewards (faster write speed) are worth it.
If the value of
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.
The default value of 1 is required for full ACID compliance. You can achieve better performance by setting the value different from 1, but then you can lose up to one second worth of transactions in a crash. With a value of 0, any mysqld process crash can erase the last second of transactions. With a value of 2, only an operating system crash or a power outage can erase the last second of transactions. InnoDB's crash recovery works regardless of the value.
In My opinion using
My opinion differs from other. innodb_flush_log_at_trx_commit = 0 if: it is my development computer or home mini database where is no sensitive data.
innodb_flush_log_at_trx_commit = 2 if: it is blog/stats/e-commerce (with ~100x shop in day), etc.
innodb_flush_log_at_trx_commit = 1 if: you have a lot of customers or you need to work with money transaction like bank. so this time you should split your dataflow between several servers to have speed & safety.
I prefer 2, because it has ~75x faster write speed and it fails ONLY if hardware fails.
Anyway you should know what you need more much more writing speed or up to 1 second information?