MySQL并发引起的死锁问题

数据库 MySQL
平台的某个数据库上面有近千个连接,每个连接对应一个爬虫,爬虫将爬来的数据放到cdb里供后期分析查询使用。前段时间经常出现cdb查询缓慢,cpu占有率高的现象。通过show processlist后发现,大量的连接卡在了执行INSERT ... ON DUPLICATE KEY UPDATE这样的语句上面。

背景:

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

实验:

先创建一张表TestA:

  1. CREATE TABLE `TestA` (  
  2. `id` int(11) NOT NULL,  
  3. `num` int(1) DEFAULT NULL,  
  4. PRIMARY KEY (`id`)  
  5. 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语句。

  1. import gevent,time  
  2. from gevent import monkey  
  3. gevent.monkey.patch_socket()  
  4. import pymysql  
  5. total=1000 
  6. def TestSql(num):  
  7. start=time.time()  
  8. def goodquery(sql,i):  
  9. db = pymysql.connect(host = 'localhost'user = 'root',passwd='root'db'test',autocommit=True)  
  10. cursor = db.cursor()  
  11. cnt=total/num  
  12. sqlsql=sql.format(thread_id=i)  
  13. for i in xrange(cnt):  
  14. cursor.execute(sql)  
  15. cursor.close()  
  16. db.close()  
  17. sql='INSERT INTO `TestA` VALUES (1,1) ON DUPLICATE KEY UPDATE num=num+1;' 
  18. jobs = [gevent.spawn(goodquery, sql,i) for i in range(num)]  
  19. gevent.joinall(jobs)  
  20. restime.time()-start  
  21. return res  
  22. sample=[1,2,5,10,20,50,100,125,200]  
  23. x=[TestSql(x) for x in sample]  
  24. print x  

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

先下降后增长的趋势 

分析:

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

通过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 TestBVALUES (1,{thread_id},1) ON DUPLICATE KEY UPDATE num=num+1。这样每个连接都有了属于自己的行锁,不会互相争夺而产生死锁了。最后只需要执行一下sum就可以获取最终结果了。

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

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

随着连接数的增加,耗时减少至稳定,cpu使用率增加至稳定 

 

责任编辑:杜宁 来源: 腾云阁
相关推荐

2021-06-08 08:38:36

MySQL数据库死锁问题

2021-10-20 20:27:55

MySQL死锁并发

2018-05-29 11:44:22

数据库MySQL死锁

2023-07-18 09:24:04

MySQL线程

2021-03-26 10:40:16

MySQL锁等待死锁

2024-04-02 11:22:01

死锁Java并发

2010-09-07 15:04:21

SQL语句存储过程

2024-10-16 11:40:47

2020-04-14 10:20:12

MySQL数据库死锁

2024-04-01 00:01:05

MySQL死锁DDL

2010-05-19 10:00:17

2017-06-07 16:10:24

Mysql死锁死锁日志

2011-08-24 17:41:16

MySQL死锁

2023-11-09 11:56:28

MySQL死锁

2022-08-05 11:55:13

FlutteriOS

2014-08-14 10:38:30

SQL Server查询

2017-06-14 22:11:57

数据库MySQL死锁

2016-10-20 15:27:18

MySQLredo死锁

2019-07-05 17:40:24

MySQL并发数据库

2023-06-25 08:05:09

MySQL事务并发
点赞
收藏

51CTO技术栈公众号