Shuo
Shuo I'm a DBA(Database Administrator), we can share and discuss MySQL, MongoDB, Redis and other databases here, also including learning Python, Shell, Golang together.

MySQL源码-变量-innodb_flush_log_at_trx_commit


一、innodb_flush_log_at_trx_commit含义

我们在实际使用中常用到的配置项,控制数据的ACID(atomicity, consistency, isolation, durability)属性与性能之间的平衡关系。

对于ACID的要求越高,则性能会降低,反之亦然。

参考:sysvar_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.

二、源码解释

本篇文章主要是展示innodb_flush_log_at_trx_commit在源码中的解释:

(1)innodb_flush_log_at_trx_commit=0

1
2
3
4
/* innodb_flush_log_at_trx_commit=0
    (write and sync once per second).
    Do not flush the redo log during binlog group commit. */
    

(2)innodb_flush_log_at_trx_commit=1

1
2
3
4
5
/* Flush the redo log buffer to the redo log file.
  Sync it to disc if we are in FLUSH LOGS, or if
 innodb_flush_log_at_trx_commit=1
  (write and sync at each commit). */
  

(3)定义

1
2
3
4
5
6
static MYSQL_SYSVAR_ULONG(flush_log_at_trx_commit, srv_flush_log_at_trx_commit,
                          PLUGIN_VAR_OPCMDARG,
                          "Set to 0 (write and flush once per second),"
                          " 1 (write and flush at each commit),"
                          " or 2 (write at commit, flush once per second).",
                          nullptr, nullptr, 1, 0, 2, 0);

三、说明

MySQL的写盘包括两个步骤,write和flush,其中:

(1)write:

将更新的数据写入到system buffer。

参考:[write-wikipedia](https://en.wikipedia.org/wiki/Write_(system_call)

(2)flush:

通常使用sync相关命令,将system buffer的数据刷到磁盘上。

参考:sync-linux

comments powered by Disqus