Mysql 并发引起的死锁问题

作者 |邵梦超

编辑 | 顾乡

背景:

平台的某个数据库上面有近千个连接,每个连接对应一个爬虫,爬虫将爬来的数据放到cdb里供后期分析查询使用。前段时间经常出现cdb查询缓慢,cpu占有率高的现象。通过show processlist后发现,大量的连接卡在了执行INSERT ... ON DUPLICATE KEY UPDATE这样的语句上面。难道并发执行INSERT ... ON DUPLICATE KEY UPDATE会导致cpu负荷直线上升吗,下面我们做一个实验。

实验:

先创建一张表TestA:

CREATE TABLE `TestA` (
  `id` int(11) NOT NULL,
  `num` int(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

再编写一个压测测试脚本,分别在并发为1、2、5、10,20,50,100,125,200的情况下测试执行1000次 INSERT INTO TestA VALUES (1,1) ON DUPLICATE KEY UPDATE num=num+1语句。

import gevent,time
from gevent import monkey
gevent.monkey.patch_socket()
import pymysql

total=1000

def TestSql(num):
    start=time.time()
    def goodquery(sql,i):
        db = pymysql.connect(host = 'localhost', user = 'root',passwd='root', db= 'test',autocommit=True)
        cursor = db.cursor()
        cnt=total/num
        sql=sql.format(thread_id=i)
        for i in xrange(cnt):
            cursor.execute(sql)
        cursor.close()
        db.close()
        
    sql='INSERT INTO `TestA` VALUES (1,1) ON DUPLICATE KEY UPDATE num=num+1;'
    jobs = [gevent.spawn(goodquery, sql,i) for i in range(num)]
    gevent.joinall(jobs)
    res= time.time()-start
    return res

sample=[1,2,5,10,20,50,100,125,200]
x=[TestSql(x) for x in sample]
print x

运行结果如下图,随着并发数的增加执行sql语句耗时呈现先下降后增加的趋势,与之相对应的是cpu使用率随着并发数增加不断增加。可以看出,当并发数大于一定125的时候,系统发生了雪崩,性能急剧下降。而在图上没有标出来的是,当并发数大于200的时候,mysql直接返回了Deadlock found when trying to get lock; try restarting transaction错误,已经无法正常执行语句了。

分析:

通过perf来分析造成上述雪崩的原因,发现是卡在了lock_rec_get_prev函数上面。

INSERT INTO TestA VALUES (1,1) ON DUPLICATE KEY UPDATE num=num+1 这个语句先在表TestA中找到是否存在id=1的行,因为id是主键,所以很快就定位到这一行上面。接下来需要执行update操作,在执行update之前需要获取该行的X锁。由于大量的连接都在执行这个操作,因此在抢夺行锁上产生了大量的竞争,因为行锁的分配也涉及了自旋锁。很多连接就卡在了自旋锁上面,白白的消耗了cpu资源。

解决方案:

其实最好的解决方案就是不要将这些爬虫直接连到mysql上面,通过一个中间层维护一个mysql的连接池,这样既能满足实际业务需求,也不会造成死锁。当然对于这个具体场景也是有简单的优化方案的。造成死锁的原因是大量连接对行锁进行争夺。既然这个行锁是性能瓶颈,那我们可以通过增加行锁来减少争夺的成本。

 

我们稍微改造一下表结构,添加一个联合主键(id、thread_id),每个连接都执行 INSERT INTO TestB VALUES (1,{thread_id},1) ON DUPLICATE KEY UPDATE num=num+1。这样每个连接都有了属于自己的行锁,不会互相争夺而产生死锁了。最后只需要执行一下sum就可以获取最终结果了。

CREATE TABLE `TestB` (
  `id` int(11) NOT NULL,
  `thread_id` int(11) NOT NULL,
  `num` int(1) DEFAULT NULL,
  PRIMARY KEY (`id`,`thread_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

压测测试结果如图,随着连接数的增加,耗时减少至稳定,cpu使用率增加至稳定。

相关推荐

Discuz! 论坛使用云数据库可能遭遇随机的“The table XXX is full”异常

【干货合辑】你有什么独家数据库优化技巧?

基于Discuz的Mysql云数据库搬迁实例解析

原创声明,本文系作者授权云+社区-专栏发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

编辑于

邵梦超的专栏

1 篇文章1 人订阅

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏大内老A

如何追踪每一笔记录的来龙去脉:一个完整的Audit Logging解决方案[上篇]

一、提出问题 在开发一个企业级 应用的时候,尤其在一个涉及到敏感数据的应用,比如财务系统、物流系统,我们往往有这样的需求:对于数据库中每一笔数据的添加、修改和删...

2129
来自专栏数据和云

实战演练:通过伪列、虚拟列实现SQL优化

本文是技术同仁 蔡亮 在日常工作中通过试验,总结出的一些技巧方案,供大家参考学习。在此,感谢蔡亮的供稿分享,希望大家也可以后续将学习工作中遇到的问题,解决方法分...

853
来自专栏杨建荣的学习笔记

一条sql语句的改进探索(r5笔记第70天)

昨天同事找我,让我帮忙看两个sql问题,第一个问题是一个sql语句执行频率极高,但是目前的执行速度还是比较慢,希望我看看能不能调优一下。 另外一个问题是一个查询...

2838
来自专栏java一日一条

单机数据库优化的一些实践

数据库优化有很多可以讲,按照支撑的数据量来分可以分为两个阶段:单机数据库和分库分表,前者一般可以支撑500W或者10G以内的数据,超过这个值则需要考虑分库分表。...

342
来自专栏Java架构沉思录

如何优雅地优化MySQL大表

除非单表数据未来会一直不断上涨,否则不要一开始就考虑拆分,拆分会带来逻辑、部署、运维的各种复杂度,一般以整型值为主的表在千万级以下,字符串为主的表在五百万以下是...

1013
来自专栏后端之路

坑爹的MySql update in subquery

背景 ? 最近收到邮件如上,在做allot时发生db超时【前一段时间由于系统负载过大忽略了相关慢sql】 收到如上邮件考虑可能是allot详情太多导致发生超时 ...

2259
来自专栏Linyb极客之路

Java性能微调之数据库性能

 大部分Java系统性能问题基本上是由于错误的数据库访问方式引起的,带来了大量额外日志和内存消耗,这些都会对JVM的垃圾回收造成冲击影响,本文主要针对这种错误的...

501
来自专栏腾讯数据库技术

如何在数据库中高效实现订座功能?

1614
来自专栏Java面试通关手册

关于MySQL的知识点与面试常见问题都在这里

我自己总结的Java学习的一些知识点以及面试问题,目前已经开源,会一直完善下去,欢迎建议和指导欢迎Star: https://github.com/Snailc...

1593
来自专栏芋道源码1024

关于 MySQL 的知识点与面试常见问题都在这里

Mysql开发技巧: MySQL开发技巧(一)  MySQL开发技巧(二)  MySQL开发技巧(三)

1160

扫码关注云+社区