有个业务场景是需要大量写入库的,就会出现一个问题,日志文件很快就满了,dba扩展,扩展没多久又满...后来我们分析代码,发现有个小可爱, 每条每条的commit,这样做不仅产生大量的redo log和undo log,还会很慢。下面我们测试下为什么会慢。仅从InnoDB日志同步这块测试。线程池呀,binlog,索引创建等等因素先不考虑。
控制缓存日志刷新到磁盘的策略。有三种策略如下:
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)
参考文档:
mysql 为啥批处理就比单次提交快 innodb_flush_at_trx_commit 测试分析
原文:https://www.cnblogs.com/--net/p/12748990.html