背景：

平臺的某個數(shù)據(jù)庫上面有近千個連接，每個連接對應(yīng)一個爬蟲，爬蟲將爬來的數(shù)據(jù)放到cdb里供后期分析查詢使用。前段時間經(jīng)常出現(xiàn)cdb查詢緩慢，cpu占有率高的現(xiàn)象。通過show processlist后發(fā)現(xiàn)，大量的連接卡在了執(zhí)行INSERT ... ON DUPLICATE KEY UPDATE這樣的語句上面。難道并發(fā)執(zhí)行INSERT ... ON DUPLICATE KEY UPDATE會導(dǎo)致cpu負(fù)荷直線上升嗎，下面我們做一個實驗。

實驗：

先創(chuàng)建一張表TestA：

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

再編寫一個壓測測試腳本，分別在并發(fā)為1、2、5、10，20，50，100，125，200的情況下測試執(zhí)行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  
sqlsql=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  

運行結(jié)果如下圖，隨著并發(fā)數(shù)的增加執(zhí)行sql語句耗時呈現(xiàn)先下降后增加的趨勢，與之相對應(yīng)的是cpu使用率隨著并發(fā)數(shù)增加不斷增加?？梢钥闯?，當(dāng)并發(fā)數(shù)大于一定125的時候，系統(tǒng)發(fā)生了雪崩，性能急劇下降。而在圖上沒有標(biāo)出來的是，當(dāng)并發(fā)數(shù)大于200的時候，mysql直接返回了Deadlock found when trying to get lock; try restarting transaction錯誤，已經(jīng)無法正常執(zhí)行語句了。

分析：

通過perf來分析造成上述雪崩的原因，發(fā)現(xiàn)是卡在了lock_rec_get_prev函數(shù)上面。

INSERT INTO TestA VALUES (1,1) ON DUPLICATE KEY UPDATE num=num+1 這個語句先在表TestA中找到是否存在id=1的行，因為id是主鍵，所以很快就定位到這一行上面。接下來需要執(zhí)行update操作，在執(zhí)行update之前需要獲取該行的X鎖。由于大量的連接都在執(zhí)行這個操作，因此在搶奪行鎖上產(chǎn)生了大量的競爭，因為行鎖的分配也涉及了自旋鎖。很多連接就卡在了自旋鎖上面，白白的消耗了cpu資源。

解決方案：

其實最好的解決方案就是不要將這些爬蟲直接連到mysql上面，通過一個中間層維護(hù)一個mysql的連接池，這樣既能滿足實際業(yè)務(wù)需求，也不會造成死鎖。當(dāng)然對于這個具體場景也是有簡單的優(yōu)化方案的。造成死鎖的原因是大量連接對行鎖進(jìn)行爭奪。既然這個行鎖是性能瓶頸，那我們可以通過增加行鎖來減少爭奪的成本。

我們稍微改造一下表結(jié)構(gòu)，添加一個聯(lián)合主鍵(id、thread_id)，每個連接都執(zhí)行 INSERT INTO TestBVALUES (1,{thread_id},1) ON DUPLICATE KEY UPDATE num=num+1。這樣每個連接都有了屬于自己的行鎖，不會互相爭奪而產(chǎn)生死鎖了。最后只需要執(zhí)行一下sum就可以獲取最終結(jié)果了。

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;  

壓測測試結(jié)果如圖，隨著連接數(shù)的增加，耗時減少至穩(wěn)定，cpu使用率增加至穩(wěn)定。