MYSQL双一配置对对写入性能影响

innodb_flush_log_at_trx_commit介绍

通过以下命令可以查询/设置innodb_flush_log_at_trx_commit的值：

• #select @@innodb_flush_log_at_trx_commit;
• #set global innodb_flush_log_at_trx_commit=2;

首先需要大致了解一下mysql日志操作步骤：

log_buff —mysql写 (write)—> log_file —OS刷新 (flush)—> disk

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

脚本准备

create table t1(id int primary key, a int, b int, index(a));
create table t2 like t1;
drop procedure idata;
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=1000)do
insert into t1 values(i, 1001-i, i);
set i=i+1;
end while;
set i=1;
while(i<=1000000)do
insert into t2 values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();

MYSQL磁盘写入监控

通过~/perf-tools-master/iosnoop |grep mysql命令监控磁盘写入延迟，最后两列为写入字节数，以及写入延迟ms。

测试结果

innodb_flush_log_at_trx_commit=0;

磁盘写入情况如下：

总耗时：

innodb_flush_log_at_trx_commit=2;

磁盘写入情况：

总耗时：

innodb_flush_log_at_trx_commit=1;

磁盘写入情况：

总耗时：

总结：

通过从以上磁盘监控可以印证0、1、2三个取值对磁盘写入的影响（1：每一个事务写入一次磁盘，每次写入较少4KB，延迟0.5ms左右，0/2：每次写入数据量根据操作系统或则MYSQL后台任务每秒执行一次刷新到磁盘），设置为1的耗时是0/2的耗时25倍

当需要大批量写入数据时，可以确定写入期间MYSQL不会停止/系统不会重启，可以提前将innodb_flush_log_at_trx_commit设置为0或则2，待写入完成后，再将配置改为1。