背景
有个业务场景是需要大量写入库的,就会出现一个问题,日志文件很快就满了,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