自建MySQL遷移到云上RDS的故障起因及優(yōu)化

長假某日，陽光明媚，春暖花開，恰逢冬奧會(huì)開幕，想著一定是一個(gè)黃道吉日，必能順風(fēng)順?biāo)?。沒想到卻遇到一個(gè)有點(diǎn)小波折 的客戶報(bào)障。

01 故障起因

故障起因是客戶前一天從自建MySQL遷移到云上RDS，在執(zhí)行某個(gè)并發(fā)較高的業(yè)務(wù)時(shí)出現(xiàn)了大量鎖等待，客戶當(dāng)時(shí)升級(jí)了實(shí)例到最高規(guī)格，但故障依舊。客戶反饋升級(jí)后的實(shí)例規(guī)格比自建實(shí)例高了一倍，自建實(shí)例上從未發(fā)生過類似情況。后客戶根據(jù)當(dāng)時(shí)的業(yè)務(wù)故障模擬了現(xiàn)場(chǎng)，主要是并發(fā)執(zhí)行如下存儲(chǔ)過程的時(shí)候性能很差：

02 初步診斷

從存儲(chǔ)過程的邏輯看，比較簡(jiǎn)單，主要涉及兩個(gè)SQL，一個(gè)從表t（隱藏了真實(shí)表名）中meeting_id根據(jù)傳入參數(shù)值查詢，具體的入?yún)⒂勺址妥兞縫_meeting_id帶入；另外一個(gè)根據(jù)meeting_id和剛查出的phone_id去更新t中的phone_id為phone_id+3。表t數(shù)據(jù)量約40w左右。

第一感覺這是個(gè)簡(jiǎn)單問題，估計(jì)兩個(gè)SQL的meeting_id索引沒有生效，查詢表上索引后果然發(fā)現(xiàn)meeting_id和phone_id上沒有索引，建議客戶在兩個(gè)字段上分別創(chuàng)建了索引，且meeting_id為主鍵。此時(shí)用戶執(zhí)行模擬的并發(fā)腳本反饋速度有了明顯提升，200個(gè)并發(fā)最高執(zhí)行時(shí)間40s左右，但模擬500個(gè)并發(fā)的時(shí)候，超過了8分鐘還沒有執(zhí)行完。用戶反饋在自建MySQL上并發(fā)500執(zhí)行都是秒級(jí)完成。此時(shí)在控制臺(tái)看，這個(gè)存儲(chǔ)過程在慢查詢?nèi)罩局信砍霈F(xiàn)，且掃描行數(shù)巨大，客戶端已經(jīng)完全hang住:

03 進(jìn)一步優(yōu)化

雖然優(yōu)化有了初步的效果， 但距離客戶自建環(huán)境性能描述還差距很大，由于并發(fā)高， 從監(jiān)控看測(cè)試期間CPU到了100%，懷疑參數(shù)innodb_thread_concurrency的設(shè)置可能不當(dāng)。此參數(shù)的作用是控制 InnoDB 的并發(fā)線程上限。也就是說，一旦并發(fā)線程數(shù)達(dá)到這個(gè)值，InnoDB 在接收到新請(qǐng)求的時(shí)候，就會(huì)進(jìn)入等待狀態(tài)，直到有線程退出。RDS默認(rèn)值為0，也就是沒有限制上限，在高并發(fā)的場(chǎng)景下可能會(huì)產(chǎn)生較多的上下文切換，導(dǎo)致CPU升高。和客戶咨詢了一下，他們自建環(huán)境的值設(shè)置為32，建議他們將RDS的值也改為32再看看效果?？蛻艉芸旆答仯薷暮蟮拇_有效果，500個(gè)并發(fā)在3分鐘內(nèi)完成，沒有再發(fā)生hang住不動(dòng)的情況，性能有了進(jìn)一步的提升。但參數(shù)innodb_thread_concurrency進(jìn)一步調(diào)整效果不明顯。

04 加trace診斷

客戶看到性能不斷提升也很有信心，但和自建環(huán)境差距還是很大，還有哪里可能有問題？突然想到，創(chuàng)建索引后，在控制臺(tái)的慢查詢列表中看到很多存儲(chǔ)過程的調(diào)用sql，且掃描記錄數(shù)巨大，如果是走meeting_id唯一索引，應(yīng)該掃描很少的記錄數(shù)才對(duì)，難道沒有走索引？或者沒有走meeting_id主鍵索引？聯(lián)系客戶，希望提供測(cè)試環(huán)境登陸測(cè)試。

在測(cè)試環(huán)境，首先希望驗(yàn)證一下兩個(gè)SQL的執(zhí)行計(jì)劃到底是怎么樣的。登陸實(shí)例后，分別對(duì)兩個(gè)存儲(chǔ)過程中的SQL執(zhí)行explain，發(fā)現(xiàn)走的確實(shí)是主鍵（meeting_id）：

為了進(jìn)一步確認(rèn)SQL在存儲(chǔ)過程中的實(shí)際執(zhí)行計(jì)劃，修改了一下測(cè)試的存儲(chǔ)過程邏輯，加入了SQL執(zhí)行的explain結(jié)果和實(shí)際執(zhí)行的trace，過程中主要增加的代碼如下：

執(zhí)行計(jì)劃結(jié)果如下：

從結(jié)果看，兩個(gè)SQL居然真的沒有走主鍵meeting_id索引，而是都走了phone_id這個(gè)普通的二級(jí)索引，其中第一個(gè)查詢SQL走的索引全掃描，掃描記錄數(shù)rows為397399，和表的記錄數(shù)一致，顯然走了全索引掃描，雖然比全表掃描好一些，但效率仍然低下；另外一個(gè)update的SQL走了正常的索引掃描，rows只有2，性能高效。為什么兩個(gè)SQL沒有走meeting_id這個(gè)主鍵索引呢？看trace打印的部分內(nèi)容：

trace顯示兩個(gè)SQL在優(yōu)化器分析時(shí)，將meeting_id做了隱式轉(zhuǎn)換，轉(zhuǎn)換函數(shù)為convert('meeting_id' using utf8mb4)，也就是將meeting_id做了字符集的轉(zhuǎn)換，熟悉索引機(jī)制的同學(xué)都清楚，這種情況下優(yōu)化器是不會(huì)走meeting_id索引的。這也可以解釋了客戶第一次創(chuàng)建索引的時(shí)候?yàn)樯队行阅芴嵘?，但效果并不明顯，原因就是只有update語句真正用到了索引帶來的性能提升，而且是phone_id索引帶來的提升，不是性能更高的主鍵meeting_id。

05 真相大白

現(xiàn)在聚焦到最關(guān)鍵的問題，meeting_id為啥要做字符集的隱式轉(zhuǎn)換？查看了一下實(shí)例相關(guān)字符集的設(shè)置：

1. 表和列的字符集都為utf8；

2. 表所在庫的字符集為utf8mb4；

3. server字符集（（character_set_server））為utf8

4. character_set_client/character_set_connection/character_set_results為utf8mb4

果然，server、database、table的字符集不完全一致，猜想一下實(shí)際流程應(yīng)該是這樣的：存儲(chǔ)過程中傳入的字符參數(shù)字符集為utf8mb4，和表中字符集為utf8的字段meeting_id比較時(shí)，meeting_id做了字符集的隱式轉(zhuǎn)換，轉(zhuǎn)換為utf8mb4后再和輸入?yún)?shù)比較，從而導(dǎo)致meeting_id上的索引無法使用。

根據(jù)這個(gè)猜測(cè)，建議用戶將表的字符集更改為utf8mb4，這樣應(yīng)該可以避免字符集的轉(zhuǎn)換。由于這個(gè)功能還未上線，用戶直接對(duì) 表做了字符集的修改：

alter table zm_meeting convert to character set utf8mb4;

修改后讓用戶再次測(cè)試，預(yù)期效果終于出現(xiàn)，并發(fā)500測(cè)試在秒級(jí)完成，trace查看執(zhí)行計(jì)劃，都走了meeting_id的主鍵索引，隱式轉(zhuǎn)換也隨之消失，性能問題得到了徹底解決。

06

后續(xù)思考

存儲(chǔ)過程的入?yún)樯妒褂昧藆tf8mb4？這是本次案例的核心，查閱mysql文檔，存儲(chǔ)過程介紹里面有一段描述：

簡(jiǎn)單說，就是存儲(chǔ)過程的字符型參數(shù)，如果沒有顯式指定字符集，默認(rèn)將會(huì)使用所在數(shù)據(jù)庫的字符集，而本案例中表所在的數(shù)據(jù)庫字符集為utf8mb4，所以參數(shù)默認(rèn)使用了utf8mb4，導(dǎo)致了匹配過程的隱式轉(zhuǎn)換。存儲(chǔ)過程外直接寫SQL為什么沒有這種情況發(fā)生，我猜測(cè)比較的字符串應(yīng)該會(huì)自動(dòng)匹配‘=’左邊表字段的字符集。

既然這樣，理論上直接修改參數(shù)的字符集應(yīng)該也可以達(dá)到同樣結(jié)果，簡(jiǎn)單測(cè)試下，將存儲(chǔ)過程參數(shù)加上表上的字符集屬性：

CREATE  PROCEDURE `zm_sp_next_phone_id`(IN `p_meeting_id` VARCHAR(36) character set utf8)

測(cè)試結(jié)果如我們預(yù)期，不會(huì)產(chǎn)生隱式轉(zhuǎn)換，執(zhí)行計(jì)劃正確。

問題雖然解決了，原因也找到了，但反思一下整個(gè)過程，如果用戶的server、庫、表字符集能夠保持一致，將完全可以避免這個(gè)故障。與字符集相關(guān)的類似故障也可以大概率避免，所以客戶側(cè)還是要有一定的設(shè)計(jì)規(guī)范；產(chǎn)品側(cè)如果有一定的檢查規(guī)則可以幫客戶發(fā)現(xiàn)類似的隱患，對(duì)提升客戶體驗(yàn)也是一種很有價(jià)值的服務(wù)。