`effective_cache_size`：PostgreSQLの「勘」を研ぎ澄ますチューニング術
PostgreSQLのチューニングにおいて、`effective_cache_size`ほど誤解されやすく、かつオプティマイザの挙動に決定的な影響を与えるパラメータは他にないかもしれません。
多くのエンジニアが、これを「PostgreSQLが使用するメモリの上限」だと勘違いして設定し、結果としてプランナに誤った道筋を歩ませてしまいます。今日は、このパラメータがクエリプランナの内部でどのように「解釈」されているのか、そして現場でどう設定すべきかについて、少し深掘りしてみましょう。
1. それは「割り当て」ではなく「見積もり」である
まず、大前提を共有させてください。`effective_cache_size`は、PostgreSQLがメモリを確保するための予約席ではありません。これは、「OSのページキャッシュを含め、ディスク上のデータがメモリ上にどれだけ存在しているか（＝ディスクI/Oなしでアクセスできる可能性が高いデータ量）」を、プランナに対して教える「ヒント」です。
プランナは、この数値を元にコスト計算を行います。具体的には、「インデックススキャンを選択した際に、インデックスやテーブルデータがメモリに載っている確率」を導き出そうとするわけです。
- 値が小さすぎる場合: プランナは「データはほとんどディスクにある」と判断し、インデックススキャンを避け、全表走査（Seq Scan）を好むようになります。
- 値が大きすぎる場合: 逆に、現実以上にメモリヒット率が高いと見積もり、本来なら全表走査の方が安上がりなケースでも、コストの低いインデックススキャンを選択し、結果としてランダムI/Oの嵐を招くことになります。
2. なぜ「物理メモリの50%〜75%」が推奨されるのか
ドキュメントには「全メモリの50%〜75%」と書かれることが多いですが、これは単なる経験則ではありません。
現代のOSは、空いているメモリを可能な限りページキャッシュとして利用します。PostgreSQLが直接管理する`shared_buffers`がメモリの一部を占有していたとしても、残りの領域の多くはOSのキャッシュとして機能しています。
ここで重要なのは、「OS上のファイルシステムキャッシュは、他のプロセスと共有されている」という点です。DB専用サーバーであれば75%程度まで積んでもいいですが、アプリケーションサーバーが同居している環境や、バックグラウンドでバッチ処理が動く環境では、この値を保守的に見積もる必要があります。
3. パフォーマンストラブルシューティング：プランが「裏切る」とき
「なぜかインデックスが使われない」「期待したプランと違う」という相談を受けるとき、私は真っ先に`effective_cache_size`の値を疑います。
例えば、メモリを大量に積んだサーバーで、この値をデフォルトのまま（128MBなど）放置していると、プランナは「インデックススキャンはコストが高い」という悲観的な計算を繰り返します。逆に、インデックスを貼りすぎたテーブルに対してこの値を過大に設定すると、インデックスの階層を辿るランダムアクセスが増大し、クエリのレスポンスが極端に悪化します。
トラブルシューティングのステップ:
1. `EXPLAIN (ANALYZE, BUFFERS)`で、実際の実行コストと見積もりコストの乖離を確認する。
2. もし見積もりよりも実際の行アクセス数が多い、あるいはI/O待機が顕著であれば、`effective_cache_size`が現実のキャッシュヒット率と整合しているか疑う。
3. `pg_stat_database` や `pg_buffercache` 拡張を使い、実際にどれだけのデータがバッファに乗っているかを計測する。
4. 最後に：結局、どう設定すべきか？
結局のところ、`effective_cache_size`は「OSの機嫌をプランナに伝える」ためのものです。
- 専用サーバーなら: 物理メモリの75%程度。
- 混在サーバーなら: 物理メモリの50%から、他のプロセスが消費する量を差し引いた分。
- 判断に迷ったら: 一度デフォルトから少しずつ上げていき、`EXPLAIN`のコスト値の変化を観察してください。
このパラメータをいじることは、エンジニアがDBエンジンの「脳内シミュレーション」に介入することに他なりません。魔法の数字を探すのではなく、サーバーのメモリという貴重な資源が、実際にどのように活用されているかという「現場の景色」と数値を一致させる。それこそが、熟練のチューニングなのです。
皆さんのPostgreSQLが、今日も最適なクエリプランを選択してくれることを願っています。それでは、また。
コメント