メモリ設定を確認(shared_buffers、work_mem、maintenance_work_mem)

コマンド

SELECT name,
       current_setting(name) AS value,
       source,
       context
FROM pg_settings
WHERE name IN ('shared_buffers', 'effective_cache_size', 'work_mem', 'hash_mem_multiplier',
               'maintenance_work_mem', 'autovacuum_work_mem', 'max_connections',
               'max_parallel_workers_per_gather', 'huge_pages', 'temp_buffers', 'wal_buffers')
ORDER BY name;

-- Rough ceilings: work_mem x max_connections and the autovacuum memory ceiling
SELECT pg_size_pretty(pg_size_bytes(current_setting('work_mem'))
                      * current_setting('max_connections')::int) AS work_mem_x_max_connections,
       pg_size_pretty(pg_size_bytes(current_setting('maintenance_work_mem'))
                      * current_setting('autovacuum_max_workers')::int) AS autovacuum_mem_ceiling;

-- Trace of operations that did not fit in work_mem: temp files per database
SELECT datname, temp_files, pg_size_pretty(temp_bytes) AS temp_size, stats_reset
FROM pg_stat_database
WHERE datname IS NOT NULL
ORDER BY temp_bytes DESC;

概要

メモリ関連のパラメータを、値、設定元(デフォルト/構成ファイル)、変更コンテキスト(postmaster = 再起動)とともに表示する。work_memとautovacuumのおおよそのメモリ上限、データベースごとの一時ファイル量も表示する。

使用する場面

新しいサーバーの構築時、RAMの追加後、OOM killerの発生後、一時ファイルの大量使用が見られるときに。

注意

work_memは接続ごとではなく、ソート/ハッシュのノードごとに(ハッシュではhash_mem_multiplier倍)、さらにパラレルワーカーごとに個別に割り当てられる。複雑なクエリはwork_memの数倍を使用する場合があるため、計算した積は上限ではなくおおよその警告値である。一般的な初期値: shared_buffersはRAMの約25%(再起動が必要)、effective_cache_sizeは約50-75%(プランナへのヒントのみで、何も割り当てない)、maintenance_work_memは512MB-2GB。autovacuum_work_mem = -1の場合、各autovacuumワーカーはmaintenance_work_memを使用できる。temp_bytesが高い場合は、グローバルではなくロールまたはクエリ単位でwork_memを引き上げる。Linuxでshared_buffersが大きい場合は、huge_pagesを検討する。