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

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

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

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

建議

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

  1. 查看「Threads_running」和「Threads_connected

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

    > SHOW STATUS like 'Threads_%';
    

    Threads_runningThreads_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 資源,以免資料庫當機或停機。

後續步驟