Ubuntu虚拟机中Rails应用关联PostgreSQL CPU使用率过高求助

• 第一步：定位高消耗慢查询
  修改postgresql.conf配置开启慢查询日志：

  log_min_duration_statement = 1000 # 记录执行时长超过1000ms的查询，可根据需求调整阈值
  log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a ' # 记录查询关联的用户、数据库、应用名
  

  执行pg_ctl reload加载配置后，查看pg_log目录下的日志文件，筛选出执行频率高、扫描行数多的SQL，重点排查Rails应用常见的N+1查询、无索引全表扫描、多表关联无关联条件的查询。
• 第二步：校验索引合理性
  执行以下SQL查看索引使用率：

  SELECT schemaname, relname, indexrelname, idx_scan FROM pg_stat_user_indexes WHERE relname = '你的表名';
  

  清理长期无扫描记录的冗余索引，同时给慢查询中涉及的WHERE、JOIN、ORDER BY、GROUP BY字段添加对应索引；如果查询使用前后模糊匹配LIKE '%xxx%'，需安装pg_trgm扩展创建GIN/GIST索引。
• 第三步：调整数据库配置适配硬件
  你当前硬件为4核CPU、33MB L3缓存，重点调整以下核心参数：
  • shared_buffers：设置为物理内存的1/4，32位架构下不要超过2GB，避免寻址溢出
  • work_mem：单查询排序/哈希操作内存，4核并发场景建议设置为4MB~16MB，避免过低触发磁盘排序消耗CPU
  • effective_cache_size：设置为物理内存的1/2~3/4，给查询优化器提供正确的内存参考
    特别注意：你当前系统架构为i686（32位），32位PostgreSQL最大寻址上限为4GB，无法充分利用大内存，存在额外的性能开销，优先更换为64位操作系统和64位PostgreSQL版本可获得明显性能提升
• 第四步：排查应用周期性任务
  你描述CPU峰值为周期性出现，优先排查Rails应用的定时任务（如Sidekiq Cron、系统Cron），是否存在固定间隔执行的批量数据统计、全表遍历类任务，可将这类任务调整到业务低峰期执行，或拆分批量操作为小批次执行降低瞬时CPU消耗。
• 第五步：连接与系统资源校验
  • 检查应用数据库连接池配置，如果存在大量短连接频繁创建销毁，可部署pgBouncer做连接池复用连接，降低连接建立的CPU开销
  • 执行iostat -x 1查看磁盘IO使用率，如果磁盘util%持续高于80%，说明IO瓶颈导致数据频繁换入换出，会间接拉高CPU使用率，需优化慢查询或升级存储
• 第六步：进程级定位
  执行pidstat -u 1 -p <高CPU Postgres进程ID>查看CPU消耗分布，如果是用户态CPU占比高，说明是查询运算/逻辑消耗导致，优先优化慢查询；如果是内核态CPU占比高，说明是系统调用/IO等待导致，优先排查磁盘、内存资源瓶颈。

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