Pgbouncer最佳实践：系列二
PostgreSQLChina 发布于 3 月 2 日
作者：王志斌，曾获得中国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的情况下，结果如下：
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）重复了这些测试。结果如下：
PgBouncer的使用如何影响sysbench-tpcc的吞吐量：首先比较不同池大小的使用
使用较小的连接池（14），其大小仅为可用CPU数量的1/4，仍然产生几乎相同的结果。说明充分利用PgBouncer进行连接处理已经有开始有效果。
将连接池池中的连接数加倍并没有任何实际的区别。但是一旦将该数字推断为600，此时并发线程数大于可用CPU数，吞吐量就变得与不使用连接池时的吞吐量相当。即使运行的并发线程数与池中可用的连接数（600）相同，也是这样。可以预料的是在PostgreSQL有一个实际的限制。
首先，将连接池大小设置为等于服务器中可用CPU的数量，似乎是个好主意。大约有150个左右的连接池可能有一个硬限制。下表是针对不同视图总结了获得的结果的表格：
从上述的测试过程可以了解到，使用连接池可以充分提高数据库的处理效率。
了解更多PostgreSQL热点资讯、新闻动态、精彩活动，请访问中国PostgreSQL官方网站
解决更多PostgreSQL相关知识、技术、工作问题，请访问中国PostgreSQL官方问答社区
下载更多PostgreSQL相关资料、工具、插件问题，请访问中国PostgreSQL官方下载网站
PostgreSQLChina
PostgreSQLChina
宣传栏
目录
作者：王志斌，曾获得中国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的情况下，结果如下：
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）重复了这些测试。结果如下：
PgBouncer的使用如何影响sysbench-tpcc的吞吐量：首先比较不同池大小的使用
使用较小的连接池（14），其大小仅为可用CPU数量的1/4，仍然产生几乎相同的结果。说明充分利用PgBouncer进行连接处理已经有开始有效果。
将连接池池中的连接数加倍并没有任何实际的区别。但是一旦将该数字推断为600，此时并发线程数大于可用CPU数，吞吐量就变得与不使用连接池时的吞吐量相当。即使运行的并发线程数与池中可用的连接数（600）相同，也是这样。可以预料的是在PostgreSQL有一个实际的限制。
首先，将连接池大小设置为等于服务器中可用CPU的数量，似乎是个好主意。大约有150个左右的连接池可能有一个硬限制。下表是针对不同视图总结了获得的结果的表格：
从上述的测试过程可以了解到，使用连接池可以充分提高数据库的处理效率。
了解更多PostgreSQL热点资讯、新闻动态、精彩活动，请访问中国PostgreSQL官方网站
解决更多PostgreSQL相关知识、技术、工作问题，请访问中国PostgreSQL官方问答社区
下载更多PostgreSQL相关资料、工具、插件问题，请访问中国PostgreSQL官方下载网站
