Is it safe to use innodb_flush_log_at_trx_commit = 2

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


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.


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.


For the greatest possible durability and consistency in a replication
setup using InnoDB with transactions, use
innodb_flush_log_at_trx_commit=1 and sync_binlog=1 in your master
server my.cnf file.


Caution


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. Then the durability of transactions is not
guaranteed even with the setting 1, and in the worst case a power
outage can even corrupt the InnoDB database. 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 using
the Unix command hdparm to disable the caching of disk writes in
hardware caches, or use some other command specific to the hardware
vendor.

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 sync_binlog=1.

According to the MySQL Documentation on sync_binlog


A value of 1 is the safest choice because in the event of a crash you
lose at most one statement or transaction from the binary log.
However, it is also the slowest choice (unless the disk has a
battery-backed cache, which makes synchronization very fast).

Your safest choice is

[mysqld]
innodb_flush_log_at_trx_commit=1
sync_binlog=1

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.

StackExchange Database Administrators Q#12611, answer score: 68