執行個體 CPU 使用率偏高的原因有很多,例如工作負載增加、交易量大、查詢速度緩慢,以及交易時間過長。
資源不足的執行個體推薦工具會分析 CPU 使用率。 如果 CPU 使用率在過去 30 天內,有相當長的時間都達到或超過 95%,建議工具就會發出警示,並提供額外深入分析資訊,協助您解決問題。
本文說明如何檢查及最佳化 MySQL 適用的 Cloud SQL 執行個體。如果資源不足的執行個體建議工具指出該執行個體的 CPU 使用率偏高,請按照本文操作。
建議
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 資源,以免資料庫當機或停機。