CentOS系统中PostgreSQL的资源限制

CentOS 上 PostgreSQL 资源限制与优化要点

一 操作系统层限制

• 文件句柄与进程数
  • 建议为数据库运行账号（通常为postgres）提升软/硬限制，避免“Too many open files”和“fork: Resource temporarily unavailable”。
  • 编辑 /etc/security/limits.conf（或 /etc/security/limits.d/99-postgres.conf）：
    • postgres soft nofile 65535
    • postgres hard nofile 65535
    • postgres soft nproc 4096
    • postgres hard nproc 4096
  • 注意：使用 systemd 时，需确保服务单元未覆盖限制，必要时在 unit 中设置：
    • 在 /usr/lib/systemd/system/postgresql-.service 的 [Service] 段加入：
      • LimitNOFILE=65535
      • LimitNPROC=4096
    • 执行：systemctl daemon-reload && systemctl restart postgresql-
• 系统级文件句柄与内核参数
  • 提升系统可分配的文件句柄与网络/内存刷新参数，编辑 /etc/sysctl.conf 并执行 sysctl -p：
    • fs.file-max = 76724200
    • fs.aio-max-nr = 40960000
    • kernel.sem = 10000 10240000 10000 1024
    • net.ipv4.ip_local_port_range = 9000 65000
    • net.core.somaxconn = 4096
    • vm.swappiness = 10
    • 对于大内存机器，平滑后台刷脏以避免 IO 峰值：
      • vm.dirty_background_ratio = 1（当内存≥8GB时更平滑）
      • 或设置 vm.dirty_background_bytes = 1073741824（约1GB，与 ratio 二选一）
      • vm.dirty_ratio = 20
      • vm.dirty_writeback_centisecs = 100
      • vm.dirty_expire_centisecs = 500
  • 说明：上述为通用生产建议值，需结合实例规格与负载压测微调。

二 PostgreSQL 层关键参数

• 连接与会话
  • 查询与调整：
    • 当前连接数：select count(1) from pg_stat_activity;
    • 最大连接数：show max_connections;（默认常见为100，不同版本可能不同）
  • 修改 postgresql.conf 的 max_connections 并重启；提高该值会增加共享内存与信号量占用，且每个连接都有内存与调度开销，不宜盲目放大。
• 内存相关
  • 常见建议（以实例总内存为基准，示例为16GB机器）：
    • shared_buffers：约 25% 内存（如 4GB）
    • effective_cache_size：约 50%–75% 内存（如 8–12GB，用于成本估计，不占用 PG 进程内存）
    • work_mem：按并发与操作类型估算，示例 64MB（排序/哈希等算子每个操作使用）
    • maintenance_work_mem：维护类操作（VACUUM/创建索引等），示例 512MB–2GB
  • 示例（postgresql.conf）：
    • shared_buffers = 4GB
    • effective_cache_size = 12GB
    • work_mem = 64MB
    • maintenance_work_mem = 2GB
    • wal_buffers = 16MB
  • 修改后通过 SELECT name, setting FROM pg_settings WHERE name IN (‘shared_buffers’,‘effective_cache_size’,‘work_mem’,‘maintenance_work_mem’,‘wal_buffers’); 验证。

三 生效与验证

• 使配置生效
  • 操作系统层：sysctl -p；必要时重启相关服务或系统。
  • PostgreSQL：
    • 仅调整非连接数参数：systemctl reload postgresql-
    • 调整 max_connections 等结构性参数：systemctl restart postgresql-
• 常用验证
  • 连接与资源：
    • 当前/最大连接：select count(1) from pg_stat_activity; 与 show max_connections;
    • 配置文件与数据目录：show config_file; 与 show data_directory;
  • 内核与限制：
    • 当前文件句柄上限：cat /proc/sys/fs/file-max
    • postgres 进程限制：prctl -n process.max-file-descriptor -p $(pidof postgres)
    • 端口与监听：ss -lntp | grep 5432 或 netstat -an | grep 5432。

四 生产实践与注意事项

• 连接数规划
  • 避免把 max_connections 设得过大；优先使用连接池（如 PgBouncer、Pgpool-II）复用连接，减少 per-connection 内存与调度开销。
• 内存与刷脏
  • 大内存（≥64GB）场景，将后台刷脏阈值调低（如 1% 或固定 1GB）可显著降低写峰抖动，提升稳定性。
• 生效路径差异
  • 使用 systemd 部署时，若 limits.conf 不生效，请在服务 unit 中显式设置 LimitNOFILE/LimitNPROC 并 reload/restart。
• 监控与回退
  • 变更前备份配置；变更后在业务低峰期执行；持续观察错误日志、连接数、I/O 与延迟，必要时回滚。