長假某日,陽光明媚,春暖花開,恰逢冬奧會開幕,想著一定是一個黃道吉日,必能順風(fēng)順?biāo)]想到卻遇到一個有點(diǎn)小波折 的客戶報障。
01 故障起因故障起因是客戶前一天從自建MySQL遷移到云上RDS,在執(zhí)行某個并發(fā)較高的業(yè)務(wù)時出現(xiàn)了大量鎖等待,客戶當(dāng)時升級了實(shí)例到最高規(guī)格,但故障依舊。客戶反饋升級后的實(shí)例規(guī)格比自建實(shí)例高了一倍,自建實(shí)例上從未發(fā)生過類似情況。后客戶根據(jù)當(dāng)時的業(yè)務(wù)故障模擬了現(xiàn)場,主要是并發(fā)執(zhí)行如下存儲過程的時候性能很差:
02 初步診斷
從存儲過程的邏輯看,比較簡單,主要涉及兩個SQL,一個從表t(隱藏了真實(shí)表名)中meeting_id根據(jù)傳入參數(shù)值查詢,具體的入?yún)⒂勺址妥兞縫_meeting_id帶入;另外一個根據(jù)meeting_id和剛查出的phone_id去更新t中的phone_id為phone_id+3。表t數(shù)據(jù)量約40w左右。
第一感覺這是個簡單問題,估計(jì)兩個SQL的meeting_id索引沒有生效,查詢表上索引后果然發(fā)現(xiàn)meeting_id和phone_id上沒有索引,建議客戶在兩個字段上分別創(chuàng)建了索引,且meeting_id為主鍵。此時用戶執(zhí)行模擬的并發(fā)腳本反饋速度有了明顯提升,200個并發(fā)最高執(zhí)行時間40s左右,但模擬500個并發(fā)的時候,超過了8分鐘還沒有執(zhí)行完。用戶反饋在自建MySQL上并發(fā)500執(zhí)行都是秒級完成。此時在控制臺看,這個存儲過程在慢查詢?nèi)罩局信砍霈F(xiàn),且掃描行數(shù)巨大,客戶端已經(jīng)完全hang住:
03 進(jìn)一步優(yōu)化
雖然優(yōu)化有了初步的效果, 但距離客戶自建環(huán)境性能描述還差距很大,由于并發(fā)高, 從監(jiān)控看測試期間CPU到了100%,懷疑參數(shù)innodb_thread_concurrency的設(shè)置可能不當(dāng)。此參數(shù)的作用是控制 InnoDB 的并發(fā)線程上限。也就是說,一旦并發(fā)線程數(shù)達(dá)到這個值,InnoDB 在接收到新請求的時候,就會進(jìn)入等待狀態(tài),直到有線程退出。RDS默認(rèn)值為0,也就是沒有限制上限,在高并發(fā)的場景下可能會產(chǎn)生較多的上下文切換,導(dǎo)致CPU升高。和客戶咨詢了一下,他們自建環(huán)境的值設(shè)置為32,建議他們將RDS的值也改為32再看看效果。客戶很快反饋,修改后的確有效果,500個并發(fā)在3分鐘內(nèi)完成,沒有再發(fā)生hang住不動的情況,性能有了進(jìn)一步的提升。但參數(shù)innodb_thread_concurrency進(jìn)一步調(diào)整效果不明顯。
04 加trace診斷客戶看到性能不斷提升也很有信心,但和自建環(huán)境差距還是很大,還有哪里可能有問題?突然想到,創(chuàng)建索引后,在控制臺的慢查詢列表中看到很多存儲過程的調(diào)用sql,且掃描記錄數(shù)巨大,如果是走meeting_id唯一索引,應(yīng)該掃描很少的記錄數(shù)才對,難道沒有走索引?或者沒有走meeting_id主鍵索引?聯(lián)系客戶,希望提供測試環(huán)境登陸測試。
在測試環(huán)境,首先希望驗(yàn)證一下兩個SQL的執(zhí)行計(jì)劃到底是怎么樣的。登陸實(shí)例后,分別對兩個存儲過程中的SQL執(zhí)行explain,發(fā)現(xiàn)走的確實(shí)是主鍵(meeting_id):
為了進(jìn)一步確認(rèn)SQL在存儲過程中的實(shí)際執(zhí)行計(jì)劃,修改了一下測試的存儲過程邏輯,加入了SQL執(zhí)行的explain結(jié)果和實(shí)際執(zhí)行的trace,過程中主要增加的代碼如下:
執(zhí)行計(jì)劃結(jié)果如下:
從結(jié)果看,兩個SQL居然真的沒有走主鍵meeting_id索引,而是都走了phone_id這個普通的二級索引,其中第一個查詢SQL走的索引全掃描,掃描記錄數(shù)rows為397399,和表的記錄數(shù)一致,顯然走了全索引掃描,雖然比全表掃描好一些,但效率仍然低下;另外一個update的SQL走了正常的索引掃描,rows只有2,性能高效。為什么兩個SQL沒有走meeting_id這個主鍵索引呢?看trace打印的部分內(nèi)容:
trace顯示兩個SQL在優(yōu)化器分析時,將meeting_id做了隱式轉(zhuǎn)換,轉(zhuǎn)換函數(shù)為convert('meeting_id' using utf8mb4),也就是將meeting_id做了字符集的轉(zhuǎn)換,熟悉索引機(jī)制的同學(xué)都清楚,這種情況下優(yōu)化器是不會走meeting_id索引的。這也可以解釋了客戶第一次創(chuàng)建索引的時候?yàn)樯队行阅芴嵘Ч⒉幻黠@,原因就是只有update語句真正用到了索引帶來的性能提升,而且是phone_id索引帶來的提升,不是性能更高的主鍵meeting_id。
05 真相大白
現(xiàn)在聚焦到最關(guān)鍵的問題,meeting_id為啥要做字符集的隱式轉(zhuǎn)換?查看了一下實(shí)例相關(guān)字符集的設(shè)置:
-
表和列的字符集都為utf8;
-
表所在庫的字符集為utf8mb4;
-
server字符集((character_set_server))為utf8
-
character_set_client/character_set_connection/character_set_results為utf8mb4
果然,server、database、table的字符集不完全一致,猜想一下實(shí)際流程應(yīng)該是這樣的:存儲過程中傳入的字符參數(shù)字符集為utf8mb4,和表中字符集為utf8的字段meeting_id比較時,meeting_id做了字符集的隱式轉(zhuǎn)換,轉(zhuǎn)換為utf8mb4后再和輸入?yún)?shù)比較,從而導(dǎo)致meeting_id上的索引無法使用。
根據(jù)這個猜測,建議用戶將表的字符集更改為utf8mb4,這樣應(yīng)該可以避免字符集的轉(zhuǎn)換。由于這個功能還未上線,用戶直接對 表做了字符集的修改:
alter table zm_meeting convert to character set utf8mb4;
修改后讓用戶再次測試,預(yù)期效果終于出現(xiàn),并發(fā)500測試在秒級完成,trace查看執(zhí)行計(jì)劃,都走了meeting_id的主鍵索引,隱式轉(zhuǎn)換也隨之消失,性能問題得到了徹底解決。
06
后續(xù)思考
存儲過程的入?yún)樯妒褂昧藆tf8mb4?這是本次案例的核心,查閱mysql文檔,存儲過程介紹里面有一段描述:
簡單說,就是存儲過程的字符型參數(shù),如果沒有顯式指定字符集,默認(rèn)將會使用所在數(shù)據(jù)庫的字符集,而本案例中表所在的數(shù)據(jù)庫字符集為utf8mb4,所以參數(shù)默認(rèn)使用了utf8mb4,導(dǎo)致了匹配過程的隱式轉(zhuǎn)換。存儲過程外直接寫SQL為什么沒有這種情況發(fā)生,我猜測比較的字符串應(yīng)該會自動匹配‘=’左邊表字段的字符集。
既然這樣,理論上直接修改參數(shù)的字符集應(yīng)該也可以達(dá)到同樣結(jié)果,簡單測試下,將存儲過程參數(shù)加上表上的字符集屬性:
CREATE PROCEDURE `zm_sp_next_phone_id`(IN `p_meeting_id` VARCHAR(36) character set utf8)
測試結(jié)果如我們預(yù)期,不會產(chǎn)生隱式轉(zhuǎn)換,執(zhí)行計(jì)劃正確。
問題雖然解決了,原因也找到了,但反思一下整個過程,如果用戶的server、庫、表字符集能夠保持一致,將完全可以避免這個故障。與字符集相關(guān)的類似故障也可以大概率避免,所以客戶側(cè)還是要有一定的設(shè)計(jì)規(guī)范;產(chǎn)品側(cè)如果有一定的檢查規(guī)則可以幫客戶發(fā)現(xiàn)類似的隱患,對提升客戶體驗(yàn)也是一種很有價值的服務(wù)。
-
故障
+關(guān)注
關(guān)注
6文章
386瀏覽量
29386 -
MySQL
+關(guān)注
關(guān)注
1文章
804瀏覽量
26531 -
RDS
+關(guān)注
關(guān)注
0文章
101瀏覽量
16847
原文標(biāo)題:一次較波折的MySQL調(diào)優(yōu)
文章出處:【微信號:OSC開源社區(qū),微信公眾號:OSC開源社區(qū)】歡迎添加關(guān)注!文章轉(zhuǎn)載請注明出處。
發(fā)布評論請先 登錄
相關(guān)推薦
評論