pg_stat_statementsを用いたPostgreSQLクエリ最適化:本番環境のスロークエリを特定・改善する

Database tutorial - IT technology blog
Database tutorial - IT technology blog

課題の所在:本番環境における突発的なCPUスパイクとI/Oボトルネック

多くの場合、それは午前2時のPagerDutyアラートから始まります。データベースのCPU使用率が98%に急上昇し、ディスクの読み取りIOPSはプロビジョニングされた上限である3,000 IOPSに達し、APIのp99レイテンシは45ミリ秒から4.2秒へと悪化します。アプリケーションログは突如として504 Gateway Timeoutで埋め尽くされます。しかし、それらのログから障害の引き金となったSQL文を突き止めることは困難です。

障害発生中に従来のクエリログ記録に頼ろうとすると、事態をさらに悪化させることが少なくありません。毎秒1,500トランザクションを処理するインスタンスで log_min_duration_statement = 250ms を設定すると、1時間あたり15GBものテキストログが生成される可能性があります。その結果生じるディスクスラッシングは、データベースの復帰に必要なI/O帯域幅そのものを奪ってしまい、しかも以下の2つの重要な疑問には答えを出せないままです。

  • 過去6時間のピークウィンドウにおいて、最も多くの総CPU時間を消費したクエリはどれか?
  • レイテンシの悪化は、いくつかの高負荷なバッチ処理によるものか、それともORMのN+1ループ内で50万回実行された2ミリ秒の軽量クエリによるものか?

プレッシャーがかかる中で勘に頼ってインデックスを追加することも同様に危険です。冗長なインデックスは、真の原因に対処することなくINSERTやUPDATEの処理速度を低下させます。必要なのは、共有メモリから直接取得される低オーバーヘッドの実行テレメトリです。

基本概念:pg_stat_statementsがクエリ実行を追跡する仕組み

PostgreSQLは、pg_stat_statements を通じてこの可視性を標準で提供します。クエリをインターセプトしてディスクに書き出す標準のテキストロギングとは異なり、この拡張機能は共有メモリ内のクエリ実行エンジンに直接フックするため、一般的な本番ワークロードにおけるCPUオーバーヘッドは1〜2%未満に抑えられます。

1. クエリの正規化とパラメータのマスキング

動的なアプリケーションは常にクエリ引数を変化させます。一意の文字列をすべて記録すると、あっという間にメモリキャッシュが枯渇してしまいます。例えば、次の2つの検索を考えてみましょう。

SELECT email, status FROM users WHERE id = 1042;
SELECT email, status FROM users WHERE id = 9811;

pg_stat_statements は、クエリのパースツリーを queryid と呼ばれる64ビットの整数にハッシュ化します。リテラル定数をバインドパラメータのプレースホルダー($1、$2)に置き換えることで、何百万回もの個別の呼び出しを単一のフィンガープリント(SELECT email, status FROM users WHERE id = $1)として集約します。

2. 重要なテレメトリメトリクス

正規化された各ステートメントに対して、この拡張機能は重要なランタイムカウンターを提供します。

  • calls: 総実行回数。
  • total_exec_time & mean_exec_time: 合計実行時間および平均実行時間(ミリ秒単位)。これらのメトリクスを比較することで、高頻度のマイクロクエリと、実行頻度は低いもののリソースを大量消費するバッチ処理を切り分けることができます。
  • shared_blks_hit & shared_blks_read: バッファキャッシュの効率性。ヒット(hit)は、PostgreSQLがRAM(shared_buffers)内の8KBページを見つけたことを意味します。リード(read)は、OSのページキャッシュに問い合わせるか、NVMe/EBSストレージからデータを取得しなければならなかったことを意味します。
  • shared_blks_dirtied & shared_blks_written: データ更新処理によって生成された書き込み量。
  • blk_read_time & blk_write_time: ディスクI/Oの待機に費やされた時間(ミリ秒単位。track_io_timing = on が必要)。

実践ガイド:セットアップ、検出、および改善

ステップ1:PostgreSQLでpg_stat_statementsを有効化する

このモジュールは起動時に共有メモリ内に固定サイズのハッシュテーブルを確保するため、あらかじめプリロードしておく必要があります。postgresql.conf に以下の行を追加します。

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

# トラッカーの容量とスコープを設定
pg_stat_statements.max = 10000
pg_stat_statements.track = top
pg_stat_statements.track_utility = off

# マイクロ秒単位のI/Oタイミングメトリクスを有効化
track_io_timing = on

track_utility = off に設定すると、VACUUM や CREATE INDEX などのDDL文が除外され、統計データを純粋にアプリケーションのクエリのみに集中させることができます。サービスを再起動して変更を適用します。

sudo systemctl restart postgresql

再起動が完了したら、対象のアプリケーションデータベースでビューを有効化します。

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

ステップ2:総実行時間の上位5クエリを特定する

単体で12ミリ秒で完了するクエリは、一見無害に思えます。しかし、ワーカーがそれを毎秒800回呼び出せば、気づかないうちにサーバーを圧迫します。以下のクエリを実行して、累積CPU負荷の高いクエリを特定します。

SELECT 
    queryid,
    calls,
    round(total_exec_time::numeric, 2) AS total_time_ms,
    round(mean_exec_time::numeric, 2) AS avg_time_ms,
    round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS percentage_overall,
    substr(query, 1, 100) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

percentage_overall に注目してください。もし1つのクエリがクラスタ全体の総実行時間の35%を占めている場合、そのステートメントを最適化するだけで、データベースの負荷を即座に3分の1削減できます。

ステップ3:I/Oボトルネックとキャッシュミスの検出

RAMからのデータ取得はナノ秒単位ですが、ストレージからの取得にはミリ秒単位の時間がかかります。shared_blks_read が shared_blks_hit を大きく上回っている場合、PostgreSQLはキャッシュヒットに飢えている状態です。

SELECT 
    queryid,
    calls,
    shared_blks_read,
    shared_blks_hit,
    round((100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0))::numeric, 2) AS cache_hit_ratio,
    round(blk_read_time::numeric, 2) AS io_read_wait_ms,
    substr(query, 1, 120) AS query_preview
FROM pg_stat_statements
WHERE (shared_blks_read + shared_blks_hit) > 5000
ORDER BY shared_blks_read DESC
LIMIT 5;

トランザクション主体のOLTPワークロードでは、キャッシュヒット率99%以上を目指す必要があります。95%を下回っている場合は、インデックスが不足してフルテーブルスキャンが発生しているか、ワーキングセットが現在の shared_buffers プールの上限を超えていることを示唆しています。

ステップ4:実践事例:コストの高いシーケンシャルスキャンの改善

1時間に12,000件の注文を処理するEコマースプラットフォームにおいて、I/O調査クエリによって長時間のディスク待機を発生させている以下の検索が特定されました。

SELECT order_id, amount, status FROM orders WHERE customer_id = $1 AND status = $2;

本番環境相当の引数を用いて EXPLAIN (ANALYZE, BUFFERS) を実行すると、生の実行計画が確認できました。

EXPLAIN (ANALYZE, BUFFERS) 
SELECT order_id, amount, status 
FROM orders 
WHERE customer_id = 91842 AND status = 'COMPLETED';

クエリプランナは既存の単一カラムインデックスを使用していませんでした。

Seq Scan on orders  (cost=0.00..84520.10 rows=4 width=28) (actual time=42.105..48.310 rows=3 loops=1)
  Filter: ((customer_id = 91842) AND (status = 'COMPLETED'::text))
  Rows Removed by Filter: 1850200
  Buffers: shared read=38410

PostgreSQLは、185万行の中からわずか3件の一致レコードを見つけるためだけに、38,410個のバッファページ(ディスクから約300MBのデータ)を読み込んでいました。稼働中のテーブルに対する書き込み操作をロックすることなくこれを修正するため、CONCURRENTLYオプションを指定して複合インデックスを作成しました。

CREATE INDEX CONCURRENTLY idx_orders_customer_status ON orders (customer_id, status);

再度実行計画を確認すると、その効果が即座に実証されました。

Bitmap Heap Scan on orders  (cost=4.30..18.40 rows=4 width=28) (actual time=0.045..0.052 rows=3 loops=1)
  Recheck Cond: ((customer_id = 91842) AND (status = 'COMPLETED'::text))
  Buffers: shared hit=4
  ->  Bitmap Index Scan on idx_orders_customer_status  (cost=0.00..4.30 rows=4 width=0) (actual time=0.021..0.021 rows=3 loops=1)
        Buffers: shared hit=2

ディスクからのブロック読み取りはゼロになり、クエリはRAM内の6回のバッファヒットのみで完全に処理されました。実行レイテンシは48.3ミリ秒から0.05ミリ秒へと激減し、960倍の高速化を達成しました。

ステップ5:基準統計情報のリセット

過去のメトリクスが残っていると、新しくデプロイしたインデックスや設定調整の効果が分かりにくくなります。古い統計情報をクリアして、その後のデプロイによるパフォーマンスの変化を正確に測定します。

-- データベースクラスタ全体のすべてのカウンターをリセット
SELECT pg_stat_statements_reset();

-- または特定のクエリIDのみをクリア(PostgreSQL 12以降)
SELECT pg_stat_statements_reset(userid, dbid, 3421890241);

まとめ

直感に頼ったデータベースチューニングはやめましょう。pg_stat_statements と track_io_timing を組み合わせることで、パフォーマンスオーバーヘッドをほぼゼロに抑えながら、メモリヒット、実行回数、ディスク待機時間に関する確実なテレメトリが得られます。まずは総実行時間の上位クエリを確認し、複合インデックスを活用して最も深刻なシーケンシャルスキャンを排除し、経験的なメトリクスに基づいてチューニングを進めていきましょう。

Share: