前言
最近系統(tǒng)（基于SpringCloud+K8s）上線(xiàn)，運(yùn)維團(tuán)隊(duì)早上8點(diǎn)左右在群里反饋，系統(tǒng)登錄無(wú)反應(yīng)！我的第一反應(yīng)是MySQL數(shù)據(jù)庫(kù)扛不住了。
排查問(wèn)題也是一波三折，有網(wǎng)絡(luò)問(wèn)題，也有mysql讀寫(xiě)分離后數(shù)據(jù)庫(kù)參數(shù)優(yōu)化問(wèn)題。
問(wèn)題回顧
1、運(yùn)維團(tuán)隊(duì)早上8點(diǎn)左右在群里反饋，系統(tǒng)登錄無(wú)反應(yīng)。
2、DevOps團(tuán)隊(duì)通過(guò)查看Kibana日志，發(fā)現(xiàn)ELK、k8s集群、redis、Mongodb、Nigix、文件服務(wù)器全部報(bào)：”Connect Unknown Error“，驚出一身冷汗。。。
心里嘀咕難道K8s容器也掛了？那還怎么玩？
3、查看監(jiān)控短信，連續(xù)收到數(shù)據(jù)庫(kù)讀寫(xiě)分離Master-Slave警告信息
問(wèn)題定位
1、Connect Unknown Error
經(jīng)過(guò)從k8s團(tuán)隊(duì)確認(rèn)，在早上8點(diǎn)左右出現(xiàn)了網(wǎng)絡(luò)中斷，持續(xù)了大概1分鐘左右，導(dǎo)致k8s平臺(tái)剔除響應(yīng)超時(shí)的微服務(wù)節(jié)點(diǎn)，同時(shí)不斷的啟動(dòng)新的容器。通過(guò)日志分析，8點(diǎn)半左右容器平臺(tái)恢復(fù)正常，但是前臺(tái)頁(yè)面查詢(xún)數(shù)據(jù)很慢（后來(lái)定位是Mysql數(shù)據(jù)庫(kù)服務(wù)器CPU占用92%，導(dǎo)致數(shù)據(jù)庫(kù)服務(wù)器處理應(yīng)用請(qǐng)求很慢）。
2、Mysql讀寫(xiě)分離Master-Slave警告信息
MHA架構(gòu)
Mysql讀寫(xiě)分離是采用MHA架構(gòu)，一主兩從（Master-Slave）。
Master負(fù)責(zé)數(shù)據(jù)的寫(xiě)操作，同時(shí)通過(guò)binlog日志同步到兩個(gè)Slave從庫(kù)，從庫(kù)負(fù)責(zé)應(yīng)用程序的查詢(xún)操作。
在報(bào)Connect Unknown Error異常后，我們檢查了Mysql服務(wù)器，發(fā)現(xiàn)Master節(jié)點(diǎn)CPU占用92%（應(yīng)用層讀寫(xiě)請(qǐng)求全部路由到了Master節(jié)點(diǎn)原因?qū)е拢鴥蓚€(gè)Slave節(jié)點(diǎn)全部處于空閑狀態(tài)，并且主從數(shù)據(jù)不同步了。
3、數(shù)據(jù)庫(kù)DBA通過(guò)查看mysql的show processlist命令，發(fā)現(xiàn)有大量的“create sort index（排序索引）”Sql語(yǔ)句（約36個(gè)）
經(jīng)排查發(fā)現(xiàn)有個(gè)cms_article表有幾百萬(wàn)的數(shù)據(jù)，客戶(hù)端分頁(yè)查詢(xún)請(qǐng)求，雖然只取10條數(shù)據(jù)行，但是實(shí)際查詢(xún)了幾百萬(wàn)行數(shù)據(jù)，而且要在數(shù)據(jù)庫(kù)內(nèi)存中進(jìn)行了幾百萬(wàn)數(shù)據(jù)內(nèi)存排序。所以出現(xiàn)了大量的create sort index排序索引。而且頻繁執(zhí)行Create Sort Index 會(huì)造成Mysql占滿(mǎn)服務(wù)器CPU，導(dǎo)致服務(wù)器請(qǐng)求無(wú)響應(yīng)，甚至假死狀態(tài)！
解決辦法
1、Connect Unknown Error
k8s平臺(tái)自動(dòng)剔除響應(yīng)超時(shí)的微服務(wù)節(jié)點(diǎn)，同時(shí)啟動(dòng)新的容器，直至恢復(fù)到故障前的容器節(jié)點(diǎn)水平，依靠k8s平臺(tái)自我修復(fù)。
2、Mysql讀寫(xiě)分離Master-Slave警告信息
恢復(fù)步驟
1、重啟Master-Slave節(jié)點(diǎn)，應(yīng)用層讀寫(xiě)請(qǐng)求正常，但是主從數(shù)據(jù)還是不同步，經(jīng)定位是mysql同步線(xiàn)程Slave_IO_Running和Slave_SQL_Running都為No。
2、晚上重啟Slave_IO_Running和Slave_SQL_Running線(xiàn)程
只有Slave_IO_Running和Slave_SQL_Running都為yes，則表示同步成功。
3、數(shù)據(jù)庫(kù)DBA通過(guò)查看mysql的show processlist命令，發(fā)現(xiàn)有大量的“create sort index（排序索引）”Sql語(yǔ)句（約36個(gè)）
innodb_buffer_pool_size從500M調(diào)整為300G（服務(wù)器共500G內(nèi)存）
innodb_buffer_pool_size
用于緩存索引和數(shù)據(jù)的內(nèi)存大小, 這個(gè)當(dāng)然是越多越好, 數(shù)據(jù)讀寫(xiě)在內(nèi)存中非?？? 減少了對(duì)磁盤(pán)的讀寫(xiě)。
當(dāng)數(shù)據(jù)提交或滿(mǎn)足檢查點(diǎn)條件后才一次性將內(nèi)存數(shù)據(jù)刷新到磁盤(pán)中。然而內(nèi)存還有操作系統(tǒng)或數(shù)據(jù)庫(kù)其他進(jìn)程使用, 一般設(shè)置 buffer pool 大小為總內(nèi)存的 1/5 至 1/4。 若設(shè)置不當(dāng), 內(nèi)存使用可能浪費(fèi)或者使用過(guò)多。
對(duì)于繁忙的服務(wù)器, buffer pool 將劃分為多個(gè)實(shí)例以提高系統(tǒng)并發(fā)性, 減少線(xiàn)程間讀寫(xiě)緩存的爭(zhēng)用。
buffer pool 的大小首先受 innodb_buffer_pool_instances 影響, 當(dāng)然影響較小。
Mysql性能調(diào)優(yōu)總結(jié)
預(yù)計(jì)44W用戶(hù) 峰值在線(xiàn)人數(shù) 5萬(wàn)左右。
1、innodb_buffer_pool_size=500M
太小，嚴(yán)重影響數(shù)據(jù)庫(kù)性能。服務(wù)器共500G內(nèi)存，但只給mysql緩沖池分配了500M，非常影響數(shù)據(jù)庫(kù)性能，且造成資源浪費(fèi)。建議設(shè)置為服務(wù)器內(nèi)存的60%。
2、expire_logs_days=7
太短，只能保留7天的binlog，只能恢復(fù)7天內(nèi)的任意數(shù)據(jù)。建議設(shè)置為參數(shù)文件里被覆蓋的90天的設(shè)置。
3、long_query_time=10
太長(zhǎng)，建議設(shè)置為2秒，讓慢查詢(xún)?nèi)罩居涗浉嗟穆樵?xún)。
4、transaction-isolation = read-committed
建議注釋掉，使用數(shù)據(jù)庫(kù)默認(rèn)的事務(wù)隔離級(jí)別
5、innodb_lock_wait_timeout = 5
設(shè)置得太小，會(huì)導(dǎo)致事務(wù)因鎖等待超過(guò)5秒，就被回滾。建議和云門(mén)戶(hù)設(shè)置得保持一致，云門(mén)戶(hù)大小為120。
6、autocommit = 0
#建議改為mysql默認(rèn)的自動(dòng)提交(autocommit=1)，提升性能，方便日常操作。
