专栏首页邵梦超的专栏Mysql 并发引起的死锁问题
原创

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 删除。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 跟腾讯工程师一起玩转 MySQL

    MySQL 作为最受欢迎的开源数据库,也是云上使用最多的数据库,在实际业务中应用广泛。但在实际应用经常遇到 SQL 各种各样的瓶颈,比如:如何优化 MySQL ...

    云加社区专栏
  • MySQL 慢查询日志

    MySQL有一种日志,叫做慢查询日志,主要就是用来记录一些耗时的查询操 作。通过这个日志我们就可以分析出哪些的操作是影响性能的,我们需要对其 进行一些优化措施。

    吴生
  • 如何在腾讯云平台中的数据库进行批量操作

    本文主要介绍了数据库三个批量操作的实操步骤,第一个是使用控制台批量续费,第二个是通过控制台批量回挡,第三个是批量SQL操作。

    腾讯云数据库团队
  • 可重复读事务隔离级别之 django 解读

    本文尝试结合 django 解释应用开发中并发访问数据库可能会遇到的可重复读引起的问题,希望能帮助大家在开发过程中有效避免类似问题。

    梅海峰
  • MySQL FAQ 系列 — MySQL 复制中 slave 延迟监控

    在MySQL复制环境中,我们通常只根据 Seconds_Behind_Master 的值来判断SLAVE的延迟。这么做大部分情况下尚可接受,但并不够准确,而应该...

    叶金荣
  • MySQL 索引与优化

    索引是存储引擎内部的一种数据结构,它能让数据库快速地找到需要的值,而不是野蛮地进行自上而下的全表遍历。索引对良好的性能非常关键,尤其是当表中的数据量越来越大时,...

    serena
  • 【开源访谈】黄东旭:“无人区”的探索者, TiDB 的前行之路

    > 日前,我司联合创始人兼 CTO 黄东旭接受了开源中国的【开源访谈】,公开解读了 TiDB 的探索之路及未来方向。本文为专访实录~ :)

    PingCAP
  • 在 MySQL 5.7下排查内存泄露和 OOM 问题全过程

    我的个人网站后台使用的是 MySQL 5.7 版本,前段时间经常被oom-kill,借助5.7的新特性,经过一番排查,终于抓到这只鬼。

    叶金荣
  • 如何登录腾讯云数据库

    本文主要介绍了使用命令行方式登录以及使用云数据库管理界面登录这两种实例登录腾讯云数据库的步骤。

    腾讯云数据库团队

扫码关注云+社区

领取腾讯云代金券