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

介绍

当 PostgreSQL 数据库面临高连接量时，性能可能会迅速下降。每个数据库连接都会消耗系统资源，而 PostgreSQL 对并发连接有实际限制。PgBouncer 是一个轻量级的连接池工具，通过维护一个数据库连接池并高效地将连接分配给客户端应用程序来解决这个问题。

为什么连接池很重要

连接问题

资源开销：每个 PostgreSQL 后端进程消耗 5-10MB 内存
连接限制：默认 max_connections 通常为 100-200
启动成本：创建新连接需要 1-5ms
上下文切换：过多进程会导致 CPU 过载

PgBouncer 的优势

将数据库连接数减少 10-100 倍
使数千个客户端连接以最小开销运行
在高峰期提供连接排队
支持多种池化模式以适应不同用例

安装和基本设置

安装 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

理解池化模式

1. 会话池化（pool_mode = session）

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

pool_mode = session

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

行为：每个事务后连接返回池中
用例：大多数 Web 应用程序，短事务
效率：高（减少 10-100 倍）

pool_mode = transaction
default_pool_size = 25
max_client_conn = 1000

3. 语句池化（pool_mode = statement）

行为：每个语句后连接返回池中
用例：简单的只读查询，无事务
效率：最大（但非常限制）

pool_mode = statement
; 谨慎使用 - 会破坏多语句事务

认证设置

创建 userlist.txt

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

示例 userlist.txt：

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

使用 PostgreSQL auth_query（高级）

直接查询 PostgreSQL 进行认证：

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

高流量应用的最佳配置

连接池大小调整

池大小公式：

default_pool_size = (num_cores × 2) + effective_spindle_count

对于 4 核服务器与 SSD：

default_pool_size = 20
reserve_pool_size = 5
max_client_conn = 1000

完整生产配置

[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

应用程序连接字符串

使用 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"

解决方案：

max_client_conn = 5000
default_pool_size = 50

问题 2：高 cl_waiting 计数

解决方案：

增加池大小
优化慢查询
添加预留池

问题 3：预处理语句错误

解决方案：

max_prepared_statements = 0

性能影响示例

使用 PgBouncer 前

500 并发请求 → 500 PostgreSQL 连接
数据库负载：95% CPU，8GB 内存
响应时间：平均 250ms

使用 PgBouncer 后

500 并发请求 → 25 PostgreSQL 连接
数据库负载：35% CPU，1GB 内存
响应时间：平均 80ms
结果：速度提高 3 倍，资源使用减少 70%

结论

PgBouncer 是扩展 PostgreSQL 应用程序的必备工具。它将连接开销减少 90% 以上，支持 10-100 倍更多客户端，并显著提高响应时间。从事务池化模式开始，并根据监控结果进行调整。