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

問題描述

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

問題原因

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

• 應用負載（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導致查詢成本高

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

1. 通過以下方式定位效率低的查詢：

   • 執行以下SQL語句，查看當前執行的查詢語句。

     show processlist;
     show full processlist;

     系統顯示類似如下結果：

     mysql> show processlist;
     +----------+-------+-------------------+---------+---------+-------+--------------+----------------------------------------------+
     | Id       | User  | Host              | db      | Command | Time  | State        | Info                                         |
     +----------+-------+-------------------+---------+---------+-------+--------------+----------------------------------------------+
     |101031643 | jacky | xxx.xxx.xxx.xx:xx | my_test | Query   | 10760 | Sending data | select b.* from perf_test_no_idx_01 a, ...   |
     |117731567 | jacky | xxx.xxx.xxx.xx:xx | my_test | Query   | 10760 | Sending data | select b.* from perf_test_no_idx_01 a, ...   |
     |134298793 | jacky | xxx.xxx.xxx.xx:xx | my_test | Query   | 10760 | Sending data | select b.* from perf_test_no_idx_01 a, ...   |
     |134384670 | jacky | xxx.xxx.xxx.xx:xx | my_test | Query   |     0 | Init         | show processlist                             |
     |234891284 | jacky | xxx.xxx.xxx.xx:xx | my_test | Query   | 10760 | Sending data | select b.* from perf_test_no_idx_01 a, ...   |
     |235125098 | jacky | xxx.xxx.xxx.xx:xx | my_test | Query   | 10760 | Sending data | select b.* from perf_test_no_idx_01 a, ...   |
     |235200576 | jacky | xxx.xxx.xxx.xx:xx | my_test | Query   | 10760 | Sending data | select b.* from perf_test_no_idx_01 a, ...   |
     |235633985 | jacky | xxx.xxx.xxx.xx:xx | my_test | Query   | 10760 | Sending data | select b.* from perf_test_no_idx_01 a, ...   |
     |235887773 | jacky | xxx.xxx.xxx.xx:xx | my_test | Query   | 10760 | Sending data | select b.* from perf_test_no_idx_01 a, ...   |
     |251990394 | jacky | xxx.xxx.xxx.xx:xx | my_test | Query   | 10760 | Sending data | select b.* from perf_test_no_idx_01 a, ...   |
     |252662718 | jacky | xxx.xxx.xxx.xx:xx | my_test | Query   | 10760 | Sending data | select b.* from perf_test_no_idx_01 a, ...   |
     +----------+-------+-------------------+---------+---------+-------+--------------+----------------------------------------------+
     11 rows in set (0.00 sec)

     查詢時間長、運行狀態為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. 找到目標執行個體，單擊執行個體ID，進入目標執行個體詳情頁。

     4. 在左側導覽列，單擊執行個體會話。

     5. 單擊SQL列中的查詢文本，即可顯示完整的查詢和其執行計畫。

   • 通過SQL洞察和審計持續監控SQL執行效能。在執行個體詳情頁左側導航選擇自治服務 > SQL洞察和審計，可查看SQL執行的詳細耗時、掃描行數等資訊，用於定位高CPU消耗的SQL語句。

2. 得到需要最佳化的查詢語句後，可以通過DMS控制台上的SQL診斷來擷取最佳化建議。診斷報告同樣適用於排查歷史執行個體CPU使用率高的問題：

   1. 通過DMS控制台登入執行個體。

   2. 單擊頁面上方的SQL視窗，選擇對應的庫。

   3. 將查詢語句粘貼到SQL視窗，單擊SQL診斷，即可得到最佳化建議。診斷結果中包含 SQL 陳述式、執行計畫（顯示查詢類型、涉及的表和掃描行數等資訊）以及索引診斷建議（提供可直接執行的 DDL 語句）。

3. 根據您實際情況，選擇最佳化建議進行處理。例如添加索引，確認執行查詢成本會大幅減少。

短期緩解措施

當CPU使用率持續偏高且短期內無法完成慢SQL最佳化時，可採取以下措施快速緩解CPU壓力。

通過SQL限流量控制慢SQL並發

1. 登入RDS管理主控台，在執行個體列表中點擊目標執行個體。

2. 在左側導覽列選擇自治服務 > 一鍵診斷。

3. 點擊會話管理標籤頁。

4. 點擊SQL限流按鈕。

5. 點擊建立限流規則，設定限流模式、限流規則、資料庫、最大並發度和限流時間。

臨時升級執行個體規格

當通過Kill會話和SQL限流仍無法有效緩解CPU壓力時，可臨時升級執行個體規格以增加CPU資源。在執行個體基本資料頁面的配置資訊地區，點擊變更配置進行升級，詳情請參見變更配置。

更多資訊

效能問題排查並解決功能

資料管理工具提供了輔助排查並解決執行個體效能問題的功能，主要有以下幾種。其中，執行個體診斷報告是排查和解決MySQL/MariaDB執行個體效能問題的最佳工具。無論何種原因導致的效能問題，建議您首先查看執行個體診斷報告，尤其是診斷報告中的SQL最佳化、會話列表和慢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 × 單位時間執行的查詢數量。

相關文檔

通過自治服務解決MySQL執行個體CPU使用率過高的問題

適用版本

• 雲資料庫RDS MySQL版

• 雲資料庫RDS MariaDB版