背景：平台上一个数据库有近千个连接，每个连接对应一个爬虫。爬虫将爬取到的数据放入cdb中，供以后分析查询。前段时间cdb查询经常很慢，cpu占用率高。通过showprocesslist后，发现大量连接卡在执行INSERT...ONDUPLICATEKEYUPDATE等语句上。并发执行INSERT...ONDUPLICATEKEYUPDATE会不会导致cpu负载急剧上升？下面我们来做个实验。实验：先创建表TestA：CREATETABLE`TestA`(`id`int(11)NOTNULL,`num`int(1)DEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;然后写一个压力测试测试脚本在1,2,5,10,20,50,100的并发下执行INSERTINTOTestAVALUES(1,1)ONDUPLICATEKEYUPDATEnum=num+1语句1000次，分别为125和200。importgevent,timefromgeventimportmonkeygevent.monkey.patch_socket()importpymysqltotal=1000defTestSql(num):start=time.time()defgoodquery(sql,i):db=pymysql.connect(host='localhost',用户='root',passwd='root',db='test',autocommit=True)cursor=db.cursor()cnt=total/numsqlsql=sql.format(thread_id=i)foriinxrange(cnt):游标。执行(sql)cursor.close()db.close()sql='INSERTINTO`TestA`VALUES(1,1)ONDUPLICATEKEYUPDATEnum=num+1;'jobs=[gevent.spawn(goodquery,sql,i)foriinrange(num)]事件。joinall(jobs)res=time.time()-startreturnressample=[1,2,5,10,20,50,100,125,200]x=[TestSql(x)forxinsample]printx运行结果如下图，随着并发数的增加，执行SQL语句消耗的时间先减少后增加。相应的，随着并发数的增加，CPU占用率也在不断增加。可以看出，当并发数大于某个125时，系统出现雪崩，性能急剧下降。图中没有标注的是，当并发数大于200时，mysql直接返回Deadlockfoundwhentryingtogetlock；tryrestarttransactionerror，语句无法正常执行。分析：通过perf分析上述雪崩的原因，发现卡在了lock_rec_get_prev函数上。INSERTINTOTestAVALUES(1,1)ONDUPLICATEKEYUPDATEnum=num+1这条语句首先查找表TestA中是否有id=1的行，因为id是主键，所以会很快定位到这一行.接下来需要进行更新操作，更新前需要获取该行的X锁。由于有大量的连接在执行这个操作，抢行锁的竞争就非常多，因为行锁的分配也涉及到自旋锁。很多连接卡在自旋锁上，白白消耗cpu资源。解决方案：其实最好的解决方案不是让这些爬虫直接连接mysql，而是通过一个中间层维护一个mysql连接池，既能满足实际业务需求，又不会造成死锁。当然，对于这种特定场景，也有简单的优化方案。死锁是由于大量连接竞争行锁造成的。由于这个行锁是性能瓶颈，我们可以通过加行锁来降低争用成本。我们稍微修改一下表结构，增加一个联合主键(id,thread_id)，对每个连接执行INSERTINTOTestBVALUES(1,{thread_id},1)ONDUPLICATEKEYUPDATEnum=num+1。这样每个连接都有自己的行锁，不会出现相互竞争造成的死锁。最后只需要执行sum就可以得到最终的结果。CREATETABLE`TestB`(`id`int(11)NOTNULL,`thread_id`int(11)NOTNULL,`num`int(1)DEFAULTNULL,PRIMARYKEY(`id`,`thread_id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;测试结果如图所示。随着连接数的增加，耗时下降到一个稳定的水平，cpu的使用率增加到一个稳定的水平。
