长时查询导致PostgreSQL StatefulSet主Pod CPU占满问题排查

环境信息 #

• 后端代码基于 TypeScript
• ORM框架为 Sequelize
• Kubernetes集群：K3S v1.23.14+k3s1 (c62b03fb)，Go版本go1.17.13

PostgreSQL StatefulSet配置 #

volume:
  size: 2500Gi
numberOfInstances: 2
postgresql:
  parameters:
    effective_cache_size: "105GB"
    effective_io_concurrency: "200"
    listen_addresses: '*'
    log_destination: "stderr"
    logging_collector: "false"
    log_line_prefix: '%t [%p]: [%l-1] [trx_id=%x] user=%u,db=%d'
    log_min_error_statement: "DEBUG1"
    log_error_verbosity: "verbose"
    maintenance_work_mem: "2GB"
    max_connections: "4000"
    max_wal_size: "16GB"
    min_wal_size: "4GB"
    max_worker_processes: "18"
    max_parallel_workers_per_gather: "9"
    max_parallel_workers: "18"
    max_parallel_maintenance_workers: "4"
    random_page_cost: "1.1"
    shared_bufIfers: "35GB"
    shared_preload_libraries: "pg_stat_statements"
    synchronous_commit: "false"
    syslog_facility: "LOCAL0"
    syslog_ident: "postgres"
    syslog_sequence_numbers: "true"
    syslog_split_messages: "true"
    wal_buffers: "16MB"
    work_mem: "300kB"
    checkpoint_completion_target: "0.9"
resources:
  limits:
    cpu: 20000m
    memory: 140Gi
  requests:
    cpu: 20000m
    memory: 140Gi

压测后查询长时运行语句 #

执行的PSQL查询语句：

SELECT
  pid,
  user,
  pg_stat_activity.query_start,
  now() - pg_stat_activity.query_start AS "QueryRunningTime",
  query,
  state,
  wait_event_type,
  wait_event
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
ORDER BY "QueryRunningTime" DESC;

查询到的长时运行语句示例：

pid              | 5537
user             | postgres
query_start      | 2023-01-02 13:15:45.801772+00
QueryRunningTime | 00:38:24.34075
query            | INSERT INTO "employeesDetails" ("deviceId","employeeId","depId","version","createdAt","updatedAt") VALUES (............... ,'2023-01-02 13:15:45.824 +00:00','2023-01-02 13:15:45.824 +00:00') ON CONFLICT DO NOTHING RETURNING "deviceId","employeeId","depId","version","createdAt","updatedAt";
state            | idle
wait_event_type  | Client
wait_event       | ClientRead

Sequelize连接池配置 #

sequelize:
  pool:
    max: 30
    min: 10
    evict: 1000
    idle: 5000
    acquire: 10000

核心疑问 #

1. 为何会出现这种长时idle的连接？
2. 即使配置了Sequelize连接池参数，为何仍会出现这类问题？
3. 该问题导致PostgreSQL运行3-6小时后CPU占用达上限，如何解决？

1. 长时idle连接的本质原因 #

状态idle+wait_event=ClientRead说明PostgreSQL已经执行完这条INSERT语句，但客户端（Sequelize/Node.js）没有主动关闭连接，也没有发送新的查询请求，导致连接一直处于空闲挂起状态。

关键诱因 #

• Node.js异步特性+Sequelize连接释放逻辑问题：Node.js是单线程异步模型，如果代码中存在未正确处理的Promise回调、未捕获的异常，或者没有确保连接在查询完成后被正确释放回连接池，就会导致连接被长期占用。
• ON CONFLICT DO NOTHING RETURNING的隐性问题：当INSERT触发冲突时，DO NOTHING会让语句不返回任何行，但RETURNING子句存在可能导致Sequelize的回调逻辑等待结果，若此时出现异步处理阻塞，连接就会被挂起。
• 连接池参数不匹配实际负载：
  • idle:5000（5秒）看似短，但如果客户端侧存在请求堆积或事件循环阻塞，连接无法及时被回收；
  • max:30每个Pod的连接池上限，但如果后端Pod数量多，加上PostgreSQL的max_connections:4000看起来大，但长期堆积的idle连接会占用PostgreSQL的进程资源，导致CPU被大量空闲连接的维护开销耗尽。

2. CPU占用达上限的关联逻辑 #

PostgreSQL每个连接对应一个独立的进程，大量长期idle的连接会持续占用进程资源，加上PostgreSQL需要维护这些连接的状态、监听客户端请求，随着时间推移，进程调度开销不断增加，最终导致CPU跑满。

3. 具体解决方案 #

（1）修复Sequelize连接释放逻辑 #

• 确保所有数据库操作都用try/catch/finally包裹，在finally块中显式释放连接（若手动持有连接），依赖Sequelize的自动连接管理逻辑回收连接：

  try {
    await EmployeeDetails.create(data, { conflict: 'do nothing' });
  } catch (err) {
    // 处理异常
  } finally {
    // 若手动获取了连接，需手动释放
    // await connection.release();
  }
  

• 检查是否存在未处理的Promise拒绝，这类情况会导致异步流程中断，连接无法被回收。

（2）调整PostgreSQL和连接池参数 #

• PostgreSQL侧：
  • 设置idle_in_transaction_session_timeout: 30000（30秒），强制关闭长时间处于idle状态的事务连接；
  • 降低max_connections到合理值（比如根据后端Pod数×连接池max计算，如10个Pod×30=300，设置为400即可），避免过多空闲连接占用资源。
• Sequelize连接池侧：
  • 调整evict: 3000（3秒），缩短空闲连接的回收周期；
  • 增加validate: true，在获取连接时验证连接有效性，避免复用已失效的连接；
  • 若数据库存在偶尔延迟，可适当调整acquire时间，但核心还是修复连接释放逻辑。

（3）优化INSERT语句 #

• 去掉不必要的RETURNING子句：因为ON CONFLICT DO NOTHING不会返回任何行，保留RETURNING只会让Sequelize等待不存在的结果，延长连接持有时间。修改为：

  INSERT INTO "employeesDetails" ("deviceId","employeeId","depId","version","createdAt","updatedAt") VALUES (...) ON CONFLICT DO NOTHING;
  

• 考虑使用批量插入减少连接占用：将多条INSERT合并为单条批量插入，降低连接的使用频率。

（4）监控与排查 #

• 启用PostgreSQL的pg_stat_activity定时监控，跟踪idle连接的来源和数量；
• 使用Node.js内置工具或第三方工具排查事件循环阻塞问题，定位导致连接无法释放的代码路径。

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