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

發行項
05/02/2024

適用於：適用於 MySQL 的 Azure 資料庫 - 單一伺服器適用於 MySQL 的 Azure 資料庫 - 彈性伺服器

重要

適用於 MySQL 的 Azure 資料庫單一伺服器位於淘汰路徑上。強烈建議您升級至適用於 MySQL 的 Azure 資料庫彈性伺服器。如需移轉至適用於 MySQL 的 Azure 資料庫彈性伺服器的詳細資訊，請參閱適用於 MySQL 的 Azure 資料庫單一伺服器會發生什麼事？

適用於 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 尖峰。因此，請務必一律說明您從 show processlist 擷取的查詢，並確保其執行計畫維持高效率。若要達成此目的，請確保其使用篩選條件/where 子句掃描最少資料列數目、利用索引並避免使用大型暫存排序以及其他不良執行計畫。如需執行計畫的詳細資訊，請參閱 EXPLAIN 輸出格式。

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

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

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

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

mysql> 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 正在執行「show full processlist」陳述式。

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

詳細的目前工作負載分析

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

從 INFORMATION_SCHEMA.PROCESSLIST 資料表產生的伺服器流程清單，您也可以執行 SHOW [FULL] PROCESSLIST 命令來存取此清單。
從 INFORMATION_SCHEMA.INNODB_TRX 資料表擷取的 InnoDB 交易中繼資料。

因為只有其中一個來源的資訊，所以無法描述連線和交易狀態。例如，流程清單不會告知是否有與任何工作階段相關聯的開啟交易。另一方面，交易中繼資料也不會顯示工作階段狀態，以及該狀態所花費的時間。

下列範例查詢結合了流程清單資訊與一些 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。

Share via