「あのクエリ、遅いんだよな…」を科学する！pg_stat_statementsでパフォーマンスの闇を照らし出せ！
どうも！データベースの沼にどっぷり浸かってるベテランエンジニアです。今日は、みんなが一度は頭を抱えるであろう「データベースのパフォーマンス問題」に立ち向かうための、とっておきの武器についてお話ししようと思います。
「うちのDB、なんか重いんだよな…」「どのSQLが一番リソース食ってるんだろう？」
こんな疑問、日々の運用で一度は感じたことがあるはず。そんな時、勘や経験だけに頼るのはちょっとリスキーですよね。そこで登場するのが、PostgreSQLの強力な拡張機能、`pg_stat_statements`なんです！
`pg_stat_statements`って、一体何者？
端的に言うと、`pg_stat_statements`は「実行されたすべてのSQL文の統計情報を、データベース内で自動的に集計してくれる仕組み」です。まるで、DBの「活動記録係」みたいなもんですね。
「へぇ、便利そうだけど、具体的にどんな情報が取れるの？」って声が聞こえてきそう。主な情報としては、こんな感じです。
- `calls`: そのSQL文が何回実行されたか。
- `total_time`: そのSQL文が実行された合計時間（ミリ秒）。
- `mean_time`: そのSQL文の平均実行時間。
- `rows`: そのSQL文が返した（あるいは影響を与えた）行数。
- `shared_blks_hit`: 共有バッファからのヒット数。
- `shared_blks_read`: 共有バッファからの読み込み数。
- `local_blks_hit`: ローカルバッファからのヒット数。
- `local_blks_read`: ローカルバッファからの読み込み数。
- `temp_blks_hit`: 一時バッファからのヒット数。
- `temp_blks_read`: 一時バッファからの読み込み数。
- `blk_read_time`: ブロック読み込みにかかった時間。
- `blk_write_time`: ブロック書き込みにかかった時間。
これらの情報を見ることで、「どのSQLが、どれくらいの頻度で、どれくらいの時間をかけて実行されているのか」が、数字でハッキリと把握できるようになります。これは、パフォーマンスチューニングの第一歩として、めちゃくちゃ強力な武器になりますよ！
どうやって使うの？ まずは有効化から！
`pg_stat_statements`を使うには、まずPostgreSQLの設定ファイル（`postgresql.conf`）で有効にする必要があります。
1. `postgresql.conf`を編集:
以下の2つの設定項目を有効にします。
shared_preload_libraries = ‘pg_stat_statements’
pg_stat_statements.track = all
- `shared_preload_libraries`: PostgreSQL起動時にロードするライブラリを指定します。ここに`pg_stat_statements`を追加することで、モジュールが利用可能になります。
- `pg_stat_statements.track`: どのSQL文を追跡するかを指定します。`all`にすると、すべてのSQL文が対象になります。他にも`none`（無効）、`top`（トップレベルのクエリのみ）、`auto`（`track_utility`設定に依存）などがあります。まずは`all`で様子を見るのがおすすめです。
2. PostgreSQLを再起動:
設定変更を反映させるためには、PostgreSQLサーバーの再起動が必要です。
3. データベースで拡張機能を有効化:
PostgreSQLを再起動したら、分析したいデータベースに接続し、以下のコマンドを実行します。
CREATE EXTENSION pg_stat_statements;
これで準備完了です！簡単でしょ？
いよいよ、統計情報を覗いてみよう！
有効化が完了したら、いよいよ本番です。`pg_stat_statements`は、`pg_stat_statements`という名前のビュー（仮想的なテーブル）として統計情報を提供してくれます。
まずは、このビューを覗いてみましょう。
SELECT FROM pg_stat_statements;
…と、いきなり全件表示しても、情報量が多すぎて見にくいですよね（笑）。
そこで、よく使う分析パターンをいくつか紹介します。
1. 遅いクエリを見つける！ (実行時間でソート)
パフォーマンス改善で最も気になるのは、やはり「実行時間が長いクエリ」ですよね。`total_time`や`mean_time`でソートして、上位のものを見てみましょう。
SELECT
query, — 実行されたSQL文
calls, — 実行回数
total_time, — 合計実行時間 (ms)
mean_time, — 平均実行時間 (ms)
rows, — 返した/影響した行数
shared_blks_hit, — 共有バッファヒット数
shared_blks_read — 共有バッファ読み込み数
FROM
pg_stat_statements
ORDER BY
total_time DESC
LIMIT 10; — 上位10件を表示
これで、最も時間を消費しているクエリがズラッと表示されます。`query`列を見て、怪しいSQLを特定していくわけですね。「あー、このバッチ処理で使ってるSELECT文、こんなに時間かかってたのか！」なんて発見があるはずです。
2. 実行回数が多いクエリを見つける！ (呼び出し回数でソート)
逆に、実行時間は短くても、めちゃくちゃ呼ばれているクエリも要注意です。頻繁な呼び出しは、それだけでシステム全体に負荷をかけますし、ちょっとした遅延の積み重ねが大きな問題になることもあります。
SELECT
query,
calls,
total_time,
mean_time
FROM
pg_stat_statements
ORDER BY
calls DESC
LIMIT 10;
「え、この単純なSELECT文が1秒間に何百回も呼ばれてるの！？」なんてことが分かれば、キャッシュ戦略の見直しや、クエリ発行頻度の最適化を検討するきっかけになります。
3. バッファヒット率が低いクエリを見つける！ (ディスクI/Oが多いクエリ)
データベースのパフォーマンスに大きく影響するのが、ディスクI/Oです。`pg_stat_statements`では、バッファヒット率やディスクI/Oにかかる時間も追跡できます。特に、`shared_blks_read`（共有バッファからの読み込み数）や`blk_read_time`（ブロック読み込み時間）が大きいクエリは、インデックスの不足やテーブルスキャンの多さを示唆している可能性があります。
SELECT
query,
calls,
total_time,
shared_blks_hit,
shared_blks_read,
(shared_blks_hit::numeric / (shared_blks_hit + shared_blks_read)::numeric) 100 AS hit_ratio
FROM
pg_stat_statements
WHERE
shared_blks_hit + shared_blks_read > 0 — 読み込みが発生しているものに限定
ORDER BY
(shared_blks_hit::numeric / (shared_blks_hit + shared_blks_read)::numeric) ASC — ヒット率が低い順
LIMIT 10;
このクエリは、共有バッファのヒット率が低い順に並べています。ヒット率が低いということは、ディスクからデータを読み込む頻度が高いということです。これは、インデックスが適切に張られていない、あるいはシーケンシャルスキャンが多発しているサインかもしれません。`EXPLAIN ANALYZE`と組み合わせて、原因を深掘りしましょう。
実際、どんな時に役立った？ (実体験エピソード)
私自身、`pg_stat_statements`には何度も助けられてきました。
- ある日突然、レスポンスが悪化したシステム: 原因を特定するために`pg_stat_statements`でトップクエリを調査したところ、普段はあまり実行されないはずの、とある管理画面用の複雑な集計クエリが、バックグラウンドで異常な回数実行されているのを発見。原因は、別のバッチ処理が誤ってそのクエリを繰り返し呼び出していたことでした。`pg_stat_statements`がなければ、原因特定に数日かかっていたかもしれません。
- 定期的なパフォーマンスチューニング: 定期的に`pg_stat_statements`の集計結果を確認し、実行時間や呼び出し回数が増加傾向にあるクエリがないかチェックしています。これにより、問題が大きくなる前に早期に対策を打つことができています。特に、新しい機能追加やデータ量の増加に伴って、パフォーマンスが劣化していないかの監視には欠かせません。
注意点と、さらに踏み込むためのヒント
`pg_stat_statements`は非常に便利ですが、いくつか注意点もあります。
- リセット: 統計情報は、`pg_stat_statements_reset()`関数を実行するとリセットされます。これは、分析を開始する前や、DBを再起動した後に実行すると、その時点からの変化を追跡しやすくなります。
SELECT pg_stat_statements_reset();
- メモリ消費: `pg_stat_statements.max`という設定項目で、いくつのクエリ統計情報を保持するかを制限できます。デフォルト値（通常は5000）を超える場合や、非常に多くのユニークなクエリが実行される環境では、この値を調整するか、メモリ消費に注意が必要です。
- 集計の粒度: `pg_stat_statements`はSQL文の「テキスト」で集計します。同じクエリでも、パラメータの値が異なると別のエントリとしてカウントされる場合があります（ただし、`pg_stat_statements.track_utility`や`pg_stat_statements.track_planning`の設定で挙動を調整できます）。
- `EXPLAIN ANALYZE`との併用: `pg_stat_statements`は「どのクエリが遅いか」を教えてくれますが、「なぜ遅いか」までは教えてくれません。遅いと特定されたクエリに対しては、必ず`EXPLAIN ANALYZE`を実行して、実行計画の詳細を分析しましょう。
まとめ
`pg_stat_statements`は、PostgreSQLのパフォーマンスチューニングにおいて、まさに「必殺技」と言える拡張機能です。
- 実行時間や呼び出し回数で、ボトルネックとなっているクエリを特定できる。
- ディスクI/Oが多いクエリを発見し、インデックスなどの改善点を見つけられる。
- 勘や経験に頼るのではなく、データに基づいて効率的にパフォーマンス改善を進められる。
まだ使ったことがないという人は、ぜひ一度試してみてください。きっと、あなたのデータベース運用が劇的に変わるはずですよ！
何か質問があれば、いつでも気軽に声をかけてくださいね！
コメント