精通 DevOps 工具与最佳实践 - DevOps 知识中心 精通 DevOps 工具与最佳实践 - DevOps 知识中心

使用 PgBouncer 配置 PostgreSQL 连接池以应对高流量应用

学习如何为 PostgreSQL 配置 PgBouncer 连接池，以处理数千个并发连接，减少资源开销，并显著提高应用程序性能。

使用PgBouncer配置PostgreSQL连接池以应对高流量应用

当PostgreSQL数据库面临高连接数时，性能会迅速下降。每个客户端连接都对应一个PostgreSQL后端进程，因此繁忙的Web应用可能会消耗大量内存和CPU来维持会话。PgBouncer连接池通过让多个客户端连接复用少量服务器连接来缓解这一压力。

为什么连接池很重要

连接问题

  • 资源开销：每个PostgreSQL连接都有后端进程和内存开销。
  • 连接限制：max_connections是有限的，设置过高反而可能导致数据库不稳定。
  • 启动成本：创建新的数据库连接会增加延迟。
  • 上下文切换：过多的活跃后端进程会浪费CPU。

PgBouncer的优势

  • 让多个应用客户端共享更少的PostgreSQL服务器连接。
  • 当连接池繁忙时，将客户端排队，而不是让数据库不堪重负。
  • 支持会话、事务和语句三种池化模式。
  • 提供操作命令，如SHOW POOLS、SHOW CLIENTS和RELOAD。

安装与基本设置

安装PgBouncer

在Ubuntu/Debian上：

sudo apt update
sudo apt install pgbouncer

在CentOS/RHEL上：

sudo yum install pgbouncer

在macOS上：

brew install pgbouncer

常见文件位置

/etc/pgbouncer/
  ├── pgbouncer.ini        # 主配置文件
  └── userlist.txt         # 认证凭据

配置文件设置

基本的pgbouncer.ini配置

[databases]
; database_name = host=hostname port=5432 dbname=actual_db
myapp = host=localhost port=5432 dbname=production_db

[pgbouncer]
; 连接池模式
pool_mode = transaction

; 最大连接数
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3

; 网络设置
listen_addr = 0.0.0.0
listen_port = 6432

; 认证
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

; 日志
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

; 性能
max_prepared_statements = 0

不同发行版的默认包配置可能不同，安装后请检查服务文件和配置路径。在许多Linux发行版中，/etc/pgbouncer/pgbouncer.ini是主配置文件。

理解池化模式

1. 会话池化（pool_mode = session）

  • 行为：连接在整个会话期间分配给客户端
  • 适用场景：使用临时表、预处理语句的应用
  • 效率：低（1:1连接比例）
pool_mode = session

2. 事务池化（pool_mode = transaction）——推荐

  • 行为：每个事务结束后连接返回池中
  • 适用场景：大多数具有短事务的Web应用
  • 效率：高，前提是应用不依赖会话状态
pool_mode = transaction
default_pool_size = 25
max_client_conn = 1000

事务池化是请求/响应式Web应用的强力默认选择，但可能会破坏与会话级别功能相关的假设。请注意临时表、会话变量、事务外持有的咨询锁、LISTEN/NOTIFY以及驱动级别的预处理语句，除非你已经用你的PgBouncer版本和设置测试过它们。

3. 语句池化（pool_mode = statement）

  • 行为：每个语句执行后连接返回池中
  • 适用场景：无事务的简单只读查询
  • 效率：最高（但限制最多）
pool_mode = statement
; 谨慎使用——会破坏多语句事务

认证设置

创建userlist.txt

PgBouncer需要一个单独的认证文件。生成MD5哈希并添加到userlist.txt。

示例userlist.txt：

"app_user" "md5d8578edf8458ce06fbc5bb76a58c5ca4"
"readonly_user" "md5a3c7f5e89d24e7c8b1f9d2e4a6c8b0d2"

对于PostgreSQL风格的MD5密码，其值为md5加上密码+用户名的MD5哈希。不要将假哈希用于生产环境；请根据真实的用户名和密码生成条目，或使用环境中支持的更安全的认证方法。

使用PostgreSQL auth_query（高级）

当用户在auth_file中找不到时，PgBouncer可以查询PostgreSQL获取用户凭据，但这需要一个PgBouncer可以登录的auth_user。一个最小示例如下：

auth_type = md5
auth_user = pgbouncer_auth
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1

限制认证用户的权限，并遵循你的PostgreSQL版本的指导。许多团队使用SECURITY DEFINER函数，而不是直接授予访问目录密码数据的权限。

高流量的最佳配置

连接池大小规划

没有通用的连接池大小公式。从数据库能良好运行的活跃查询数量出发，选择一个保守的初始值，然后根据实际指标进行调整。

对于典型的Web应用，你可以从以下设置开始并调整：

default_pool_size = 25
reserve_pool_size = 5
max_client_conn = 1000

监控PostgreSQL的CPU、查询延迟、锁等待以及PgBouncer的cl_waiting计数。如果客户端在数据库空闲时等待，则池可能太小。如果数据库已饱和，增加池大小可能会使情况更糟。

完整的生产配置

[databases]
production = host=db.example.com port=5432 dbname=prod_db pool_size=30
analytics = host=db-replica.example.com port=5432 dbname=prod_db pool_size=15

[pgbouncer]
pool_mode = transaction

; 连接限制
max_client_conn = 2000
default_pool_size = 25
min_pool_size = 10
reserve_pool_size = 8
reserve_pool_timeout = 3
server_lifetime = 3600
server_idle_timeout = 600

; 网络设置
listen_addr = 0.0.0.0
listen_port = 6432
so_reuseport = 1
pkt_buf = 8192

; 安全
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
ignore_startup_parameters = extra_float_digits,options

; 日志
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
stats_period = 60

; 性能
max_prepared_statements = 0
query_timeout = 30
query_wait_timeout = 120

max_prepared_statements = 0禁用了PgBouncer的预处理语句跟踪。较新的PgBouncer版本在将此值设置为大于0时，可以在事务池化中支持协议级别的预处理语句，但在启用前应测试你的驱动和工作负载。

应用连接字符串

使用PgBouncer前

# 直接连接PostgreSQL
DATABASE_URL = "postgresql://user:[email protected]:5432/mydb"

使用PgBouncer后

# 通过PgBouncer连接
DATABASE_URL = "postgresql://user:[email protected]:6432/mydb"

监控与管理

管理控制台命令

连接到PgBouncer管理控制台：

psql -h localhost -p 6432 -U pgbouncer pgbouncer

基本命令：

-- 显示连接池统计信息
SHOW POOLS;

-- 显示活跃连接
SHOW CLIENTS;
SHOW SERVERS;

-- 显示配置
SHOW CONFIG;

-- 重新加载配置
RELOAD;

常见问题排查

问题1："no more connections allowed"

这可能意味着PgBouncer拒绝客户端连接，或PostgreSQL拒绝服务器连接。检查错误出现的位置。

可能的PgBouncer端更改：

max_client_conn = 5000
default_pool_size = 50

在同时增加两者之前，确认你的操作系统文件描述符限制和PostgreSQL的max_connections能够支持新的总数。PgBouncer本身也需要足够的文件描述符来处理客户端和服务器套接字。

问题2：高cl_waiting计数

解决方案：

  1. 增加连接池大小
  2. 优化慢查询
  3. 添加备用连接池

问题3：预处理语句错误

如果你的应用或驱动使用预处理语句，并且你处于事务池化模式，当后续语句落在不同的服务器连接上时，可能会看到错误。选项包括：

  1. 禁用驱动端的预处理语句。
  2. 对该工作负载使用会话池化。
  3. 在较新的PgBouncer版本上，使用正数的max_prepared_statements值测试PgBouncer的预处理语句支持。

保守设置：

max_prepared_statements = 0

实际部署示例

假设你的应用服务器可以打开数百个并发HTTP请求，但数据库在几十个活跃查询时表现最佳。将应用指向端口6432上的PgBouncer，将max_client_conn设置得足够高以应对客户端突发流量，并将default_pool_size保持在你希望该数据库/用户对实际活跃的数据库连接数附近。

然后通过以下命令验证：

SHOW POOLS;
SHOW STATS;

如果在正常流量下cl_waiting仍大于零，在简单增加池大小之前，先调查慢查询。PgBouncer保护PostgreSQL免受连接风暴的影响，但它不会让慢SQL变得廉价。

实用要点

从无状态Web工作负载的事务池化开始，有意保持池大小较小，并根据PgBouncer和PostgreSQL的指标进行调整。如果你的应用依赖会话行为或预处理语句，在将PgBouncer置于生产流量之前，请测试这些路径。