rds for mysql cpu 性能問題淺析
<a href="#1">1. 原因</a>
<a href="#1.1">1.1 應用負載高</a>
<a href="#1.2" target="_blank">1.2 查詢執行成本高</a>
<a href="#2">2. 解決方法</a>
<a href="#2.1">2.1 相關工具</a>
<a href="#2.2">2.2 應用負載高</a>
<a href="#2.3">2.3 查詢語句執行成本高</a>
<a href="#3">3. 避免出現的一般原則</a>
rds for mysql 執行個體在日常使用中，會碰到 cpu 使用率達到 100% 的情況。比如：
根本原因：
應用送出的查詢通路的 邏輯讀（邏輯 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 總量
公式：
兩種典型場景：
特征：執行個體的 qps 高，查詢比較簡單、單個sql執行成本低（邏輯讀低，需要通路的資料量小）、優化餘地小。
表現：沒有出現慢查詢（或者慢查詢不是問題主要原因），qps 和 cpu 使用率曲線變化吻合。
常見于應用優化過的線上事務交易系統（比如訂單系統）、高讀取率的熱門web網站應用、第三方壓力工具測試中（sysbench）等：
cpu：
qps/tps：
在診斷報告中，沒有對應的 慢查詢（或者該慢查詢不是主要原因），并且 qps/tps 曲線和 cpu 曲線變化吻合
控制台
登入資料庫
dms
執行個體資訊
診斷報告 ：
sql 優化部分沒有需要優化的查詢（或者需要優化的查詢不是主要原因）。
cpu 使用率變化曲線和 qps and tps 變化曲線吻合。
特征：qps 不高；查詢執行成本高、優化餘地大。
表現：存在慢查詢，qps 和 cpu 使用率曲線變化不吻合。
查詢執行成本高，為了獲得結果集需要通路大量的資料（平均邏輯讀高），在 qps 并不高的情況下，rds 執行個體的 cpu 使用率高。
注：由于查詢成本高導緻執行個體 cpu 使用率高是 rds for mysql 非常常見的問題。
dms 和 rds 産品提供了幾種不錯的工具來輔助排查解決執行個體性能問題。
dms主要有：
執行個體診斷報告
sql視窗提供的查詢優化建議 和 檢視執行計劃
執行個體會話
其中執行個體診斷報告，是排查和解決 rds for mysql 執行個體性能問題的快捷工具。
出現性能問題時，建議首先參考下執行個體診斷報告，尤其建議關注診斷報告的 "sql優化"、"會話清單"、"慢sql彙總" 部分（請參考 2.3 小節）。
rds 控制台主要有：
診斷報告
sql分析
慢日志明細、慢日志統計
診斷報告、sql 分析 和 慢日志 等工具友善定位導緻性能問題的具體 sql 。
這種情況 sql 優化的餘地不大，建議考慮從應用架構、執行個體規格等方面來解決：
更新執行個體規格，增加 cpu 資源。
增加隻讀執行個體，将對資料一緻性不敏感的查詢（比如商品種類查詢、列車車次查詢）轉移到隻讀執行個體上，分擔主執行個體壓力。
使用阿裡雲 drds 産品，自動進行分庫分表，将查詢壓力分擔到多個 rds 執行個體上。
使用雲 redis 或 雲 memcache 産品，靜态重複性查詢盡量依靠緩存處理，減輕 rds 執行個體壓力。
對于資料比較靜态、查詢重複度高、查詢結果集小于 1 mb 的應用，考慮開啟查詢緩存（query cache）。
定期歸檔曆史資料、采用分庫分表或者分區的方式減小查詢通路的資料量。
定期優化查詢，減少其執行成本（執行需要通路的表資料行數），提高應用可擴充性。
解決的原則：
定位高成本查詢（通常是慢查詢），優化其執行效率，降低其執行成本。
背景知識 - 如何衡量 sql 的執行效率：
查詢語句的執行效率可以通過其需要掃描的表資料行數 和 結果集資料行數 比率 來衡量。
該比率越小說明查詢語句效率越高。
比如：
#
通路表資料行數
傳回結果集行數
比率
說明
效率
1
1000
10
100
平均每掃描 100 行表資料傳回 1 行結果
比較低
2
20
平均每掃描 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 個會話在執行下面這個查詢：
點選 "sql" 列中的查詢文本，可以顯示完整的查詢和其執行計劃。
通過執行計劃可以看到，對 2 張約為 30 萬行資料表執行了全表掃描。
由于 2 張表是聯接操作，是以這個查詢的執行成本 約為 298267 x 298839 = 大約 900 億，是以查詢會執行相當長的時間并且多個會話會導緻執行個體 cpu 使用率達到 100%。
對比 1.1 小節中的截圖，同樣規格的執行個體對于優化良好的查詢，qps 可以達到 25000；而目前 qps 僅為 5。
注：
在 qps 高導緻 cpu 使用率高的場景中，查詢執行時間通常比較短，show processlist; 或執行個體會話中可能會不容易捕捉到目前執行的查詢。
也可以通過指令
來擷取該查詢 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。
設定 cpu 使用率告警，執行個體 cpu 使用率保證一定的備援度。
應用設計和開發過程中，要考慮查詢的優化，遵守 mysql 優化的一般優化原則，降低查詢的邏輯 io，提高應用可擴充性。
新功能、新子產品上線前，要使用生産環境資料進行壓力測試（可以考慮使用阿裡雲 pts 壓力測試工具）。
新功能、新子產品上線前，建議使用生産環境資料進行回歸測試。
建議經常關注和使用 rds 控制台、dms 中的診斷報告、sql 分析 和 慢日志等資訊。