Cloud SQL PostgreSQL查询超时，如何释放内存并优化配置？

一、现有配置的问题分析 #

• shared_buffers：当前13.5GB（默认值）偏高，对于40GB内存的实例，PG通常推荐设置为物理内存的25%（即10GB左右）。过高的shared_buffers会挤占操作系统页缓存、后台进程及查询工作内存的空间，反而降低整体内存利用率。
• work_mem：400MB虽在推荐的1%-5%区间，但需注意work_mem是单操作的内存配额（如排序、哈希操作），若夜间任务存在多并发查询，总内存占用会是work_mem × 并发数，极易击穿内存上限。
• maintenance_work_mem：4GB（10%内存）符合推荐区间，但如果夜间任务同时包含业务查询与维护操作（如VACUUM、建索引），过大的配额会加剧内存竞争。

二、不扩容下的配置优化建议 #

1. 调整shared_buffers #

将shared_buffers降低至10GB（物理内存的25%），释放的内存可分配给操作系统页缓存和查询工作内存，更符合PG的内存架构设计。

2. 精细化管控work_mem #

• 避免全局设置过高work_mem：将全局默认值调低至200MB，仅对需要大内存的单个夜间任务，在执行前临时设置会话级配额：

  SET work_mem = '800MB';
  -- 执行目标任务
  SET work_mem = DEFAULT;
  

• 开启监控定位内存消耗点：启用track_activity_query_size和log_temp_files，记录触发临时文件的查询（说明当前work_mem不足以支撑操作），针对性优化SQL（如添加索引、改写关联逻辑），从根源减少内存需求。

3. 限制并发与并行度 #

• 管控夜间任务并发数：通过max_connections全局限制总连接数，或使用PgBouncer连接池精准控制并发查询数量，避免多查询同时占用work_mem导致内存溢出。
• 限制单查询并行度：调整max_parallel_workers_per_gather（PG12默认4），避免单个查询占用过多CPU和内存资源。

4. 错峰配置maintenance_work_mem #

• 若夜间任务包含维护操作，将维护任务与业务查询错峰执行；或临时调低maintenance_work_mem至2GB，减少内存竞争。
• 单独配置autovacuum_work_mem，限制自动清理进程的内存占用，避免autovacuum抢占业务内存。

5. 辅助配置优化 #

• 设置effective_cache_size为28GB左右（物理内存的70%），帮助查询优化器生成更高效的执行计划，减少不必要的大内存操作。
• 若Cloud SQL支持，启用memory_limit限制PG进程总内存占用，避免触发系统OOM。

三、无需重启实例释放内存的方法 #

1. 终止高内存查询
   执行以下SQL定位内存占用高的活跃查询：

   SELECT pid, query, pg_size_pretty(pg_total_relation_size(relid)) AS table_size,
          pg_stat_get_memory_usage(pid) AS memory_usage
   FROM pg_stat_activity
   WHERE state = 'active' AND query NOT LIKE '%pg_stat_activity%';
   

   找到目标进程后执行终止命令：

   SELECT pg_terminate_backend(目标pid);
   

2. 手动清理表内存
   对大表执行VACUUM FREEZE VERBOSE table_name;，释放表的无效缓存与内存空间（建议在低峰期执行，避免影响业务）。

3. 清理共享缓存
   执行SELECT pg_stat_reset();重置统计信息，或通过SELECT pg_prewarm('核心业务表', 'buffer');预热高频访问表的缓存，挤出无用缓存数据（注意：该操作可能短期影响查询性能）。

4. 动态调整会话级work_mem
   对正在运行但未挂起的大内存查询，在会话中调低work_mem，让查询改用临时文件释放内存：

   SET work_mem = '200MB';
   

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