はじめに

パフォーマンスチューニングを始めたばかりの頃、よくやっていた失敗があります。クエリを直して計測し直すと、たしかに速くなっている。喜んでいたら、実は2回目の実行でキャッシュに乗っていただけだった、というものです。

それからは1回目の計測値を外し、2回目以降の平均を見るようにしました。ところが、この測り方にも落とし穴があります。2回目以降の値はキャッシュに乗った状態での速さなので、これだけを見て実行計画を選ぶと、キャッシュにないときに遅くなる計画を選んでしまうことがあるのです。ユーザーが実際に開いたときはキャッシュに乗っていないこともあるので、結果的にUXを下げることになります。

そこで今は、キャッシュに乗っていないcoldの状態と、乗っているwarmの状態の両方を計測しています。本記事では、coldの状態を作るために、PostgreSQLを再起動せずにキャッシュを完全に消す手順を紹介します。

PostgreSQLの2種類のキャッシュ

PostgreSQLのキャッシュには、PostgreSQL自身が持つshared buffersと、OSが持つページキャッシュの2種類があります。shared buffersにないページでも、OSページキャッシュに残っていればディスクを読まずに返ってくるので、coldの状態を作るには両方をクリアする必要があります。

MySQLを使っていたときは、キャッシュを消すためにいちいちDBを再起動していました。PostgreSQLでは、shared buffersはpg_buffercache拡張機能の関数で、OSページキャッシュはLinuxのコマンドで、どちらも再起動せずにクリアできます。

shared buffersのクリア

pg_buffercacheはPostgreSQLに付属する拡張機能で、PostgreSQL 17から、指定したバッファをshared buffersから追い出すpg_buffercache_evict()が使えます。PostgreSQL 18からは、テーブル単位で追い出すpg_buffercache_evict_relation()と、すべてを追い出すpg_buffercache_evict_all()も使えます。

まず拡張機能を有効にします。

CREATE EXTENSION IF NOT EXISTS pg_buffercache;

PostgreSQL 18では、次の1行でshared buffersをすべてクリアできます。

SELECT * FROM pg_buffercache_evict_all();

特定のテーブルだけを消したいときはpg_buffercache_evict_relation()を使います。インデックスはテーブルとは別のリレーションなので、インデックスも消すときはインデックス名も渡します。

SELECT * FROM pg_buffercache_evict_relation('orders'::regclass);
SELECT * FROM pg_buffercache_evict_relation('orders_pkey'::regclass);

PostgreSQL 17にはpg_buffercache_evict()しかないので、pg_buffercacheビューのバッファIDを渡して1つずつ追い出します。

SELECT count(*) FILTER (WHERE pg_buffercache_evict(bufferid))
FROM pg_buffercache
WHERE relfilenode IS NOT NULL;

どの関数もスーパーユーザーでしか実行できません。ほかの接続が使用中のバッファは追い出せず、追い出したページもほかのクエリが動いていればすぐに読み込み直されます。

参照：https://www.postgresql.org/docs/18/pgbuffercache.html

OSページキャッシュのクリア

pg_buffercacheが消すのはshared buffersだけで、OSページキャッシュはクリアしてくれません。OSページキャッシュはroot権限で次のコマンドを実行してクリアします。

sync; echo 3 > /proc/sys/vm/drop_caches

drop_cachesは書き込み待ちのページを解放しないので、先にsyncでディスクへ書き出してから実行します。3は、ページキャッシュとdentryやinodeのキャッシュの両方を解放する指定です。

参照：https://docs.kernel.org/admin-guide/sysctl/vm.html#drop-caches

coldの状態での計測

2つを組み合わせると、DBを再起動せずにcoldの状態を作れます。shared buffersから追い出すときに書き出されたページはOSページキャッシュに入るので、shared buffers、OSページキャッシュの順にクリアします。

psql -U postgres -d mydb -c 'SELECT * FROM pg_buffercache_evict_all();'
sync; echo 3 > /proc/sys/vm/drop_caches
psql -U postgres -d mydb -c 'EXPLAIN (ANALYZE, BUFFERS) SELECT ...;'

coldの値を複数回取るときは、計測のたびにクリアからやり直します。

EXPLAINにBUFFERSオプションをつけると、shared buffersから読んだページ数がshared hit、shared buffersになかったページ数がshared readとして出ます。ただし、shared readのページがOSページキャッシュから返ってきたかディスクから読んだかはこの数字では区別できません。shared buffersだけをクリアしてshared readが増えたのを見ても、coldの状態になったとは言えないので、OSページキャッシュのクリアも忘れずに行いましょう。

おわりに

warmの状態での高速化にこだわるのもよいですが、coldの状態での速さにも気を付けましょう。pg_buffercacheとdrop_cachesを使えば再起動せずにcoldの状態を作れるので、実行計画を比べるときはcoldとwarmの両方を計測してみてください。