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读写。

  

参考文档:

 

mysql 为啥批处理就比单次提交快 innodb_flush_at_trx_commit 测试分析

上一篇:ASP.NET页面之间传递值(2):Session


下一篇:MySQL 数据类型之日期时间类型