PostgreSQL 9.6频繁触发FATAL: out of memory的参数调优方案咨询

嘿，针对你PostgreSQL 9.6频繁抛出FATAL: out of memory错误的情况——服务器明明有125GB物理内存，却只给shared_buffers分配了8GB——我整理了一套针对性的调优建议，帮你缓解内存压力：

1. 核心共享内存参数调优 #

这部分是PostgreSQL缓存数据的核心，你的当前配置远未利用到硬件潜力：

• shared_buffers：建议设置为物理内存的25%左右，也就是shared_buffers = 32GB。PostgreSQL 9.6完全支持大内存配置，但要先调整系统内核参数：执行sysctl -w kernel.shmmax=34359738368（对应32GB），并把这个配置写入/etc/sysctl.conf持久化，避免重启失效。
• wal_buffers：默认是shared_buffers的1/32，若你的写入负载较高，可手动设置为wal_buffers = 16MB，减少WAL日志的频繁刷盘操作，降低内存波动。

2. 会话级工作内存优化 #

单个会话的内存溢出是out of memory的常见原因，要结合并发数合理调整：

• work_mem：这个参数控制排序、哈希连接等操作的内存，默认4MB过小。建议先设置为work_mem = 64MB，但要注意：这是每个操作的内存配额——比如一个查询包含多个排序步骤，会多次占用该内存。所以要结合最大并发连接数计算（比如100个并发的话，64MB×100=6.4GB，要留足其他内存开销的空间）。
• maintenance_work_mem：用于VACUUM、CREATE INDEX等维护任务，默认64MB效率极低。建议调到maintenance_work_mem = 2GB，加快维护速度，但要注意：如果同时运行多个维护进程，要适当降低该值，避免内存过载。

3. 限制并发与并行查询资源 #

过多的并发连接或并行进程会让会话级内存累加超过物理内存：

• max_connections：不要盲目设置过大，OLTP场景建议设为max_connections = 200，同时搭配PgBouncer等连接池复用连接，减少PostgreSQL实际运行的进程数。
• max_parallel_workers_per_gather：PostgreSQL 9.6支持并行查询，默认4个工作进程。如果内存紧张，可降低到max_parallel_workers_per_gather = 2，避免并行进程抢占过多内存。

4. 辅助参数与执行计划优化 #

这些参数不会直接分配内存，但能帮优化器更合理地使用资源：

• effective_cache_size：告诉优化器系统可用的总缓存（包含shared_buffers和OS缓存），建议设为物理内存的70%，也就是effective_cache_size = 88GB。这会让优化器生成更高效的执行计划，减少内存密集型操作。
• temp_buffers：控制每个会话的临时表缓存，默认8MB。若你的查询频繁使用临时表，可调高到temp_buffers = 64MB，但不要过大（每个会话都会分配）。
• checkpoint_completion_target：设为checkpoint_completion_target = 0.9，让检查点操作平滑完成，避免短时间内的IO和内存波动。

5. 系统层面补充调优 #

• 配置合适的swap空间：建议设置16GB作为内存缓冲，但不要依赖swap（swap会严重拖慢性能）。
• 调整OOM Killer规则：在/etc/sysctl.conf中添加vm.overcommit_memory = 2和vm.overcommit_ratio = 90，让系统在内存使用超过90%时拒绝新的内存分配，而不是直接杀死PostgreSQL进程。

调优小贴士 #

每次只调整一个参数，观察系统内存使用和查询性能变化——一次性改多个参数会让你难以定位哪个配置起作用，甚至引发新问题。

内容的提问来源于stack exchange，提问作者varun kamal