我要评论引言
在 postgresql 的生产运维中，“连接数过多”是最常见且影响深远的性能问题之一。当数据库连接数接近或达到 max_connections 限制时，新连接请求将被拒绝，导致应用报错“too many connections”，服务不可用。即使未达上限，大量空闲连接也会消耗内存、文件描述符和 cpu 资源，降低整体吞吐能力。
本文将系统性地剖析 连接数过多的根本原因，详解 postgresql 连接机制与资源开销，并对比主流 连接池方案（pgbouncer、pgpool-ii、应用层池） 的原理、配置与适用场景，提供一套从诊断到治理的完整解决方案。
一、postgresql 连接机制与资源模型
1. 进程模型
postgresql 采用 “进程每连接”（process-per-connection） 模型：
- 每个客户端连接对应一个独立的后端进程（backend process）；
- 该进程负责处理该连接的所有 sql 请求，直至断开。
对比：mysql 默认使用线程模型（可配置为线程池），而 postgresql 坚持进程模型以保障稳定性与隔离性。
2. 连接资源开销
每个连接消耗的资源包括：
| 资源类型 | 默认大小 | 说明 |
|---|---|---|
| 内存 | 约 5–10 mb | 包括 work_mem、maintenance_work_mem、本地缓存等 |
| 文件描述符 | 1~3 个 | 用于 socket、日志等 |
| 进程上下文 | 内核开销 | 进程调度、内存管理等 |
假设 max_connections = 1000，仅连接本身即可消耗 5–10 gb 内存，还不包括查询执行时的额外内存（如排序、哈希）。
3. 关键参数：max_connections
- 定义数据库允许的最大并发连接数；
- 默认值通常为 100；
- 修改需重启 postgresql；
- 实际可用连接数 =
max_connections - superuser_reserved_connections（默认保留 3 个给超级用户）。
盲目调高 max_connections 是反模式——它掩盖问题而非解决问题，且极易引发 oom（out-of-memory）。
二、连接数过多的根本原因分析
1. 应用层连接泄漏（最常见）
- 应用代码未正确关闭数据库连接；
- 连接池配置不当（如未设置最大连接数、未启用超时回收）；
- 异常路径未释放连接（try-finally 缺失）。
典型表现：
- 连接数随时间持续增长，不随业务低峰下降；
pg_stat_activity中大量idle状态连接。
2. 高并发短连接风暴
- 应用未使用连接池，每次请求新建连接；
- http 服务每秒处理数千请求，每个请求建连+查+断开；
- 导致连接频繁创建/销毁，系统负载飙升。
典型表现：
- 连接数剧烈波动；
pg_stat_activity中大量active→idle快速切换；- 系统 cpu 消耗在进程 fork/exit 上。
3. 长事务或长查询阻塞
- 某些连接执行长时间运行的查询或事务；
- 连接被占用无法释放；
- 新请求不断堆积，连接数激增。
典型表现：
pg_stat_activity中存在state = 'active'且query_start很早的记录；wait_event显示锁等待或 i/o 等待。
4. 连接池配置不合理
- 连接池的最大连接数 > postgresql 的
max_connections； - 多个应用实例各自维护连接池，总和远超数据库承载能力。
典型表现：
- 多个应用同时报 “too many connections”；
- 数据库连接数稳定在
max_connections附近。
三、诊断：如何确认连接数问题？
1. 查看当前连接数
-- 总连接数（含后台进程） select count(*) from pg_stat_activity; -- 用户连接数（排除 autovacuum 等） select count(*) from pg_stat_activity where backend_type = 'client backend'; -- 按状态分类 select state, count(*) from pg_stat_activity where backend_type = 'client backend' group by state;
常见状态：
active：正在执行查询；idle：已执行完，等待新查询；idle in transaction：在事务中但无活动（危险！可能长事务）；idle in transaction (aborted)：事务出错但未结束。
2. 识别异常连接
（1）长时间空闲连接
select pid, usename, application_name, client_addr,
now() - state_change as idle_duration, query
from pg_stat_activity
where state = 'idle'
and backend_type = 'client backend'
and now() - state_change > interval '30 minutes'
order by idle_duration desc;
（2）长事务
select pid, usename, xact_start,
now() - xact_start as xact_duration, query
from pg_stat_activity
where xact_start is not null
and backend_type = 'client backend'
and now() - xact_start > interval '5 minutes'
order by xact_duration desc;
3. 监控连接趋势
- 使用 prometheus +
postgres_exporter采集pg_stat_activity指标； - grafana 面板展示连接数随时间变化；
- 设置告警：
pg_stat_activity_count > 0.8 * max_connections。
四、解决方案：连接池的核心价值
连接池通过 “连接复用” 解决上述问题：
- 应用向连接池请求连接，而非直接连数据库；
- 连接池维护一个固定大小的“后端连接池”；
- 应用使用完后归还连接，供其他请求复用；
- 有效解耦 应用并发数 与 数据库连接数。
例如：1000 个应用并发请求，可通过 50 个数据库连接处理。
五、主流连接池方案对比
| 特性 | pgbouncer | pgpool-ii | 应用层连接池（hikaricp, etc.） |
|---|---|---|---|
| 架构 | 独立中间件 | 独立中间件 | 嵌入应用进程 |
| 协议支持 | 仅连接池（不解析 sql） | 支持查询缓存、负载均衡 | 仅连接池 |
| 连接模式 | session / transaction / statement | session / transaction | 通常 session |
| 内存开销 | 极低（c 语言） | 中等 | 依赖 jvm/语言运行时 |
| 高可用 | 需配合 haproxy | 内置主从切换 | 无 |
| 适用场景 | 通用，尤其 oltp | 需要读写分离/缓存 | 单体应用、微服务 |
推荐组合：
- 微服务架构：应用层池（如 hikaricp） + pgbouncer
- 单体/传统架构：pgbouncer
六、pgbouncer 详解（最广泛使用的连接池）
1. 工作模式
- session 模式：连接绑定到客户端会话，直到断开；
- transaction 模式（推荐）：每个事务结束后立即归还连接；
- statement 模式：每条语句后归还（不支持多语句事务）。
transaction 模式可最大化连接复用率，适用于无状态应用。
2. 安装与配置
（1）安装（以 ubuntu 为例）
sudo apt-get install pgbouncer
（2）核心配置文件/etc/pgbouncer/pgbouncer.ini
[databases] mydb = host=localhost port=5432 dbname=prod [pgbouncer] listen_port = 6432 listen_addr = * auth_type = md5 auth_file = /etc/pgbouncer/userlist.txt logfile = /var/log/pgbouncer/pgbouncer.log pidfile = /var/log/pgbouncer/pgbouncer.pid ; 连接池大小（关键！） default_pool_size = 50 ; 每个用户-数据库对的最大后端连接数 max_db_connections = 100 ; 单个数据库的最大总连接数 max_user_connections = 100 ; 单个用户的最大总连接数 ; 超时设置 server_idle_timeout = 600 ; 后端连接空闲 10 分钟后关闭 server_lifetime = 3600 ; 后端连接存活 1 小时后重建
（3）用户认证文件/etc/pgbouncer/userlist.txt
"app_user" "md5加密密码"
密码可通过 pg_md5 工具生成。
3. 应用连接方式
应用不再连接 5432，而是连接 6432：
# python 示例
conn = psycopg2.connect(
host='localhost',
port=6432,
database='mydb',
user='app_user',
password='xxx'
)
4. 监控与管理
连接 pgbouncer 的虚拟数据库 pgbouncer：
-- 查看连接池状态 show pools; -- 输出：database, user, cl_active, cl_waiting, sv_active, sv_idle... -- 查看客户端连接 show clients; -- 查看后端连接 show servers;
关键指标：
cl_waiting：等待连接的客户端数（>0 表示池不足）；sv_idle：空闲的后端连接数。
七、应用层连接池配置建议（以 hikaricp 为例）
若使用 java + spring boot，hikaricp 是首选。
1. 核心配置
spring:
datasource:
hikari:
maximum-pool-size: 20 # 应用实例的最大连接数
minimum-idle: 5 # 最小空闲连接
idle-timeout: 600000 # 10 分钟空闲超时
max-lifetime: 1800000 # 连接最大存活 30 分钟
connection-timeout: 3000 # 获取连接超时 3 秒
2. 多实例部署下的总连接数控制
假设有 n 个应用实例，每个配置 maximum-pool-size = m，则总连接数 ≈ n × m。
必须满足：
n × m ≤ pgbouncer.max_db_connections ≤ postgresql.max_connections
示例：10 个实例 × 20 连接 = 200，需确保数据库
max_connections ≥ 210（含预留）。
八、高级优化与陷阱规避
1. 避免“连接池嵌套”
- 应用层池 + pgbouncer 是合理的；
- 但不要在 pgbouncer 后再接另一个连接池（如 pgpool-ii），会导致复杂性和性能损耗。
2. 正确处理事务
- 在 pgbouncer 的 transaction 模式下，禁止跨事务的会话级设置：
-- 错误：set 会在事务结束后丢失 begin; set local timezone = 'utc'; select ...; commit; -- 此时 set 生效，但下次事务无效 -- 更危险：跨多个 begin/commit set timezone = 'utc'; -- 在 transaction 模式下无效！ begin; select ...; commit; begin; select ...; commit; -- timezone 不是 utc
解决方案：使用 application_name 传递上下文，或改用 session 模式（牺牲复用率）。
3. 监控连接池健康度
- 应用层：监控
hikaripool-connection-acquired-nanoseconds等指标； - pgbouncer：监控
cl_waiting，若持续 >0，需扩容池大小； - 数据库：确保
pg_stat_activity中后端连接数稳定。
4. 自动扩缩容（kubernetes 场景）
- 使用 horizontal pod autoscaler (hpa) 基于
cl_waiting指标扩缩 pgbouncer； - 或基于应用的连接等待时间动态调整
maximum-pool-size。
九、连接数治理 sop（标准操作流程）
监控告警：
- 设置连接数阈值告警（>80% max_connections）；
- 监控
idle in transaction连接。
根因分析：
- 区分是连接泄漏、短连接风暴还是长事务；
- 使用
pg_stat_activity定位源头。
短期缓解：
- 终止异常连接：
select pg_terminate_backend(pid); - 临时增加
max_connections（仅应急）。
长期治理：
- 引入 pgbouncer 或应用层连接池；
- 修复代码中的连接泄漏；
- 优化长事务。
容量规划：
- 基于业务峰值 qps 和平均查询耗时，计算所需连接数：
所需连接数 ≈ (qps × 平均查询时间) / 并发系数
- 预留 20% 余量。
结语：连接数过多本质是 “资源错配” ——应用并发需求与数据库连接能力不匹配。解决之道不在盲目扩容，而在 引入连接池、规范应用行为、精细化监控。
pgbouncer 作为轻量、高效、稳定的连接池中间件，已成为 postgresql 生态的事实标准。结合应用层连接池，可构建弹性、可扩展的数据库访问架构。
记住：一个设计良好的连接池，胜过十倍的硬件升级。
以上就是postgresql连接数过多的原因分析与连接池方案的详细内容，更多关于postgresql连接数过多的资料请关注代码网其它相关文章！
发表评论