RDS for PostgreSQL または Aurora PostgreSQL インスタンスで、パフォーマンスの問題や実行速度の遅いクエリを特定して解決するにはどうすればよいですか?
PostgreSQL インスタンス用の Amazon Relational Database Service (Amazon RDS) が遅いです。実行速度の遅いクエリを特定してトラブルシューティングしたいと思います。
解決方法
Amazon RDS for PostgreSQL インスタンスのパフォーマンスが、次の複数の理由で影響を受けている可能性があります。
- ハードウェアの容量不足
- ワークロードの変化
- トラフィックの増加
- メモリの問題
- 次善のクエリプラン
原因を特定する
これらのツールを組み合わせて使用して、実行速度の遅いクエリの原因を特定します。
- Amazon CloudWatch メトリクス
- 拡張モニタリングメトリクス
- パフォーマンスインサイトメトリクス
- データベース統計
- ネイティブデータベースツール
CloudWatch メトリクス
リソース不足が原因で発生するパフォーマンスのボトルネックを特定するには、Amazon RDS DB インスタンスで利用可能なこれらの一般的な CloudWatch メトリクスをモニタリングします。
- CPUUtilization - 使用されたコンピューター処理能力のパーセンテージ
- FreeableMemory - DB インスタンスで使用可能な RAM (メガバイト単位)
- SwapUsage - DB インスタンスによって使用されるスワップスペース (メガバイト単位)
CPU 使用率の割合が高い場合は、通常、インスタンスでアクティブなワークロードがあり、より多くの 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';
それから、以下のコマンドを実行して、リソースを消費するアイドル状態の接続を終了します。
注: example-pid は、pg_stat_activity から取得したアイドル接続の PID に必ず置き換えてください。
SELECT pg_terminate_backend(example-pid);
次のメトリクスを確認して、RDS インスタンスで目的のネットワークスループットが達成されているかどうかを判断します。
- NetworkReceiveThroughput
- NetworkTransmitThroughput
これらのメトリクスは、着信および発信ネットワークトラフィックをバイト/秒で示します。サイズが小さい、または Amazon Elastic Block Service (Amazon EBS) に最適化されていないインスタンスクラスは、ネットワークスループットに影響を与え、インスタンスの速度が低下する可能性があります。ネットワークスループットが低いと、データベースのパフォーマンスに関係なく、すべてのアプリケーションリクエストに対する応答が遅くなる可能性があります。
次のメトリクスを確認して、I/O パフォーマンスを評価します。
- ReadIOPS および WriteIOPS - 1 秒あたりのディスク読み取りまたは書き込み操作の平均数
- ReadLatency および WriteLatency - 読み取りまたは書き込み操作にかかる平均時間 (ミリ秒)
- ReadThroughput および WriteThroughput - 1 秒あたりにディスクから読み取られたまたはディスクに書き込まれたメガバイトの平均数
- DiskQueueDepth - ディスクへの書き込みまたはディスクからの読み取りを待機している I/O 操作の数
詳細については、「Amazon RDS インスタンスの IOPS ボトルネックによって引き起こされる Amazon EBS ボリュームのレイテンシーを解決するにはどうすればよいですか?」を参照してください。
拡張モニタリングメトリクス
拡張モニタリングを使用すると、オペレーティングシステムレベルでメトリクスを表示し、CPU とメモリを大量に消費する上位 100 のプロセスのリストを表示できます。1 秒あたりのレベルで拡張モニタリングをアクティブ化して、RDS インスタンスの断続的なパフォーマンスの問題を特定できます。
使用可能なオペレーティングシステムのメトリクスを評価して、CPU、ワークロード、I/O、メモリ、およびネットワークに関連する可能性のあるパフォーマンスの問題を診断できます。
プロセスリストから、CPU％ または Mem％ の値が高いプロセスを特定します。次に、データベースから関連する接続を見つけます。
例:
| 名前 | 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 | 無制限 |
データベースに接続し、次のクエリを実行して、接続とクエリ関連の情報を見つけます:
SELECT * FROM pg_stat_activity WHERE pid = 10322;
パフォーマンスインサイトメトリクス
パフォーマンスインサイトを使用すると、待機、SQL、ホスト、またはユーザーによってスライスされたデータベースワークロードを評価できます。データベースおよび SQL レベルのメトリクスをキャプチャすることもできます。
パフォーマンスインサイトダッシュボードの [Top SQL] (トップ SQL) タブには、DB のロードに最も寄与する SQL ステートメントが表示されます。DB のロードまたは待機によるロード (AAS) が、最大 vCPU 値よりも高い場合は、インスタンスクラスのワークロードが抑制されていることを示します。
SQL 統計の呼び出しごとの平均待機時間は、クエリの平均実行時間を提供します。平均実行時間が最も長い SQL とは異なる SQL が DB 負荷の最大の要因であることがよくあります。これは、最上位の SQL リストが合計実行時間に基づいているためです。
データベース統計
以下の統計情報は、PostgreSQL のデータベースパフォーマンスを評価するのに役立ちます。
- データ配信統計
- 拡張統計情報
- モニタリング統計
これらの統計を読み取る方法については、「PostgreSQL の統計を理解する」を参照してください。
ネイティブデータベースツール
遅いクエリを特定するには、ネイティブの pgBadger ツールを使用してください。詳細については、「ネイティブツールおよび外部ツールに基づく、Amazon RDS for PostgreSQL でのクエリの最適化と調整」を参照してください。
パフォーマンスの最適化
メモリ設定を調整する
PostgreSQL DB サーバーは、データをキャッシュして読み取りおよび書き込みアクセスを改善するために、全存続期間にわたって特定のメモリ領域を割り当てます。このメモリ領域は、共有バッファと呼ばれます。データベースが共有メモリバッファに使用するメモリの量は、shared_buffers パラメータによって制御されます。
共有メモリ領域とは別に、各バックエンドプロセスはDBサーバー内の操作を実行するためにメモリを消費します。使用されるメモリ量は、work_mem パラメータと maintenance_work_mem パラメータに設定されている値に基づいています。詳細については、「サーバー設定に関する PostgreSQL のドキュメント」を参照してください。
DB インスタンスのメモリプレッシャーが継続的に高い場合は、これらのパラメーターの値を下げることを検討してください。DB インスタンスにアタッチされているカスタムパラメータグループのこれらのパラメータの値を下げることができます。
Aurora PostgreSQL クエリプランの管理
Amazon Aurora PostgreSQL 互換エディションのクエリプランの管理を使用して、クエリ実行プランを変更する方法とタイミングを制御します。詳細については、「Aurora PostgreSQL と互換性のあるクエリプラン管理のベストプラクティス」を参照してください。
実行速度の遅いクエリのトラブルシューティング
通常、インフラストラクチャに問題がある場合、または全体的なリソース消費量が高い場合に、実行速度の遅いクエリが発生します。実行速度の遅いクエリは、クエリプランナーによる最適ではないクエリプランニングの結果である可能性もあります。PostgreSQL クエリプランナーは、テーブル用に作成された統計を使用してクエリプランを作成します。これらの計画は、スキーマの変更や古い統計によって影響を受ける可能性があります。テーブルとインデックスが肥大化すると、クエリの実行が遅くなる可能性があります。
autovacuum デーモンは、デッドタプルのしきい値に達するたびにテーブルからデッドタプルを削除する自動バキュームワーカープロセスを作成します。autovaccum デーモンは、特定のテーブルに格納されている統計を更新する分析操作の実行も行います。
次のクエリを実行して、次の情報を検索します。
- デッドタプル
- 自動バキューム操作またはバキューム操作の数
- 自動分析または分析の実行回数
- これらの操作の最後実行日時
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 ビューを使用して、現在のアクティビティに関するデータを見つけることができます。このビューは、バックエンド pid、クエリ、およびその他の詳細を提供します。実行時間の長いクエリを見つけるには、次のクエリを実行します:
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;
RDS for PostgreSQL インスタンスを使用すると、データベース内から pg_stat_statements 拡張機能を作成できます。
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
pg_stat_statements を使用すると、クエリの統計を表示できます。拡張機能を作成する前に、必ず pg_stat_statements エントリを shared_preload_libraries に追加してください。
注: このモジュールのパラメーターを変更できるのは、カスタムパラメーターグループが 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_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 5 ASC LIMIT 10;
実行時間の長いクエリまたはクエリプランをデータベースエラーログに取り込むには、インスタンスに log_min_duration_statement パラメーターを適切に設定してから、auto_explain モジュールを使用します。log_min_duration_statement パラメーターにより、ステートメントが少なくとも指定された時間実行された場合、完了した各ステートメントの期間がログに記録されます。例えば、このパラメーターを 250 ミリ秒に設定すると、250 ミリ秒以上実行されるすべての SQL ステートメントがログに記録されます。auto_explain モジュールを使用すると、データベースで実行されるクエリのExplainプランをキャプチャできます。
また、explain および explainan alyze コマンドを使用して計画をキャプチャすることもできます。auto_explain モジュールに基づいてクエリ調整の機会を特定するか、クエリのコマンドを説明します。詳細については、PostgreSQL の「EXPLAIN の使用 に関するドキュメント」を参照してください。
システムが適切に調整されていても、依然としてリソーススロットリングが発生する場合は、インスタンスクラスを拡大することを検討してください。インスタンスクラスをスケールアップして DB インスタンスにより多くのコンピューティングリソースとメモリリソースを割り当てます。詳細については、「DB インスタンスクラスのハードウェア仕様 」を参照してください。
関連情報
Amazon RDS または Amazon Aurora PostgreSQL - 互換性のあるエディションの CPU 使用率が高い場合のトラブルシューティングを行うにはどうすればよいですか?
関連するコンテンツ
- AWS公式更新しました 2年前
- AWS公式更新しました 2年前
