本頁面由 Cloud Translation API 翻譯而成。

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

MySQL | PostgreSQL | SQL Server

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

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

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

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

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

使用 MySQL 資料庫稽核功能

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

建議

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

查看「Threads_running」和「Threads_connected」

使用下列查詢查看有效執行緒數量：
```
> SHOW STATUS like 'Threads_%';
```
Threads_running 是 Threads_connected 的子集。其餘執行緒則處於閒置狀態。Threads_running 增加會導致 CPU 使用率提高。建議您檢查這些執行緒上執行的內容。
查看查詢狀態

執行 SHOW PROCESSLIST 指令，查看進行中的查詢。並依序傳回所有已連線的執行緒，以及目前執行的 SQL 陳述式。
```
mysql> SHOW [FULL] PROCESSLIST;
```
請注意「狀態」和「時間長度」欄。檢查是否有許多查詢停滯在相同狀態。
- 如果許多執行緒都顯示 Updating，可能表示記錄鎖定爭用。請參閱下一個步驟。
- 如果許多執行緒都顯示資料表中繼資料鎖定 Waiting，請檢查查詢以瞭解資料表，然後尋找可能保留中繼資料鎖定的 DDL (例如 ALTER TABLE)。如果早期查詢 (例如長時間執行的 SELECT query) 保留資料表中繼資料鎖定，DDL 也可能會等待該鎖定。
檢查記錄鎖定爭用

如果交易鎖定熱門索引記錄，就會禁止其他交易要求相同的鎖定作業。這可能會產生連鎖效應，導致大量要求停滯，並增加 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 範圍指派給相同執行緒。
找出長時間執行的交易
- 使用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
```
如果交易顯示目前長時間執行的陳述式，您可以決定停止這些交易，以減輕伺服器壓力，或是等待重要交易完成。如果較舊的交易記錄沒有顯示任何活動，請前往下一個步驟，查看交易記錄。
檢查長時間執行的交易的 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 中使用記錄檢視器查看記錄。
檢查信號燈爭用情形

在並行環境中，共用資源上的互斥和讀取/寫入閂鎖可能是爭用點，會降低伺服器效能。此外，如果信號燈等待時間超過 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 停用這項功能。
最佳化長查詢SELECT

首先，請檢查查詢。找出查詢目標和取得結果的最佳方式。最佳查詢計畫是盡量減少資料存取的計畫。
- 檢查查詢執行計畫：
```
mysql> EXPLAIN <the query>;
```
請參閱 MySQL 說明文件，瞭解如何解讀輸出內容及評估查詢效率。
- 使用正確的索引
檢查索引鍵資料欄，確認是否使用預期索引。如果不是，請更新索引統計資料：
```
mysql> analyze table <table_name>
```
增加用於計算索引統計資料的樣本網頁數量。詳情請參閱 MySQL 說明文件。
- 充分利用索引
使用多欄索引時，請檢查 key_len 欄，確認索引是否已充分用於篩選記錄。最左側的欄必須是相等比較，且索引可使用至第一個範圍條件 (含該條件)。
- 使用最佳化工具提示
如要確保系統使用正確的索引，也可以套用索引提示和資料表聯結順序提示。
避免使用 READ COMMITTED 產生過長的記錄清單

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

伺服器設定的相關內容非常多，雖然完整說明超出本文範圍，但值得一提的是，伺服器也會回報各種狀態變數，提供相關設定成效的提示。例如：
- 如果 Threads_created/Connections 很大，請調整 thread_cache_size。適當的執行緒快取可縮短執行緒建立時間，並協助處理高並行工作負載。
- 如果 Table_open_cache_misses/Table_open_cache_hits 不重要，請調整 table_open_cache。將資料表存放在資料表快取中，可節省查詢執行時間，在高度並行的環境中，這項功能可能會有顯著效果。
結束不想要的連線

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

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

後續步驟

Google Cloud 建議工具