shared_buffersとwork_memの役割
PostgreSQLのメモリは大きく2種類ある。全プロセスで共有する領域と、バックエンドプロセスごとに確保する領域である。shared_buffersは前者、work_memは後者を制御するパラメータである。
graph TD C1[クライアント接続1] --> B1[バックエンドプロセス1] C2[クライアント接続2] --> B2[バックエンドプロセス2] B1 --> W1["work_mem<br/>プロセスごとに確保"] B2 --> W2["work_mem<br/>プロセスごとに確保"] B1 --> S["shared_buffers<br/>全プロセスで共有"] B2 --> S W1 -.超過時.-> T[一時ファイル] W2 -.超過時.-> T S --> D[(ディスク)]
shared_buffersはテーブルやインデックスのページをキャッシュする共有メモリのサイズである。クエリが必要とするページがshared_buffers上にあればディスクI/Oが発生しない。サイズが小さいとキャッシュから追い出されるページが増え、ディスク読み込みが増加する。デフォルト値は128MBである。
work_memはソートやハッシュ結合などの処理1つあたりに使用できる作業メモリの上限である。上限を超えるとディスク上の一時ファイルを使うため処理が遅くなる。デフォルト値は4MBである。
現在の設定値を確認する
SHOWコマンドで単位付きの値を確認できる。
SHOW shared_buffers;
shared_buffers
----------------
128MB
(1 row)
SHOW work_mem;
work_mem
----------
4MB
(1 row)
メモリ関連のパラメータをまとめて確認する場合はpg_settingsを参照する。
SELECT name, setting, unit, context, source
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'maintenance_work_mem',
'effective_cache_size', 'hash_mem_multiplier', 'temp_buffers');
name | setting | unit | context | source
----------------------+---------+------+------------+--------------------
effective_cache_size | 524288 | 8kB | user | default
hash_mem_multiplier | 2 | | user | default
maintenance_work_mem | 65536 | kB | user | default
shared_buffers | 16384 | 8kB | postmaster | configuration file
temp_buffers | 1024 | 8kB | user | default
work_mem | 4096 | kB | user | default
(6 rows)
併記した3つのパラメータの意味は以下のとおり。
- maintenance_work_mem:
VACUUMやインデックス作成などの保守処理で使う作業メモリの上限 - effective_cache_size: OSのページキャッシュを含めてディスクキャッシュに使える容量のプランナへの見積もり値(実際のメモリは確保しない)
- temp_buffers: セッションごとに一時テーブル用へ確保するバッファのサイズ
pg_settingsのsettingはunitカラムの単位での値になる。shared_buffersの単位は8kB(ブロック数)のため、バイト数へ換算するにはpg_size_prettyと組み合わせる。
SELECT name, setting, unit, pg_size_pretty(setting::bigint * 8192) AS size
FROM pg_settings
WHERE name = 'shared_buffers';
name | setting | unit | size
----------------+---------+------+--------
shared_buffers | 16384 | 8kB | 128 MB
(1 row)
contextカラムのとおり、shared_buffersはpostmasterのため変更にはPostgreSQLの再起動が必要である。work_memはuserのためSETコマンドでセッション単位に変更できる。
参考: 【PostgreSQL】SHOW ALLやpg_settingsで設定値を一覧・検索する
shared_buffersが足りているかを確認する
pg_stat_databaseでキャッシュヒット率を見る
データベース単位の累積値から共有バッファのヒット率を計算する。
SELECT datname, blks_hit, blks_read,
round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS hit_ratio
FROM pg_stat_database
WHERE datname = current_database();
datname | blks_hit | blks_read | hit_ratio
----------+----------+-----------+-----------
postgres | 30875 | 1200 | 96.26
(1 row)
blks_hitはshared_buffersから読めたブロック数、blks_readはshared_buffersになくディスクから読んだブロック数である。OLTP用途ではヒット率99%以上が目安であり、大きく下回る場合はshared_buffersの不足を疑う。値は統計情報のリセット以降の累積であり、集計の起点はpg_stat_databaseのstats_resetカラムで確認できる。
ただしblks_readはPostgreSQLから見た読み込みであり、実際にはOSのページキャッシュから返っている場合も含む。ヒット率の低さが直ちに物理ディスクI/Oの多さを意味するわけではない。
pg_buffercacheでキャッシュの中身を見る
pg_buffercache拡張を導入すると、共有バッファにどのテーブルがどれだけ載っているかを確認できる。
CREATE EXTENSION pg_buffercache;
SELECT c.relname, count(*) AS buffers, pg_size_pretty(count(*) * 8192) AS size
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
AND b.reldatabase = (SELECT oid FROM pg_database
WHERE datname = current_database())
GROUP BY c.relname
ORDER BY buffers DESC
LIMIT 5;
relname | buffers | size
--------------+---------+--------
t1 | 2304 | 18 MB
pg_attribute | 45 | 360 kB
pg_proc | 18 | 144 kB
pg_class | 16 | 128 kB
pg_type | 9 | 72 kB
(5 rows)
頻繁にアクセスするテーブルのサイズに対してキャッシュされているサイズが極端に小さい場合、shared_buffersを増やす効果が見込める。
PostgreSQL 16以降のpg_buffercacheはバッファマネージャのロックを取得しないため通常の処理への影響は小さくなったが、共有バッファ全体を走査するコストはかかる。本番環境での頻繁な実行は避ける。
クエリ単位で共有バッファの利用状況を確認する場合はEXPLAIN (ANALYZE, BUFFERS)を使用する。
参考: 【PostgreSQL】EXPLAIN (ANALYZE, BUFFERS)でキャッシュヒット率を確認する
work_memが足りているかを確認する
EXPLAIN ANALYZEでSort Methodを見る
work_memの不足は、ソートやハッシュ処理が一時ファイルを使ったかどうかで判断する。EXPLAIN ANALYZEのSort Methodがexternal mergeの場合、work_memに収まらずディスクを使用している。
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) SELECT * FROM t1 ORDER BY val;
QUERY PLAN
----------------------------------------------------------------------
Sort (actual time=375.825..472.613 rows=300000 loops=1)
Sort Key: val
Sort Method: external merge Disk: 13816kB
Buffers: shared hit=2307 read=256, temp read=1727 written=1732
-> Seq Scan on t1 (actual time=0.028..17.212 rows=300000 loops=1)
Buffers: shared hit=2304 read=256
Planning:
Buffers: shared hit=79
Planning Time: 0.181 ms
Execution Time: 483.239 ms
(10 rows)
Disk: 13816kBは一時ファイルへ書き出したサイズ、temp readとtemp writtenは一時ファイルへのブロック単位の読み書きを示す。
なおSort Methodにはexternal mergeとquicksortのほかに、上位N件のみを取り出すtop-N heapsortや、ディスクを使う別方式のexternal sortもある。externalが付く方式は一時ファイルを使用している。
work_memを必要なサイズまで増やすと、Sort Methodがquicksortに変わり一時ファイルが不要になる。
SET work_mem = '64MB';
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) SELECT * FROM t1 ORDER BY val;
QUERY PLAN
----------------------------------------------------------------------
Sort (actual time=430.784..445.547 rows=300000 loops=1)
Sort Key: val
Sort Method: quicksort Memory: 28695kB
Buffers: shared hit=2563
-> Seq Scan on t1 (actual time=0.007..15.920 rows=300000 loops=1)
Buffers: shared hit=2560
Planning:
Buffers: shared hit=79
Planning Time: 0.169 ms
Execution Time: 455.632 ms
(10 rows)
Sort Method: quicksort Memory: 28695kBから、ソート処理には約28MBの作業メモリが必要と分かる。一時ファイルのサイズ13816kBよりも必要な作業メモリのほうが大きい点に注意する。Diskの値をそのままwork_memに設定しても足りない場合がある。
ハッシュ結合の場合はHashノードのBatchesを確認する。Batchesが2以上であれば、ハッシュテーブルがメモリに収まらず分割処理している。
SET work_mem = '1MB';
EXPLAIN (ANALYZE, COSTS OFF) SELECT * FROM t1 JOIN t2 ON t1.val = t2.val;
QUERY PLAN
----------------------------------------------------------------------------
Hash Join (actual time=70.246..205.859 rows=300000 loops=1)
Hash Cond: (t1.val = t2.val)
-> Seq Scan on t1 (actual time=0.007..13.330 rows=300000 loops=1)
-> Hash (actual time=70.055..70.055 rows=300000 loops=1)
Buckets: 32768 Batches: 16 Memory Usage: 1515kB
-> Seq Scan on t2 (actual time=0.016..15.853 rows=300000 loops=1)
Planning Time: 0.138 ms
Execution Time: 213.937 ms
(8 rows)
work_memが1MBにもかかわらずMemory Usageが1515kBとなっているのは、ハッシュ処理の上限がwork_memそのものではなくwork_mem × hash_mem_multiplierのためである(詳細は後述)。
参考: 【PostgreSQL】EXPLAIN ANALYZEの読み方の基本
pg_stat_databaseで一時ファイルの発生量を見る
データベース全体で一時ファイルがどれだけ発生しているかはpg_stat_databaseで確認する。
SELECT datname, temp_files, pg_size_pretty(temp_bytes) AS temp_bytes
FROM pg_stat_database
WHERE datname = current_database();
datname | temp_files | temp_bytes
----------+------------+------------
postgres | 3 | 30 MB
(1 row)
temp_filesが継続的に増加している場合、work_memの不足かクエリ自体の非効率を疑う。
log_temp_filesで一時ファイルをログに記録する
log_temp_filesにサイズをkB単位で指定すると、指定サイズ以上の一時ファイルの削除時にログを出力する。0を指定するとすべての一時ファイルを記録する。デフォルト値は-1でログ出力は無効である。
ALTER SYSTEM SET log_temp_files = 0;
SELECT pg_reload_conf();
一時ファイルを伴うクエリを実行すると、サイズと該当クエリがログに出力される。
LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp129.0", size 12959744
STATEMENT: SELECT count(*) FROM (SELECT * FROM t1 ORDER BY val) x;
どのクエリがどれだけ一時ファイルを使ったかが分かるため、work_memを引き上げる対象のクエリを特定できる。並列クエリでは1つのクエリに対してワーカーごとのログ行が出力される。
参考: 【PostgreSQL】log_min_duration_statementでスロークエリをログに出力する
work_memはクエリ全体の上限ではない
work_memはクエリ単位ではなく、ソートやハッシュなどの操作1つあたりの上限である。1つのクエリに複数のソートノードがあれば、それぞれが最大work_memまで消費する。
さらに並列クエリではワーカープロセスごとにwork_memが確保される。以下はwork_memをデフォルトの4MBに戻し、小さなテーブルでも並列実行されるようコストパラメータを調整した例である。
RESET work_mem;
SET max_parallel_workers_per_gather = 2;
SET parallel_setup_cost = 0;
SET parallel_tuple_cost = 0;
SET min_parallel_table_scan_size = 0;
EXPLAIN (ANALYZE, COSTS OFF) SELECT val, count(*) FROM t1 GROUP BY val;
QUERY PLAN
------------------------------------------------------------------------------------------
GroupAggregate (actual time=137.979..277.845 rows=300000 loops=1)
Group Key: val
-> Gather Merge (actual time=137.972..228.393 rows=300000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (actual time=122.002..125.827 rows=100000 loops=3)
Sort Key: val
Sort Method: quicksort Memory: 3073kB
Worker 0: Sort Method: quicksort Memory: 3073kB
Worker 1: Sort Method: quicksort Memory: 3073kB
-> Parallel Seq Scan on t1 (actual time=0.005..6.160 rows=100000 loops=3)
Planning Time: 0.187 ms
Execution Time: 286.672 ms
(13 rows)
リーダーと2つのワーカーの3プロセスがそれぞれ約3MBを消費しており、合計では約9MBに達する。最悪ケースのメモリ使用量は「同時実行クエリ数 × クエリ内の操作数 × プロセス数(ワーカー数 + リーダー) × work_mem」まで膨らむため、work_memを大きくしすぎるとメモリ枯渇を招く。
なおハッシュ結合やハッシュ集約ではwork_mem × hash_mem_multiplierがメモリ上限になる。PostgreSQL 15以降のhash_mem_multiplierのデフォルト値は2.0であり、ハッシュ処理はソートの2倍のメモリを使用できる。最悪ケースの見積もりでは、ハッシュ処理の分をさらに2倍で計算する。
参考: 【PostgreSQL】接続数の上限と現在の接続数を確認する
設定値の目安と変更方法
shared_buffersは、1GB以上のメモリを積んだ専用のデータベースサーバーであれば搭載メモリの25%程度が出発点になる。PostgreSQLはOSのページキャッシュにも依存するため、搭載メモリの40%を超えて割り当てても、それより小さい値と比べて性能が向上しにくい。
work_memはデフォルトの4MBのまま運用されがちだが、集計処理が多いシステムでは不足しやすい。全体を引き上げる前に、一時ファイルを多用する特定のクエリへセッション単位で設定する方が安全である。
SET work_mem = '64MB';
[対象のクエリ];
RESET work_mem;
特定のユーザーやデータベースに限定した設定も可能である。
ALTER ROLE batch_user SET work_mem = '256MB';
ALTER DATABASE analytics SET work_mem = '128MB';
サーバー全体の設定を変更する場合はpostgresql.confまたはALTER SYSTEMを使用する。
ALTER SYSTEM SET shared_buffers = '4GB';
ALTER SYSTEM SET work_mem = '16MB';
work_memはSELECT pg_reload_conf()で反映されるが、shared_buffersはPostgreSQLの再起動が必要である。再起動待ちの設定はpg_settingsのpending_restartカラムで確認できる。
参考: 【PostgreSQL】postgresql.confの場所を探す(show config_file)
