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

CPU 使用率が大きくなると、インスタンスのパフォーマンスに悪影響を及ぼします。インスタンスで実行されるアクティビティには、CPU が使用されます。そのため、高い CPU 使用率の通知がある場合は、まず、適切に記述されていないクエリ、長時間実行トランザクション、その他のデータベースアクティビティなど、問題の根本原因を特定する必要があります。

このドキュメントでは、インスタンスの CPU ボトルネックを特定し、インスタンスの CPU 使用率の問題を改善する方法について説明します。

CPU のボトルネックを特定する

Query Insights を使用して、CPU 使用量が高いクエリを特定する

Query Insights では、Cloud SQL データベースに対するクエリパフォーマンスの問題を検出、診断、防止できます。

`pg_proctab` 拡張機能を使用する

pg_proctab 拡張機能と pg_top ユーティリティを組み合わせて使用し、プロセスごとの CPU 使用率情報が入ったオペレーティングシステムの出力を取得します。

クエリを使用する

状態別にアクティブな接続を特定する

データベースへのアクティブな各接続は、ある程度の CPU を使用するため、インスタンスの接続数が多いと、累積使用率が高くなることがあります。次のクエリを使用して、状態別に接続数に関する情報を取得します。

SELECT
  state,
  usename,
  count(1)
FROM
  pg_stat_activity
WHERE
  pid <> pg_backend_pid()
group by
  state,
  usename
order by
  1;

出力は次のようになります。


        state        |    usename    | count
---------------------+---------------+-------
 active              | ltest         |   318
 active              | sbtest        |    95
 active              |               |     2
 idle                | cloudsqladmin |     2
 idle in transaction | ltest         |    32
 idle in transaction | sbtest        |     5
                     | cloudsqladmin |     3
                     |               |     4
(8 rows)

アクティブな接続数が多い場合は、長時間動作しているクエリを確認するか、クエリの実行をブロックしているイベントの終了を待ちます。

アイドル状態の接続数が多い場合は、必要な承認を行った後に次のクエリを実行して接続を終了します。

SELECT
  pg_terminate_backend(pid)
FROM
  pg_stat_activity
WHERE
  usename = 'sbtest'
  and pid <> pg_backend_pid()
  and state in ('idle');

次のクエリを使用して、pg_terminate_backend で接続を個別に終了することもできます。

SELECT pg_terminate_backend (<pid>);

ここで、PID は pg_stat_activity から取得できます。

長時間実行の接続を特定する

以下に、長時間動作しているクエリを返すクエリの例を示します。この場合、5 分以上アクティブになっているクエリを特定できます。

SELECT
  pid,
  query_start,
  xact_start,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM
  pg_stat_activity
WHERE
  (
    now() - pg_stat_activity.query_start
  ) > interval '5 minutes' order by 4 desc;

説明プランを確認し不適切なクエリを特定する

説明プランを使用して、適切に記述されていないクエリを調査し、必要に応じてクエリを書き直します。必要に応じて、次のコマンドを使用して必要な長時間動作しているクエリのキャンセルを検討します。

SELECT pg_cancel_backend(<pid>);

VACUUM アクティビティをモニタリングする

デッドタプルを消去する AUTOVACUUM アクティビティは、CPU 使用率の高いオペレーションです。インスタンスが PostgreSQL バージョン 11 以降を使用している場合は、次のクエリを使用して、アクティブな AUTOVACUUM や VACUUM のアクティビティがあるかどうかを確認します。

SELECT
  relid :: regclass,
  pid,
  phase,
  heap_blks_total,
  heap_blks_scanned,
  heap_blks_vacuumed,
  index_vacuum_count,
  max_dead_tuples,
  num_dead_tuples
FROM
  pg_stat_progress_vacuum;

次のクエリを使用して、インスタンスに進行中の VACUUM アクティビティがあるかどうかを確認します。

SELECT
  pid,
  datname,
  usename,
  query
FROM
  pg_stat_activity
WHERE
  query like '%vacuum%';

さらに、PostgreSQL の VACUUM オペレーションを最適化してトラブルシューティングすることもできます。

pg_stat_statements 拡張機能を追加する

インスタンスアクティビティに関する拡張辞書情報を取得するために、pg_stat_statements 拡張機能を設定します。

定期的なチェックポイント

定期的なチェックポイントは、パフォーマンスを低下させます。PostgreSQL アラートログに checkpoint occurring too frequently の警告が報告される場合は、checkpoint_timeout フラグの調整を検討してください。

統計情報を収集する

クエリプランナーがテーブルに関する最新の統計情報を取得して、クエリに最適なプランを選択できるようにします。ANALYZE オペレーションは、データベース内のテーブルの内容に関する統計情報を収集し、その結果を pg_statistic システムカタログに保存します。その後、クエリプランナーはこれらの統計情報を使用して、クエリで最も効率的な実行プランの決定をサポートします。AUTOVACUUM プロセスは、テーブルを定期的に自動で分析するため、次のコマンドを実行して、すべてのテーブルが分析されており、最新のメタデータがプランナーで利用できるかどうかを確認します。

SELECT
  relname,
  last_autovacuum,
  last_autoanalyze
FROM
  pg_stat_user_tables;

不適切なシステム設定

クエリのパフォーマンスに影響を与える他の要因とフラグ設定またはシステム要因があります。次のクエリを実行して、待機イベントと待機イベントタイプを確認し、他のシステム設定のパフォーマンスに関する分析情報を取得します。

SELECT
  datname,
  usename,
  (
    case when usename is not null then state else query end
  ) AS what,
  wait_event_type,
  wait_event,
  backend_type,
  count(*)
FROM
  pg_stat_activity
GROUP BY
  1,
  2,
  3,
  4,
  5,
  6
ORDER BY
  1,
  2,
  3,
  4 nulls first,
  5,
  6;