RDS for MySQL CPU 性能問題淺析

1. 原因

1.1 應用負載高

1.2 查詢執行成本高

2. 解決方法

2.1 相關工具

2.2 應用負載高

2.3 查詢語句執行成本高

3. 避免出現的一般原則

---

RDS for MySQL 實例在日常使用中，會碰到 CPU 使用率達到 100% 的情況。比如：

1. 原因

根本原因：
應用提交的查詢訪問的 邏輯讀（邏輯 IO） 總量 （需要訪問的 表 數據） 過高。
大量邏輯讀會導致數據緩存 Buffer Pool 中用於維護數據一致性的 Latch 和 Mutex 爭搶過於頻繁，進而大量消耗 CPU 資源。

背景知識：

• 物理讀 - 當執行一個查詢時，為了返回滿足查詢的結果集，係統必須訪問 表 中的數據。這些數據以 16 KB 大小的數據頁（Page，Oracle DB 中稱之為 Block）形式存儲在磁盤上。當查詢需要訪問該數據時，如果該數據 不在 InnoDB Buffer Pool 中，則係統會將該頁從磁盤上的數據文件中加載到 InnoDB Buffer Pool 中，每一個 16 KB 頁的加載動作被稱之為一個物理讀（物理 IO）。
• 邏輯讀 - 檔執行一個查詢時，為了返回滿足查詢的結果集，係統必須訪問 表 中的數據。這些數據以 16 KB 大小的數據頁（Page，Oracle DB 中稱之為 Block）形式存儲在磁盤上。當查詢需要訪問該數據時，如果該數據 在 InnoDB Buffer Pool 中，則對每一個 16 KB 頁的內存訪問稱之為一個邏輯讀（邏輯 IO）。
• TPS - Transaction Per Second， 每秒的事務數。
• QPS - Query Per Second，每秒的查詢數。

    物理讀涉及到 IOPS 資源的消耗，邏輯讀涉及到 CPU 資源的消耗。

注：本文不排除由於其他原因（比如大量行鎖衝突、行鎖等待）導致的實例 CPU 使用率高，但這種情況出現的概率非常低，在此不做討論。

通過一個簡化的公式來說明 CPU資源、語句執行成本 以及 QPS 之間的關係：

條件：應用模型恒定

avg_lgc_io：每條查詢執行需要的平均邏輯 IO ，可以簡化為 查詢 需要訪問 的 表 數據行數。

total_lgc_io：實例 CPU 資源單位時間能夠處理的 邏輯IO 總量

公式：

total_lgc_io = avg_lgc_io x QPS 
單位時間 CPU 資源 = 查詢執行平均成本 x 單位時間執行的查詢數量

 兩種典型場景：

1.1 應用負載高

特征：實例的 QPS 高，查詢比較簡單、單個SQL執行成本低（邏輯讀低，需要訪問的數據量小）、優化餘地小。

表現：沒有出現慢查詢（或者慢查詢不是問題主要原因），QPS 和 CPU 使用率曲線變化吻合。

常見於應用優化過的在線事務交易係統（比如訂單係統）、高讀取率的熱門Web網站應用、第三方壓力工具測試中（Sysbench）等：

CPU：

QPS/TPS：

在診斷報告中，沒有對應的 慢查詢（或者該慢查詢不是主要原因），並且 QPS/TPS 曲線和 CPU 曲線變化吻合 
控製台  登錄數據庫  DMS  實例信息  診斷報告 ：

SQL 優化部分沒有需要優化的查詢（或者需要優化的查詢不是主要原因）。

  CPU 使用率變化曲線和 QPS and TPS 變化曲線吻合。

1.2. 查詢執行成本高

特征：QPS 不高；查詢執行成本高、優化餘地大。

表現：存在慢查詢，QPS 和 CPU 使用率曲線變化不吻合。

查詢執行成本高，為了獲得結果集需要訪問大量的數據（平均邏輯讀高），在 QPS 並不高的情況下，RDS 實例的 CPU 使用率高。

注：由於查詢成本高導致實例 CPU 使用率高是 RDS for MySQL 非常常見的問題。 

2 解決方法 

2.1 相關工具

DMS 和 RDS 產品提供了幾種不錯的工具來輔助排查解決實例性能問題。
DMS主要有：

• 實例診斷報告

• SQL窗口提供的查詢優化建議 和 查看執行計劃

• 實例會話

其中實例診斷報告，是排查和解決 RDS for MySQL 實例性能問題的快捷工具。
出現性能問題時，建議首先參考下實例診斷報告，尤其建議關注診斷報告的 "SQL優化"、"會話列表"、"慢SQL匯總"  部分（請參考 2.3 小節）。

RDS 控製台主要有：

• 診斷報告

• SQL分析

• 慢日誌明細、慢日誌統計

診斷報告、SQL 分析 和 慢日誌 等工具方便定位導致性能問題的具體 SQL 。

2.2 應用負載高

這種情況 SQL 優化的餘地不大，建議考慮從應用架構、實例規格等方麵來解決：

• 升級實例規格，增加 CPU 資源。

• 增加隻讀實例，將對數據一致性不敏感的查詢（比如商品種類查詢、列車車次查詢）轉移到隻讀實例上，分擔主實例壓力。

• 使用阿裏雲 DRDS 產品，自動進行分庫分表，將查詢壓力分擔到多個 RDS 實例上。

• 使用雲 Redis 或 雲 Memcache 產品，靜態重複性查詢盡量依靠緩存處理，減輕 RDS 實例壓力。

• 對於數據比較靜態、查詢重複度高、查詢結果集小於 1 MB 的應用，考慮開啟查詢緩存（Query Cache）。

• 定期歸檔曆史數據、采用分庫分表或者分區的方式減小查詢訪問的數據量。

• 定期優化查詢，減少其執行成本（執行需要訪問的表數據行數），提高應用可擴展性。

  注：能否從開啟查詢緩存（Query Cache）中獲益需要經過測試，具體設置請參考 RDS for MySQL 查詢緩存（Query Cache）的設置和使用。

2.3 查詢語句執行成本高

解決的原則：
定位高成本查詢（通常是慢查詢），優化其執行效率，降低其執行成本。
背景知識 - 如何衡量 SQL 的執行效率：
查詢語句的執行效率可以通過其需要掃描的表數據行數 和 結果集數據行數 比率 來衡量。
該比率越小說明查詢語句效率越高。
比如：

#	訪問表數據行數	返回結果集行數	比率	說明	效率
1	1000	10	100	平均每掃描 100 行表數據返回 1 行結果	比較低
2	20	10	2	平均每掃描 2 行表數據返回 1 行結果	很高

2.2.1  

如果 當前 CPU 使用率比較高，可以通過 show processlist; 、show full processlist; 命令或者 DMS  實例信息  實例會話 來查看當前執行的查詢（繼續1.2小節中的例子）：

對於查詢時間長、運行狀態（State 列）是"Sending data"，"Copying to tmp table"、"Copying to tmp table on disk"、"Sorting result"、"Using filesort" 、“Creating Sort Index”等都是可能有性能問題的查詢。

可以通過執行 kill 101031643; 命令來終止該長時間執行的會話。

注：關於長時間執行會話的管理，請參考 RDS for MySQL 管理長時間運行查詢。

可以看到有 10 個會話在執行下麵這個查詢：

select b.*
  from perf_test_no_idx_01 a,
       perf_test_no_idx_02 b
 where a.created_on>= '2015-01-01'
   and a.detail= b.detail;

 點擊 "SQL" 列中的查詢文本，可以顯示完整的查詢和其執行計劃。

通過執行計劃可以看到，對 2 張約為 30 萬行數據表執行了全表掃描。
由於 2 張表是聯接操作，因此這個查詢的執行成本 約為 298267 x 298839 = 大約 900 億，因此查詢會執行相當長的時間並且多個會話會導致實例 CPU 使用率達到 100%。
對比 1.1 小節中的截圖，同樣規格的實例對於優化良好的查詢，QPS 可以達到 25000；而當前 QPS 僅為 5。

注：
在 QPS 高導致 CPU 使用率高的場景中，查詢執行時間通常比較短，show processlist; 或實例會話中可能會不容易捕捉到當前執行的查詢。

也可以通過命令

explain select b.* 
from perf_test_no_idx_01 a, perf_test_no_idx_02 b 
where a.created_on >= 2015-01-01 
and a.detail = b.detail 

來獲取該查詢 SQL 的執行計劃，或者在 SQL 窗口的"執行計劃"子標簽頁獲取。

2.2.2

得到需要優化的查詢後，可以通過 DMS  SQL 窗口  優化按鈕 來獲取查詢的優化建議：

 

根據診斷報告的優化建議，添加索引後查詢執行成本大幅減少，從 900 億行減小到 30 萬行，查詢成本降低 30 萬倍，CPU 使用率 100% 的問題解決。

2.2.3 

對於非當前的負載問題，可以通過 實例診斷報告（DMS  實例信息  診斷報告）獲取優化建議，來達到優化的目的。

點擊"發起診斷" 按鈕，可以創建一個針對當前實例運行情況的報告。

對於CPU使用率高的問題，建議關注診斷報告的 "SQL優化"、"會話列表"、"慢SQL匯總"  部分。

注：對於 QPS 高和查詢效率低的混合模式導致的 CPU 使用率高問題，建議從優化查詢入手。

 
2.2.4 
RDS 控製台的 診斷報告 （控製台  性能優化  診斷報告）會提供 實例整體的 SQL 執行分析，便於快速的定位到問題 SQL。

反饋存在問題嫌疑的 SQL。

3 避免出現 CPU 使用率達到 100% 影響業務的一般原則

• 設置 CPU 使用率告警，實例 CPU 使用率保證一定的冗餘度。

• 應用設計和開發過程中，要考慮查詢的優化，遵守 MySQL 優化的一般優化原則，降低查詢的邏輯 IO，提高應用可擴展性。

• 新功能、新模塊上線前，要使用生產環境數據進行壓力測試（可以考慮使用阿裏雲 PTS 壓力測試工具）。

• 新功能、新模塊上線前，建議使用生產環境數據進行回歸測試。

• 建議經常關注和使用 RDS 控製台、DMS 中的診斷報告、SQL 分析 和 慢日誌等信息。