centos postgresql内存配置技巧

1. 核心内存参数配置

• shared_buffers：用于缓存表和索引数据，是PostgreSQL性能优化的基础参数。建议设置为系统可用物理内存的25%-50%（如8GB内存可设为2-4GB）。需重启服务生效，配置示例如下：

  echo "shared_buffers = 4GB" >> /var/lib/pgsql/data/postgresql.conf
  sudo systemctl restart postgresql
  

• work_mem：控制每个排序、哈希或聚合操作的内存使用量（如ORDER BY、GROUP BY）。需根据查询复杂度调整，建议设置为64MB-1GB（单个操作），且work_mem * max_connections需小于系统总内存（避免内存溢出）。配置示例如下：

  echo "work_mem = 64MB" >> /var/lib/pgsql/data/postgresql.conf
  

• maintenance_work_mem：用于维护操作（如VACUUM、CREATE INDEX、ALTER TABLE），建议设置为512MB-1GB（比work_mem大，提升维护效率）。配置示例如下：

  echo "maintenance_work_mem = 512MB" >> /var/lib/pgsql/data/postgresql.conf
  

• effective_cache_size：协助查询优化器估算操作系统和PostgreSQL的总缓存大小，建议设置为系统内存的50%-75%（如8GB内存可设为4-6GB）。该参数不实际占用内存，仅作为优化参考。配置示例如下：

  echo "effective_cache_size = 6GB" >> /var/lib/pgsql/data/postgresql.conf
  

• wal_buffers：用于WAL（预写日志）的缓冲区，建议设置为shared_buffers的3%-4%（如4GB shared_buffers可设为128MB-16MB）。配置示例如下：

  echo "wal_buffers = 16MB" >> /var/lib/pgsql/data/postgresql.conf
  

2. 操作系统内核参数优化

• 禁用内存超额使用：编辑/etc/sysctl.conf，添加vm.overcommit_memory = 2（禁止系统超额分配内存，防止PostgreSQL因内存不足崩溃），然后执行sysctl -p使配置生效。
• 调整内存换页阈值：降低vm.swappiness（默认60，建议设为10-20），减少系统将内存换页到磁盘的频率，提升内存访问效率。配置示例如下：

  echo "vm.swappiness = 10" >> /etc/sysctl.conf
  sysctl -p
  

3. 并行处理与WAL优化

• max_parallel_workers_per_gather：根据CPU核心数设置并行工作进程数（如4核CPU可设为2-4），提升查询并行处理能力。配置示例如下：

  echo "max_parallel_workers_per_gather = 2" >> /var/lib/pgsql/data/postgresql.conf
  

• checkpoint_completion_target：调整检查点完成时间比例（0-1之间，建议设为0.7-0.9），分散检查点写入负载，减少对性能的影响。配置示例如下：

  echo "checkpoint_completion_target = 0.7" >> /var/lib/pgsql/data/postgresql.conf
  

4. 监控与调优流程

• 监控内存使用：使用top、htop、free -m查看系统内存占用，通过PostgreSQL内置视图pg_stat_activity、pg_stat_bgwriter监控内存使用情况（如work_mem是否溢出、shared_buffers命中率）。
• 逐步调整参数：每次调整1-2个参数，重启服务后观察性能变化（如查询延迟、吞吐量），避免一次性调整过多导致系统不稳定。
• 结合工作负载优化：根据查询类型（如OLTP、OLAP）调整参数——OLTP场景侧重work_mem、max_parallel_workers_per_gather；OLAP场景侧重shared_buffers、effective_cache_size。