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。
