建站
咨詢
售后
建站咨詢
新聞中心
背景：
創(chuàng)新互聯(lián)建站專注于企業(yè)全網(wǎng)整合營銷推廣、網(wǎng)站重做改版、洛南網(wǎng)站定制設(shè)計、自適應(yīng)品牌網(wǎng)站建設(shè)、H5頁面制作、購物商城網(wǎng)站建設(shè)、集團(tuán)公司官網(wǎng)建設(shè)、外貿(mào)網(wǎng)站制作、高端網(wǎng)站制作、響應(yīng)式網(wǎng)頁設(shè)計等建站業(yè)務(wù)，價格優(yōu)惠性價比高，為洛南等各大城市提供網(wǎng)站開發(fā)制作服務(wù)。
平臺的某個數(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)定。
文章標(biāo)題：MySQL并發(fā)引起的死鎖問題
標(biāo)題URL：http://www.5511xx.com/article/cdghpsc.html
