mysql 批处理 innodb_flush_at_trx_commit 测试分析

背景

　　有个业务场景是需要大量写入库的，就会出现一个问题，日志文件很快就满了，dba扩展，扩展没多久又满...后来我们分析代码，发现有个小可爱， 每条每条的commit，这样做不仅产生大量的redo log和undo log，还会很慢。下面我们测试下为什么会慢。仅从InnoDB日志同步这块测试。线程池呀，binlog，索引创建等等因素先不考虑。

innodb_flush_at_trx_commit 

　　控制缓存日志刷新到磁盘的策略。有三种策略如下：

innodb_flush_log_at_trx_commit =1   每次事务提交从log buffer 提交到 logfile on disk中，默认策略
innodb_flush_log_at_trx_commit =0   每秒从log buffer 提交到 Log  file on disk中，最多丢失1s
innodb_flush_log_at_trx_commit =2   0，1值的混合，即每秒提交一次，每次会话提交也会同步到Log file 中去。

创建测试表

表不是我写的，从网上摘得，也是够懒的。

drop table if exists test_flush_log;
create table test_flush_log(id int,name char(50))engine=innodb;

创建存储过程

存储过程也是从网上摘得，批量的是根据单次的调整的。

# 批量提交
drop procedure if exists proc_batch;
delimiter $$
create procedure proc_batch(i int)
begin
    declare s int default 1;
    declare c char(50) default repeat('a',50);
    start transaction;
    while s<=i do
        
        insert into test_flush_log values(null,c);
        set s=s+1;
    end while;
     commit;
end$$
delimiter ;
#  单次提交
drop procedure if exists proc;
delimiter $$
create procedure proc(i int)
begin
    declare s int default 1;
    declare c char(50) default repeat('a',50);
    while s<=i do
        start transaction;
        insert into test_flush_log values(null,c);
        commit;
        set s=s+1;
    end while;
end$$
delimiter ;

 

测试场景

##############innodb_flush_log_at_trx_commit =1   默认策略就是1

[mysql> call proc(100000);
Query OK, 0 rows affected (12.95 sec)

[mysql> call proc_batch(100000);
Query OK, 0 rows affected (0.97 sec)


##############innodb_flush_log_at_trx_commit =0

set @@global.innodb_flush_log_at_trx_commit = 0;
[mysql> call proc(100000);
Query OK, 0 rows affected (1.60 sec)

[mysql> call proc_batch(100000);
Query OK, 0 rows affected (0.95 sec)

##############innodb_flush_log_at_trx_commit =2

[mysql> set @@global.innodb_flush_log_at_trx_commit =2;

[mysql> call proc(100000);
Query OK, 0 rows affected (2.99 sec)

[mysql> call proc_batch(100000);
Query OK, 0 rows affected (0.96 sec)

• 批处理比单数据提交快
• 在大量的写情况下 =1策略最慢，但是最安全。
• 在每次事务提交过程中，都会涉及到redo log undo log 缓存同步到磁盘，调用fsync函数，会出现IO等待。

解决方案

• 修改单次改为批量，设置会话级别的innodb_flush_log_at_trx_commit=0策略，当然看实际业务要求。
• 使用固盘，加快IO读写。

　　

参考文档：

• https://www.cnblogs.com/f-ck-need-u/archive/2018/05/08/9010872.html
• https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit