数据库微观案例第45期：pgbouncer隐藏的性能参数

原创彭冲 2024-07-16

316

本案例来自于罗海雄老师在墨天轮平台的文章：<<PG连接池PGBouncer里一个参数，让压测性能提高N倍>>

https://www.modb.pro/db/1807951726257999872

推荐理由(罗老师原话)：很奇怪的是，官方文档竟然没有把非常重要的这个多进程能力作为单独章节或者目录项来描述。国内技术文章也没有单独作为一个文章来描述。这也是我为什么专门写这么一篇文章的重要原因。

0.问题背景

使用pgbouncer作为连接池进行pgbench压测，tps指标基本都在1万左右，对比PG直连的效果，最高衰减达到80%。这显然不应该是一个“轻量级”的连接池该有的表现，那么问题可能在什么地方呢？

1.测试结论

使用pgbouncer时，一定要注意单进程的吞吐量瓶颈，使用so_reuseport=1这个参数允许多个pgbouncer进程监听同一个端口，以达到最佳效果。

在使用多个pgbouncer进程时，注意pool_size需要适当降低。可通过实际压测选择合适的pgbouncer实例数和pool_size值。

2.so_reuseport参数介绍

官方文档链接：http://www.pgbouncer.org/config.html

so_reuseport
Specifies whether to set the socket option SO_REUSEPORT on TCP listening sockets. On some operating systems, this allows running multiple PgBouncer instances on the same host listening on the same port and having the kernel distribute the connections automatically. This option is a way to get PgBouncer to use more CPU cores. (PgBouncer is single-threaded and uses one CPU core per instance.)

so_reuseport的作用是让pgbouncer启动并监听网络端口时增加Socket选项SO_REUSEPORT ，这个选项允许多个进程在同一个端口上进行监听。

文档描述可以手动起多个pgbouncer进程，监听同一端口，但要设置so_reuseport=1且需要设置不同的unix_socket_dir、pidfile和logfile参数。

3.使用脚本创建多进程pgbouncer

考虑使用8个或16个单独的pgbouncer实例来作为连接池。手动配置和启动太麻烦，下面用脚本来批量创建。

第一步：创建一个通用的pgboucerbase.ini文件，包含一些通用的配置但不包含unix_socket_dir、pidfile和logfile。

[databases]
 pgbs5000 = host=/tmp port=5432 dbname=pgbs5000 pool_size=16

[pgbouncer]
 pool_mode=transaction
 default_pool_size=16
 listen_port = 6432
 listen_addr = 192.168.88.101
 max_client_conn=100000
 max_db_connections=32
 auth_user=pg16
复制

注意把[pgbouncer]放在所有Section的最后面。另外，由于使用了多个进程，要考虑降低pool_size和max_db_connections的值，不然并发量可能会涨得太多。

第二步：在pgbouncer同一目录下创建pgb-mulit.sh脚本文件，接收两个参数，分别是启动的并发进程数量和通用参数文件名。

#!/bin/bash
function usage(){
echo "Usage: $0 <processCount> <baseIniFile>" 
}
if [ $# -lt 2 ] ; then
  usage
  exit
fi
if [ ! -f $2 ] ; then
   echo "File $2 does not exists"
   usage
   exit
fi

ProcessCnt=$1
BASEINI=$2
usage
for i in `seq -w 1 $ProcessCnt ` ; do
  SOCKDIR=/tmp/pgb_socket.${i}
  INIFILE=${BASEINI}.${i}
  mkdir -p $SOCKDIR
  cp $BASEINI $INIFILE
  cat >>  $INIFILE <<EOF
  unix_socket_dir=$SOCKDIR
  logfile = pgbouncer.log.${i}
  pidfile = pgbouncer.pid.${i}
  so_reuseport =1
EOF
  echo "Starting pgbouncer instance #i using ini file $INIFILE"
  ./pgbouncer -d $INIFILE
done
复制

脚本赋权：

chmod +x pgb-multi.sh
复制

第二步：执行脚本

./pgb-multi.sh 16 pgboucerbase.ini
复制

启动后，就有了16个独立的pgbouncer进程提供连接池服务了。

4.多进程pgbouncer压测

压测脚本如下：

HOST=192.168.88.101 # 服务器IP
PGPORT=6432 # 端口号,注意这里改成PGBoucer的端口号
DBNAME=pgbs5000 #数据库名称
DBUSER=pgbench #登录用户
export PGPASSWORD=Test@123 #密码，作为环境变量

RUNSEC=60 # 运行时间
THREADS="16 32 64 128 192 256 384 512 768 1000 1500 2000 2500 3000 4000 5000 6000 7000 8000 9000 10000 11000 12000"
for threadCnt in $THREADS ; do #逐个并发
    tps=`pgbench -r -T$RUNSEC $DBNAME -U$DBUSER -h $HOST -p $PGPORT -c$threadCnt -j$threadCnt  2>&1 |awk '/tps/{print $3}'`
    echo "concurrent: $threadCnt ,PGBouncer tps: ${tps:-0}" 
done
复制

压测过程中观察每个pgbouncer的CPU占用率均低于50%，不再是瓶颈。

多进程pgbouncer对比直连PG的tps图如下：

上面的结果图，可以发现，在高并发下，pgbouncer并没有达到低并发的效果。原因是，启动16个进程的时候，当时每个进程设了16的pool_size,也就是总共用256个实际连接。根据PG的结果，最高值是出现在128个并发。因此，尝试降低为每个进程对应8的pool_size, 这样就正好16*8=128。（当然，也可以考虑8个pgbouncer进程，每个进程16个pool_size, 效果应该类似）

修改pgboucerbase.ini文件，设置pool_size=8重新测试：