【PostgreSQL】SELECT結果がshared_buffers / OSキャッシュ / ディスクのどこから来ているか実行時間で検証してみた
PostgreSQL の shared_buffers は、ディスク上のデータをメモリにキャッシュするための領域です。
実際に SELECT を実行したとき、そのデータが以下のどこから来ているのかを、実行時間の違いから検証して確認してみます。
- shared_buffers（PostgreSQLのキャッシュ）
- OS メモリキャッシュ
- 実ディスク
shared_buffers (integer)
データベースサーバが共有メモリバッファのために使用するメモリ量を設定します。
19.4. 資源の消費
shared_buffers については他社様の記事ではありますが以下も図解で大変わかりやすいため「図2 共有バッファー（shared_buffers）」などを参照ください。
共有バッファー（shared_buffers）
ディスク上にあるテーブルやインデックスのデータを、ブロック単位で共有メモリー上にキャッシュするための領域です。
PostgreSQLのアーキテクチャー概要 | 富士通
検証の流れ
以下の3パターンでそれぞれ同一クエリの EXPLAIN (ANALYZE, BUFFERS) を実施し、Execution Time と buffers(shared hit/read) を比較します。
| ステップ | 状態を作る操作 | 期待される結果 |
|---|---|---|
| ① 完全コールド | shared_buffers再起動 ＋ OSメモリキャッシュもクリア（drop_caches） | 実ディスクから読むため最も遅いはず |
| ② OSメモリキャッシュのみ残っている状態 | shared_buffersだけ再起動でクリア | ①より速い（OSメモリキャッシュ経由）はず |
| ③ shared_buffersにもキャッシュが乗っている状態 | 再起動せず同じクエリを再実行 | 最も速い（shared_buffers を直読み）はず |
※ shared_buffers の read は「shared_buffersになかった」ことしか示さず、その先が OS キャッシュか実ディスクかは区別できません。そのため、実行時間の差を使って間接的に判断していきます。
PostgreSQL インストール
検証のための実行環境は EC2（AL2023, t3.micro）にインストールした PostgreSQL を使用しました。
EC2 に PostgreSQL をインストールする手順としては下記をご参照ください。
PostgreSQL 17.10 を利用し、検証していきます。
[ec2-user@ip-xx-xx-xx-xx ~]$ sudo -u postgres psql
psql (17.10)
Type "help" for help.
postgres=# SELECT version();
version
---------------------------------------------------------------------------------------------------------------
PostgreSQL 17.10 on x86_64-amazon-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-5), 64-bit
(1 row)
サンプルデータ準備
検証クエリ先となるテーブルを作成しておきます。
-- 検証用DB作成
postgres=# CREATE DATABASE blog_sample_db;
CREATE DATABASE
-- DB 切り替え
postgres=# \c blog_sample_db
You are now connected to database "blog_sample_db" as user "postgres".
-- 検証用テーブル作成
blog_sample_db=# CREATE TABLE big_table (
id SERIAL PRIMARY KEY,
data TEXT
);
CREATE TABLE
-- 100万行レコードのテーブルを作成
-- テーブルの作り方：random な小数を文字列にキャストし、その後 md5 関数で 32 文字のハッシュ値に変換
blog_sample_db=# INSERT INTO big_table (data)
SELECT md5(random()::text)
FROM generate_series(1, 1000000);
INSERT 0 1000000
-- どんなテーブルが作成されたのか、最初と最後の 5 行を表示
blog_sample_db=# SELECT * FROM big_table ORDER BY id ASC LIMIT 5;
id | data
----+----------------------------------
1 | c943384495245f45c87661f6629cb898
2 | e00844fed8718498e41393e56fabfdde
3 | d97ab8ff14cca83e726da5c2776e61d9
4 | 9d89f5b2864708d8d7e0a5c0783426f8
5 | 93fa1c1c3de54ba83115e0611736f747
(5 rows)
blog_sample_db=# SELECT * FROM big_table ORDER BY id DESC LIMIT 5;
id | data
---------+----------------------------------
1000000 | 93c1633c643055bf6fb5c3cf5a85e693
999999 | 1ff04315a60926c5cab1497bf8b40d89
999998 | 5adc0722a57595e6eb8d2cd234762281
999997 | c03fcbc497703eca5e0c1a0836d1b795
999996 | d73e2d08540fbbd00393665e3b7d8fe7
(5 rows)
ステップ1) 完全コールド
まず最初に完全コールド（shared_buffers も OS メモリキャッシュも空の状態）時の実行時間を確認します。
PostgreSQL再起動し、shared_buffersを空にします。
$ sudo systemctl restart postgresql
続いて OS メモリキャッシュを削除します。
OS のメモリキャッシュをクリアする前に、念の為 ダーティページ（メモリ上にあるが、まだディスクに書き込まれていない変更）を先にディスクへ書き出しておきます。
$ sync
sync - Synchronize cached writes to persistent storage
（sync - キャッシュされた書き込みを、永続的なストレージへ同期する）
man syncコマンドより参照
sync ができたら OS キャッシュをクリアします。
$ echo 3 | sudo tee /proc/sys/vm/drop_caches
drop_caches
Writing to this will cause the kernel to drop clean caches, as well as reclaimable slab objects like dentries and inodes. Once dropped, their memory becomes free.
（このファイルに書き込むと、カーネルは「クリーンなキャッシュ」に加えて、dentryやinodeのような「再利用可能なslabオブジェクト」を解放します。解放されると、そのメモリは空き領域になります。）
...
To free slab objects and pagecache:
echo 3 > /proc/sys/vm/drop_cachesDocumentation for /proc/sys/vm/ — The Linux Kernel documentation
以上で完全コールド状態になったので、間を空けずに検証クエリを実行します。
# PostgreSQL へログイン
$ sudo -u postgres psql
-- データベース切り替え
postgres=# \c blog_sample_db
You are now connected to database "blog_sample_db" as user "postgres".
-- 検証クエリの実行
blog_sample_db=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM big_table WHERE id = 500000;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Index Scan using big_table_pkey on big_table (cost=0.42..8.44 rows=1 width=37) (actual time=2.531..2.534 rows=1 loops=1)
Index Cond: (id = 500000)
Buffers: shared read=4
Planning:
Buffers: shared hit=66 read=23
Planning Time: 19.401 ms
Execution Time: 7.720 ms
(7 rows)
Buffers: shared read=4 となっており、shared_buffers 上にデータが無かったことがわかります。
また Execution Time は 7.720 ms でした。この時点では OS メモリキャッシュも空にしているため、実ディスクまで読みに行った結果、次項以降で検証するステップ2と3と比較し速度が遅い状態と考えられます。
shared hit 共有バッファ（メモリ）上にあったページを参照した数
shared read ディスク（またはOSキャッシュ）から読み込んだページ数
【PostgreSQL】 UPDATE するたびにdead tupleが溜まりクエリが遅くなることを検証してみた | DevelopersIO
ステップ2) OS メモリキャッシュのみ残っている状態
前項のステップ 1 からそのまま続けます。
今回は OS メモリキャッシュのみ残しておきたいため、一度 PostgreSQL から抜けて、再起動（shared_buffers のみクリア）します。
$ sudo systemctl restart postgresql
再起動後 PostgreSQL にログインし、EXPLAIN ANALYZE を実施します。
-- ログイン
$ sudo -u postgres psql
psql (17.10)
Type "help" for help.
-- DB 切り替え
postgres=# \c blog_sample_db
You are now connected to database "blog_sample_db" as user "postgres".
-- 検証クエリ実行
blog_sample_db=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM big_table WHERE id = 500000;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Index Scan using big_table_pkey on big_table (cost=0.42..8.44 rows=1 width=37) (actual time=0.041..0.042 rows=1 loops=1)
Index Cond: (id = 500000)
Buffers: shared read=4
Planning:
Buffers: shared hit=66 read=23
Planning Time: 0.527 ms
Execution Time: 0.074 ms
(7 rows)
Buffers: shared read=4 と、ステップ1と全く同じ数値になっています。
一方で Execution Time は 0.074 ms となり、ステップ1（7.720 ms）と比べて約100倍高速化しました。
buffers の数値だけを見ると同じ「read=4」ですが、実際にはこの裏側で「実ディスクから読んだか」
「OS メモリキャッシュから読んだか」という違いが起きており、それが実行時間の差として表れたと考えられます。
ステップ3) shared_buffers にもキャッシュが乗っている状態
ステップ2から続けて、間をおかず、同じクエリを再度実行します。
（先ほどステップ2で同一クエリを実行した際に、該当データが含まれるブロックが shared_buffers に読み込まれています。今回は PostgreSQL を再起動していないため、そのブロックが共有メモリに残ったまま次のクエリを実行することになり、速くなるはずです。）
blog_sample_db=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM big_table WHERE id = 500000;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Index Scan using big_table_pkey on big_table (cost=0.42..8.44 rows=1 width=37) (actual time=0.017..0.018 rows=1 loops=1)
Index Cond: (id = 500000)
Buffers: shared hit=4
Planning Time: 0.054 ms
Execution Time: 0.031 ms
(5 rows)
Buffers: shared hit=4 となり、ついに shared_buffers 上でヒットしました。
Execution Time も 0.031 ms と、ステップ2（0.074 ms）よりさらに高速化しています。
shared_buffers は PostgreSQL サーバ内の共有メモリ上に直接データを保持しているため、
OS メモリキャッシュを経由するステップ2よりも、さらに高速にデータを返せることが確認できました。
終わりに
3つの状態でクエリを実行した結果をまとめると以下の通りです。
| ステップ | 状態 | Execution Time | Buffers |
|---|---|---|---|
| ① 完全コールド | shared_buffers・OSメモリキャッシュともに空 | 7.720 ms | shared read=4 |
| ② OSメモリキャッシュのみ残っている | shared_buffersのみ空 | 0.074 ms | shared read=4 |
| ③ shared_buffersにも乗っている | 両方に残っている | 0.031 ms | shared hit=4 |
buffers の read/hit の数値だけでは、「実ディスクから読んだのか」「OS メモリキャッシュから読んだのか」までは判別できません。
しかし実行時間を比較することで、同じ read=4 でも裏側で起きていることが全く異なっていることが確認できたと思います。
shared_buffers はデータベースサーバ内の共有メモリ上に直接データを保持できるため、OS メモリキャッシュ経由よりもさらに高速にデータへアクセスできる、という結果になりました。
今回の検証で、PostgreSQLの共有メモリ(shared_buffers)、OSメモリキャッシュ、ディスクの3層構造の動きが少しでもイメージしやすくなったのでとても有意義でした。
本記事がお役に立てば幸いです。
参考情報
