Azure Database for MySQL - フレキシブルサーバーでの CPU 使用率の高さをトラブルシューティングする

[アーティクル]
12/19/2023

適用対象: Azure Database for MySQL - 単一サーバー Azure Database for MySQL - フレキシブルサーバー

重要

Azure Database for MySQL の単一サーバーは提供終了パスにあります。 Azure Database for MySQL フレキシブルサーバーにアップグレードすることを強くお勧めします。 Azure Database for MySQL フレキシブルサーバーへの移行の詳細については、「Azure Database for MySQL 単一サーバーの動作」を参照してください。

Azure Database for MySQL フレキシブルサーバーには、サーバー上のリソースのボトルネックとパフォーマンスの問題を特定するために使用できるさまざまなメトリックが用意されています。サーバーで CPU 使用率が高いかどうかを判断するには、"ホスト CPU 使用率"、"合計接続数"、"ホストメモリの割合"、"IO 割合" などのメトリックを監視します。これらのメトリックの組み合わせを表示すると、Azure Database for MySQL フレキシブルサーバーインスタンスでの CPU 使用率の増加の原因に関する分析情報が得られる場合があります。

たとえば、CPU 使用率が上昇する原因となるデータベースクエリの急増の発端となる接続の急増を考えてみましょう。

メトリックのキャプチャに加えて、ワークロードをトレースして、1 つ以上のクエリが 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 プロセスリストからキャプチャしたクエリを常に説明し、実行プランが効率的であることを確認することが重要です。これを実現するには、フィルター/where 句を使用して最小限の数の行をスキャンし、インデックスを使用し、大規模な一時的な並べ替えを他の不適切な実行プランと共に使用しないようにします。実行プランの詳細については、「EXPLAIN 出力形式」を参照してください。

現在のワークロードの詳細のキャプチャ

SHOW (FULL) PROCESSLIST コマンドは、現在 Azure Database for MySQL フレキシブルサーバーインスタンスに接続されているすべてのユーザーセッションの一覧を表示します。また、各セッションの現在の状態とアクティビティに関する詳細も提供します。

このコマンドは、現在のセッション状態のスナップショットのみを生成し、セッションの履歴アクティビティに関する情報を提供しません。

このコマンドの実行からの出力例を見てみましょう。

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" が所有するセッションが 2 つあり、どちらも同じ IP アドレスから取得されていることに注意してください。

セッション 24835 では、過去 7 秒間、SELECT ステートメントが実行されています。
セッション 24837 では、"show full processlist" ステートメントが実行されています。

必要に応じて、運用ワークロードの CPU 使用率が急増する原因となったレポートまたは HTAP クエリなどのクエリを終了することが必要になる場合があります。ただし、CPU 使用率を下げるためにアクションを実行する前に、クエリを終了した場合の潜在的な結果を常に考慮してください。 CPU スパイクにつながる長時間実行されるクエリが特定された場合は、リソースが最適に使用されるようにこれらのクエリを調整します。

詳細な現在のワークロード分析

セッション、トランザクション、クエリの状態に関する正確な情報を取得するには、少なくとも 2 つの情報ソースを使用する必要があります。

INFORMATION_SCHEMA.PROCESSLIST テーブルのサーバーのプロセス一覧です。SHOW [FULL] PROCESSLIST コマンドを実行してアクセスすることもできます。
INFORMATION_SCHEMA.INNODB_TRX テーブルからの InnoDB のトランザクションメタデータです。

これらのうち 1 つだけのソースの情報では、接続とトランザクションの状態を説明することはできません。たとえば、プロセスの一覧では、セッションに関連付けられている開いているトランザクションがあるかどうかは通知されません。一方、トランザクションメタデータには、セッションの状態とその状態で費やされた時間は表示されません。

次のクエリ例では、プロセスリスト情報と 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	セッションは現在、これまで 15 秒間実行されている SELECT ステートメントを実行しています。 trx_started と trx_age_seconds の NULL 値で示されているように、セッション内で実行されているクエリはありません。セッションでは、より緩やかな READ COMMITTED 分離を使用していない限り、ガベージコレクションが実行されている限り、その境界を保持し続けます。

領域

分析

セッション 11

このセッションは現在アイドル状態 (スリープ状態) であり、クエリは実行されておらず、636 秒間その状態でした。セッション内で、過去 2908 秒間開かれているトランザクションでは 17,825,792 行が変更され、REPEATABLE READ 分離が使用されています。

セッション 12

セッションは現在、これまで 15 秒間実行されている SELECT ステートメントを実行しています。 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 の値を確認する必要があります。詳細については、Azure Database for MySQL フレキシブルサーバーのメモリ不足に関する問題のトラブルシューティングを参照してください。

更新中

通常、この状態は、スレッドで書き込み操作が実行されていることを意味します。パフォーマンスモニターの IO 関連のメトリックを確認して、現在のセッションの内容について理解を深めましょう。

<lock_type> ロックを待機中

この状態は、スレッドが 2 つ目のロックを待機していることを示します。ほとんどの場合、メタデータロックである可能性があります。他のすべてのスレッドを確認し、ロックを取得している対象を確認する必要があります。

待機イベントの理解と分析

データベース内で長い待機または多数の待機が発生すると CPU 使用率が増加する可能性があるため、MySQL エンジンの基になる待機イベントを理解することが重要です。次の例は、適切なコマンドとサンプル出力を示しています。

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 パラメーターを構成することをお勧めします。

Recommendations

クエリを実行するのに十分なリソースがデータベースに割り当てられていることを確認します。場合によっては、インスタンスサイズをスケールアップして、ワークロードに合わせて CPU コアをさらに取得する必要が生じることもあります。
小さいトランザクションに分割することにより、大規模または実行時間の長いトランザクションを回避します。
可能な場合は、読み取りレプリカサーバーで SELECT ステートメントを実行します。
システムが指定されたしきい値のいずれかを超えた場合に通知を受け取るように、"ホストの CPU 使用率" アラートを使用します。
Query Performance Insights または Azure Workbooks を使用して、問題が発生していたり実行に時間がかかったりするクエリを特定し、それらを最適化します。
運用データベースサーバーの場合は、診断を定期的に収集して、すべてがスムーズに実行されていることを確認します。そうでない場合は、特定した問題をトラブルシューティングして解決します。

次の手順

重要度の高い質問に対する回答を見つける、質問を投稿する、または回答するには、Stack Overflow にアクセスしてください。

Azure Database for MySQL - フレキシブル サーバーでの CPU 使用率の高さをトラブルシューティングする