DBbrain診斷日 | DBA休假，資料庫CPU使用率過高怎麼辦？

2020 年 2 月 14 日
筆記

為更好的幫助DBA運維資料庫，騰訊雲將於每月12日在社群直播開展DBbrain診斷日，騰訊雲高級產品經理迪B哥直播解析經典資料庫運維難題，結合騰訊雲資料庫智慧管家DBbrain的能力，為大家提供問題優化思路和方法，玩轉資料庫！

本期診斷日主要分享內容：如何使用智慧管家DBbrain解決MySQL實例CPU使用率過高的問題？

前言

在使用MySQL的過程中，經常會遇到由於資料庫性能問題導致的業務故障。對於研發、運營、產品等非運維職能的同事來說，往往更願意請DBA來協助定位問題和優化。如果公司確有DBA在，那麼經驗豐富的DBA可能可以依靠經驗快速解決某些常見故障，但大多數情況下往往會通過下面「三板斧」進行排查：

1. 網路層面檢查

比如是否有網路抖動延遲，偶發性大量丟包或者上聯交換機故障等等。

2. OS層面的檢查

常用檢查負載的命令w或者sar -q 1 、通過top查看%us、%wa，sar -d 、iotop確認磁碟I /O等等。

3. 資料庫層面檢查

通過查看processlists、information_schema、slow query、error log等方面溯源問題SQL或者存在的隱患。

對於經驗豐富的DBA來說，這「三板斧」早已瞭然於心，但大多數研發同學為了處理看似簡單的資料庫問題（現象往往是報錯、超時或者業務明顯示卡頓以及不可用），不僅要面對一系列繁雜的命令、參數，而且更重要的一點是通過這些命令和方法查看到的都是故障的「現場」和「證據」，因此還需要依靠豐富的運維經驗去判斷和識別，至於處理方法和優化手段更是極度依靠DBA的資料庫知識體系和實戰經驗的。

如果碰巧遇到DBA休假，就算研發同學會使用「三板斧」，真正處理問題的時候也會面面相覷。

莫慌，騰訊雲資料庫智慧管家DBbrain能夠輕鬆幫助客戶解決資料庫運維問題，是一個可為用戶提供資料庫性能、安全、管理等功能的智慧運維平台。

本文就以「CPU使用率過高」的常見資料庫問題為例，通過理論和實踐相結合的方式為大家介紹如何使用DBbrain來提高資料庫運維效率。

DBbrain處理CPU使用率過高的三大法寶

大家都知道資料庫CPU使用率過高常常容易導致系統異常，比如響應變慢、無法獲取連接、超時（大量的超時重試往往是性能「雪崩」的罪魁禍首）等。而在CPU使用率過高的場景中，很多均是由異常SQL所導致的（大量鎖衝突、鎖等待或事務未提交也有可能導致實例CPU使用率高）。

簡單的分析下CPU使用率過高的原因，當資料庫執行業務查詢、修改語句時，CPU會先從記憶體中請求數據塊（默認是8kB，可以人工調整）。如果記憶體中存在對應的數據，CPU執行計算任務後會將結果返回給用戶（可能涉及到排序類高消耗CPU的動作）。如果記憶體中不存在對應的數據，資料庫會觸發從磁碟獲取數據的動作。這兩個數據獲取過程分別稱為邏輯讀和物理讀。

因此，性能較低的SQL，在執行時容易讓資料庫產生大量的邏輯讀，從而導致CPU使用率過高（也可能讓資料庫產生大量的物理讀，從而導致IOPS和I/O延時過高）。

DBbrain為用戶提供了三大法寶來排查和優化導致CPU使用率過高的異常SQL語句：

一. 異常診斷：7*24小時異常發現診斷，提供實時優化建議。

二. 慢SQL分析：針對當前實例的出現的慢SQL進行分析，並給出慢SQL的優化建議。

三. SQL透視：利用雲資料庫審計數據（全量SQL），多維度深入分析SQL語句並給出優化建議。

下面我們詳細介紹每個法寶的使用方法。

一、使用「異常診斷」功能排查資料庫異常

本功能為官方推薦，「異常診斷」功能提供了「傻瓜式」的故障主動定位和優化，不需要資料庫運維經驗均可理解，而且不僅僅包括CPU使用率過高的異常，幾乎涵蓋所有資料庫高頻的異常和故障。

操作步驟及示例如下：

1. 登錄騰訊雲資料庫管理控制台。

2. 在左側導航欄中，選擇資料庫智慧管家DBbrain > 異常診斷，進入異常診斷主頁。

3. 在左上角選擇（可輸入和搜索）目標實例，切換至目標實例。

4. 在頁面中選擇「實時」或「要查詢的歷史時間」。

5. 若該時間段記憶體在故障，可在「診斷提示」中查看到概要資訊，可點擊查看詳情。

6. 單擊【查看詳情】或【診斷提示】的記錄項可進入診斷詳情頁。

事件概要：包括診斷項、起止時間、風險等級、持續時長、概要等資訊。
現象描述：異常事件（或健康巡檢事件）的外在表現現象的快照和性能趨勢。
智慧分析：分析導致性能異常的根本原因，定位具體操作。
專家建議：提供優化指導建議，包括但不限於 SQL 優化（索引建議、重寫建議）、資源配置優化和參數調優。

7. 單擊【專家建議】，即可查看DBbrain針對該故障給出的優化建議（本例中是SQL語句的優化建議）。本例中的SQL語句執行時缺少對應的索引，導致該語句執行時要進行全表掃描，單次執行成本高，所以大量並發場景下就很容易導致CPU使用率過高，可能會達到100%的狀況。

二、使用「慢SQL分析」功能排查導致CPU使用率過高的SQL

1. 登錄騰訊雲資料庫管理控制台。

2. 在左側導航欄中，選擇資料庫智慧管家DBbrain >慢SQL分析，進入慢SQL分析頁。

3. 在左上角選擇（可輸入和搜索）目標實例，切換至目標實例。

4. 在頁面中選擇要查詢的時間。

5. 若此實例在該時間段中有慢SQL，圖示中會以柱形圖的方式顯示慢SQL產生的時間點和個數。單擊柱形圖，下方的列表就會顯示其對應的所有慢SQL資訊（模板聚合之後的SQL），右方會顯示該時間段內SQL的耗時分布。

6. 針對SQL列表中SQL執行的數據進行判斷和篩選，下面簡單介紹一種判斷方式：

先按照平均耗時（或者最大耗時）逆序，重點關注耗時處在top的SQL（不推薦使用總耗時，容易受到執行次數多而累加的干擾。
然後關注返回行數和掃描行數的值。
發現「返回行數」和與「掃描行數」值相等的SQL，大概率是全表查找並返回了。

接著發現幾行SQL都有很多掃描行數但返回行數都為0或特別小，說明系統產生了大量的邏輯讀和物理讀。當查找的數據量過大且記憶體不足時，該請求必然會產生大量物理I/O請求。而且大量的邏輯讀會佔用大量的CPU資源，導致CPU使用率上漲。

7. 單擊SQL語句，可查看該SQL語句的詳情、資源消耗以及優化建議。

分析頁：可查看完整的 SQL 模板、SQL 樣例以及優化建議和說明，您可根據 DBbrain 給出的專家建議優化 SQL，提升 SQL 品質，降低延遲。

統計頁：可根據統計報表的總鎖等待時間佔比、總掃描行數佔比、總返回行數佔比，橫向分析該條慢 SQL 產生的具體原因，以及進行對應優化。
耗時分布頁：可查看該類型的 SQL（進過聚合後匯總的）運行的時間分布區間，以及來源 IP 的訪問佔比。

三、使用「SQL透視」功能排查導致CPU使用率過高的SQL

1. 登錄騰訊雲資料庫管理控制台。

2. 在左側導航欄中，選擇資料庫智慧管家DBbrain >SQL透視，進入SQL透視頁。

3. 在左上角選擇（可輸入和搜索）目標實例，切換至目標實例。

前提條件：實例需要開通 資料庫審計 功能。如未開通，則在創建 SQL 透視任務時會報如下錯誤，可單擊【一鍵開通】，完成資料庫 SQL 審計的開通和配置。

4. SQL透視圖可選擇 QPS 或慢查詢次數，單擊視圖右上角的【創建審計任務】，選擇任務開始時間和時間間隔，單擊【確定】。任務創建成功後，頁面列表中會顯示任務生成，任務創建完成後，找到目標記錄並單擊「查看SQL分析」，進入SQL分析詳情頁。

5. 在 SQL 分析頁，可選擇 SQL Type、Host、User 或 SQL Code 維度的視圖，並可選擇時間段拉伸視圖來查看具體時間點的數據。下面表格中會展示該時間段內 SQL 的聚合詳情以及執行資訊。若對圖中時間進行部分拉伸選中，表格中的 SQL 數據會隨之變化，只顯示圖中時間範圍內的 SQL 分析結果。

6. SQL分析詳情頁下方表格中展示了聚合後的SQL執行的資訊，包括執行次數、總延遲、最大延遲、最小延遲、總影響行數、最大影響行數、最小影響行數等。可根據各項資訊的組合排序，識別出待優化的SQL。下面列舉一個示例：

根據執行次數逆序排列，以定位執行次數較大或者執行次數有異常變化的語句，然後分析SQL執行次數的合理性並根據DBbrain給出的建議優化SQL語句。

通過查看執行次數、總延遲、最大延遲，可以判斷出執行次數最多的前兩個SQL語句的平均執行延遲很短，說明這兩個SQL語句性能未出現異常。但觀察第三條語句就會明顯發現其單條執行時間在100s左右，因為執行次數較少，故總延遲未在top前2，但這類SQL是需要進行優化的，可點擊SQL查看DBbrain給出的專家建議、資源消耗分析曲線以及來源IP分析等。

還有一類SQL也需要引起重視，可以看到第四條語句的最大延遲和最小延遲相差很大，達到了200s以上，說明整個系統存在波動，需要進一步分析波動是因為網路問題還是數據量變化導致了執行計劃改變。

如果SQL語句的執行次數和平均耗時相對比較合理，而且執行次數大的SQL也是最優的，那麼性能達到瓶頸的話，建議升級實例規格配置或者進行讀寫分離功能來打散執行次數較大的SQL語句。或者採用前置快取資料庫，例如mc、redis等方式進行優化。

避免資料庫出現CPU使用率過高的tips

當然，在我們運維過程中，能避免問題的出現肯定比問題出現再去解決好得多，所以給看到這裡的小夥伴一些避免資料庫出現CPU使用率過高的小妙招：

應用設計和開發過程中，要考慮SQL的性能，遵守MySQL優化的一般優化原則，降低查詢的邏輯IO，避免複雜和低效的SQL堆砌。
新功能上線前，要使用測試環境數據（盡量模擬）進行SQL性能測試。
對核心資料庫實例設置CPU使用率告警（留有餘量），CPU使用率保證一定的冗餘。
熟悉DBbrain的性能優化類的功能（比如實例概覽、異常診斷、慢日誌分析等），時刻掌握資料庫運行情況。同時DBbrain還提供了SQL限流功能，協助業務進行降級來緊急應對性能洪峰。