CentOS 上 PostgreSQL 资源占用优化指南
一 系统层优化
- 资源限制与文件句柄
- 提升数据库运行账户（通常为 postgres）的资源上限，避免连接、打开表/索引时受限：
- 编辑 /etc/security/limits.conf：
- postgres soft nproc 4096
- postgres hard nofile 65535
- postgres soft nofile 65535
- 使限制生效：重启会话或重启 sshd 服务（生产环境按维护窗口执行）。
- 内核与 I/O 平滑
- 降低后台刷脏阈值，避免大内存机器一次性回写造成 I/O 抖动：
- 编辑 /etc/sysctl.conf：
- vm.dirty_background_ratio = 0
- vm.dirty_background_bytes = 1073741824（约 1GB）
- 使配置生效：执行 sysctl -p。
- 减少换页倾向，优先保留热点数据在物理内存：
- vm.swappiness 建议设置为 10（范围 0–100，越低越倾向避免交换）。
二 数据库内存与 I/O 参数
- 关键参数建议（需结合实例规格与负载逐步验证）
- shared_buffers：数据库共享缓存，建议专用库设为内存的 25%–40%；超过 32GB 内存的专用库可保守设为 8GB，其余交由操作系统页缓存。
- work_mem：每个排序/哈希操作可用内存。从 32–64MB 起步，依据查询复杂度与并发度逐步上调；注意并发多个操作会成倍放大总内存占用。
- maintenance_work_mem：维护类操作（VACUUM、CREATE INDEX 等）内存。可设为系统内存的 10%、上限 1GB；若启用 autovacuum，需考虑 autovacuum_max_workers × maintenance_work_mem 的总量。
- effective_cache_size：供成本估计使用的“可用缓存”假设值，非实际分配。专用库可设为空闲内存的 25%–50%，促使优化器更倾向于索引扫描。
- wal_buffers：WAL 共享缓存，默认 -1（自动）；写入密集型可适当增大到 数 MB 至 16MB，通常不超过 shared_buffers 的 1/32。
- effective_io_concurrency：并发 I/O 能力提示。使用 SSD 时可设为 200 左右以提升顺序/随机访问效率。
- 并行度：如 max_parallel_workers_per_gather，依据 CPU 核心数 与负载开启，避免无谓上下文切换与内存争用。
三 连接与并发治理
- 控制连接数，优先使用连接池
- 过高的 max_connections 会显著增加后端进程数与内存占用（每个后端都有本地内存：work_mem、temp_buffers 等）。建议通过连接池（如 PgBouncer 的 session 或 transaction 模式）复用连接，数据库侧仅保留必要的常驻连接。
- 连接开销与资源隔离
- 连接建立/销毁、认证与上下文切换均有成本。结合业务峰值与 PgBouncer 的池化配置，平滑突发连接，降低数据库瞬时资源压力。
四 查询与索引优化
- 识别与改写慢查询
- 使用 EXPLAIN (ANALYZE, BUFFERS) 检查是否发生磁盘溢出（如 “Sort Method: external merge Disk” 或 “Buffers: shared read” 偏高），据此调整 work_mem 或改写 SQL/索引。
- 启用 pg_stat_statements 定位高成本 SQL，持续跟踪执行计划与资源消耗变化。
- 索引策略
- 为高频 WHERE/JOIN/ORDER BY 列建立合适索引；多列条件使用复合索引；对大表结合 分区表 降低扫描与维护成本；必要时定期重建/重组索引以抑制碎片。
五 监控与维护
- 资源与空间监控
- 库内：使用 pg_stat_user_tables、pg_size_pretty、pg_total_relation_size 观察表/索引增长与膨胀；结合 pg_stat_statements 发现异常 SQL 与资源热点。
- 系统：用 df -h、du -sh 巡检数据目录与日志盘空间，避免 WAL/临时文件/备份占满磁盘。
- 例行维护与日志分析
- 保持 VACUUM/ANALYZE 及时执行（含 autovacuum 调参），回收死元组并更新统计信息，避免查询计划劣化与表膨胀。
- 使用 pgBadger 对 PostgreSQL 日志进行可视化分析，定位慢查询、错误与连接风暴等性能隐患。