解決RDSMySQL/MariaDB版執行個體CPU使用率較高問題 -

問題描述

阿里雲雲資料庫RDS MySQL/MariaDB版使用過程中，出現CPU使用率過高甚至達到100%的情況。

問題原因

應用提交查詢操作或資料修改操作時，系統需要執行大量的邏輯讀操作，其中邏輯IO包含執行查詢所需訪問表的資料行數。所以系統需要消耗大量的CPU資源以維護從儲存系統讀取到記憶體中的資料一致性。本文以CPU使用率為100%的情境為例，介紹了兩個引起該狀況的原因及其解決方案，即應用負載（QPS）高和慢SQL導致查詢成本高。其中由於慢SQL導致查詢成本高（查詢訪問表資料行數多），導致執行個體CPU使用率高是MySQL非常常見的問題。

說明

大量行鎖衝突、行鎖等待或背景工作導致執行個體CPU使用率過高的問題，由於出現的機率非常低，本文不做討論。

應用負載（QPS）高：
- 特徵：執行個體的QPS高，查詢比較簡單，執行效率高，最佳化餘地小。
- 表現：沒有出現慢查詢，或者慢查詢不是主要原因，且QPS和CPU使用率曲線變化吻合。
- 常見情境：該狀況常見於應用最佳化過的線上事務交易系統（例如訂單系統）、高讀取率的熱門Web網站應用程式、第三方壓力工具測試（例如Sysbench）等。
慢SQL導致查詢成本高（查詢訪問表資料行數多）：
- 特徵：執行個體的QPS不高，查詢執行效率低、執行時需要掃描大量表資料、最佳化餘地大。
- 表現：存在慢查詢，QPS和CPU使用率曲線變化不吻合。
- 原因分析：由於查詢執行效率低，為獲得預期的結果需要訪問大量的資料導致平均邏輯IO高，因此在QPS並不高的情況下（例如網站訪問量不大），也會導致執行個體的CPU使用率偏高。

解決方案

根據您實際情況選擇對應的解決方案。

應用負載（QPS）高

對於因應用負載高導致CPU使用率高的狀況，使用SQL進行最佳化的餘地不大，建議您從應用架構、執行個體規格等方面來處理問題。請參考以下方法：

升級執行個體規格，增加CPU資源，詳情請參見變更配置。
增加唯讀執行個體，將對資料一致性不敏感的查詢（比如商品種類查詢、列車車次查詢）轉移到唯讀執行個體上，分擔主執行個體壓力，詳情請參見建立MySQL唯讀執行個體。
使用阿里雲PolarDB-X雲原生分散式資料庫，自動進行分庫分表，將查詢壓力分擔到多個RDS執行個體上。
使用阿里雲KVStore for Memcache或者雲資料庫 Tair（相容 Redis），盡量從緩衝中擷取常用的查詢結果，減輕RDS執行個體的壓力。
對於查詢資料比較靜態、查詢重複度高、查詢結果集小於1MB的應用，考慮開啟查詢快取（Query Cache）。
說明
能否從開啟查詢快取（Query Cache）中獲益需要經過測試，具體設定請參見RDS MySQL查詢快取（Query Cache）的設定和使用。
定期歸檔歷史資料、採用分庫分表或者分區的方式減小查詢訪問的資料量。盡量最佳化查詢，減少查詢的執行成本，提高應用可擴充性。

慢SQL導致查詢成本高

解決該問題的原則：定位效率低的查詢、最佳化查詢的執行效率、降低查詢執行的成本。

通過以下方式定位效率低的查詢：
- 執行以下SQL語句，查看當前執行的查詢語句。
```
show processlist;
show full processlist;
```
  系統顯示類似如下結果：
  查詢時間長、運行狀態為Sending data、Copying to tmp table、Copying to tmp table on disk、Sorting result、Using filesort的查詢會話可能均包含效能問題。
  - 若在QPS高導致CPU使用率高的情境中，查詢執行時間通常比較短，show processlist;命令或執行個體會話中可能會不容易捕捉到當前執行的查詢。但是您可以通過執行以下SQL語句進行查詢。
```
explain [$SQL]
```
    說明
    [$SQL]為有效能問題的SQL查詢語句。
  - 您可以通過執行類似kill [$ID];的命令來終止長時間執行的會話，終止會話請參見RDS MySQL如何終止會話。
    說明
    [$ID]為該查詢語句對應的會話ID。
- 通過資料庫自治服務DAS查看當前執行的查詢：
  1. 登入DAS控制台。
  2. 依次單擊目標執行個體右側的效能 > 執行個體會話。
  3. 單擊SQL列中的查詢文本，即可顯示完整的查詢和其執行計畫。
得到需要最佳化的查詢語句後，可以通過DMS控制台上的SQL診斷來擷取最佳化建議。診斷報告同樣適用於排查歷史執行個體CPU使用率高的問題：
1. 通過DMS控制台登入執行個體。
2. 單擊頁面上方的SQL視窗，選擇對應的庫。
3. 將查詢語句粘貼到SQL視窗，單擊SQL診斷，即可得到最佳化建議。
根據您實際情況，選擇最佳化建議進行處理。例如添加索引，確認執行查詢成本會大幅減少。

更多資訊

效能問題排查並解決功能

資料管理工具提供了輔助排查並解決執行個體效能問題的功能，主要有以下幾種。其中，執行個體診斷報告是排查和解決MySQL/MariaDB執行個體效能問題的最佳工具。無論何種原因導致的效能問題，建議您首先查看執行個體診斷報告，尤其是診斷報告中的SQL最佳化、會話列表和慢SQL匯總。

避免出現CPU使用率達到100%的原則

如何避免CPU使用率達到100%的處理方法如下：

設定CPU使用率警示，保證執行個體CPU使用率有一定的冗餘度。
應用設計和開發過程中，需要考慮查詢的最佳化，遵守MySQL最佳化的一般最佳化原則，降低查詢的邏輯IO，提高應用可擴充性。
新功能、新模組上線前，需要使用生產環境資料進行壓力測試。
新功能、新模組上線前，建議使用生產環境資料進行迴歸測試。

系統資源演算法

下文通過一個簡化的模型來說明系統資源、SQL語句執行成本以及QPS（Query Per Second每秒執行的查詢數）之間的關係：

條件：應用程式模型恒定，即應用沒有修改。
avg_lgc_io：執行每條查詢需要的平均邏輯IO。
total_lgc_io：執行個體的CPU資源在單位時間內能夠處理的邏輯IO總量。
關係公式：total_lgc_io = avg_lgc_io × QPS，即單位時間CPU資源總量 = 執行查詢的平均邏輯IO × 單位時間執行的查詢數量。

適用於

雲資料庫RDS MySQL版
雲資料庫RDS MariaDB版

：RDS MySQL/MariaDB版執行個體CPU使用率較高