Linux PostgreSQL内存配置怎样合理

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。