Amazon RDS または Amazon Aurora PostgreSQL の CPU 使用率が高い場合のトラブルシューティング方法を教えてください。

簡単な説明

負荷による CPU 使用率が高い場合は、次のツールを組み合わせて原因を特定します。

• Amazon CloudWatch メトリクス
• 拡張モニタリングのメトリクス
• Performance Insights メトリクス
• ネイティブの PostgreSQL のビューとカタログ ( pg_stat_statements、pg_stat_activity、および pg_stat_user_tables など)
• データベース内のアイドル接続
• ANALYZE コマンド
• 長時間実行されるクエリ、自動バキューム、ロック待機、および接続と切断のリクエストをログ記録するための PostgreSQL ログ記録パラメータ

解決策

Amazon CloudWatch メトリクス

CloudWatch メトリクスを使用して、長期間にわたる CPU パターンを識別します。WriteIOPs、ReadIOPs、ReadThroughput、および WriteThroughput のグラフを CPU 使用率と比較して、ワークロードによって高い CPU が発生した時間を調べます。

時間枠が特定されたら、DB インスタンスに関連付けられている拡張モニタリングのデータを確認します。1、5、10、15、30、または 60 秒の間隔でデータを収集するように拡張モニタリングを設定できます。これにより、CloudWatch よりも詳細なレベルでデータを収集できます。

拡張モニタリング

拡張モニタリングでは、オペレーティングシステム (OS) レベルのビューを提供します。このビューは、CPU の負荷が高くなっている原因を詳しく特定するのに役立ちます。例えば、負荷平均、CPU 配分 (System% または Nice%)、OS プロセスリストを確認できます。

拡張モニタリングを使用すると、1、5、および 15 分間隔で loadAverageMinute データを確認できます。負荷平均が vCPU の数よりも大きい場合は、インスタンスが高負荷の状態であることを示しています。負荷平均が DB インスタンスクラスの vCPU の数より小さい場合、CPU スロットリングがアプリケーションのレイテンシーの原因ではない可能性があります。CPU 使用率の原因を診断する際に誤検出を避けるためにも、負荷平均を確認してください。

例えば、プロビジョンド IOPS が 3000 の db.m5.2xlarge インスタンスクラスを使用する DB インスタンスがあり、CPU 制限に達したとします。次の例では、インスタンスクラスに 8 個の vCPU が関連付けられています。同じ負荷平均で 170 を超える場合は、測定された時間枠の間、マシンが重い負荷を受けていることを示します。

負荷平均 (分)

15	170.25
5	391.31
1	596.74

CPU 使用率

ユーザー (%)	0.71
システム (%)	4.9
Nice (%)	93.92
合計 (%)	99.97

**注:**拡張モニタリングの Nice% は、データベースに対してワークロードが使用している CPU の量を表します。

拡張モニタリングを有効にすると、DB インスタンスに関連付けられている OS プロセスリストも確認できます。また、最大 100 個のプロセスが表示されるため、パフォーマンスに最大の影響を与えるプロセスを特定するのに役立ちます。拡張モニタリングの結果を pg_stat_activity の結果と組み合わせると、クエリのリソース使用状況を特定しやすくなります。

Performance Insights

Amazon RDS Performance Insights を使用して、データベース負荷の原因となっているクエリを特定します。そのためには、特定の時間枠に対応する SQL タブを確認します。

ネイティブ PostgreSQL のビューとカタログ

データベースエンジンレベルでは、pg_stat_activity および pg_stat_statements を使用できます。問題がリアルタイムで発生する場合には、pg_stat_activity または pg_stat_statements を使用して、最も多くのトラフィックを送信するマシン、クライアント、IP アドレスをグループ化します。このデータを使用して、時間の経過に伴う増加やアプリケーションサーバーでの増加を確認することもできます。また、アプリケーションサーバーでのセッションの停止や、ロックの問題が発生していないかについても確認できます。詳細については、PostgreSQL ウェブサイトの pg_stat_activity と pg_stat_statements を参照してください。

pg_stat_statements を有効にするには、既存のカスタムパラメータグループを変更し、次の値を設定します。

• pg_stat_statements を shared_preload_libraries に追加する
• track_activity_query_size = 4096
• pg_stat_statements.track = ALL
• pg_stat_statements.max = 10000

[すぐに適用] を選択し、DB インスタンスを再起動します。次に、監視するデータベースで次のようなコマンドを実行します。

demo=> select current_database();current_database
------------------
demo
(1 row)     

demo=> CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

注: 上記のコマンドで、デモデータベースに拡張機能をインストールします。

pg_stat_statements をセットアップした後、次の方法のうちのいずれかを使用して出力を監視します。

total_time でクエリを一覧表示し、データベースで最も多くの時間を費やしているクエリを確認するには、次のクエリのいずれかを実行します。

PostgreSQL バージョン 12 以前

SELECT total_time, query
FROM pg_stat_statements
ORDER BY total_time DESC LIMIT 10;

PostgreSQL バージョン 13 以降

SELECT total_plan_time+total_exec_time as total_time, query
FROM pg_stat_statements
ORDER BY 1 DESC LIMIT 10;

バッファキャッシュヒット率が低いクエリを一覧表示するには、次のクエリのいずれかを実行します。

PostgreSQL バージョン 12 以前

SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY total_time DESC LIMIT 10;

PostgreSQL バージョン 13 以降

SELECT query, calls, total_plan_time+total_exec_time as total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit +shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY 3 DESC LIMIT 10;

実行ごとにクエリを一覧表示して、時間の経過とともにクエリをサンプリングするには、次のクエリを実行します。

PostgreSQL バージョン 12 以前

SELECT query, calls, total_time/calls as avg_time_ms, rows/calls as avg_rows,temp_blks_read/calls as avg_tmp_read, temp_blks_written/calls as avg_temp_written
FROM pg_stat_statements
WHERE calls != 0
ORDER BY total_time DESC LIMIT 10;

PostgreSQL バージョン 13 以降

SELECT query,
calls,
(total_plan_time+total_exec_time as total_time)/calls as avg_time_ms,
 rows/calls as avg_rows,
temp_blks_read/calls as avg_tmp_read,
 temp_blks_written/calls as avg_temp_written
FROM pg_stat_statements
WHERE calls != 0
ORDER BY 3 DESC LIMIT 10;

データベース内のアイドル接続

データベース内のアイドル接続は、メモリや CPU などのコンピューティングリソースを消費する可能性があります。インスタンスの CPU 使用率が高い場合は、データベースでアイドル接続がないか確認してください。詳細については、「Performance impact of idle PostgreSQL connections」を参照してください。アイドル状態の接続がないか確認するには、拡張モニタリングを使用して OS プロセスリストを表示します。ただし、このリストに表示されるプロセスは 100 個までです。

アイドル接続がないか確認するには、データベースレベルで下記のクエリを実行します。

次のクエリを実行すると、アイドル状態のアクティブな現在のセッションが表示されます。

SELECT pid, datname, state, current_timestamp-least(query_start,xact_start) age, application_name, usename, queryFROM pg_stat_activity
WHERE query != '<IDLE>
'AND query NOT ILIKE '%pg_stat_activity%'
AND usename!='rdsadmin'
ORDER BY query_start desc;

SELECT application_name,pid,wait_event_type,wait_event,current_timestamp-least(query_start,xact_start) AS runtime, query AS current_query
FROM pg_stat_activity
WHERE not pid=pg_backend_pid()
AND query NOT ILIKE '%pg_stat_activity%'
AND usename!='rdsadmin';

次のクエリを実行すると、ユーザーとアプリケーション名ごとの接続数を取得できます。

postgres=> SELECT application_name,count(*) FROM pg_stat_activity GROUP BY application_name;
    application_name    | count
------------------------+-------
 psql                   |     1
 PostgreSQL JDBC Driver |     1
                        |     5
(3 rows)

postgres=> SELECT usename,count(*) FROM pg_stat_activity GROUP BY usename;
 usename  | count
----------+-------
 master   |     4
 user1    |     1
 rdsadmin |     2
(3 rows)

アイドル状態の接続を特定した後に、次のクエリのいずれかを実行して接続を終了します。

psql=> SELECT pg_terminate_backend(pid)
   FROM pg_stat_activity
   WHERE usename = 'example-username'
   AND pid <> pg_backend_pid()
   AND state in ('idle');

または

SELECT pg\_terminate\_backend (example-pid);

アプリケーションで発生する接続数が多すぎる場合は、それらの接続の管理にメモリや CPU リソースが消費されないように変更を行ってください。アプリケーションを変更して接続数を制限するか、PgBouncer などの接続プーラーを使用できます。接続プールを設定できるマネージドサービスである Amazon RDS Proxy を使用することもできます。

ANALYZE コマンド

ANALYZE コマンドは、データベース内のテーブルの内容に関する統計情報を収集し、その結果を pg_statistic システムカタログに保存します。その後、クエリプランナーがクエリの最も効率的な実行プランを決定する際に、それらの統計情報を利用します。データベース内のテーブルで ANALYZE を頻繁に実行しない場合、クエリによって消費されるコンピューティングリソースが増える可能性があります。クエリがより多くのリソースを消費することになるのは、アクセスするリレーションについての古い統計情報がシステムに存在するためです。このような問題は次の条件下で発生します。

• autovacuum が頻繁に実行されていない。
• メジャーバージョンのアップグレード後に ANALYZE が実行されなかった。

**autovacuum が頻繁に実行されていない:**autovacuum は VACUUM コマンドと ANALYZE コマンドの実行を自動化するデーモンで、データベース内の肥大化したテーブルをチェックして再利用のために領域を解放します。また、設定されたタプルのしきい値が停止するたびに ANALYZE オペレーションを実行して、テーブルの統計情報が定期的に更新されるようにします。これにより、クエリプランナーは最新の統計情報に基づく最も効率的なクエリプランを使用できるようになります。autovacuum が実行されていない場合、クエリプランナーが次善のクエリプランを作成し、そのクエリによるリソース消費量が増加する可能性があります。詳細については、以下を参照してください。

• Understanding autovacuum in Amazon RDS for PostgreSQL environments
• A case study of tuning autovacuum in Amazon RDS for PostgreSQL

autovacuum と autoanalyze がテーブルで最後に実行された日時に関する情報を取得するには、次のクエリを実行します。

SELECT relname, last\_autovacuum, last\_autoanalyze FROM pg\_stat\_user\_tables;

**メジャーバージョンのアップグレード後に ANALYZE が実行されなかった:**通常、PostgreSQL データベースではエンジンのメジャーバージョンのアップグレード後にパフォーマンスの問題が発生します。これらの問題の一般的な理由は、アップグレード後に pg_statistic テーブルを更新するために ANALYZE オペレーションが実行されないことです。そのため、RDS for PostgreSQL DB インスタンスのすべてのデータベースで ANALYZE オペレーションを実行します。オプティマイザーの統計情報は、メジャーバージョンのアップグレード中には転送されません。このため、リソース使用率の上昇によるパフォーマンスの問題を回避するために、すべての統計情報を再生成することになります。

メジャーバージョンのアップグレード後に現在のデータベースのすべての通常テーブルの統計情報を生成するには、パラメータなしで次のコマンドを実行します。

ANALYZE VERBOSE

PostgreSQL のログ記録パラメータ

Amazon RDS for PostgreSQL を使用してクエリログ記録を有効化します。次に、PostgreSQL エラーログを確認して、log_min_duration_statement および log_statement パラメータが適切な値に設定されていることを確認します。詳細については、PostgreSQL ウェブサイトの「Error Reporting and Logging」を参照してください。

CPU 使用率を下げる

高い CPU 使用率の原因となっているクエリを特定したら、次の方法を使用して CPU 使用率をさらに下げます。

• チューニングの機会を確認するには、EXPLAIN と EXPLAIN ANALYZE を使用して注意事項を特定します。詳細については、PostgreSQL ウェブサイトの EXPLAIN を参照してください。
• 繰り返し実行されているクエリがある場合は、プリペアドステートメントを使用して CPU への負担を軽減します。プリペアドステートメントを繰り返し実行すると、クエリプランがキャッシュされます。さらなる実行に備えてプランが既にキャッシュに存在することにより、プランニングの時間はさらに短くなります。

関連情報

PostgreSQL を使用するためのベストプラクティス