Ubuntu下PostgreSQL内存管理实用技巧
一 内存参数快速建议
二 Ubuntu 系统层优化
- 共享内存与信号量：现代发行版已使用 POSIX 共享内存，通常无需再手动设置内核的 SHMMAX/SHMALL；仅在异常或老版本需要时再调整。
- 透明大页（THP）：建议关闭或设置为 madvise，避免内存碎片与额外开销：
- 检查：cat /sys/kernel/mm/transparent_hugepage/enabled
- 临时关闭：echo never | sudo tee /sys/kernel/mm/transparent_hugepage/enabled
- 持久化：在 /etc/default/grub 的 GRUB_CMDLINE_LINUX 增加 transparent_hugepage=never，然后更新 GRUB 并重启
- huge_pages（大页）：当数据库内存较大（如 >32GB）时，可启用大页减少 TLB 缺失：
- 估算所需大页数：HugePages_Total ≈ shared_buffers / 大页大小（常见 2MB 或 1GB）
- 预留：sudo sysctl -w vm.nr_hugepages=…
- 赋权：将数据库运行用户加入 hugetlb 组，或在 postgresql.conf 设置 huge_pages=on
- 内存过量使用策略：为降低 OOM 风险，可设置 vm.overcommit_memory=2（严格模式）；同时结合合理的 work_mem 与连接数，避免总体内存承诺过高。
- 资源限制：在 /etc/security/limits.conf 为数据库用户放宽 memlock/fd 等限制，确保大页与连接资源可用。
三 避免常见内存陷阱
- 过度增加 max_connections：每个连接会消耗共享内存与后端进程栈，连接风暴会放大内存压力；优先使用连接池（如 PgBouncer）复用连接。
- 误把 effective_cache_size 当作真实内存分配：它只是成本估计值，不会占用内存；将其设为物理内存的**约50%**通常即可。
- 忽视 work_mem 的并发叠加效应：work_mem 按每个排序/哈希操作计费，总消耗 ≈ 并发操作数 × work_mem；在并发较高或存在大量哈希聚合/排序时，应下调 work_mem 并控制并发。
- 维护任务“吃满”内存：将 maintenance_work_mem 调大可加速 VACUUM/CREATE INDEX，但并行或多个 worker 同时运行时，总内存会成倍增加；必要时为 autovacuum 单独设置 autovacuum_work_mem，并限制 worker 数量。
四 监控与容量规划
- 观察与诊断 SQL 内存使用：
- 使用 EXPLAIN (ANALYZE, BUFFERS) 查看排序/哈希是否落盘（Disk: 与 Buffers: 信息），据此微调 work_mem。
- 查询当前活跃语句与内存占用：SELECT pid, query, work_mem, (shared_blks_hit+shared_blks_read) AS total_blks FROM pg_stat_statements JOIN pg_stat_activity USING (pid) WHERE state=‘active’;
- 观察检查点与 WAL：
- 关注检查点频率与完成度：SELECT checkpoints_timed, checkpoints_req, checkpoint_write_time, checkpoint_sync_time FROM pg_stat_bgwriter;
- 结合 min_wal_size / max_wal_size / checkpoint_completion_target 平滑 I/O 峰值。
- 观察系统层内存与大页：
- 内存与压力：free -m、vmstat 1、sar -r
- 大页使用：grep -i huge /proc/meminfo；确保 HugePages_Total 与数据库需求匹配
- 变更流程与验证：
- 一次只调整少量参数，变更后用 pg_reload_conf() 或重启生效，并用上述 SQL 复核效果与稳定性。
