• MySQL知识库 :: innodb
  • InnoDB log buffer flushes to disk after each transaction

  • Description

    InnoDB will regularly flush logs to the disk. It's possible to choose when InnoDB will do this. If innodb_flush_log_at_trx_commit is set to 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. However, nothing is done at a transaction commit. When this 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 set to 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it.

    Default value of innodb_flush_log_at_trx_commit is 1 which is required for ACID compliance. You can achieve better performance by setting the value to something other than 1. However, you could lose at much as one second of transactions in a crash. This may be significant on a very large and active server. There are several situations when performance is more important than data safety. For example, if you running slave server which can restore all data from the master in case of failure. Otherwise, if every transaction is critical it may be better to use default value of 1 for the innodb_flush_log_at_trx_commit variable. The reduction of disk writes will increase performance.

    Best Practice

    Before setting the system variable innodb_flush_log_at_trx_commit, look to see it's current value by running the SHOW VARIABLES statement:

    SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
    
    +--------------------------------+-------+
    | Variable_name                  | Value |
    +--------------------------------+-------+
    | innodb_flush_log_at_trx_commit | 1     | 
    +--------------------------------+-------+

    If you set innodb_flush_log_at_trx_commit to 0, then any mysqld process crash can erase the last second of transactions. If you set the value to 2, though, then 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. For many systems, therefore, the best practice would be to set innodb_flush_log_at_trx_commit to a value of 2.

    You can set the innodb_flush_log_at_trx_commit variable with the SET statement of in the server's options file (i.e., my.cnf or my.ini, depending on the system). Below is an example of how you might set it while logged into the server:

    SET GLOBAL innodb_flush_log_at_trx_commit  = 2;