Controlling InnoDB Log Flushing

If you compare the insert speeds of InnoDB and MyISAM, sometimes there might be quite a large performce difference. Compared to MyISAM, InnoDB performs additional safeguards to guarantee data integrity. This results in overhead that impacts performance. You can influence the amount of overhead by using the following configuration option: innodb_flush_log_at_trx_commit.

The default value is set to 1. This means that at a transaction commit the log is flushed to disk, and the modifications made by the transaction become permanent and will survive a database crash.

If you are willing to compromise this safety, and you are running small transactions, you may set this to 0 or to 2 to reduce disk I/O to the logs.

If you set the value to 0, InnoDB only writes to the log file and flushes it to disk, approximately once per second, or when the log buffer becomes full.

If the value is 2, then InnoDB writes the log to the log file at each commit, but only flushes the log file to disk approximately once per second.

Starting from MySQL 4.0.13, the default value of this parameter is 1. Previously, the default was 0.