Innodb log buffer的flush

innodb为了保持事务的特性,需要redo log的支持,
即ib_logfile0,ib_logfile1这几个file(它的undo log是放在innodb的共享表空间的)
innodb向redo log进行write和flush操作.

注意,write和flush是两种不同的操作,write是向文件写入内容,此时内容可能只是在操作系统缓存中,flush才是真正把文件内容刷到硬盘上(有些情况下操作系统即使flush了也是flush到硬盘的缓存中,还没有真正到硬盘上.有些操作系统会报告flush成功,其实也没有进行flush).但是总之flush是一个非常昂贵的IO操作.

innodb向log file进行flush,是由参数innodb_flush_log_at_trx_commit控制的.
http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit


默认情况是1.即每一次commit导致一次log file的flush.这保证了最大的事务持久性(即ACID的D)
比如看下表和如下sp

show create table 20130228t1;
CREATE TABLE `20130228t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

show create PROCEDURE  sp1;
CREATE  PROCEDURE `sp1`()
BEGIN
DECLARE v_counter int DEFAULT 0;
 while v_counter<1000 do
  insert into 20130228t1 values(null,v_counter);
  commit;
  set v_counter:=v_counter+1;
 end while;

END

一开始在innodb_flush_log_at_trx_commit=1的时候执行该sp,观察Innodb_os_log_fsyncs变量的变化

show STATUS like 'Innodb_os_log_fsyncs';
执行前Innodb_os_log_fsyncs 为 5441,然后执行 call sp1();
执行后Innodb_os_log_fsyncs 为7447,也就是说执行了2000多次文件flush
插入这1000条数据,用了1分钟左右


下面把innodb_flush_log_at_trx_commit设为2
set global innodb_flush_log_at_trx_commit=2

执行前Innodb_os_log_fsyncs 为 7450,然后执行 call sp1();
执行后Innodb_os_log_fsyncs 为7452.
这次居然连1s都不到就执行完了.....
不过速度虽然快了,事务持久性不一定能保证了.这种情况下如果机器突然断电,很有可能会丢失一部分事务.


想到如果导入大量数据的情况下,如果减少log file的flush,是不是会更快呢.
比如load data infile的情况下.（见http://kabike.iteye.com/blog/1812206）
于是把innodb_log_buffer_size改为了16M(原来是8M),希望在有big transaction的情况下减少Innodb_os_log_fsyncs的次数.
不料这次效果提升并不显著.Innodb_os_log_fsyncs并没有明显减少.

原来innodb无论如何要每秒flush一次redo log
http://dev.mysql.com/doc/refman/5.1/en/innodb-tuning.html

引用

If you can afford the loss of some of the latest committed transactions if a crash occurs,
you can set the innodb_flush_log_at_trx_commit parameter to 0.
  InnoDB tries to flush the log once per second anyway,
  although the flush is not guaranteed.
  Also, set the value of innodb_support_xa to 0,
  which will reduce the number of disk flushes due to synchronizing on disk data and the binary log.

它这个每秒flush redo log的特性应该加到innodb_flush_log_at_trx_commit的说明里嘛.
要不大家怎么知道innodb_log_buffer_size设置过大也没有意义呢.