Command-Line Format | --innodb_flush_log_at_trx_commit[=#] | ||
System 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 |
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 up to a second of transactions in a crash.
-
The default value of 1 is required for full ACID compliance. With this value, the contents of the
InnoDB
log buffer are written out to the log file at each transaction commit and the log file is flushed to disk. -
With a value of 0, the contents of the
InnoDB
log buffer are written to the log file approximately once per second and the log file is flushed to disk. No writes from the log buffer to the log file are performed at transaction commit. Once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues. Because the flush to disk operation only occurs approximately once per second, you can lose up to a second of transactions with any mysqld process crash. -
With a value of 2, the contents of the
InnoDB
log buffer are written to the log file after each transaction commit and the log file is flushed to disk approximately once per second. Once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues. Because the flush to disk operation only occurs approximately once per second, you can lose up to a second of transactions in an operating system crash or a power outage. -
InnoDB
's crash recovery works regardless of the value. Transactions are either applied entirely or erased entirely.
For the greatest possible durability and consistency in a replication setup using InnoDB
with transactions, use innodb_flush_log_at_trx_commit=1
andsync_binlog=1
in your master server my.cnf
file.
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 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 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.