記錄生產(chǎn)mysql的問(wèn)題點(diǎn)。

業(yè)務(wù)場(chǎng)景與問(wèn)題描述

請(qǐng)求一個(gè)外部接口時(shí)，每天的請(qǐng)求量在900萬(wàn)左右。

分為請(qǐng)求項(xiàng)目和回執(zhí)這兩個(gè)項(xiàng)目。請(qǐng)求是用來(lái)調(diào)用外部接口，回執(zhí)是接收發(fā)送的接口。

在發(fā)送請(qǐng)求前會(huì)先插入數(shù)據(jù)庫(kù)。

在請(qǐng)求后，如果接口返回調(diào)用失敗，會(huì)更新數(shù)據(jù)庫(kù)狀態(tài)為失敗。

如果發(fā)送成功，則會(huì)等待上游給出回執(zhí)消息后，然后更新數(shù)據(jù)庫(kù)狀態(tài)。

而在生產(chǎn)運(yùn)行過(guò)程中，半年出現(xiàn)過(guò)兩次mysql導(dǎo)致的mq消費(fèi)者堆積的問(wèn)題。

問(wèn)題分析

記錄兩次不同的原因?qū)е碌纳a(chǎn)問(wèn)題及原因分析。

mysql死鎖問(wèn)題

查看mq聚合平臺(tái)TPS
上生產(chǎn)發(fā)現(xiàn)mq數(shù)據(jù)一直堆積，且不斷上升。而TPS僅為30左右，一直上不去。

這就會(huì)使mq消費(fèi)變慢了，導(dǎo)致不斷堆積。具體什么原因?qū)е耺q一直堆積，需要繼續(xù)排查。

查看生產(chǎn)服務(wù)器日志

查看生產(chǎn)服務(wù)器日志，發(fā)現(xiàn)有報(bào)錯(cuò)dead Lock的錯(cuò)誤。

error response from MySQLConnection [node=24, id=277499, threadId=2735941, state=borrowed, closed=false, autocommit=true, host=10.1.10.74, port=3306, database=sep_4, localPort=27744, isClose:false, toBeClose:false, MySQLVersion:5.7.25], err: Deadlock found when trying to get lock; try restarting transaction, code: 1213

具體的sql如下：

update stage set status = 'success',reply_time = '2021-03-07 10:40:11'  where code = '000123' and create_time > '2021-03-03 00:00:00';

也就是說(shuō)在執(zhí)行服務(wù)時(shí)出現(xiàn)了死鎖的情況。

具體有多少條以及耗時(shí)，在生產(chǎn)服務(wù)器看著不直觀，于是就讓dba將慢sql的語(yǔ)句和耗時(shí)查出來(lái)。

查出后發(fā)現(xiàn)最長(zhǎng)的慢sql的耗時(shí)長(zhǎng)達(dá)7780ms。

仔細(xì)查看會(huì)發(fā)現(xiàn)，sql會(huì)發(fā)現(xiàn)相同的id一個(gè)在執(zhí)行中，一個(gè)在Lock Wait狀態(tài)。

而這慢sql中有大量的Lock Wait狀態(tài)。

什么原因?qū)е碌乃梨i

mysql使用的數(shù)據(jù)庫(kù)引擎時(shí)InnoDB。先了解下什么是死鎖：

所謂死鎖: 是指兩個(gè)或兩個(gè)以上的進(jìn)程在執(zhí)行過(guò)程中,
因爭(zhēng)奪資源而造成的一種互相等待的現(xiàn)象,若無(wú)外力作用,它們都將無(wú)法推進(jìn)下去.
此時(shí)稱系統(tǒng)處于死鎖狀態(tài)或系統(tǒng)產(chǎn)生了死鎖,這些永遠(yuǎn)在互相等竺的進(jìn)程稱為死鎖進(jìn)程.

通過(guò)上面的排查可以看出，出現(xiàn)死鎖的問(wèn)題就是：

在執(zhí)行sql更新一條數(shù)據(jù)時(shí)，會(huì)將這一行數(shù)據(jù)鎖定，執(zhí)行完成后會(huì)釋放行鎖，而沒(méi)有執(zhí)行的sql處于Lock Wait狀態(tài)。

而程序中導(dǎo)致此原因在于，在發(fā)送前后和回執(zhí)時(shí)，頻繁操作數(shù)據(jù)庫(kù)，可能會(huì)出現(xiàn)同時(shí)操作同一條數(shù)據(jù)的情況。

所以在執(zhí)行中就出現(xiàn)了鎖等待的情況。

分庫(kù)分表未帶分片鍵

首先告警的是stage_prod庫(kù)的CPU飆到了85%。

數(shù)據(jù)庫(kù)線程數(shù)是否被打滿

經(jīng)過(guò)查看數(shù)據(jù)庫(kù)連接情況可知，數(shù)據(jù)庫(kù)連接數(shù)并沒(méi)有被占滿。

查出慢sql和耗時(shí)

查出的問(wèn)題sql：

update stage set status = 'success',reply_time = '2021-03-07 10:40:11'  where create_time > '2021-03-03 00:00:00';

查看sql會(huì)發(fā)現(xiàn)，這條sql竟然沒(méi)有帶分片鍵code字段。而這條sql是回執(zhí)時(shí)執(zhí)行的。

排查生產(chǎn)服務(wù)器日志

代碼中有做判斷，如果code值不為空，sql會(huì)帶上code的值。那么沒(méi)帶上，就需要查看為何沒(méi)有帶上。

查看代碼會(huì)發(fā)現(xiàn)，code是從redis中獲取的，是在發(fā)送時(shí)set到redis中的。但是沒(méi)有set進(jìn)去就很奇怪了。

初步懷疑是redis問(wèn)題，然后就與redis維護(hù)的平臺(tái)溝通，發(fā)現(xiàn)果真是因?yàn)閞edis故障導(dǎo)致的問(wèn)題。

為什么不帶分片鍵CPU就會(huì)飆升

首先公司用的是hotdb分庫(kù)分表，因?yàn)槊刻斓娜霂?kù)量是在900萬(wàn)左右，一個(gè)表是上億條數(shù)據(jù)。

如果只是單純用索引，是無(wú)法滿足要求的。

分庫(kù)分表hotdb，根據(jù)code值做hash分片，做了64個(gè)分片。也就是說(shuō)64個(gè)數(shù)據(jù)庫(kù)，分布在8臺(tái)服務(wù)器上的16個(gè)實(shí)例里面。

這樣可以避免各分片數(shù)據(jù)不均，理論上避免了過(guò)度集中在某個(gè)分片上。

而如果不帶分片鍵code的sql，所有的dml操作全部下發(fā)到所有的底層庫(kù)上進(jìn)行執(zhí)行，相當(dāng)于遍歷了一遍庫(kù)。

這樣就可能會(huì)導(dǎo)致CPU直接飆到99%，甚至直接導(dǎo)致服務(wù)器直接崩掉，這樣操作是很可怕的。

解決辦法

應(yīng)急處理：先停掉幾臺(tái)服務(wù)減少數(shù)據(jù)庫(kù)操作

數(shù)據(jù)持續(xù)堆積，會(huì)影響數(shù)據(jù)處理速度。那么，就要先降低操作的速度，最快速的辦法就是停服務(wù)，減少數(shù)據(jù)庫(kù)的操作頻率。

減少數(shù)據(jù)庫(kù)操作避免數(shù)據(jù)庫(kù)死鎖

死鎖一般時(shí)由于程序上沒(méi)有控制好dml操作的提交，沒(méi)有及時(shí)提交.

減少重復(fù)操作同一條數(shù)據(jù)。在批量操作時(shí)減少每批dml數(shù)，保證快速提交，避免長(zhǎng)事務(wù)，避免重復(fù)提交dml。

那么怎樣減少操作呢？

合并sql

將發(fā)送前插入和發(fā)送失敗時(shí)更新，直接合并到一條sql，這樣就可以避免多次操作同一條數(shù)據(jù)的情況。

批量執(zhí)行時(shí)減少長(zhǎng)事務(wù)和條數(shù)

執(zhí)行時(shí)發(fā)現(xiàn)，每次批量執(zhí)行20條sql，比一次性執(zhí)行200條的效率更快。

所以盡可能避免這種問(wèn)題。

每條sql必須帶分庫(kù)分表分片鍵

原則就是不能因?yàn)橐粭l數(shù)據(jù)就拖累整個(gè)數(shù)據(jù)庫(kù)的操作速度。

分片鍵必須帶上，如果不帶分片鍵，就拋錯(cuò)。

增加時(shí)間區(qū)間開閉區(qū)間

用code來(lái)做分片鍵，用createTime做分區(qū)。那么在保證code存在的情況下，可以寫上開閉區(qū)間，可以提高執(zhí)行效率。

更優(yōu)解：sql順序執(zhí)行

這種方案可以通過(guò)把將要執(zhí)行的sql統(tǒng)一發(fā)到一個(gè)mq來(lái)消費(fèi)執(zhí)行，這樣可以保證sql順序執(zhí)行，從而避免死鎖的產(chǎn)生。

但是這個(gè)需要根據(jù)業(yè)務(wù)場(chǎng)景來(lái)區(qū)分。

復(fù)盤

mysql死鎖問(wèn)題，要盡可能避免頻繁操作同一條數(shù)據(jù)，也要避免長(zhǎng)事務(wù)；
針對(duì)分庫(kù)分表問(wèn)題，一定要帶上分片鍵；
監(jiān)控機(jī)制不可少；

總結(jié)

到此這篇關(guān)于mysql死鎖和分庫(kù)分表問(wèn)題的文章就介紹到這了,更多相關(guān)mysql死鎖和分庫(kù)分表內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！