MYSQL數據庫服務CPU高問題分析與優化

MYSQL數據庫服務CPU高問題分析與優化

   MySQL服務性能監控分析與優化是永恒的主題，做為性能測試人員有時也要站在DBA角度出發進行適當分析與優化，這也是性能測試人員能長期生存發展之路。而資源的使用監控分析才是性能故障分析的根本首要任務。在數據庫服務器內部，如果執行的操作會嚴重受到內存、CPU或磁盤吞吐量中任何一個的影響，則可以將它視為瓶頸。

   因此理解服務器如何運行，資源損耗在哪些方面對問題進行故障診斷是非常有價值有意義的活動，具體案例如下。

這些監控分析優化方法等細節我們在品課學院性能實戰課堂中都會以實戰方式進行實操性測試監控分析實踐理解學習，特別是資源利用問題花費在什么地方，課堂中都會操作項目案例模擬講解，來提高學員對性能監控分析的認知。

1、  識別瓶頸

在分析性能問題時，首先需要識別瓶頸，然后將該瓶頸解決。產生瓶頸的原因通常有兩類，一類是由于錯誤的配置，另一類是達到了軟件或硬件的性能或可伸縮性的限制，例如SQL語法問題。

2、  識別CPU瓶頸

前面章節我們有講到《MYSQL數據庫服務磁盤IO高問題分析與優化》有提到IO 問題故障分析與解決方案，而本章主要是講解CPU 問題定位分析與解決方案。

CPU時間開銷是最昂貴、最寶貴的服務器資源，并且系統總體性能往往對CPU使用率非常敏感。例如，用戶經常可以察覺到高CPU使用率的狀況，就是響應時間慢，超時現象等。

3、瓶頸根源分析

MYSQL自身經常會導致高CPU使用率的情形，包括執行差效率的查詢、哈希連接或多表合并連接、參數設置不合理等。

4、  案例說明如下

測試場景，在壓力測試某銀行系統登錄退出時，因用戶登錄需要查詢對應的客戶相關交易信息等，而需要涉及SQL查詢，這時LR 并發100用戶時，響應時間5秒多，人工登錄發現出現超時錯誤信息，這時通過top命令監控到CPU使用率超過90%，如下圖：

 4.1 前端頁面響應超時：

    4.2 數據庫服務器資源使用率

       4.3 LR響應時間指標分析

      4.4 MYSQL 語法分析

 在監控過程中發現若干SQL語法都是走全表掃描方式，導致響應時間和CPU使用率偏高，其中一個SQL如下

4.6、  優化方法

這時對表的需要檢索字段建立索引后各項性能指標如下圖

這時 同樣是100用戶并發，如下圖建立索引前后響應時間走勢圖：

SQL 檢索數據路徑：

發現雖然建立索引，響應時間降低到2秒以下，但是數據庫服務器cpu資源使用率仍然70%以上，偏高，這時發現緩存命中率不高，針對query_cache適當調整大小后，mysql數據庫cpu使用率講到30%以下和響應時間1秒以下，如下圖。

響應時間指標如下：