2018即将结束。今天的雪来的那么突然,虽然寒冷,但是又让人充满兴奋,下午带娃出去门口看雪,剩下的时间在家整理下文档,准备今天发出2018年的最后一篇文章。2018年是成长和收获的一年,今年的工作格外的忙碌,生活也充实,同时业余时间DBARUN社区共分享了15篇文章,当然15篇不算多,但是都是在工作和娃的间隙中整理和分享出来的,希望2019能继续分享出更多的文章给大家吧,借用马总(马明哲)一句话:致敬来时路,感恩再出发。

以下为文章正文(IT相关,非IT从业者请自动忽略以下内容)

之前由于业务变更,我在使用pt的OSC工具在对一个线上不是很繁忙的数据库做变更时,发现每次都是产生了锁等待,后面排查发现是和OSC的bulk insert和autoinc lock有关,后面大家也对innodb_autoinc_lock_mode这个参数的锁的行为做过一些讨论。本篇文档就为大家介绍下MySQL的innodb_autoinc_lock_mode锁行为机制,以及不同设置下性能的分析。

首先我们对insert语句做个分类,大致上可以分成三类:

1、simple insert 如insert into t(name) values('dbarun')2、bulk insert 如load data | insert into ... select .... from ....3、mixed insert 如insert into t(id,name) values(1,'a'),(2,'b'),(3,'c');

其次我们对innodb_autoinc_lock_mode这个参数的值做个了解,这个参数可以设置为0,1,2,其代表的含义如下:

1、设置为0 这个表示tradition (传统模式)

2、设置为1 这个表示consecutive (连续模式)3、设置为2 这个表示interleaved (交错模式)

tradition(innodb_autoinc_lock_mode=0) 传统模式:

在这一模式下,所有的insert语句("insertlike") 都要在语句开始的时候得到一个表级的auto_inc锁,在语句结束的时候才释放这把锁,注意呀,这里说的是语句级而不是事务级的,一个事务可能包含有一个或多个语句。它能保证值分配的可预见性,与连续性,可重复性,这个也就保证了insert语句在复制到slave的时候还能生成和master那边一样的值(它保证了基于语句级复制的安全)。由于在这种模式下auto_inc锁一直要保持到语句的结束,所以这个就影响到了并发的写入性能。

consecutive(innodb_autoinc_lock_mode=1) 连续模式:

这一模式下对simpleinsert 做了优化,由于simple insert一次性插入值的个数可以立马得到确定,所以mysql可以一次生成几个连续的值,用于这个insert语句;总的来说这个对复制也是安全的(它保证了基于语句级复制的安全)。这一模式也是mysql的默认模式,这个模式的好处是auto_inc锁不要一直保持到语句的结束,只要语句得到了相应的值后就可以提前释放锁。

interleaved(innodb_autoinc_lock_mode=2) 模式:

由于这个模式下已经没有了auto_inc锁,所以这个模式下的性能是最好的;但是它也有一个问题,就是对于同一个语句来说它所得到的auto_incremant值可能不是连续的。

最后我们讨论下,innodb_autoinc_lock_mode这个参数该如何设置?

在大多数情况下,此变量设置为默认值1,此模式下对任何模式(binlog_format=sql/row/mixed)的数据复制都是安全的。

在binlog_format=row时,也可以将其设置为2。对于交错语句,insert语句不使用表级的auto-inc锁,多个语句可以同时执行,从而提高并发性能。但如果在从二进制日志重放SQL语句时使用基于语句的复制或恢复方案,设置为2则是不安全的。还有另一个考虑因素,对于自增ID键强联续性依赖的,请不要设置为2。假如设置为2,这意味着您可以进行三次insert写入,并期望ID为100,101和103,但最终可能会得到100、102和104。

如果您只做简单的插入,设置为2可能对您没有帮助。我用100个线程在AmazonRDS的MySQL5.7上做了一个SysBench测试,发现锁模式1和锁模式2在性能和吞吐量上没有区别。当无法确定行数时(例如使用INSERT INTO…SELECT语句)时,设置为2对您的帮助最大。

最后总结一下,innodb_autoinc_lock_mode通常情况下设置为1即可,安全性和性能平衡下来最好的。通常情况下innodb_autoinc_lock_mode设置为1的性能影响可以忽略,如果有性能问题,需要检视下业务代码逻辑是否正常;当binlog_format=row时,在有大量的bulk insert并且不care自增键的连续性时可以设置为2,可以帮助你提高写入性能。

DBARUN社区2018年度文档汇总: