了解 MySQL CPU 使用情況：工具與技術

MySQL 中的 CPU 使用率是多少？

MySQL中的CPU使用率是指MySQL伺服器用於執行作業（包括處理查詢、管理資料庫事務和維護資料庫系統）的電腦處理器資源的比例。本質上，它測量在任何給定時間有多少 CPU 運算能力專用於運行 MySQL 任務。

當 MySQL CPU 使用率非常高時，這可能表示您的資料庫正在積極處理大量請求或複雜查詢。另一方面，持續較低的 CPU 使用率可能表示您沒有充分利用資源。這可能意味著系統其他地方存在效率低下或瓶頸。

如何檢查MySQL CPU使用率

使用頂部命令

top 指令是 Linux 和其他類 Unix 作業系統（如 CentOS）中可用的通用工具。它提供系統目前狀態的動態即時視圖，包括所有進程的 CPU 使用情況。

1. 打開你的終端機。
2. 執行top指令。

在清單中尋找mysql進程。 %CPU 欄位將顯示 MySQL 執行個體的目前 CPU 使用情況。

使用顯示狀態指令

MySQL 的 SHOW STATUS 指令是一個強大的工具，用於取得各種伺服器狀態資訊。雖然它不會直接顯示 CPU 使用情況，但它允許您透過各種計數器和狀態指示器推斷 CPU 負載。
透過 CLI 或 MySQL 用戶端存取您的 MySQL 伺服器。
執行顯示狀態：如'Threads_%'指令。

從伺服器狀態和變數清單中，threads_running和threads_connected是衡量CPU使用率的兩個最重要的指標：

• Threads_running – 顯示未休眠的執行緒數。這裡的數字較高可能表示 CPU 負載較高。
• Threads_connected – 這顯示了 MySQL 伺服器的開啟連線總數。當此數字明顯高於 threads_running 時，可能表示許多連線處於閒置狀態或正在等待可用的 CPU 資源來處理其要求。

使用 SHOW PROCESSLIST 指令

SHOW PROCESSLIST 指令提供 MySQL 伺服器中所有活動執行緒的即時快照。

1. 透過 CLI 或 MySQL 用戶端存取您的 MySQL 伺服器。
2. 執行顯示進程清單；指令。

輸出包含每個執行緒的幾列資料。請密切注意以下事項：

• 命令列 – 指示正在形成的操作類型。可以在此處識別長時間運行的查詢，尤其是 CPU 密集型查詢。
• 時間列 – 顯示指令運行的時間（以秒為單位）。時間列中值較高的執行緒可能會增加 CPU 負載。

使用性能模式

為了進行更深入的分析，可以使用 MySQL 的 Performance Schema。它提供有關伺服器事件的詳細信息，並且可以查詢以分析每個線程或每個查詢的 CPU 使用情況。

啟用效能架構（如果尚未啟用）：

1. 找到名為 my.cnf 或 my.ini 的 MySQL 設定檔。
2. 新增或修改下列項目performance_schema=ON.
3. 重新啟動 MySQL 伺服器以套用此變更。

執行連線 performance_schema.threads 表與 performance_schema.events_statements_summary_by_thread_by_event_name 表的查詢。此查詢將提供與伺服器執行的不同執行緒和查詢相關的 CPU 使用情況的詳細資訊。

使用外部監控工具

一系列外部監控解決方案，包括 Percona 監控和管理 (PMM) 和 Releem，提供對 MySQL 效能指標（例如 CPU 使用率）的深入分析。

這些工具透過直覺的介面提供 CPU 和相關指標的全面概述，使發現趨勢和問題變得更加容易。

診斷 MySQL 中的高 CPU 使用率

MySQL 資料庫中的高 CPU 使用率會影響依賴應用程式的效能和可靠性。為了有效診斷這個問題，徹底檢查MySQL的運作機制、查詢執行流程以及更廣泛的系統環境是關鍵。

1. 查詢效率低下

CPU 使用率高的最常見原因之一是 SQL 查詢效率低或複雜。缺乏正確索引或涉及全表掃描的查詢可能會導致資料庫引擎消耗過多的 CPU 週期。造成這種低效率的原因是資料庫必須讀取超出必要數量的數據，單獨處理每一行，並在沒有索引的幫助下執行複雜的計算。

• 次優索引使用 – 如果沒有索引，MySQL 必須執行全表掃描，這是高度 CPU 密集的。應根據查詢模式和頻繁存取的列有策略地建立索引。
• 複雜聯結 – 涉及多個聯結的查詢，尤其是在大型表或缺乏適當索引的表上，可能會導致 CPU 使用率較高。每個連接操作都可以倍增處理的資料量。
• 聚合函數 – 對大型資料集進行 SUM()、COUNT()、AVG())，如果沒有適當的索引，會迫使 MySQL 掃描和處理大量資料。

您可以設定慢查詢日誌（啟用並設定您首選的長查詢閾值）或使用 Releem 查詢分析來識別哪些特定查詢需要很長時間才能完成執行。這些是需要您注意的疑問。

2、高並發

MySQL 旨在同時處理多個連線和事務。然而，隨著並發層級的增加，管理這些並發連結的複雜性也隨之增加。一個單獨的線程處理每個活動連接。管理大量執行緒需要更多的 CPU 資源，不僅用於執行查詢，還用於執行緒之間上下文切換的開銷。

3. 鎖爭用

當多個事務同時嘗試存取相同的資料時，就會發生鎖定爭用，導致它們必須等待彼此釋放鎖定才能繼續操作。當交易競爭行或表上的鎖時，資料庫引擎會花費額外的 CPU 週期來管理這些鎖。

您可以透過檢視information_schema.innodb_lock_waits 表來決定是否發生鎖爭用。使用以下查詢來識別被鎖定的交易：

SELECT 
  t.trx_id, 
  t.trx_state, 
  t.trx_started, 
  COUNT(distinct w.requesting_trx_id) AS blocked_trxs
FROM 
  information_schema.innodb_lock_waits w 
INNER JOIN information_schema.innodb_trx t
   ON t.trx_id = w.blocking_trx_id 
GROUP BY t.trx_id,t.trx_state, t.trx_started
ORDER BY t.trx_id;

長時間運作的事務也可以深入了解資料庫內的爭用程度。使用 SHOW ENGINE INNODB STATUS 指令查看從最新到最舊的所有開啟事務的清單。檢查最舊的事務以了解它們運行了多長時間、涉及哪些表和行以及涉及哪些語句。

4. MySQL伺服器配置不當

MySQL 的效能高度依賴它的配置。未針對您的工作負載或硬體進行最佳化的參數可能會導致 CPU 使用效率低。一些關鍵參數包括：

• 不適當的緩衝池大小 – InnoDB 緩衝池透過在記憶體中快取資料和索引來減少磁碟 I/O。緩衝池大小不正確可能會導致頻繁的磁碟讀取。由於系統處理磁碟I/O操作，MySQL CPU佔用率增加。
• 線程快取大小 – 如果線程快取太小，MySQL 可能會花費額外的 CPU 週期為每個新連接建立和銷毀線程，而不是重複使用現有線程。

5. 模式設計問題

資料庫的實體設計，包括表格結構和資料類型，也會影響CPU效率。如果您使用超出需要的資料類型（例如 BIGINT over INT），MySQL 會處理超出所需的數據，從而消耗更多的 CPU 資源。

標準化是一種資料庫設計技術，用於以減少資料冗餘並提高資料完整性的方式組織表。這個過程涉及將資料庫劃分為兩個或多個表並定義表之間的關係。雖然過度標準化可能會導致複雜的連接，但標準化不足可能會導致冗餘資料處理和更大的掃描，這兩者都會增加 CPU 使用率。

緩解 MySQL CPU 使用率過高的 11 個技巧

1. MySQL 的資源控制功能可以幫助防止任何單一使用者消耗過多的 CPU 時間，確保跨應用程式的負載平衡。 Percona 發表了相關文章。
2. 分析查詢模式並在 WHERE 子句、JOIN 條件中以及作為 ORDER BY 和 GROUP BY 語句的一部分經常使用的列上建立索引。請小心過度索引，因為它可能會導致寫入作業期間的開銷增加。
3. 此指令向您展示資料庫如何執行特定查詢，包括使用哪些索引。執行計劃可以揭示查詢是否導致全表掃描，或者查詢是否使用索引效率低下或根本不使用索引。這些結果將成為查詢最佳化的目標。
4. 降低連接的複雜性，尤其是涉及多個表或大型資料集的連接。所有連接表都應該在連接列上有適當的索引。如果某些連線嚴重影響 CPU 使用率，請考慮對架構進行非規範化。
5. 將複雜的查詢分解為更簡單的查詢，以避免全表掃描並減少臨時表的使用，尤其是在磁碟上建立的臨時表。小心使用子查詢和派生表。
6. 調整 InnoDB 緩衝池大小，以將盡可能多的工作資料集放入記憶體中，從而減少磁碟讀取的磁碟 I/O 和 CPU 使用率。理想情況下，緩衝池大小應設定為可用系統記憶體的 70-80% 左右。
7. 對於負載較重或連線頻繁切換的系統來說，執行緒快取大小的預設值可能太低。配置執行緒快取大小以減少建立和銷毀連接執行緒的開銷。起點可以是將執行緒快取大小設定為系統通常在高峰時間處理的並發連線數。
8. 在應用程式層實作連線池，減少頻繁連線和斷開資料庫的開銷。連接池允許在多個客戶端之間重複使用一組連接，從而減少與連接管理相關的 CPU 負載。
9. MySQL 應該運行在具有專用資源的伺服器上。與其他 CPU 密集型應用程式共享資源可能會導致爭用和高 CPU 使用率。
10. 使用符合您的工作負載要求的硬體。在 CPU 密集的場景中，選擇具有更高核心數量和更快時脈速度的處理器可以提高效能。考慮使用 SSD 而不是 HDD 進行存儲，以減少與磁碟 I/O 操作相關的 CPU 負載。
11. MySQL 的效能模式和慢查詢日誌可用於監控資料庫操作並識別高 CPU 使用率模式。這些工具可以幫助找出導致 CPU 使用率過高的低效率查詢和次優配置。

Releem 的 MySQL CPU 最佳化能力

Releem 提供了一系列功能來幫助您監控和管理 CPU 使用情況：

• 直覺的介面：使用者友善的介面以直覺易懂的格式呈現一段時間內的 CPU 使用趨勢。它簡化了優化過程，讓您輕鬆做出明智的決策。無需從其他變數手動解釋 CPU 使用情況或使用 CLI。
• 監控：Releem 監控您的伺服器，捕獲重要參數和關鍵指標，例如 CPU 使用率、InnoDB 緩衝池大小、執行緒快取大小等。這種即時數據收集為您的伺服器效能動態提供了寶貴的見解。 自動性能分析 透過分析監控期間收集的數據，找出可以提高 CPU 效率的領域。
• 設定建議：然後 Releem 會提出易於應用的設定建議來最佳化 MySQL CPU 使用率。這些建議是根據您的伺服器的特定特徵和工作負載模式量身定制的。
• 查詢分析：深入研究慢速查詢日誌，以識別和分析可能佔用 CPU 資源的查詢。有了這些知識，您就可以微調查詢、套用適當的索引並採取其他糾正措施。

如果您想了解這些功能，可以免費註冊並開始使用！

以上是了解 MySQL CPU 使用情況：工具與技術的詳細內容。更多資訊請關注PHP中文網其他相關文章！