Linux 上 PostgreSQL 内存配置的合理做法
一 内存架构与关键参数
- 共享内存:由数据库启动时一次性分配,包含Shared Buffers、WAL Buffers、CLOG等,服务于所有后端进程。
- 本地内存:每个后端进程私有,包含work_mem、maintenance_work_mem、temp_buffers、排序/哈希操作临时区等。
- 关键参数与作用
- shared_buffers:共享数据页缓存,默认值128MB,通常设为内存的约 25%(专用库可更高,超大内存时不必超过8GB)。
- work_mem:每个排序/哈希操作的内存上限,默认4MB;并发多时总占用会成倍放大。
- maintenance_work_mem:VACUUM/CREATE INDEX 等维护操作内存,默认64MB;通常可设得比 work_mem 大。
- temp_buffers:会话级临时表/临时页缓存,默认8MB,按需放大。
- effective_cache_size:供成本估计使用的“可用缓存”提示值(不实际分配),专用库常设为内存的50%–75%。
- wal_buffers:WAL 写入缓冲,默认**-1**(自动,约为 shared_buffers 的1/32),通常无需手动放大。
二 快速配置基线
- 专用数据库服务器(示例总内存为32GB)
- shared_buffers = 8GB(约 25%)
- effective_cache_size = 24GB(约 75%)
- work_mem = 64MB(先保守,再按实际溢出调优)
- maintenance_work_mem = 512MB(或按“总内存的 10%,上限 1GB”)
- wal_buffers = 256MB(约为 8GB/32)
- 说明:work_mem 为“每个排序/哈希操作”的上限,需结合并发谨慎放大。
- 小型或混合负载(示例总内存为8GB)
- shared_buffers = 2GB
- effective_cache_size = 4–6GB
- work_mem = 32MB
- maintenance_work_mem = 256MB
- wal_buffers = 64MB
- 超大内存主机(>32GB)
- shared_buffers 不必线性增长,维持8GB左右通常更稳健;把更多内存留给操作系统文件缓存与后台 I/O 更划算。
三 计算与调优方法
- 计算 work_mem 的安全上限
- 公式:worst_mem ≈ work_mem × 并发排序/哈希操作数
- 并发操作数 ≈ 活跃连接 × 每个查询的排序/哈希操作数
- 示例:work_mem=64MB,活跃连接100,每查询平均2个排序/哈希 ⇒ worst_mem ≈ 12.8GB。若物理内存仅32GB且需为 OS/缓存留足空间,应降低 work_mem 或限制并发。
- 判断是否该增大 work_mem
- 打开日志临时文件:log_temp_files = 0(记录所有临时文件);或在 SQL 级设置 work_mem。
- 用 EXPLAIN (ANALYZE) 观察是否出现 “Sort Method: external merge Disk: … / Hash Join …” 等外部操作;将 work_mem 提高到能容纳“最大单次临时文件”的约 2–3 倍,再验证是否消除溢出。
- 维护类操作
- 大表 VACUUM/创建索引时,临时提高 maintenance_work_mem(如1GB)可显著加速;日常保持默认或按“总内存 10%,上限 1GB”更稳妥。
- 若启用 autovacuum,注意总占用 ≈ autovacuum_work_mem × autovacuum_max_workers;必要时用 autovacuum_work_mem 单独控制,避免与 maintenance_work_mem 叠加过大。
四 Linux 系统层面的配合
- 透明大页 THP
- 建议关闭或设置为 madvise:echo never > /sys/kernel/mm/transparent_hugepage/enabled
- 或在 postgresql.conf 使用 huge_pages = try/on 并预留足够的大页(vm.nr_hugepages × 2MB ≥ shared_buffers)。
- 共享内存与 IPC
- 现代发行版多使用 POSIX 动态共享内存,通常无需再手动调 kernel.shmmax/shmall;仅在异常或老系统上检查并调整。
- 内存超分与 OOM
- 建议保持 vm.overcommit_memory = 0/2(启发式/禁止超分),避免内存过度承诺导致 OOM。
- 交换倾向
- OLTP 场景建议 vm.swappiness ≤ 10(如 5),减少换页抖动;数据仓库可适当提高。
- 脏页刷写
- 结合负载调节 vm.dirty_background_ratio / dirty_ratio,避免突发大量刷盘造成 I/O 卡顿。
五 监控与常见误区
- 监控要点
- 系统:free -m、vmstat 1、iostat -x 1;关注 swap、读写延迟、CPU iowait。
- 数据库:pg_stat_activity(活跃连接/长事务)、pg_stat_statements(高成本 SQL)、日志中临时文件与溢出、检查点统计(checkpoint spikes)。
- 常见误区
- 把 effective_cache_size 当成“真实分配内存”(它只是成本估计提示)。
- 盲目提高 max_connections 并同步拉高 work_mem,导致 worst_mem 超出物理内存。
- 过度放大 shared_buffers(>8GB)反而增加锁争用与检查点压力,收益有限。
- 忽视 Linux 层 THP、overcommit、swappiness 等设置,导致性能抖动或 OOM。