前言

最近系統(tǒng)（基于SpringCloud+K8s）上線，運維團隊早上8點左右在群里反饋，系統(tǒng)登錄無反應！我的第一反應是MySQL數(shù)據(jù)庫扛不住了。

微服務架構下，Mysql讀寫分離后，數(shù)據(jù)庫CPU飆升卡殼問題解析

排查問題也是一波三折，有網(wǎng)絡問題，也有mysql讀寫分離后數(shù)據(jù)庫參數(shù)優(yōu)化問題。

問題回顧

1、運維團隊早上8點左右在群里反饋，系統(tǒng)登錄無反應。

2、DevOps團隊通過查看Kibana日志，發(fā)現(xiàn)ELK、k8s集群、redis、Mongodb、Nigix、文件服務器全部報：”Connect Unknown Error“，驚出一身冷汗。。。

心里嘀咕難道K8s容器也掛了？那還怎么玩？

3、查看監(jiān)控短信，連續(xù)收到數(shù)據(jù)庫讀寫分離Master-Slave警告信息

問題定位

1、Connect Unknown Error

經(jīng)過從k8s團隊確認，在早上8點左右出現(xiàn)了網(wǎng)絡中斷，持續(xù)了大概1分鐘左右，導致k8s平臺剔除響應超時的微服務節(jié)點，同時不斷的啟動新的容器。通過日志分析，8點半左右容器平臺恢復正常，但是前臺頁面查詢數(shù)據(jù)很慢（后來定位是Mysql數(shù)據(jù)庫服務器CPU占用92%，導致數(shù)據(jù)庫服務器處理應用請求很慢）。

2、Mysql讀寫分離Master-Slave警告信息

MHA架構

Mysql讀寫分離是采用MHA架構，一主兩從（Master-Slave）。

Master負責數(shù)據(jù)的寫操作，同時通過binlog日志同步到兩個Slave從庫，從庫負責應用程序的查詢操作。

在報Connect Unknown Error異常后，我們檢查了Mysql服務器，發(fā)現(xiàn)Master節(jié)點CPU占用92%（應用層讀寫請求全部路由到了Master節(jié)點原因?qū)е拢鴥蓚€Slave節(jié)點全部處于空閑狀態(tài)，并且主從數(shù)據(jù)不同步了。

3、數(shù)據(jù)庫DBA通過查看mysql的show processlist命令，發(fā)現(xiàn)有大量的“create sort index（排序索引）”Sql語句（約36個）

經(jīng)排查發(fā)現(xiàn)有個cms_article表有幾百萬的數(shù)據(jù)，客戶端分頁查詢請求，雖然只取10條數(shù)據(jù)行，但是實際查詢了幾百萬行數(shù)據(jù)，而且要在數(shù)據(jù)庫內(nèi)存中進行了幾百萬數(shù)據(jù)內(nèi)存排序。所以出現(xiàn)了大量的create sort index排序索引。而且頻繁執(zhí)行Create Sort Index 會造成Mysql占滿服務器CPU，導致服務器請求無響應，甚至假死狀態(tài)！

解決辦法

1、Connect Unknown Error

k8s平臺自動剔除響應超時的微服務節(jié)點，同時啟動新的容器，直至恢復到故障前的容器節(jié)點水平，依靠k8s平臺自我修復。

2、Mysql讀寫分離Master-Slave警告信息

恢復步驟

1、重啟Master-Slave節(jié)點，應用層讀寫請求正常，但是主從數(shù)據(jù)還是不同步，經(jīng)定位是mysql同步線程Slave_IO_Running和Slave_SQL_Running都為No。

2、晚上重啟Slave_IO_Running和Slave_SQL_Running線程

只有Slave_IO_Running和Slave_SQL_Running都為yes，則表示同步成功。

3、數(shù)據(jù)庫DBA通過查看mysql的show processlist命令，發(fā)現(xiàn)有大量的“create sort index（排序索引）”Sql語句（約36個）

innodb_buffer_pool_size從500M調(diào)整為300G（服務器共500G內(nèi)存）

innodb_buffer_pool_size

用于緩存索引和數(shù)據(jù)的內(nèi)存大小, 這個當然是越多越好, 數(shù)據(jù)讀寫在內(nèi)存中非常快, 減少了對磁盤的讀寫。

當數(shù)據(jù)提交或滿足檢查點條件后才一次性將內(nèi)存數(shù)據(jù)刷新到磁盤中。然而內(nèi)存還有操作系統(tǒng)或數(shù)據(jù)庫其他進程使用, 一般設置 buffer pool 大小為總內(nèi)存的 1/5 至 1/4。若設置不當, 內(nèi)存使用可能浪費或者使用過多。

對于繁忙的服務器, buffer pool 將劃分為多個實例以提高系統(tǒng)并發(fā)性, 減少線程間讀寫緩存的爭用。

buffer pool 的大小首先受 innodb_buffer_pool_instances 影響, 當然影響較小。