There are 3 different configuration is available for innodb_flush_log_at_trx_commit
innodb_flush_log_at_trx_commit = 0
innodb_flush_log_at_trx_commit = 1
innodb_flush_log_at_trx_commit = 2
Which is the best configuration for innodb_flush_log_at_trx_commit?
what is the difference between this three configuration?
innodb_flush_log_at_trx_commit has default value of 1 - To disk on each transaction commit. is extremely expensive. Your applications can get incredible performance boost if you change the value to 2 - To OS cache on each transaction commit. To disk every second.
With a value of 2, only an operating system crash or a power outage can erase the last second of transactions. However, InnoDB's crash recovery is not affected and thus crash recovery does work regardless of the value.
