為「適用於 MySQL 的 Azure 資料庫 - 彈性伺服器」中的高 CPU 使用率疑難排解

發行項
12/18/2024

適用於 MySQL 的 Azure 資料庫彈性伺服器提供一系列計量，可用來識別伺服器上的資源瓶頸和效能問題。若要判斷您的伺服器是否遇到高 CPU 使用率，請監視計量，例如「主機 CPU 百分比」、「總計連線」、「主機記憶體百分比」和「IO 百分比」。有時，檢視這些計量的組合，將深入解析造成適用於 MySQL 的 Azure 資料庫彈性伺服器實例 CPU 使用率增加的原因。

例如，假設連線數突然激增，起始資料庫查詢激增，導致 CPU 使用率暴增。

除了擷取計量之外，請務必追蹤工作負載，以瞭解一或多個查詢是否導致 CPU 使用率激增。

高 CPU 原因

CPU 尖峰可能因各種原因而發生，主要是由於連線尖峰和撰寫不夠周全的 SQL 查詢，或兩者兼具：

連線尖峰

連線增加可能會導致執行緒增加，進而導致 CPU 使用量增加，因為它必須隨著查詢和資源來管理這些連線。若要針對連線尖峰進行疑難排解，應檢查總連線計量，並參閱下一節以取得這些連線的詳細資料。您可以使用下列命令，利用 performance_schema 來識別目前已連線到伺服器的主機和使用者：

目前已連線的主機

select HOST,CURRENT_CONNECTIONS From performance_schema.hosts
where CURRENT_CONNECTIONS > 0
and host not in ('NULL','localhost');

目前已連線的使用者

select USER,CURRENT_CONNECTIONS from performance_schema.users
where CURRENT_CONNECTIONS >0
and USER not in ('NULL','azure_superuser');

撰寫不夠周全的 SQL 查詢

執行和掃描大量沒有索引的資料列，或是執行暫存排序和其他效率不佳計劃的查詢，可能會導致 CPU 尖峰。雖然某些查詢可能會在單一會話中快速執行，但在多個會話中執行時，可能會導致 CPU 尖峰。因此，請務必一律解釋您從顯示進程清單擷取的查詢，並確保其執行計劃有效率。若要達成此目的，請確保其使用篩選條件/where 子句掃描最少資料列數目、利用索引並避免使用大型暫存排序以及其他不良執行計畫。如需執行計畫的詳細資訊，請參閱 EXPLAIN 輸出格式。

擷取目前工作負載的詳細資料

SHOW （FULL） PROCESSLIST 命令會顯示目前連線到適用於 MySQL 的 Azure 資料庫彈性伺服器實例的所有使用者會話清單。此命令還會提供每個工作階段的目前狀態和活動詳細資料。

此命令只會產生目前工作階段狀態的快照集，而且不會提供歷程工作階段活動的相關資訊。

讓我們看看執行此命令的範例輸出。

SHOW FULL PROCESSLIST;
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
| +-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+ |
| 1 | event_scheduler | localhost | NULL | Daemon | 13 | Waiting for next activation | NULL |
| 6 | azure_superuser | 127.0.0.1:33571 | NULL | Sleep | 115 | | NULL |
|
| 24835 | adminuser | 10.1.1.4:39296 | classicmodels | Query | 7 | Sending data | select * from classicmodels.orderdetails; |
| 24837 | adminuser | 10.1.1.4:38208 | NULL | Query | 0 | starting | SHOW FULL PROCESSLIST |
| +-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+ |
| 5 rows in set (0.00 sec) |

客戶擁有的使用者「adminuser」擁有兩個工作階段，這兩個工作階段都來自相同的 IP 位址：

工作階段 24835 在過去七秒內執行了一個 SELECT 陳述式。
工作階段 24837 正在執行「顯示完整進程清單」語句。

必要時，可能需要終止查詢，例如導致生產工作負載 CPU 使用量尖峰的報告或 HTAP 查詢。不過，在嘗試採取動作以減少 CPU 使用率之前，請務必考慮終止查詢的潛在結果。如果發現任何長時間執行的查詢導致 CPU 尖峰，請微調這些查詢，以充分利用資源。

詳細的目前工作負載分析

您必須使用至少兩個資訊來源，以取得工作階段、交易和查詢狀態的準確資訊：

來自INFORMATION_SCHEMA的伺服器進程清單。PROCESSLIST 資料表，您也可以執行 SHOW [FULL] PROCESSLIST 命令來存取此資料表。
來自INFORMATION_SCHEMA的 InnoDB 交易元數據。INNODB_TRX數據表。

只有來自其中一個來源的信息，無法描述連線和交易狀態。例如，進程清單不會通知您是否有與任何會話相關聯的開啟交易。另一方面，交易元數據不會顯示會話狀態和花費在該狀態的時間。

下列範例查詢結合了流程清單資訊與一些 InnoDB 交易中繼資料重要片段：

mysql> select p.id as session_id, p.user, p.host, p.db, p.command, p.time, p.state, substring(p.info, 1, 50) as info, t.trx_started, unix_timestamp(now()) - unix_timestamp(t.trx_started) as trx_age_seconds, t.trx_rows_modified, t.trx_isolation_level   from information_schema.processlist p left join information_schema.innodb_trx t on p.id = t.trx_mysql_thread_id \G

下列範例顯示此查詢的輸出：

****************** 1. row ******************
        session_id: 11
               user: adminuser
               host: 172.31.19.159:53624
                 db: NULL
            command: Sleep
               time: 636
              state: cleaned up
               info: NULL
        trx_started: 2019-08-01 15:25:07
    trx_age_seconds: 2908
  trx_rows_modified: 17825792
trx_isolation_level: REPEATABLE READ
****************** 2. row ******************
         session_id: 12
               user: adminuser
               host: 172.31.19.159:53622
                 db: NULL
            command: Query
               time: 15
              state: executing
               info: select * from classicmodels.orders
        trx_started: NULL
    trx_age_seconds: NULL
  trx_rows_modified: NULL
trx_isolation_level: NULL

下表會按工作階段列出這項資訊的分析。

適用範圍	分析
工作階段 11	此工作階段目前因沒有執行中的查詢而處於閒置 (睡眠中) 狀態，且已持續 636 秒。在會話中，已開啟 2908 秒的交易修改了 17,825,792 個數據列，並使用 REPEATABLE READ 隔離。
工作階段 12	工作階段目前正在執行 SELECT 陳述式，此陳述式已執行 15 秒。該工作階段內沒有任何正在執行的查詢，如 trx_started 和 trx_age_seconds 的 NULL 值所指示。除非會話使用較寬鬆的 READ COMMITTED 隔離，否則會話會繼續保留垃圾收集界限。

如果會話回報為閑置，就不會再執行任何語句。此時，工作階段已完成任何先前的工作，並且正在等候來自用戶端的新陳述式。然而，閒置的工作階段仍須承擔一些 CPU 耗用量和記憶體使用量。

列出開啟交易

下列查詢的輸出會依交易開始時間的順序，提供目前針對資料庫伺服器執行的所有交易清單，可讓您輕鬆識別是否有任何長時間執行且封鎖的交易超過預期執行時間。

SELECT trx_id, trx_mysql_thread_id, trx_state, Unix_timestamp() - ( To_seconds(trx_started) - To_seconds('1970-01-01 00:00:00') ) AS trx_age_seconds, trx_weight, trx_query, trx_tables_in_use, trx_tables_locked, trx_lock_structs, trx_rows_locked, trx_rows_modified, trx_isolation_level, trx_unique_checks, trx_is_read_only FROM information_schema.innodb_trx ORDER BY trx_started ASC;

了解執行緒狀態

在執行期間產生較高 CPU 使用率的交易會有各種狀態的執行緒，如下列各節所述。使用這項資訊以進一步了解查詢生命週期和各種執行緒狀態。

檢查權限/開啟資料表

此狀態通常表示開啟資料表作業會耗用很長的時間。通常，您可以增加資料表快取大小以改善此問題。不過，開啟速度緩慢的資料表也可能表示發生其他問題，例如在同一個資料庫下有太多資料表。

傳送資料

雖然此狀態可能表示執行緒透過網路傳送資料，但也表示查詢正從磁碟或記憶體讀取資料。此狀態可能是由循序資料表掃描所造成。您應檢查 innodb_buffer_pool_reads 和 innodb_buffer_pool_read_requests 的值，以判斷是否從磁碟擷取了大量頁面到記憶體。如需詳細資訊，請參閱針對適用於 MySQL 的 Azure 資料庫 - 彈性伺服器中的低記憶體問題進行疑難解答。

更新

此狀態通常表示執行緒正在執行寫入作業。請檢查效能監視器中的 IO 相關計量，以進一步了解目前工作階段的工作內容。

正在等候 <lock_type> 鎖定

此狀態表示執行緒正在等候第二個鎖定。在大部分情況下，可能是中繼資料鎖定。您應檢閱所有其他執行緒，並查看是誰在鎖定。

了解和分析等候事件

請務必瞭解 MySQL 引擎中的基礎等候事件，因為長時間等候或資料庫中大量的等候可能會導致 CPU 使用率增加。下列範例顯示適當的命令和範例輸出。

SELECT event_name AS wait_event,
count_star AS all_occurrences,
Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_wait_time,
 Concat(Round(avg_timer_wait / 1000000000, 2), ' ms') AS
avg_wait_time
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE count_star > 0 AND event_name <> 'idle'
ORDER BY sum_timer_wait DESC LIMIT 10;

+--------------------------------------+-----------------+-----------------+---------------+
| wait_event | all_occurrences | total_wait_time | avg_wait_time |
| +--------------------------------------+-----------------+-----------------+---------------+ |
| wait/io/file/sql/binlog | 7090 | 255.54 s | 36.04 ms |
| wait/io/file/innodb/innodb_log_file | 17798 | 55.43 s | 3.11 ms |
| wait/io/file/innodb/innodb_data_file | 260227 | 39.67 s | 0.15 ms |
| wait/io/table/sql/handler | 5548985 | 11.73 s | 0.00 ms |
| wait/io/file/sql/FRM | 1237 | 7.61 s | 6.15 ms |
| wait/io/file/sql/dbopt | 28 | 1.89 s | 67.38 ms |
| wait/io/file/myisam/kfile | 92 | 0.76 s | 8.30 ms |
| wait/io/file/myisam/dfile | 271 | 0.53 s | 1.95 ms |
| wait/io/file/sql/file_parser | 18 | 0.32 s | 17.75 ms |
| wait/io/file/sql/slow_log | 2 | 0.05 s | 25.79 ms |
| +--------------------------------------+-----------------+-----------------+---------------+ |
| 10 rows in set (0.00 sec) |

限制 SELECT 陳述式執行時間

如果您不知道涉及 SELECT 查詢的資料庫作業執行成本和運行時間，任何長時間執行的 SELECT 都可能導致資料庫伺服器的不可預測性或波動性。陳述式和交易的大小，以及相關聯的資源使用率，會隨著基礎資料集成長而持續增加。由於這種未繫結的成長，終端使用者終端使用者和交易需要的時間越來越長，耗用的資源越來越多，直到壓垮資料庫伺服器為止。使用未系結的 SELECT 查詢時，建議您設定 max_execution_time 參數，以便中止超過此持續時間的任何查詢。

建議

請確定您的資料庫已配置足夠的資源來執行查詢。有時，您可能需要擴大執行個體大小，以取得更多 CPU 核心以承擔您的工作負載。
將交易分成較小的交易，以避免出現大型或長時間執行的交易。
盡可能在讀取複本伺服器上執行 SELECT 陳述式。
使用「主機 CPU 百分比」上的警示，以便在系統超過任何指定的閾值時收到通知。
使用查詢效能深入解析或 Azure Workbooks 來識別任何有問題或執行速度緩慢的查詢，然後將其最佳化。
針對生產資料庫伺服器，定期收集診斷，以確保所有事項都能順利執行。如果沒有，則請針對您識別的任何問題進行疑難排解，並加以解決。

Stack Overflow

共用方式為

為「適用於 MySQL 的 Azure 資料庫 - 彈性伺服器」中的高 CPU 使用率疑難排解

高 CPU 原因

連線尖峰

撰寫不夠周全的 SQL 查詢

擷取目前工作負載的詳細資料

詳細的目前工作負載分析

列出開啟交易

了解執行緒狀態

檢查權限/開啟資料表

傳送資料

更新

正在等候 <lock_type> 鎖定

了解和分析等候事件

限制 SELECT 陳述式執行時間

建議

意見反應

其他資源

共用方式為

為「適用於 MySQL 的 Azure 資料庫 - 彈性伺服器」中的高 CPU 使用率疑難排解

高 CPU 原因

連線尖峰

撰寫不夠周全的 SQL 查詢

擷取目前工作負載的詳細資料

詳細的目前工作負載分析

列出開啟交易

了解執行緒狀態

檢查權限/開啟資料表

傳送資料

更新

正在等候 <lock_type> 鎖定

了解和分析等候事件

限制 SELECT 陳述式執行時間

建議

相關內容

意見反應

其他資源