微服務架構下，Mysql讀寫分離後，資料庫CPU飆升卡殼問題解析
- 2019 年 10 月 6 日
- 筆記
前言
最近系統（基於SpringCloud+K8s）上線，運維團隊早上8點左右在群里回饋，系統登錄無反應！我的第一反應是Mysql資料庫扛不住了。
排查問題也是一波三折，有網路問題，也有mysql讀寫分離後資料庫參數優化問題。
問題回顧
1、運維團隊早上8點左右在群里回饋，系統登錄無反應。
2、DevOps團隊通過查看Kibana日誌，發現ELK、k8s集群、Redis、Mongodb、Nigix、文件伺服器全部報：」Connect Unknown Error「，驚出一身冷汗。。。
心裡嘀咕難道K8s容器也掛了？那還怎麼玩？
3、查看監控簡訊，連續收到資料庫讀寫分離Master-Slave警告資訊
問題定位
1、Connect Unknown Error
經過從k8s團隊確認，在早上8點左右出現了網路中斷，持續了大概1分鐘左右，導致k8s平台剔除響應超時的微服務節點，同時不斷的啟動新的容器。通過日誌分析，8點半左右容器平台恢復正常，但是前台頁面查詢數據很慢（後來定位是Mysql資料庫伺服器CPU佔用92%，導致資料庫伺服器處理應用請求很慢）。
2、Mysql讀寫分離Master-Slave警告資訊
MHA架構
Mysql讀寫分離是採用MHA架構，一主兩從（Master-Slave）。 Master負責數據的寫操作，同時通過binlog日誌同步到兩個Slave從庫，從庫負責應用程式的查詢操作。
在報Connect Unknown Error異常後，我們檢查了Mysql伺服器，發現Master節點CPU佔用92%（應用層讀寫請求全部路由到了Master節點原因導致），而兩個Slave節點全部處於空閑狀態，並且主從數據不同步了。
3、資料庫DBA通過查看mysql的show processlist命令，發現有大量的「create sort index（排序索引）」Sql語句（約36個）
經排查發現有個cms_article表有幾百萬的數據，客戶端分頁查詢請求，雖然只取10條數據行，但是實際查詢了幾百萬行數據，而且要在資料庫記憶體中進行了幾百萬數據記憶體排序。所以出現了大量的create sort index排序索引。而且頻繁執行Create Sort Index 會造成Mysql佔滿伺服器CPU，導致伺服器請求無響應，甚至假死狀態！
解決辦法
1、Connect Unknown Error
k8s平台自動剔除響應超時的微服務節點，同時啟動新的容器，直至恢復到故障前的容器節點水平，依靠k8s平台自我修復。
2、Mysql讀寫分離Master-Slave警告資訊
恢復步驟
1、重啟Master-Slave節點，應用層讀寫請求正常，但是主從數據還是不同步，經定位是mysql同步執行緒Slave_IO_Running和Slave_SQL_Running都為No。
2、晚上重啟Slave_IO_Running和Slave_SQL_Running執行緒
只有Slave_IO_Running和Slave_SQL_Running都為yes，則表示同步成功。
3、資料庫DBA通過查看mysql的show processlist命令，發現有大量的「create sort index（排序索引）」Sql語句（約36個）
innodb_buffer_pool_size從500M調整為300G（伺服器共500G記憶體）
innodb_buffer_pool_size
用於快取索引和數據的記憶體大小, 這個當然是越多越好, 數據讀寫在記憶體中非常快, 減少了對磁碟的讀寫。 當數據提交或滿足檢查點條件後才一次性將記憶體數據刷新到磁碟中。然而記憶體還有作業系統或資料庫其他進程使用, 一般設置 buffer pool 大小為總記憶體的 1/5 至 1/4。若設置不當, 記憶體使用可能浪費或者使用過多。 對於繁忙的伺服器, buffer pool 將劃分為多個實例以提高系統並發性, 減少執行緒間讀寫快取的爭用。 buffer pool 的大小首先受 innodb_buffer_pool_instances 影響, 當然影響較小。
Mysql性能調優總結
預計44W用戶 峰值在線人數 5萬左右。
1、innodb_buffer_pool_size=500M
太小，嚴重影響資料庫性能。伺服器共500G記憶體，但只給mysql緩衝池分配了500M，非常影響資料庫性能，且造成資源浪費。建議設置為伺服器記憶體的60%。
2、expire_logs_days=7
太短，只能保留7天的binlog，只能恢復7天內的任意數據。建議設置為參數文件里被覆蓋的90天的設置。
3、long_query_time=10
太長，建議設置為2秒，讓慢查詢日誌記錄更多的慢查詢。
4、transaction-isolation = read-committed
建議注釋掉，使用資料庫默認的事務隔離級別
5、innodb_lock_wait_timeout = 5
設置得太小，會導致事務因鎖等待超過5秒，就被回滾。建議和雲門戶設置得保持一致，雲門戶大小為120。
6、autocommit = 0
#建議改為mysql默認的自動提交(autocommit=1)，提升性能，方便日常操作。
