インスタンスでの CPU の高使用率を最適化する

インスタンスで CPU 使用率が高くなる原因は、ワークロードの増加、大量のトランザクション、遅いクエリ、長時間実行トランザクションなどさまざまです。

アンダープロビジョニング インスタンス Recommender は、CPU 使用率を分析します。過去 30 日内で、CPU 使用率レベルがかなりの時間にわたって 95% 以上になった場合、この Recommender はアラートを出して、問題の解決に役立つ補足的な分析情報を提供します。

このドキュメントでは、アンダープロビジョニング インスタンス Recommender によって Cloud SQL for MySQL インスタンスの CPU 使用率が高いことが確認された場合に、そのインスタンスを調査し最適化する方法について説明します。

推奨事項

CPU 使用率はワークロードに比例して増加します。CPU 使用率を抑えるには、実行中のクエリを確認して最適化します。CPU の使用量を確認する手順は次のとおりです。

  1. Threads_running と Threads_connected を確認する

    次のクエリを使用して、アクティブなスレッドの数を確認します。

    > SHOW STATUS like 'Threads_%';

    Threads_running は Threads_connected の一部です。残りのスレッドはアイドル状態です。Threads_running の増加は、CPU 使用率増加の原因になる場合があります。このようなスレッドで何が実行されているのか確認することをおすすめします。

  2. クエリの状態を確認する

    SHOW PROCESSLIST コマンドを実行して、進行中のクエリを表示します。これにより、すべての接続スレッドが順に返され、またこれらのスレッドで現在実行中の SQL ステートメントが返されます。

    mysql> SHOW [FULL] PROCESSLIST;

    state 列と duration 列に注意してください。同じ状態で停止しているクエリが多数ないか確認します。

    • 多くのスレッドで Updating が示されている場合は、レコードロックの競合が発生している可能性があります。次の手順をご覧ください。
    • 多くのスレッドがテーブル メタデータ ロックに対する Waiting を示している場合は、クエリを調べてテーブルを確認し、メタデータ ロックを保持する DDL（ALTER TABLE など）を探します。先行するクエリ（長時間動作している SELECT query など）が保持している場合、DDL がテーブル メタデータのロックを待っていることもあります。

  3. レコードロックの競合を確認する

    トランザクションがよく使用されるインデックス レコードのロックを保持すると、同じロックをリクエストする他のトランザクションはブロックされます。これにより連鎖的な影響が発生し、多くのリクエストが停止して、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;

    1 つの大きな DML や多くの小さな同時実行 DML は、どちらも行ロックの競合を引き起こす可能性があります。これをアプリケーション側から最適化するには、次の操作を行います。

    • 行ロックはトランザクションが終了するまで保持されるため、長いトランザクションは避ける。
    • 1 つの大きな DML を小さな DML に分割する。
    • 1 行の DML を小さなチャンクに一括処理する。
    • スレッド間の競合を最小限に抑える。たとえば、アプリケーション コードが接続プールを使用する場合は、同じスレッドに ID 範囲を割り当てます。

  4. 長時間実行トランザクションを見つける

    • SHOW ENGINE INNODB STATUS を使用する

      TRANSACTIONS セクションには、開いているすべてのトランザクションが古い順に表示されます。

      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

    トランザクションが、現在長時間実行されているステートメントを示している場合は、こうしたトランザクションを停止してサーバーの負荷を軽減するか、重要なトランザクションが完了するまで待つかを選択できます。古いトランザクションが何の動作も示していない場合は、次のステップに進んでトランザクション履歴を確認します。

  5. 長時間実行トランザクションの 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 でログを表示します。

  6. セマフォの競合を確認する

    同時実行の環境では、共有リソースのミューテックスと読み取り / 書き込みラッチが競合ポイントとなり、サーバーのパフォーマンスが低下する可能性があります。さらに、セマフォの待ち時間が 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 を使用して、それを無効にしてみてください。

  7. 長い SELECT クエリを最適化する

    最初にクエリを確認します。クエリの目的と、結果を得る最適な方法を特定します。最適なクエリプランは、データアクセスを最小限に抑えるクエリプランです。

    • クエリ実行プランを確認します。
    mysql> EXPLAIN <the query>;

    出力を解釈してクエリの効率を評価する方法については、MySQL のドキュメントをご覧ください。

    • 適切なインデックスを使用する

    キー列を確認して、想定されるインデックスが使用されているかどうかを確認します。使用されていない場合は、インデックスの統計情報を更新します。

    mysql> analyze table <table_name>

    インデックスの統計情報の計算に使用するサンプルページの数を増やします。詳しくは、MySQL のドキュメントをご覧ください。

    • インデックスを最大限に活用する

    複数列インデックスを使用する場合は、key_len 列を調べて、インデックスがレコードのフィルタリングに完全に活用されているかどうかを確認します。左端の列は等価比較である必要があり、インデックスは最初の範囲条件まで使用できます。

    • オプティマイザー ヒントを使用する

    正しいインデックスを確保する別の方法は、インデックスのヒントとテーブル結合順序のヒントを使用することです。

  8. READ COMMITTED を使用して長い履歴リストを回避する

    履歴リストは、undo テーブルスペース内のパージされていないトランザクションのリストです。トランザクションのデフォルトの分離レベルは、REPEATABLE READ です。この分離レベルでは、トランザクションは期間全体を通して同じスナップショットを読み取る必要があります。したがって、SELECT クエリは、クエリ（またはトランザクション）の開始後に作成された undo ログレコードのパージをブロックします。したがって、長い履歴リストがあるとクエリのパフォーマンスが低下します。長い履歴リストを作成しないようにする 1 つの方法は、トランザクション分離レベルを READ COMMITTED に変更することです。READ COMMITTED では、整合性のある読み取りビューのために履歴リストを保持する必要がありません。トランザクション分離レベルは、すべてのセッション、1 つのセッション、または次の 1 つのトランザクションに対してグローバルに変更できます。詳しくは、MySQL のドキュメントをご覧ください。

  9. サーバー構成を調整する

    サーバーの構成については説明すべき内容が多数あります。全体的なことはこのドキュメントの範囲外になりますが、重要な点は、サーバーも、関連する構成の働きに関するヒントとなるさまざまなステータス変数を報告するということです。次に例を示します。

    • Threads_created/Connections が大きい場合は thread_cache_size を調整します。適切なスレッド キャッシュを使用すると、スレッドの作成時間が短縮され、ワークロードの同時実行数が増えます。
    • Table_open_cache_misses/Table_open_cache_hits を無視できない場合は、table_open_cache を調整します。テーブル キャッシュにテーブルを含めると、クエリの実行時間が節約され、同時実行数が多い環境で効果があります。

  10. 不要な接続を終了する

    クエリが無効な場合や、不要になった場合は、そのクエリを停止できます。MySQL スレッドを特定して終了する方法については、データベース接続を管理するをご覧ください。

最後に、CPU 使用率が依然として高く、クエリによって必要なトラフィックが形成されている場合は、データベースのクラッシュやダウンタイムを避けるため、インスタンスの CPU リソースを増やすことを検討してください。