innodb_flush_log_at_trx_commit参数

innodb_flush_log_at_trx_commit参数

简介

今天在工作中遇到了一个问题，就是某个服务器的从库由于磁盘问题，产生了延迟，而监控和报警没有发觉，没有报警提示，当我清理磁盘之后，发现一个问题，从库已经无法落后主库24个小时了，好的一点是主库的binlog文件都还在，但是从库应用relay-log的速度小于relay-log的生成速度，所以导致这个从库的SBM(second behind master)一直缓慢上升，想了半天没有好的办法，最终通过设置innodb_flush_log_at_trx_commit=0的方法暂时得到缓解。关于mysql中的这个参数，之前简单做过一些了解，今天看了下官方的手册，大概翻译如下：

当innodb_flush_log_at_trx_commit被 设置为0，日志缓冲每秒一次地被写到日志文件，并且对日志文件做到磁盘操作的刷新，但是在一个事务提交不做任何操作。

当这个值为1（默认值）之时，在每个事务提交时，日志缓冲被写到日志文件，对日志文件做到磁盘操作的刷新。当设置为2之时，在每个提交，日志缓冲被写到文件，但不对日志文件做到磁盘操作的刷新。尽管如此，在对日志文件的刷新在值为2的情况也每秒发生一次。我们必须注意到，因为进程安排问题，每秒一次的刷新不是100%保证每秒都发生。你可以通过设置这个值不为1来获得较好的性能，但随之你会在一次崩溃中损失二分之一价值的事务。如果你设置这个值为0，那么任何mysqld进程的崩溃会删除崩溃前最后一秒的事务。

如果你设置这个值为2，那么只有操作系统崩溃或掉电才会删除最后一秒的事务。尽管如此，InnoDB的崩溃恢复不受影响，而且因为这样崩溃恢复开始作用而不考虑这个值。注意，许多操作系统和一些磁盘硬件会欺骗刷新到磁盘操作。尽管刷新没有进行，你可以告诉mysqld刷新已经进行。即使设置这个值为1，事务的持久程度不被保证，且在最坏情况下掉电甚至会破坏InnoDB数据库。在SCSI磁盘控制器中，或在磁盘自身中，使用有后备电池的磁盘缓存会加速文件刷新并且使得操作更安全。你也可以试着使用Unix命令hdparm来在硬件缓存中禁止磁盘写缓存，或使用其它一些对硬件提供商专用的命令。这个选项的默认值是1。

上面这段文字看着比较绕口，翻译下就是：

innodb_flush_log_at_trx_commit 参数解释：

0（延迟写）：

log_buff --每隔1秒--> log_file —实时—> disk

1（实时写，实时刷）：

log_buff —实时—> log_file —实时—> disk

2（实时写，延迟刷）：

log_buff —实时—> log_file --每隔1秒--> disk

0：最快减少mysql写的等待

1：最大安全性,不会丢失数据

2:折中，减少操作系统文件写入等待时间

关于这个参数的详细试验，下次会专门用一篇文章来分析，敬请期待。