DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ

遅いPostgreSQLクエリをデバッグするための体系的なガイド

pg_stat_statements、EXPLAIN ANALYZE、バッファ読み取り、行推定、インデックス、検証を使って遅いPostgreSQLクエリをデバッグします。

遅いPostgreSQLクエリをデバッグするための体系的なガイド

遅いPostgreSQLクエリは、それを謎として扱うのをやめれば、修正が容易になります。データベースは通常、選択したパス、予想した行数、実際に触れた行数、キャッシュまたはディスクからの読み取り、別のセッションでの待機を表示できます。

私が最もよく見かける間違いは、「このエンドポイントが遅い」から「インデックスを作成する」に直接飛びつくことです。時にはそれがうまくいきます。時には、述語がインデックスを使用できない方法で記述されているため、インデックスが無視されます。時にはクエリ自体は問題ないが、20分間開いているトランザクションの背後でブロックされています。体系的なアプローチは、クエリの形状、プランナーの見積もり、I/O、メモリ、ロックを分離するため、時間を節約します。

クエリパフォーマンスのボトルネックを理解する

ツールに飛び込む前に、PostgreSQLクエリのパフォーマンスが低下する一般的な理由を認識することが不可欠です。これらの問題は通常、いくつかの主要なカテゴリに分類されます。

  • インデックスの欠如または非効率: インデックスがあれば高速アクセスを提供できたはずなのに、データベースは大きなテーブルでシーケンシャルスキャンを強制されます。
  • 最適でないクエリ構造: 複雑な結合、不要なサブクエリ、関数の不適切な使用は、プランナーを混乱させる可能性があります。
  • 古い統計情報: PostgreSQLは効率的な実行計画を構築するために統計情報に依存しています。統計情報が古い場合、プランナーは非効率なパスを選択する可能性があります。
  • リソース競合: 高いI/O待機時間、過剰なロック、PostgreSQLに割り当てられた不十分なメモリなどの問題。

ステップ1: 遅いクエリの特定

遅いクエリを修正する前に、それを正確に特定する必要があります。ユーザーの苦情に頼るのは非効率的です。データベース自体からの経験的データが必要です。

pg_stat_statements の使用

本番環境でリソースを大量に消費するクエリを追跡する最も効果的な方法は、pg_stat_statements 拡張機能を使用することです。このモジュールは、データベースに対して実行されたすべてのクエリの実行統計を追跡します。

拡張機能の有効化（スーパーユーザー権限と設定のリロードが必要）:

-- 1. postgresql.conf にリストされていることを確認
-- shared_preload_libraries = 'pg_stat_statements'

-- 2. データベースに接続して拡張機能を作成
CREATE EXTENSION pg_stat_statements;

主な原因のクエリ:

最も総時間を消費しているクエリを見つけるには、次のクエリを使用します:

SELECT
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows
FROM
    pg_stat_statements
ORDER BY
    total_exec_time DESC
LIMIT 10;

古いPostgreSQLバージョンでは、これらの列は total_time と mean_time という名前の場合があります。サーバーが公開する名前を使用してください。

総時間と平均時間は異なる質問に答えます。平均20ミリ秒でも100万回実行されるクエリは、データベースの最大のコストになる可能性があります。1時間に1回40秒実行されるクエリは、1人のユーザーには苦痛かもしれませんが、システム全体としては重要度が低い場合があります。両方を確認してください。

履歴的に高コストなクエリではなく、現在遅いクエリが必要な場合は、アクティブなセッションを確認してください:

SELECT pid, now() - query_start AS age, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY query_start;

ステップ2: EXPLAIN ANALYZE を使用した実行計画の分析

遅いクエリが特定されたら、次の重要なステップは、PostgreSQLがそれをどのように実行しているかを理解することです。EXPLAIN コマンドは意図された計画を示しますが、EXPLAIN ANALYZE は実際にクエリを実行し、各ステップにかかった実際の時間を報告します。

構文と使用法

最も詳細な出力を得るには、遅いクエリを常に EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) でラップしてください。BUFFERS オプションはディスクI/Oアクティビティを示すため重要です。

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * 
FROM large_table lt 
JOIN other_table ot ON lt.id = ot.lt_id
WHERE lt.status = 'active' AND lt.created_at > NOW() - INTERVAL '1 day';

出力の解釈

出力は下から上、右から左に読み取ります。最も内側のノードが最初に実行されるためです。注目すべき主要な指標は次のとおりです:

  1. cost=: プランナーの推定コストであり、実際の時間ではありません。計画の選択肢を比較するために使用し、ミリ秒として扱わないでください。
  2. rows=: そのノードで処理されると推定される行数。
  3. actual time=: この特定の操作に費やされた実際の時間（ミリ秒）。
  4. rows= (Actual): このノードによって返された実際の行数。
  5. loops=: このノードが実行された回数（ネストループでは高いことが多い）。

非効率性の発見:

  • 大きなテーブルでのシーケンシャルスキャン: 大きなテーブルへのアクセスが Index Scan や Bitmap Index Scan ではなく Seq Scan を使用している場合、より良いインデックスが必要な可能性があります。
  • 推定行数と実際の行数の大きな乖離: プランナーが10行と推定したが、ノードが実際に1,000,000行を処理した場合、統計情報が古いか、プランナーが不適切な選択をしました。
  • 結合/ソートでの高い actual time: Hash Join、Merge Join、Sort 操作に過剰な時間がかかっている場合、多くの場合、メモリ不足（work_mem）またはインデックスを効果的に使用できないことを示しています。

また、Buffers 行にも注目してください。shared hit はPostgreSQLがキャッシュ内にページを見つけたことを意味します。shared read はストレージからページを読み取る必要があったことを意味します。クエリが遅い理由は、計画が悪いか、計画は妥当だがディスクから大量のコールドデータを読み取っているかのいずれかです。

ヒント: 複雑な計画については、explain.depesz.com や pgAdmin のビジュアルExplainプランビューアなどのオンラインツールを使用して、結果をグラフィカルに解釈してください。

ステップ3: 一般的なボトルネックへの対処

EXPLAIN ANALYZE の結果に基づいて、対象を絞った修正を適用します。

インデックスの最適化

大きなテーブルで Seq Scan が支配的で、クエリが選択的である場合は、WHERE、JOIN、ORDER BY 句で使用される列にインデックスを検討してください。シーケンシャルスキャンが自動的に悪いわけではありません。ほとんどの行が必要な場合、PostgreSQLは正しくそれを選択することがあります。

例: クエリが status でフィルタリングし、次に user_id で結合する場合:

-- 高速なルックアップと結合のための複合インデックスを作成
CREATE INDEX idx_large_table_status_user_id ON large_table (status, user_id);

本番システムでは、書き込みをブロックしないようにするために CREATE INDEX CONCURRENTLY を使用し、通常のトランザクションブロック内では実行できないことに注意してください:

CREATE INDEX CONCURRENTLY idx_large_table_status_user_id
ON large_table (status, user_id);

統計情報の更新（VACUUM ANALYZE）

プランナーが極端に不正確な推定を行っている場合（推定行数と実際の行数の不一致）、テーブルの統計情報を強制的に更新します。

ANALYZE VERBOSE table_name;
-- アクティビティの高いテーブルでは、テーブルごとのautovacuum/analyzeしきい値を低くすることを検討してください。

メモリチューニング

ソートやハッシュ操作がディスクにあふれている場合（BUFFERS 出力の高いI/Oや遅いソートで示されることが多い）、PostgreSQLの利用可能なワークメモリを増やします。

-- 特定のクエリテストのためにセッションレベルで work_mem を増やす
SET work_mem = '128MB'; 
-- または、持続的なパフォーマンス向上のために postgresql.conf でグローバルに設定

警告: 多くの複雑なクエリが同時に実行される場合、work_mem をグローバルに高く設定しすぎると、システムメモリを使い果たす可能性があります。サーバー容量に基づいて慎重に調整してください。

計画内のソートやハッシュのあふれの詳細を探してください。新しいPostgreSQLの出力では、external merge Disk: ... などのソート方法が表示される場合があり、これは操作が利用可能なメモリを超え、一時ファイルを書き込んだ明確な兆候です。

クエリの書き換え

時には、構造自体が問題である場合があります。WHERE 句でインデックス列に関数を適用するなど、非SARG可能な述語（インデックスの使用を妨げる条件）を避けてください:

非効率（インデックスの使用を妨げる）:

WHERE DATE(created_at) = '2023-10-01'

効率的（インデックスの使用を許可）:

WHERE created_at >= '2023-10-01 00:00:00' AND created_at < '2023-10-02 00:00:00'

もう1つの一般的なパターンは、アプリケーションが必要とするよりもはるかに多くの列を選択することです。SELECT * は計画の最適化を難しくし、メモリ使用量を増やし、インデックスオンリースキャンが機能する可能性がある場合に余分なヒープ読み取りを強制する可能性があります。ホットパスでは、列を意図的にリストしてください。

ロックチェック

EXPLAIN ANALYZE がセッションでは高速でもアプリケーションが遅い場合、クエリは有用な実行時間を得る前に待機している可能性があります。ロック待機を確認してください:

SELECT pid, wait_event_type, wait_event, now() - query_start AS age, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY query_start;

次に、ブロッカーを見つけます:

SELECT blocked.pid AS blocked_pid,
       blocker.pid AS blocker_pid,
       blocked.query AS blocked_query,
       blocker.query AS blocker_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks
  ON blocked_locks.pid = blocked.pid AND NOT blocked_locks.granted
JOIN pg_locks blocker_locks
  ON blocker_locks.locktype = blocked_locks.locktype
 AND blocker_locks.database IS NOT DISTINCT FROM blocked_locks.database
 AND blocker_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
 AND blocker_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
 AND blocker_locks.pid <> blocked_locks.pid
JOIN pg_stat_activity blocker
  ON blocker.pid = blocker_locks.pid;

修正はアプリケーションレベルである可能性があります: トランザクションの短縮、遅い外部API呼び出しをトランザクションの外に移動、不要な SELECT ... FOR UPDATE の回避、または競合するトランザクションがデッドロックしないようにテーブルを更新する順序の変更。

小さな例: 遅いダッシュボードクエリ

ダッシュボードが数秒ごとにこのクエリを実行するとします:

SELECT *
FROM orders
WHERE DATE(created_at) = CURRENT_DATE
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

テーブルには数百万行あります。EXPLAIN ANALYZE はシーケンシャルスキャン、フィルタによって削除された多数の行、およびソートを示しています。最初の直感は created_at にインデックスを作成することかもしれませんが、述語は列を DATE(created_at) でラップしているため、created_at の通常のインデックスはあまり役に立ちません。

日付フィルタを範囲として書き換えます:

SELECT id, customer_id, total_cents, created_at
FROM orders
WHERE created_at >= CURRENT_DATE
  AND created_at < CURRENT_DATE + INTERVAL '1 day'
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

次に、フィルタとソートに一致するインデックスを検討します:

CREATE INDEX CONCURRENTLY idx_orders_paid_created_at
ON orders (created_at DESC)
WHERE status = 'paid';

これは普遍的なインデックスのレシピではありません。paid が一般的なダッシュボードフィルタであり、アプリケーションが通常最新の支払い済み注文を要求する場合に機能します。アプリケーションがアカウントで頻繁にフィルタリングする場合、より良いインデックスは account_id で始まるかもしれません。ポイントは、クエリで言及されている単一の列ではなく、実際のアクセスパターンに基づいてインデックスを設計することです。

変更後、計画はスキャンされる行数が少なくなり、理想的には明示的なソートを回避するはずです。計画がまだシーケンシャルスキャンを選択する場合は、日付範囲が広すぎるか、統計情報が古いか、本番環境のクエリパラメータがテストケースと異なるかを確認してください。

ステップ4: 検証と監視

変更を実装した後、比較可能なパラメータでまったく同じクエリに対して EXPLAIN ANALYZE を再実行します。目標は常にインデックススキャンを確認することではありません。目標は、より少ない作業を確認することです: フィルタによって削除される行が少ない、読み取られるバッファが少ない、ディスクあふれがない、行推定が改善されている、または高コストなノードで費やされる時間が短いこと。

pg_stat_statements を引き続き監視して、修正されたクエリが主要な原因リストに表示されなくなり、修正がグローバルにプラスの影響を与えていることを確認します。

また、インデックス追加後の書き込みコストにも注意してください。新しいインデックスはすべて、挿入、更新、削除の際に維持する必要があります。1つのダッシュボードにとって完璧な読み取りインデックスは、大量の取り込みパスを遅くする場合、悪いトレードオフになる可能性があります。重要なテーブルについては、両方を確認してください: 遅いクエリは改善されましたか？書き込みレイテンシやテーブルの肥大化はその後悪化しましたか？

実際のインシデント中に役立つもう1つの習慣は、現実的なパラメータ値でテストすることです。PostgreSQLは、10行の顧客と1000万行の顧客で異なる計画を選択する場合があります。アプリケーションがプリペアドステートメントを使用している場合、ジェネリックプランは psql に貼り付ける1回限りのクエリとは異なる動作をすることもあります。本番の問題が1つのテナント、1つのアカウント、または1つの日付範囲に影響を与える場合は、安全な環境でその形状をできるだけ正確に再現してください。

クエリが破壊的であるか、EXPLAIN ANALYZE で実行するにはコストが高すぎる場合は、プレーンな EXPLAIN から始め、ステージングで実行するか、ロールバックするトランザクションでテストをラップしてください。UPDATE と DELETE の場合でも、本番変更を行う前に、計画のスキャンと結合部分から多くのことを学ぶことができます。

変更の前後の計画、タイミング、理由を簡単にメモしておいてください。その習慣は、後で偶発的な後退を防ぎ、次の人にスキーマ内の謎のインデックス名ではなく、実際の説明を提供します。