MySQL innodb_flush_log_at_trx_commit parameter


Setting innodb_flush_log_at_trx_commit in my.cnf file can have huge impact to the performance of your MySQL server. The impact of setting this parameter is stated below:

innodb_flush_log_at_trx_commit=X

  • Setting X to 2: causes the log buffer to be written to the log file after each commit, but file writes are flushed to disk approximately once a second. This is somewhat slower than a setting of 0. However the committed changes will not be lost if it is only the MySQL server that crashes and not the operating system or server host. In this scenario the machine continues to run, so the changes written to the log file are in the filesystem cache and eventually will be flushed normally.

  • Setting X to 1: changes are written from log buffer to the log file and the log file is flushed to disk for each commit. This guarantees that the changes will not be lost even in the event of a crash. This is the safest setting, and is also the required setting if you need ACID durability. It is also the slowest setting.

  • Setting X to 0: causes the log file to be written and flushed to disk approximately once a second, but not after each commit. on a busy system, this can reduce log-related disk activity significantly, but in the event of a crash can result in loss of about a second’s worth of commited changes.

  1. No comments yet.
(will not be published)
*