PGbouncer-轻量级PG连接池管理工具

导言：曾经听说过一句话，用 PostgreSQL 而不用连接池，绝对是坑爹的做法…… 哪怕是像 pgbouncer 这样的“轻量级”连接池，有和没有的区别都不是一般的大。

虽然这句话说得比较绝对，但从某种角度上来讲，这句话是有一定道理的。

• pgbouncer优势：
• pgbouncer介绍：
• pgbouncer连接池模式：
• pgbouncer安装配置方法：
• 性能体验：

pgbouncer优势：

• 能够有效提高连接的利用率，避免过多的无效连接，导致数据库消耗资源过大，CPU占用过高。
• 对客户端连接进行限制，预防过多或恶意的连接请求。

pgbouncer介绍：

PG 是多进程结构，每新增一个会话就会新增一个进程，相对而言对数据库的开销就会比较巨大。

因为在正常业务会话中，有不少session 都有长时间的 idle的状态,而这个状态导致,此时如果需要连接,就需要建立新的进程,来访问数据库,那么连接数就上来了. 而使用pgbouncer的主要原因

PGBrouncer能够缓存和PostgreSQL的连接，当有连接请求进来的时候，直接分配pgbouncer与postgresql之间的空闲连接去执行，而不需要PostgreSQL fork出新进程来建立连接，以节省创建新进程，创建连接的资源消耗。

关键的是pgbouncer是使用libevent进行socket通信，通信效率高。每个连接仅消耗2kB内存。相对pg自身动辄 4MB 的work_mem算是很轻量级了。

如果要用大白话来说,没有使用pgbouncer的连接方式是私家车,如果车子太多,则路就塞满了,而使用了pgbouncer 的方式则类似公交车或小巴, 有人上车有人下车,座位是固定的,所以公交车如果本身有30个座位,但实际上在整个的路途中可不是仅仅支持30个人,至于支持了多少人,那就看连接到数据库的事务执行的快慢,是否能对一个连接进行复用, 这就有点CPU 的分时使用的概念.

pgbouncer连接池模式：

pgbouncer支持三种连接池模式：

• session 这里是针对session来说的,当用户主动释放了连接，pgbouncer才会把其与数据库之间管理的连接进行释放复用。这样的设置本身和PG的连接逻辑的意义基本上一致.所以还不如不用，一般不使用这种模式。
• Transaction 最常用也是最合适的模式，一个连接的通道分时被 事务 所使用, 这样的好处比上面的session模式对比要明显的多,连接的使用率会跟随相关的分配有更高的复用,和性能方面的提高.当事务从客户端发出之后，pgbouncer会在与数据库之间的连接中找到一个连接是idle的去扔进去执行，完成事务之后，连接将重新被pgbouncer 交给给其他新事务去使用。
• SQL 类似于autocommit，粒度最细，一条SQL执行即释放连接，连接就会被其他会话所占用掉。虽然资源利用最极致，但是不符合一般业务场景。破坏了事务逻辑。

pgbouncer安装配置方法：

官网地址：PgBouncer - lightweight connection pooler for PostgreSQL

1、下载最新的release版本 ，1.160.0 版本。

• 准备资源，1台云服务器，1个云数据库PostgreSQL主实例，1个云数据库PostgreSQL只读实例。
• 要求云服务器和云数据库都在统一VPC内，要求能够通过CVM访问到数据库实例中。
• 注意：我这里测试使用的centos 7.2版本云服务器，数据库版本购买的是 PostgreSQL 11.8.

2、登录至云服务器中，并上传pgbouncer至云服务器。

# 安装一个pgbench，后续可以用以测试：

# yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# yum install -y postgresql11-server

# 创建pgbouncer的存放目录

# mkdir /opt/pgbouncer

# 进入上传的pgbouncer目录中，进行解压：

# tar -xf pgbouncer-1.16.0.tar.gz

# cd pgbouncer-1.16.0

#执行编译安装，并且指定安装路径为/opt/pgbouncer：

# ./configure --prefix=/opt/pgbouncer

# make

# make install

# 注意，在执行第一步的时候，会提示各种依赖库没有安装，此时仅仅需要安装提示的依赖库的devel包即可。如：libevent-devel，openssl-devel

# yum install -y libevent-devel openssl-devel

# 最后把整个文件夹权限都交给postgres用户来进行管理

# chown postgres:postgres /opt/pgbouncer -R

3、执行完以上语句之后，即可将pgbouncer 安装完成。此时就需要仅pgbrouncer 的配置了。

• 主执行文件为：/opt/pgbouncer/bin/pgbouncer
• 配置文件为：/opt/pgbouncer/share/doc/pgbouncer/pgbouncer.ini
• 用户白名单配置文件为：/opt/pgbouncer/share/doc/pgbouncer/userlist.txt

4、当然也可以将上面三个文件拷贝到一个目录中去进行管理，方便一些，相关命令介绍如下：

• -v 参数 为前台输出执行，可以打印相关日志到屏幕，但是关闭窗口后，程序会关闭掉，一般用于debug。 示例：/opt/pgbouncer/bin/pgbouncer -v /opt/pgbouncer/share/doc/pgbouncer/pgbouncer.ini
• -d 参数 为daemon执行，正常程序运行即可这样执行。 示例：/opt/pgbouncer/bin/pgbouncer -d /opt/pgbouncer/share/doc/pgbouncer/pgbouncer.ini
• -R 参数为 重启程序，当修改了配置文件之后，即可这样重启加载配置文件。 /opt/pgbouncer/bin/pgbouncer -R /opt/pgbouncer/share/doc/pgbouncer/pgbouncer.ini
• 其他的参数不怎么重要，可执行pgbouncer --help 来查看。

4、pgbouncer.ini文件如何配置，直接看下面的示例即可：

• [databases] 区块，主要配置数据库的访问连接串。
• [users]区块，主要配置用户级别的一些私有配置，如配置某一个用户的连接池模式
• [pgbouncer]区块，主要配置全局的pgbouncer模式。

[databases]

db_rw = host=172.27.32.11 port=5432 dbname=test

db_ro = host=172.27.32.12 port=5432 dbname=test

; 这里 最开始的xxx= 可以任意配置，主要是连接表示，易于记住即可。建议根据业务情况来进行配置。

; 注意应用通过此连接串连接到database中后，无法切换database。

; host, port, dbname 必须为数据库的访问连接信息，我这里将云数据库的主实例配置为db_rw，将此主实例的只读实例配置为db_ro，而我使用客户端访问数据库时候，访问db_ro即访问后面的库。

; 此处还可以配置用户名和密码，但是不建议配置在此，因为业务或者数据库有多个用户，如果配置了用户在此处，就只能使用此用户对数据库进行访问了。如果想要设置哪些用户可以访问，可以用userlist文件来进行限制。

[users]

; 示例：user1 = pool_mode=transaction max_user_connections=10

; 此处配置用户级别的连接池模式，如果针对某一用户有特殊配置可以在此配置，如无，可留空。

[pgbouncer]

listen_addr = 172.27.32.17

listen_port = 5432

; 服务监听地址和端口。

logfile = /opt/pgbouncer/pgbouncer.log

pidfile = /opt/pgbouncer/pgbouncer.pid

unix_socket_dir = /var/run/pgbouncer

unix_socket_mode = 0777

unix_socket_group =

auth_type = trust

auth_file = /opt/pgbouncer/share/doc/pgbouncer/userlist.txt

; 此处auth_type 可以配置md5，但是数据库侧的 pg_hba.conf文件也需要配置为md5，且userlist 配置文件就需要存md5 后的密码，不能配置为明文，如果设置为trust，则需要设置为明文。

pool_mode = transaction

; 连接池模式，可配置 session，transaction，sql，建议使用transaction。默认是session。

max_client_conn = 100

;最大连接数配置

default_pool_size = 20

;默认连接池大小

min_pool_size = 0

reserve_pool_size = 5

reserve_pool_timeout = 3

5、配置userlist.txt：

• 格式为："user1" "password" ，第一列为 允许访问的用户名，第二列为此用户的密码。
• 如主配置文件中auth_type设置为trust，则密码配置为明文。
• 如主配置文件中auth_type设置为md5，则密码配置为md5值，可使用超户登录至数据库中，执行语句：select rolname,rolpassword from pg_authid，将用户名和显示的加密后密码复制过来。

6、配置完成后，即可通过postgres用户启动pgbouncer。

$ 启动命令：/opt/pgbouncer/bin/pgbouncer -d /opt/pgbouncer/share/doc/pgbouncer/pgbouncer.ini

7、完成启动之后，可通过下列命令查看是否启动成功

$ ps -ef |grep pgbouncer

postgres 17171 1 0 16:41 ? 00:00:00 /opt/pgbouncer/bin/pgbouncer -d /opt/pgbouncer/share/doc/pgbouncer/pgbouncer.ini

$ netstat -anlp|grep pgbouncer

tcp 0 0 172.27.32.17:5432 0.0.0.0:* LISTEN 17171/pgbouncer

性能体验：

测试结果，使用pgbouncer 对性能影响极低，并且更充分利用了连接资源，当前测试的数量集并不大，基于当前测试结果来看，相信在大规模并发场景下，亦能减轻数据库不少负担，但稳定性待验证。

通过PostgreSQL原生连接与pgbouncer进行访问压测看一下执行情况，设定连接为100个，分别都压测2分钟，中间间隔一分钟。

• 通过原生连接压测主实例： pgbench -U haha -p 5432 -h 172.27.32.11 -d test -r -P 1 -c 100 -j 32 -T 120 -D scale=10000 -D range=100000000
• 通过pgbouncer压测主实例： pgbench -U haha -p 5432 -h 172.27.32.17 -d db_rw -r -P 2 -c 100 -j 32 -T 120 -D scale=10000 -D range=100000000

查看数据库监控，可以看到使用了pgbouncer的连接数降低了33%，QPS几乎无变化，平均响应时延也没有明显变化。

• 第一个凸起部分即为 使用pg原生的连接进行访问的连接数，平均在113左右，和压测情况相符，而后面一个较低的线则为通过pgboucer进行压测的结果，可见连接数降低了30%，平均在30左右。

• 平均执行时延也没有发生明显的升高，基本和未使用连接池是一致的。

• QPS几乎未发生变化，曲线一致