盡可能改善執行個體 CPU 用量偏高的情形

執行個體 CPU 使用率偏高的原因有很多，例如工作負載增加、交易量大、查詢速度緩慢，以及交易時間過長。

資源不足的執行個體推薦工具會分析 CPU 使用率。 如果 CPU 使用率在過去 30 天內，有相當長的時間都達到或超過 95%， 建議工具就會發出警示，並提供額外深入分析資訊，協助您解決問題。

本文說明如何檢查及最佳化 MySQL 適用的 Cloud SQL 執行個體。如果資源不足的執行個體建議工具指出該執行個體的 CPU 使用率偏高，請按照本文操作。

使用查詢洞察找出 CPU 耗用量高的查詢

查詢洞察可協助您偵測、診斷及預防查詢效能問題，避免 Cloud SQL 資料庫耗用大量 CPU。

使用 MySQL 資料庫稽核功能

使用 MySQL 資料庫稽核功能，查看執行個體的記憶體和耗用量。

建議

CPU 使用率會隨著工作負載成比例增加。如要降低 CPU 使用率，請檢查正在執行的查詢並進行最佳化。請按照下列步驟檢查 CPU 使用量。

  1. 查看「Threads_running」和「Threads_connected」

    使用下列查詢查看有效執行緒數量：

    > SHOW STATUS like 'Threads_%';

    Threads_running 是 Threads_connected 的子集。其餘執行緒則處於閒置狀態。Threads_running 增加會導致 CPU 使用率提高。建議您檢查這些執行緒上執行的內容。

  2. 查看查詢狀態

    執行 SHOW PROCESSLIST 指令，查看進行中的查詢。並依序傳回所有已連線的執行緒，以及目前執行的 SQL 陳述式。

    mysql> SHOW [FULL] PROCESSLIST;

    請注意「狀態」和「時間長度」欄。檢查是否有許多查詢停滯在相同狀態。

    • 如果許多執行緒顯示 Updating，可能會有記錄鎖定爭用情形。請參閱下一個步驟。
    • 如果許多執行緒都顯示資料表中繼資料鎖定 Waiting，請檢查查詢以瞭解資料表，然後尋找可能保留中繼資料鎖定的 DDL (例如 ALTER TABLE)。如果早期查詢 (例如長時間執行的 SELECT query) 保留資料表中繼資料鎖定，DDL 也可能會等待該鎖定。

  3. 檢查記錄鎖定爭用

    如果交易鎖定熱門索引記錄，就會禁止其他交易要求相同的鎖定作業。這可能會產生連鎖效應，導致大量要求停滯，並增加 Threads_running 的值。 如要診斷鎖定爭用情形，請使用 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;

    單一大型 DML 和多個並行小型 DML 都可能導致列鎖定爭用。 您可以按照下列步驟，從應用程式端進行最佳化：

    • 避免長時間交易，因為系統會保留資料列鎖定，直到交易結束為止。
    • 將單一大型 DML 分成多個小型 DML。
    • 將單一資料列 DML 分成小塊。
    • 盡量減少執行緒之間的爭用；舉例來說，如果應用程式程式碼使用連線集區，請將 ID 範圍指派給相同執行緒。

  4. 找出長時間執行的交易

    • 使用SHOW ENGINE INNODB STATUS

      「交易」部分會顯示所有未結交易，並依時間排序 (從最早到最晚)。

      mysql> SHOW ENGINE INNODB STATUS\G
……
------------
TRANSACTIONS
------------
…
---TRANSACTION 245762, ACTIVE 262 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 9210, OS thread handle 140262286128896, query id 202218 localhost root

      從最舊的交易開始，找出下列問題的答案：

      • 這些交易已執行多久？
      • 有多少個鎖定結構體和列鎖定項目？
      • 有多少個復原記錄項目？
      • 連結主機和使用者有什麼好處？
      • 什麼是持續查詢的 SQL 陳述式？

    • 使用information_schema.innodb_trx

      如果 SHOW ENGINE INNODB STATUS 遭到截斷，您可以使用 information_schema.innodb_trx 資料表，以替代方式檢查所有未完成的交易：

      SELECT
 trx_id, trx_state,
 timestampdiff(second, trx_started, now()) AS active_secs,
 timestampdiff(second, trx_wait_started, now()) AS wait_secs, trx_tables_in_use,
 trx_tables_locked,
 trx_lock_structs,
 trx_rows_locked,
 trx_rows_modified,
 trx_query
FROM information_schema.innodb_trx

    如果交易顯示目前長時間執行的陳述式，您可以決定停止這些交易，以減輕伺服器壓力，或是等待重要交易完成。如果較舊的交易記錄沒有顯示任何活動，請前往下一個步驟，查看交易記錄。

  5. 檢查長時間執行的交易的 SQL 陳述式

    • 使用performance_schema

      如要使用 performance_schema，請先開啟這項功能。這項變更需要重新啟動執行個體才會生效。開啟 performance_schema 後，請確認已啟用儀器和消費者：

      SELECT * FROM setup_consumers where name like 'events_statements_history';
SELECT * FROM setup_instruments where name like 'statement/sql/%';

      如果尚未啟用，請按照下列步驟操作：

      UPDATE setup_instruments SET ENABLED = 'YES', timed = 'YES' WHERE NAME LIKE 'statement/%';
UPDATE setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE 'events_statements%';

      根據預設，每個執行緒都會保留 performance_schema_events_statements_history_size 定義的最後 10 個事件。 這些資訊通常足以在應用程式程式碼中找到交易。這項參數不是動態參數。

      使用 mysql thread id (即 processlist_id) 查詢歷史記錄事件：

      SELECT
 t.thread_id,
 event_name,
 sql_text,
 rows_affected,
 rows_examined,
 processlist_id,
 processlist_time,
 processlist_state
FROM events_statements_history h
INNER JOIN threads t
ON h.thread_id = t.thread_id
WHERE processlist_id = <mysql thread id>
ORDER BY event_id;

    • 使用慢速查詢記錄

      如要進行偵錯，您可以將執行時間超過 N 秒的所有查詢擷取到慢速查詢記錄中。如要啟用慢速查詢記錄，請在Google Cloud 主控台或 gcloud CLI 的執行個體頁面中編輯執行個體設定，然後在Google Cloud 主控台或 gloud CLI 中使用記錄檢視器查看記錄。

  6. 檢查信號燈爭用情形

    在並行環境中，共用資源上的互斥和讀取/寫入閂鎖可能是爭用點，會降低伺服器效能。此外，如果信號燈等待時間超過 600 秒，系統可能會當機，以擺脫停滯狀態。

    如要查看信號燈爭用情形，請使用下列指令：

    mysql> SHOW ENGINE INNODB STATUS\G
----------
SEMAPHORES
----------
...
  --Thread 140396021667584 has waited at row0purge.cc line 862 for 241.00 seconds the semaphore:
  S-lock on RW-latch at 0x30c03e8 created in file dict0dict.cc line 1183
  a writer (thread id 140395996489472) has reserved it in mode  exclusive
  number of readers 0, waiters flag 1, lock_word: 0
  Last time read locked in file row0purge.cc line 862
  Last time write locked in file /build/mysql-5.7-FFKPr6/mysql-5.7-5.7.22/storage/innobase/dict/dict0stats.cc line 2376
...

    每次等待信號時，第一行會顯示等待的執行緒、特定信號，以及等待時間長度。如果重複執行 SHOW ENGINE INNODB STATUS 時經常出現信號燈等待情形 (尤其是等待時間超過幾秒)，表示系統遇到並行瓶頸。

    不同工作負載和設定的爭用點不同。

    如果信號燈經常位於 btr0sea.c，則適應性雜湊索引可能就是爭用來源。請嘗試使用 Google Cloud 控制台或 gcloud CLI 停用這項功能。

  7. 最佳化長查詢SELECT

    首先，請檢查查詢。找出查詢目標和取得結果的最佳方式。最佳查詢計畫是盡量減少資料存取的計畫。

    • 檢查查詢執行計畫：
    mysql> EXPLAIN <the query>;

    請參閱 MySQL 說明文件，瞭解如何解讀輸出內容及評估查詢效率。

    • 使用正確的索引

    檢查索引鍵資料欄，確認是否使用預期索引。如果不是，請更新索引統計資料：

    mysql> analyze table <table_name>

    增加用於計算索引統計資料的樣本網頁數量。詳情請參閱 MySQL 說明文件。

    • 充分利用索引

    使用多欄索引時，請檢查 key_len 欄，確認索引是否已充分用於篩選記錄。最左側的資料欄必須是相等比較，且索引最多只能用於第一個範圍條件 (含該條件)。

    • 使用最佳化工具提示

    如要確保系統使用正確的索引，也可以套用索引提示和資料表聯結順序提示。

  8. 使用「READ COMMITTED」時，避免產生過長的記錄清單

    歷史記錄清單是復原表空間中未清除的交易清單。 交易的預設隔離等級為 REPEATABLE READ，這表示交易必須在整個期間讀取相同的快照。因此，SELECT 查詢會封鎖自查詢 (或交易) 開始以來所做的復原記錄清除作業。因此，如果記錄清單過長，查詢效能就會變慢。如要避免建立長長的記錄清單，其中一種方法是將交易隔離等級變更為 READ COMMITTED。使用 READ COMMITTED 時，不再需要保留歷記錄清單，即可維持一致的讀取檢視畫面。您可以為所有工作階段、單一工作階段或下一個單一交易，全域變更交易隔離層級。詳情請參閱 MySQL 說明文件。

  9. 調整伺服器設定

    伺服器設定的相關內容非常多，雖然完整說明超出本文範圍，但值得一提的是，伺服器也會回報各種狀態變數，提供相關設定成效的提示。例如：

    • 如果 Threads_created/Connections 很大，請調整 thread_cache_size。適當的執行緒快取可縮短執行緒建立時間，並協助處理高並行工作負載。
    • 如果 Table_open_cache_misses/Table_open_cache_hits 不重要，請調整 table_open_cache。將資料表存放在資料表快取中，可節省查詢執行時間，在高度並行的環境中，這項功能可能會有顯著效果。

  10. 結束不想要的連線

    如果查詢無效或不再需要，可以停止查詢。如要瞭解如何找出並終止 MySQL 執行緒，請參閱「管理資料庫連線」。

最後，如果 CPU 使用率仍然偏高，且查詢是必要的流量，請考慮增加執行個體的 CPU 資源，以免資料庫當機或停機。

後續步驟