Amazon RDS for PostgreSQL または Aurora PostgreSQL 互換 DB インスタンスで、パフォーマンスの問題や実行速度が遅いクエリを特定してトラブルシューティングする方法を教えてください。

解決策

ハードウェアのサイズ不足、ワークロードの変化、トラフィックの増加、メモリの問題、最適化されていないクエリにより、DB インスタンスのパフォーマンスが影響を受けます。パフォーマンスの問題を解決するには、次のアクションを実行します。

原因を特定する

CloudWatch メトリクスを確認する

Amazon CloudWatch メトリクス CPUUtilization、FreaableMemory、SwapUsage を監視してパフォーマンスのボトルネックを特定します。

CPU 使用率が高い場合、DB インスタンスのアクティブなワークロードには、より多くの CPU リソースが必要です。ワークロードで使用できるメモリが少ないとスワップが頻繁に発生するため、メモリ使用率が高くなり、スワップが多くの容量を使用するようになります。実行時間の長いクエリ、トラフィックの急激な増加、多数のアイドル接続により、CPU 使用率が高くなり、多くのメモリリソースを使用する場合があります。

実行時にアクティブなクエリを確認するには、次のコマンドを実行します。

SELECT pid, usename, age(now(),xact_start) query_time, query FROM pg_stat_activity WHERE state='active';

データベース内のアイドル接続を確認するには、次のコマンドを実行します。

SELECT count(*) FROM pg_stat_activity WHERE state='idle';

アイドル状態の接続を終了するには、次のコマンドを実行します。

SELECT pg_terminate_backend(example-pid);

注: example-pid は、アイドル状態の接続のプロセス ID に置き換えます。

DB インスタンスが想定されるネットワークスループットに達していることを確認するには、メトリクス NetworkReceiveThroughput および NetworkTransmitThroughput を確認します。Amazon Elastic Block Service (Amazon EBS) インスタンスクラスが不足しているか最適化されていない場合、ネットワークスループットが影響を受け、インスタンスが遅くなる可能性があります。ネットワークスループットが低いと、データベースのパフォーマンスには関係なく、すべてのアプリケーションリクエストへの応答が遅くなる可能性があります。

メトリクス ReadIOPS、WriteIOPS、ReadLatency、WriteLatency、ReadThroughput、WriteThroughput、DiskQueueDepth を確認して I/O パフォーマンスを評価します。詳細については、「Amazon RDS インスタンスで、IOPS のボトルネックを原因とする Amazon EBS ボリュームの遅延をトラブルシューティングする方法を教えてください」を参照してください。

拡張モニタリングを使用する

拡張モニタリングを使用すると、オペレーティングシステム (OS) レベルでメトリクスを表示したり、CPU とメモリを大量に使用している上位 100 のプロセスを一覧表示したりすることができます。[詳細度] を 1 に設定して拡張モニタリングを有効化し、DB インスタンスでの断続的なパフォーマンスの問題を特定します。

使用できる OS メトリクスを評価し、CPU、ワークロード、I/O、メモリ、ネットワークに関連するパフォーマンスの問題を診断します。プロセスリストから、CPU% または Mem% の値が高いプロセスを特定します。

例:

NAME	VIRT	RES	CPU%	MEM%	VMLIMIT
postgres: postgres postgres 178.2.0.44(42303) SELECT [10322]	250.66 MB	27.7 MB	85.93	2.21	unlimited

データベースに接続して次のクエリを実行し、データベース内の CPU 使用率が高い接続を確認します。

SELECT * FROM pg_stat_activity WHERE pid = 10322;

注: 10322 は、該当する接続のプロセス ID に置き換えます。

Performance Insights メトリクスを確認する

Performance Insights を使用して、待機数、SQL、ホスト、ユーザーごとのデータベースのワークロードを評価します。データベースと SQL レベルのメトリクスを取得することもできます。

Performance Insights ダッシュボードの [トップ SQL] タブを使用すると、DB 負荷の最大の原因である SQL ステートメントを確認できます。DBの 負荷または待機負荷 (AAS) が Max vCPU よりも高い場合、DB インスタンスクラスのワークロードにスロットリングがかかります。

SQL 統計情報で呼び出しごとの平均遅延を参考にすると、クエリの平均実行時間を確認できます。トップ SQL は、合計実行時間に基づいています。したがって、実行時間が最も長い SQL は、DB 負荷の最大の原因となる SQL とは異なる場合がよくあります。

データベース統計をチェックする

データ分散統計、拡張統計情報、モニタリング統計情報を確認し、PostgreSQL のデータベースパフォーマンスを評価します。統計情報の詳細については、「PostgreSQL の統計情報について」を参照してください。

ネイティブデータベースツールをチェックする

遅いクエリを特定するには、GitHub のウェブサイトにあるネイティブ pgbadger ツールを使用します。詳細については、「ネイティブツールと外部ツールに基づき、Amazon RDS for PostgreSQL のクエリを最適化、調整する」を参照してください。

パフォーマンスを最適化する

メモリ設定を調整する

shared_buffers パラメータの値を設定すると、クエリのパフォーマンスを向上できます。

work_mem パラメータと maintenance_work_mem パラメータは、バックエンドプロセスで使用するメモリ量を定義します。詳細については、PostgreSQL のウェブサイトで「20.4 リソースの消費」を参照してください。DB インスタンスのメモリ使用量が頻繁に多くなる場合は、インスタンスにアタッチされているカスタムパラメータグループのパラメータ値を下げます。

Aurora PostgreSQL 互換のクエリプラン管理を使用する

Aurora PostgreSQL 互換のクエリプラン管理を使用すると、クエリ実行プランを変更する方法とタイミングを制御できます。詳細については、「Aurora PostgreSQL クエリプラン管理のベストプラクティス」を参照してください。

実行速度が遅いクエリのトラブルシューティング

インフラストラクチャの問題、最適化されていないクエリ計画、全体的に高いリソース使用率などが原因で、クエリの実行が遅くなります。PostgreSQL クエリプランナーは、テーブルの統計情報を使用してクエリプランを作成します。スキーマの変更や古い統計情報がプランに影響する可能性があります。テーブルやインデックスの負荷が高い場合も、クエリの実行速度が遅くなる場合があります。

テーブルがデッドタプルのしきい値に達すると、autovacuum デーモンは autovacuum ワーカープロセスを作成してデッドタプルをテーブルから削除します。さらに、autovacuum デーモンは、ANALYZE を操作を実行し、テーブルの統計情報を更新します。

次のクエリを実行してデッドタプル、autovacuum または vacuum 操作、autoanalyze または analyze の実行について確認します。

SELECT schemaname, relname, n_live_tup,n_dead_tup, last_autoanalyze, last_analyze, last_autovacuum, last_vacuum,autovacuum_count+vacuum_count vacuum_count, analyze_count+autoanalyze_count analyze_count
FROM pg_stat_user_tables
ORDER BY 5 DESC;

pg_stat_activity ビューを参考に、バックエンドプロセス ID やクエリなど、現在のアクティビティに関連するデータを特定します。実行時間の長いクエリを特定するには、次のクエリを実行します。

SELECT pid, datname, query, extract(epoch from now()) - extract(epoch from xact_start) AS duration, case
WHEN wait_event IS NULL THEN 'CPU'
ELSE wait_event_type||':'||wait_event end wait FROM pg_stat_activity
WHERE query!=current_query() AND xact_start IS NOT NULL ORDER BY 4 DESC;

ロックを待っているクエリは遅くなる可能性があります。クエリがロックを待っているかどうかを確認するには、次のクエリを実行します。

SELECT pid, virtualtransaction AS vxid, locktype AS lock_type, mode AS lock_mode, granted,fastpath,CASE
WHEN virtualxid IS NOT NULL AND transactionid IS NOT NULL
THEN virtualxid || ' ' || transactionid
WHEN virtualxid::text IS NOT NULL
THEN virtualxid
ELSE transactionid::text
END AS xid_lock, relname, page, tuple, classid, objid, objsubid
FROM pg_locks LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
WHERE -- do not show our view's locks
pid != pg_backend_pid() AND
virtualtransaction IS DISTINCT FROM virtualxid
ORDER BY 1, 2, 5 DESC, 6, 3, 4, 7;

pg_stat_statement を参考にクエリの統計情報を確認します。pg_stat_statements 拡張を作成する前に、pg_stat_statements エントリを shared_preload_libraries に追加してください。データベース内に pg_stat_statements 拡張を作成するには、次のクエリを実行します。

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

注: カスタムパラメータグループが DB インスタンスにアタッチされている場合のみ、pg_stats_statements のパラメータを変更できます。

DB インスタンスのパフォーマンスに影響している SQL クエリを特定するには、次のクエリを実行します。

PostgreSQL バージョン 12 以前

SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY 3 DESC;

PostgreSQL バージョン 13 以降

SELECT query, calls, total_plan_time+total_exec_time AS total_time, mean_plan_time + mean_exec_time AS mean_time FROM pg_stat_statements ORDER BY 3 DESC;

バッファキャッシュのヒット率が低いクエリを特定するには、次のクエリを実行します。

PostgreSQL バージョン 12 以前

SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percentFROM 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 5 ASC
LIMIT 10;

データベースエラーログで実行時間が長いクエリまたはクエリプランを見つけるには、DB インスタンスに log_min_duration_statement パラメータを設定してから auto_explain モジュールを使用します。

explain および explain analyze コマンドを使用してクエリプランを取得することもできます。auto_explain または explain コマンドを使用して、クエリの調整方法を判断します。詳細については、PostgreSQL のウェブサイトで「14.1 EXPLAIN の使用方法」および「F3. auto_explain - 遅いクエリのログ実行プラン」を参照してください。

システムを最適化してもパフォーマンスの問題が発生する場合は、DB インスタンスクラスをスケールアップするのがベストプラクティスです。DB インスタンスをスケールアップすると、より多くのコンピューティングリソースとメモリリソースを割り当てることができます。

関連情報

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

RDS for PostgreSQL DB インスタンスのパラメータを操作する

十分なメモリがあっても、Amazon RDS DB インスタンスがスワップメモリを使用している理由を知りたいです