MySQL日志双一配置分析实战

mysql双一配配置分析

mysql双一配置主要是binlog和redolog的落盘时间的
binlog的write 和 fsync 的时机，是由参数 sync_binlog 控制的：
sync_binlog=0的时候，表示每次提交事务都只 write，不 fsync；
sync_binlog=1的时候，表示每次提交事务都会执行 fsync；sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

write是指将日志文件写入到文件系统的page cache，并没有持久化到磁盘，速度很快
fsync是指将数据持久化到磁盘的操作，一般情况下我们认为fsync才占磁盘的IOPS

redo log 的写入策略是由 innodb_flush_log_at_trx_commit参数控制的，它有三种可能取值：

设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;
设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；
设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。
InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。

准备数据

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000) do
    insert into t values(i,i);
    set i=i+1;
  end while;
end;;
delimiter ;

mysql默认sync_binlog为1， innodb_flush_log_at_trx_commit 也是1，也就是双一配置。也就是说在mysql异常宕机的时候不会出现任何数据的丢失。

设置环境

set global sync_binlog=1;
set global innodb_flush_log_at_trx_commit=1;

生成新的binlog文件

flush logs;

执行存储过程

call idata();

执行时间为22分钟

mysql> call idata();
Query OK, 1 row affected (22 min 8.30 sec)

将sync_binlog设为0，innodb_flush_log_at_trx_commit设为2

set global sync_binlog=0;
set global innodb_flush_log_at_trx_commit=2;

再次执行存储过程

truncate table t;
call idata();

执行时间

mysql> call idata();
Query OK, 1 row affected (15.26 sec)

发现时间变了，只要就15秒就可以了，快了接近10倍
在生产环境中如果磁盘压力比较大, 在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0，比较常见的是将其设置为 100-1000 中的某个数值。