Pgbouncer 最佳实践：系列二

作者：王志斌，曾获得中国 PostgreSQL 数据库管理工程师（PGCE），是 PostgreSQL 官方认证讲师，盘古云课堂特邀金牌讲师。

连接池选择必须有测试数据作为支撑，才能更好来决定如何选择。通过下面的测试结果，能够更加直观的看到两者之间的差异（相关数据及测试结果来源自 Percona[3]）：

一般来说，PostgreSQL 通过将它的主要操作系统进程“分叉”到每个新连接的子进程中来实现连接处理。在操作系统级别上获得了 PostgreSQL 中每个连接的资源利用率的完整视图（以下输出来自 top 命令）：

表 1 直连内存占用情况

PID USER      PR NI VIRT RES  SHR S %CPU %MEM TIME+  COMMAND             

24379 postgres  20 0 346m 148m 122m R 61.7  7.4 0:46.36 postgres: sysbench sysbench ::1(40120)

24381 postgres  20 0 346m 143m 119m R 62.7  7.1 0:46.14 postgres: sysbench sysbench ::1(40124)

24380 postgres  20 0 338m 137m 121m R 57.7  6.8 0:46.04 postgres: sysbench sysbench ::1(40122)

24382 postgres  20 0 338m 129m 115m R 57.4  6.5 0:46.09 postgres: sysbench sysbench ::1(40126)

首先，在时间和内存方面，分叉一个操作系统进程要比为一个现有进程生成一个新线程要昂贵得多。随着时间的推移，考虑变得越来越重要。这可能是为什么在基于 PostgreSQL 的应用程序的扩展生命周期中早期就需要连接池机制的原因之一。

为了说明连接池可能对 PostgreSQL 服务器的性能产生的影响，利用在 sysbench-tpcc 上对 PostgreSQL 进行的测试，并通过使用 PgBouncer 作为连接池来部分重复了这些测试。

当第一次运行测试时，目标是针对 PostgreSQL 的 sysbench-tpcc 工作负载优化 PostgreSQL，该工作负载运行 56 个并发客户端（线程），并且服务器具有相同数量的可用 CPU，运行时间定为 30 分钟。这次的目标是更改并发客户端的数量（56、150、300 和 600），以查看服务器如何应对连接的扩展。

使用事务池进行测试，因为 sysbench-tpcc 的工作量由几个短语句和单语句事务组成。下表为完整使用的配置文件，命名为 pgbouncer.ini：

表 2 pgbouncer.ini 文件

[databases]

sbtest = host=127.0.0.1 port=5432 dbname=sbtest

[pgbouncer]

listen_port = 6543

listen_addr = 127.0.0.1

auth_type = md5

auth_file = userslist.txt

logfile = pgbouncer.log

pidfile = pgbouncer.pid

admin_users = postgres

pool_mode = transaction

default_pool_size=56

max_client_conn=600

除了 pool_mode 以外，其他最重要的变量是：

• default_pool_size：每个用户/数据库对允许多少个服务器连接。

• max_client_conn：允许的最大客户端连接数

userslist.txt 通过指定文件 AUTH_FILE 只包含用于连接到 PostgreSQL 的用户和口令的信息;该文件中的密码可以是纯文本密码，也可以是使用 MD5 或 SCRAM 加密的密码，具体取决于要使用的身份验证方法。

定义用户的另一种方法是让 PgBouncer 在需要时直接查询 PostgreSQL 后端。这是通过配置参数设置的 auth_user，可以在全局或每个数据库中设置。设置此选项后，PgBouncer 使用该用户连接到 PostgreSQL 后端，并运行该设置定义的查询 auth_query 以查找用户和密码。如果 auth_user 本身需要用于该连接的密码，则需要在 user.txt 中进行设置。关于相关细节请参见Pgbouncer官网。

使用以下命令将 PgBouncer 作为守护程序启动：

$pgbouncer -d pgbouncer.ini

除了仅运行基准测试 30 分钟并每次更改并发线程数之外，线程数=56。下面的示例来自第一次运行：

$ ./tpcc.lua --pgsql-user=postgres --pgsql-db=sbtest --time=1800 --threads=56 --report-interval=1 --tables=10 --scale=100 --use_fk=0  --trx_level=RC --pgsql-password=****** --db-driver=pgsql run > /var/lib/postgresql/Nando/56t.txt

对于使用连接池的测试，调整连接选项，以便与 PgBouncer 而不是 PostgreSQL 直接连接。请注意，它仍然是本地连接：

./tpcc.lua --pgsql-user=postgres --pgsql-db=sbtest --time=1800 --threads=56 --report-interval=1 --tables=10 --scale=100 --use_fk=0  --trx_level=RC --pgsql-password=****** --pgsql-port=6543 --db-driver=pgsql run > /var/lib/postgresql/Nando/P056t.txt

每次执行 sysbench-tpcc 之后，使用以下命令清除操作系统缓存：

$ sudo sh -c 'echo 3 >/proc/sys/vm/drop_caches'

在 default_pool_size=56 的情况下，结果如下：

图3 连接池相同测试结果

sysbench-tpcc 的 TPS：比较与 PostgreSQL 的直接连接和将 PgBouncer 作为连接池

在只有 56 个并发客户端的情况下运行 sysbench-tpcc 时，使用到 PostgreSQL 的直接连接可以提供比使用 PgBouncer 时高 2.5 倍的吞吐量（TPS 表示每秒事务）。在这种情况下，使用连接池会极大地影响性能。在如此小的规模下，连接池没有任何收益，只有开销。

但是，当使用 150 个并发客户端运行基准测试时，我们开始看到使用连接池的好处。显然测试 TPS 值明显高于直连方式。

即使并发客户端数量增加一倍然后四倍，PgBouncer 仍可以保持这样的吞吐量，在这种情况下，所发生的是没有立即充满大量请求到服务器，而是全部停止在 PgBouncer 外面。一旦释放了其池中的一个连接，PgBouncer 仅允许下一个请求继续进行到 PostgreSQL。

该策略对于 sysbench-tpcc 似乎非常有效。对于其他工作负载，平衡点可能位于其他地方。

对于上述测试，在 PgBouncer 上将 default_pool_size 设置为等于此服务器上可用的 CPU 内核数（56）。为了探索此参数的调整，我使用较大的连接池（150、300、600）和较小的连接池（14）重复了这些测试。结果如下：

图4 连接池不同测试结果

PgBouncer 的使用如何影响 sysbench-tpcc 的吞吐量：首先比较不同池大小的使用

使用较小的连接池（14），其大小仅为可用 CPU 数量的 1/4，仍然产生几乎相同的结果。说明充分利用 PgBouncer 进行连接处理已经有开始有效果。

将连接池池中的连接数加倍并没有任何实际的区别。但是一旦将该数字推断为 600，此时并发线程数大于可用 CPU 数，吞吐量就变得与不使用连接池时的吞吐量相当。即使运行的并发线程数与池中可用的连接数（600）相同，也是这样。可以预料的是在 PostgreSQL 有一个实际的限制。

首先，将连接池大小设置为等于服务器中可用 CPU 的数量，似乎是个好主意。大约有 150 个左右的连接池可能有一个硬限制。下表是针对不同视图总结了获得的结果的表格：

图5 测试汇总结果

从上述的测试过程可以了解到，使用连接池可以充分提高数据库的处理效率。

了解更多 PostgreSQL 热点资讯、新闻动态、精彩活动，请访问中国PostgreSQL官方网站

解决更多 PostgreSQL 相关知识、技术、工作问题，请访问中国PostgreSQL官方问答社区

下载更多 PostgreSQL 相关资料、工具、插件问题，请访问中国PostgreSQL官方下载网站