首页 > 数据库 > MySQL > innodb_flush_log_at_trx_commit


翻译 MySQL 作者:kakaxi9521 时间:2021-07-15 10:37:23 0 删除 编辑

-- innodb_flush_log_at_trx_commit

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 transactions in a crash.

可以通过将批量提交事务IO的方式来提高数据库性能。 你可以通过修改innodb_flush_log_at_trx_commit的默认值来达到这一目的,但是当数据库crash 的时候可能会丢事务。

  • The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit.

innodb_flush_log_at_trx_commit 参数的默认值是1。只要事务一提交,就会将会log buffer进行刷盘。

  • With a setting of 0, logs are written and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.

innodb_flush_log_at_trx_commit 设置为0时, log buffer就会每秒钟进行刷盘。  当机器出现宕机时,没有刷盘的事务会丢失,也就是会丢失1秒钟的事务。

  • With a setting of 2, logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.

innodb_flush_log_at_trx_commit 设置为2时, mysql 先调用flush 将log buffer 刷新到磁盘内存映射,每隔1s 将os cache中的数据刷新到磁盘。

  • For settings 0 and 2, once-per-second flushing is not 100% guaranteed. Flushing may occur more frequently due to DDL changes and other internal  InnoDB activities that cause logs to be flushed independently of the  innodb_flush_log_at_trx_commit setting, and sometimes less frequently due to scheduling issues. If logs are flushed once per second, up to one second of transactions can be lost in a crash. If logs are flushed more or less frequently than once per second, the amount of transactions that can be lost varies accordingly.

对于将innodb_flush_log_at_trx_commit 设置为0或2两个参数都是每隔1s 进行落盘操作,但这是无法保证数据的完整性的。  刷盘的操作也受DDL操作和一些内部操作的影响。  

  • Log flushing frequency is controlled by  innodb_flush_log_at_timeout, which allows you to set log flushing frequency to  N seconds (where  N is  1 ... 2700, with a default value of 1). However, any unexpected  mysqld process exit can erase up to  N seconds of transactions.

刷盘操作的频率受innodb_flush_log_at_timeout 参数的控制。innodb_flush_log_at_timeout 的默认值为1, 可以将其设置为1 - 2700中的任意值。  

For durability and consistency in a replication setup that uses InnoDB with transactions:

为了保证复制的持久性和一致性,如果sync_binlog参数设置为1, 那么innodb_flush_log_at_trx_commit参数也要设置为1.

For information on the combination of settings on a replica that is most resilient to unexpected halts, see  Section 16.3.2, “Handling an Unexpected Halt of a Replica”.


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. In this case, the durability of transactions is not guaranteed even with the recommended settings, and in the worst case, a power outage can 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 to disable the caching of disk writes in hardware caches.


来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

上一篇: sql_slave_skip_counter
请登录后发表评论 登录


  • 博文量
  • 访问量