专注,勤学,慎思。戒骄戒躁,谦虚谨慎

just do it

导航

MySQL自增列锁模式 innodb_autoinc_lock_mode不同参数下性能测试

 

20190701:感谢@接天居士的提示,在多核心CPU的配置环境下测试,确实是有差异的,感谢纠错,本来想删除这篇文章的,留着当错教训吧,测试环境差异造成的错误教训

 

对于innodb_autoinc_lock_mode 各种参数的值的含义,网上也有各种详解,看完觉得意犹未尽,这里不做阐述,只动手测试,看看性能上,到底有没有理论上所说的差别。
对于自增列的锁定,据说是innodb_autoinc_lock_mode = 2模式下有较高的性能,MySQL 8.0下innodb_autoinc_lock_mode 默认值为2。
于是通过修改改参数,测试不同参数下的一些性能表现,其结果还是比较出乎意料的……

 

测试环境:
MySQL 8.0.12 ON CentOS 7,1核1G内存
虽然环境资源配置有限,这里目的不是做性能测试,主要是对比在innodb_autoinc_lock_mode = 0和 2模式下的性能对比(没有做参数为1的情况)

测试方法:
本地Python开启多个线程,每个线程循环一定的数量,向某一个表中插入数据,看最终的时间表现情况
测试代码如下,开启20个线程,每个线程循环插入10000条数据,同时将当前线程Id写入当前数据行中(用来多个线程的执行是否是均匀或者说是交替的),看最终的时间。

#coding=utf-8
import threading
import pymysql
from time import ctime,sleep

connstr_tencent = {'host': '***.***.***.***', 'port': 3306, 'user': 'root', 'password': 'root', 'db': 'db01', 'charset': 'utf8mb4'}
def access_mysql(para):
    conn = pymysql.connect(host=connstr_tencent['host'],
                           port=connstr_tencent['port'],
                           user=connstr_tencent['user'],
                           password=connstr_tencent['password'],
                           db=connstr_tencent['db'],
                           charset=connstr_tencent['charset'],
                           connect_timeout = 100000 )
    cursor = conn.cursor()
    for i in range(10000):
        cursor.execute(" insert into test_autoicrement(col2,col3,col4) values ('thread:{0}','thread:{0}','thread:{0}'); ".format(str(para)))
    cursor.close()
    conn.commit()
    conn.close()

def main():
    # 生成线程
    threads = []
    for i in range(20):
        t = threading.Thread(target=access_mysql, args=(i,))
        threads.append(t)
for t in threads: t.setDaemon(True) t.start() for t in threads: t.join() if __name__ == '__main__': print("begin at {0}".format(ctime())) main() print("finsh at {0}".format(ctime()))

 

测试结果1,未开启binlog的情况

测试结果2,开启binlog的情况

测试结果仅仅是本地软硬件环境下表出现来的结果,主要目的是不同参数的性能对比

测试说明:
1,每次修改innodb_autoinc_lock_mode 之后,truncate测试表,并重启MySQL服务。
2,innodb_autoinc_lock_mode =0或者2做交叉测试,
   也即测试一次innodb_autoinc_lock_mode =0的,truncate 测试表,重启MySQL服务,然后再测试innodb_autoinc_lock_mode =2的情况。
3,不管是innodb_autoinc_lock_mode 为0或者2,(当前测试条件下)均未发现跳号的情况。
4,多线程测试下,记录都记录是当前线程的id,每次检验最终测试数据的分布情况,基本上都是每个线程均匀交替执行插入的,参考下图。

结论:
1,至少在MySQL 8.0下,innodb_autoinc_lock_mode 的值设置为0或者2的情况下,在性能上,没有发现明显的差异。
2,开启binlog的情况下,可能是测试数据量或者并发量不够,未发现比没有开启binlog有明显的性能下降。

 

另外:
一开始本地的Python连接5.7是没有问题的,连接MySQL8.0是直接报错,然后升级pymysql即可,
pymysql的版本笔者直接从0.7升级到0.9,不知道0.8版本的是否可以连接MySQL 8.0。

 

 

20190701:感谢@接天居士的提示,在多核心CPU的配置环境下测试,确实是有差异的,感谢纠错,本来想删除这篇文章的,留着当错教训吧,测试环境差异造成的错误教训

 

关于innodb_autoinc_lock_mode参数,参考:
http://seanlook.com/2017/02/16/mysql-autoincrement/
https://www.cnblogs.com/JiangLe/p/6362770.html

posted on 2018-09-13 13:08  MSSQL123  阅读(2816)  评论(3编辑  收藏  举报