「とりあえず `effective_cache_size` は物理メモリの半分か75%にしておけばいいよ」
そんな先輩の適当なアドバイスを鵜呑みにしていませんか？ もちろん、それも一つの正解ではあるんですが、データベースエンジニアとしてもう少し踏み込んだ話をしましょう。ここを正しく理解しているかどうかで、PostgreSQLのクエリプランナの「視界」が劇的に変わります。
今日は、パフォーマンスチューニングの影の立役者、`effective_cache_size` の正体について深掘りしていきます。
—
そもそも、こいつは何をしているのか？
まず、勘違いしやすいポイントを一つ。`effective_cache_size` は、PostgreSQLがメモリを確保するための設定値ではありません。ここ、意外とみんな間違えるんです。
これはあくまで「プランナへのヒント（見積もり）」です。
「OS側でこれくらいのキャッシュが効いているはずだから、ディスクI/Oが発生する確率をこのくらいと見積もっていいよ」と、プランナに教えてあげるための指標なんです。
なぜこれが重要なのか？
PostgreSQLのプランナは、「インデックススキャン」と「シーケンシャルスキャン」のどちらが速いかを判断するとき、そのデータがメモリ（OSキャッシュ）上に載っている確率を考慮します。
- 値が大きすぎる場合: 「どうせキャッシュに乗ってるだろ！」とプランナが楽観視しすぎて、本来なら避けるべき重いインデックススキャンを選択し、結果としてディスクI/Oが爆発します。
- 値が小さすぎる場合: 「キャッシュなんて期待できない」とプランナが悲観的になり、全件スキャン（シーケンシャルスキャン）ばかりを選ぶようになり、本来なら一瞬で終わる検索が遅くなります。
—
実践：どう設定するのが「現場」の正解か
結論から言うと、「OSのページキャッシュとして利用可能なメモリ量」を計算するのが鉄則です。
計算のステップ
多くのLinuxサーバーでは、空きメモリは積極的にファイルキャッシュとして利用されます。つまり、計算式はこうなります。
effective_cache_size = (物理メモリ合計) – (PostgreSQLのshared_buffers) – (OSや他アプリが占有するメモリ)
もし、サーバーがDB専用機なら、ざっくり言えば 「物理メモリの 50% 〜 75%」 というのは、あながち間違いではありません。しかし、Webサーバーや他のミドルウェアが同居しているなら、もっと保守的な見積もりが必要です。
設定を確認するコマンド
今の設定値を確認するなら、psqlからこう叩いてみてください。
SHOW effective_cache_size;
もしこれが標準の `4GB` のままなら、最近のサーバースペックに対しては少なすぎることがほとんどです。
—
チューニングの判断基準：プランナの裏を読む
設定を変えた後、実際にプランナがどう動いているかを確認するのが一番の近道です。`EXPLAIN` コマンドで、実行計画の変化を追いかけましょう。
— チューニング前後の比較
EXPLAIN ANALYZE SELECT FROM large_table WHERE indexed_column = ‘target_value’;
もし、設定値を上げた後に「Cost」の値が変化し、実行計画が「Seq Scan」から「Index Scan」に切り替わったなら、プランナがより効率的なルートを見つけられた証拠です。
ただし、注意点があります。
ここで「インデックススキャンに変わったから万々歳！」と喜ぶのはまだ早いです。もし、インデックススキャンを選んだ結果、実際の実行時間（`execution time`）が伸びてしまったなら、それはプランナが「キャッシュに乗っている」と見積もったのに、実際にはディスクから読み出している証拠。つまり、`effective_cache_size` が実態よりも楽観的すぎるということです。
—
最後に：先輩からのアドバイス
`effective_cache_size` は「魔法のボタン」ではありません。どれだけ設定を追い込んでも、物理的なメモリが足りていなければプランナの予測は外れます。
まずは、`pg_stat_user_indexes` などを確認して、「本当に期待したインデックスが使われているか？」を観察することから始めてみてください。
チューニングは、データと対話する作業です。教科書通りの数字を入れるのではなく、そのサーバーが実際にどう動いているか、その「息遣い」を感じながら数値を調整してみてください。そうすれば、あなたのDBはもっと速く、もっと賢く動いてくれるはずです。
何か詰まったら、またいつでも聞きに来てくださいね。現場からは以上です！
コメント