PostgreSQL连接池软件-ZQPool
2023-02-12 11:33:39 
阿炯
ZQPool 是中启乘数科技发布的 PostgreSQL 数据的开源连接池软件。主要解决 PostgreSQL 生态中流行的连接池软件 pgbouncer 的一些缺点。采用Go语言开发并在木兰公共许可证第2版下授权。软件架构
应用程序连接到 ZQPool，ZQPool 再连接到 PostgreSQL 数据库。
安装方法如下：
把发行版本 zqpool1.0.x86_64.xz 拷贝到一个目录中
解压：tar xf zqpool1.0.x86_64.xz 同时把 zqpool.conf 也拷贝到此目录中
解压后有两个文件 zqpool.conf 和 zqpool，其中 zqpool.conf 是配置文件，做相应的配置。
然后启动./zqpool 即可
编译的方法：
git clone https://gitee.com/csudata/zqpool
cd zqpool
make
使用说明
配置 zqpool.conf 文件，各个配置项说明如下：
listen_port = 5436 ： 设置 zqpool 的监听端口
listen_addr = * : 设置 zqpool 的监听 IP，设置为 *，表示在本地的所有 IP 地址上监听
default_pool_size = 10 : 设置到后端数据库的总连接数
max_client_conn = 3000 ： 允许应用程序连接 zqpool 的总连接数
db.1.user=u01 ： 第一个数据库的用户名 db.1.dbname=postgres ： 第一个库的数据库名称 #db.1.ipport=192.168.56.39:5432,192.168.0.34:5432 db.1.ipport=172.22.224.10:5432 ： 第一个数据库的 IP 和端口 db.1.passwd=u01 : 目前用户的密码
上面的 db.1 代表第一个数据库，还可以有 db.2、db.3 等多个数据库。db.1.ipport 后面可以配置多个 IP 地址端口，以逗号分隔，如果配置了多个则随机均衡到后面的多个 IP 地址上。
zqpool.conf:
listen_port = 5436
listen_addr = *
default_pool_size = 10
max_client_conn = 3000
db.1.user=u01
db.1.dbname=postgres
db.1.ipport=172.22.224.10:5432
db.1.passwd=u01
启动 zqpool:
$ ./zqpool
2022/05/24 09:12:30 server.go:2188: Starting server on :5436 ...
在另一个窗口中，使用 psql 连接 zqpool 的 5436 端口：
]$ /usr/pgsql-10/bin/psql -h 172.22.224.10 -p 5436 -Uu01 -d postgres
Password for user u01:
psql (10.20, server 10.5)
Type "help" for help.
postgres=> \\d
List of relations
Schema | Name | Type | Owner
--------+--------+-------+----------
public | test01 | table | postgres
(1 row)
postgres=> select * from test01;
id | t
----+---
(0 rows)
postgres=> insert into test01 values(1, '111'),(2,'222'),(3,'333');
INSERT 0 3
postgres=> select * from test01;
id | t
----+-----
1 | 111
2 | 222
3 | 333
(3 rows)
使用数据库连接池的主要目的有两个：
1.减少到数据库上的连接数。应用程序到连接池软件上有 M 个连接，这 M 个连接不是同时都繁忙的，这 M 个连接上同一个时刻发来的并发 SQL 可能只有 N 个（N 通常大大小于 M)，这样连接池软件只需要在后端数据库上建 N 个连接。就可以满足了要求。这个场景通常是 java 应用。 我们可以想象一个场景：一个 java 应用可能部署在 200 台主机上，而每个主机上 java 应用自身会开启一个 java 连接池，这个 java 连接池假设开 20 个连接，这时到数据库上就有 200*20=4000 个连接，这些连接实际上多数时间都是空闲的，少数时间才是活跃的。 4000 个连接，PostgreSQL 数据库就需要启动 4000 个进程，太多连接会降低数据库的效率。
2.减少断连接应用花在新建数据库连接的时间。PostgreSQL 数据库对每一个连接需要 fork 出一个新的进程来提供服务，而每次 fork 一个进程是需要时间的。而连接池软件可以预先建好到数据库的连接，应用程序连接到连接池软件后，连接池软件可以从池中取一个已经建好的连接马上提供服务，这样就大大减少了新连接的时间。这个场景的典型应用是 php 应用。php 应用到数据库通常是短连接。
而 PostgreSQL 数据库中流行的 pgbouncer 通常解决不了上面的第一个问题（java 应用）：即减少到数据库上连接的目的。要减少到数据库上的连接数，pgbouncer 连接池的模式只能配置成语句级或事务级，不能配置成会话级。因为 pgbouncer 在会话级下，前面来多少个连接，到数据库也必须建多少个连接，起不到减少数据库连接的目的。
当把 pgbouncer 配置成语句级或事务级时，java 应用连接 pgbouncer 会报错:
org.postgresql.util.PSQLException: ERROR: prepared statement "S_1" already exists
这个原因是 jdbc 执行 SQL 是分两个步骤的:
1.先使用 Prepare 的 SQL，即：“prepare S_1 as select * from test01 where id=$1;”
2.然后再 “execute S1 (1);”
当 “prepare S_1 as select * from test01 where id=$1;” 这条 SQL 来的时候，从连接池中那个一个连接 A，执行了后，就释放了此连接；
这时来了另一个 SQL，可能从连接池中取到的还是之前的连接 A，然后再执行 “prepare S_1 as select * from test02 where id=$1;”，但这个 prepare SQL 的名字 S_1 已经被前面的 SQL 占用，这时就报上面的错了。
注：jdbc 实际的行为比上面的更复杂，但原理大致就是上面描述的这个过程。
而 ZQPool 通过记录一个连接上的 Prepare SQL 的名字，并替换成不重复的名字的方式解决了这个问题。pgbouncer 还有一个缺点，处理 SQL 的转发只能用到 CPU 的一个核，即 pgbouncer 是单线程程序。对于高并发的情况下，超过单核的性能时，就立即回出现瓶颈。而 ZQPool 是使用 golang 的协程技术，可以利用了多核的性能，下面是在一台物理机上做的测试：
这是 pgbouncer 的测试情况：
$ pgbench -h 10.197.160.18 -p 6432 -Uu01 -S -P 2 -T 30 -c 32
pgbench (14.3)
starting vacuum...end.
progress: 2.0 s, 30407.5 tps, lat 1.050 ms stddev 0.180
progress: 4.0 s, 30108.6 tps, lat 1.062 ms stddev 0.182
progress: 6.0 s, 30231.5 tps, lat 1.058 ms stddev 0.179
progress: 8.0 s, 31157.9 tps, lat 1.026 ms stddev 0.176
progress: 10.0 s, 30491.7 tps, lat 1.049 ms stddev 0.178
progress: 12.0 s, 30463.0 tps, lat 1.050 ms stddev 0.180
progress: 14.0 s, 30366.2 tps, lat 1.053 ms stddev 0.179
progress: 16.0 s, 30177.5 tps, lat 1.060 ms stddev 0.180
progress: 18.0 s, 30067.1 tps, lat 1.064 ms stddev 0.181
progress: 20.0 s, 30420.1 tps, lat 1.051 ms stddev 0.177
...
这是使用 ZQPool 测试的情况：
$ pgbench -h 10.197.160.18 -p 5436 -Uu01 -S -P 2 -T 30 -c 32
Password:
pgbench (14.3, server 10.5)
starting vacuum...end.
progress: 2.0 s, 111134.7 tps, lat 0.213 ms stddev 0.058
progress: 4.0 s, 112688.1 tps, lat 0.209 ms stddev 0.058
progress: 6.0 s, 114570.8 tps, lat 0.207 ms stddev 0.054
progress: 8.0 s, 107305.3 tps, lat 0.216 ms stddev 0.066
progress: 10.0 s, 108680.1 tps, lat 0.215 ms stddev 0.063
progress: 12.0 s, 108867.6 tps, lat 0.214 ms stddev 0.064
...
可以看到 ZQPool 的 tps 可以到 10 万每秒，而 pgbouncer 最多到 3 万每秒就上不去了。
最新版本：1.1
在2023年2月中旬发布的 1.1 版本中主要做了如下改进：
之前的版本，前端连接过来的用户名和数据库名称必须与后端数据库中的完全一样，现在可以不一样。例如原先的版本如果使用 scott 用户连接 ZQPool 上的数据库 maydb，那么连接到后端的真实数据库也是 maydb，而新版本使用 scott 用户连接 ZQPool 上的数据库 maydb，但实际上可能是使用 yellow 用户连接到后端的 fourdb 数据库上（具体看 zqpool.conf 中的配置）。
增加了后端连接的生命周期的功能，到达生命周期后，此后端连接会重建，以便避免数据库连接长时间不释放而可能的内存泄漏风险。
增加了管理端口，可以通过向 ZQPool 发送 http 请求动态改变一些配置。
优化了 ZQPool 配置
修复了一些 Bug。
最新版本：1.3
在2023年6月下旬发布的1.3版本中主要做了一下改进：
添加了 ZQPool 的一些监控指标接口，可以通过接口获取 ZQPool 的实时性能统计数据。
增加了对 Prometheus 的支持，提供 exporter 供 Prometheus 获取 ZQPool 实时监控指标，可以通过 grafana+Prometheus 来展示对应的一些性能统计指标。
增加的监控指标
| 指标名称 | 说明 | 状态 |
|---|---|---|
| zqpool_backend_connections | 后端连接数，记录了每个连接池的后端连接数目。 | ✅ |
| zqpool_active_backend_connections | 活跃后端连接数，记录了每个连接池的活跃后端连接数目。 | ✅ |
| zqpool_frontend_connections | 前端连接数，记录了每个连接池的前端连接数目。 | ✅ |
| zqpool_active_frontend_connections | 活跃前端连接数，记录了每个连接池的活跃前端连接数目。 | ✅ |
| zqpool_total_requests | 请求总数，记录了每个连接池的请求总数。 | ✅ |
| zqpool_total_simple_queries | 简单查询总数，记录了每个连接池的简单查询总数。 | ✅ |
| zqpool_total_extended_queries | 扩展查询总数，记录了每个连接池的扩展查询总数。 | ✅ |
| zqpool_backend_connection_limit_reached_times | 记录了每个连接池的后端连接数被占满的次数。 | ✅ |
修复 select 一张不存在的表有概率 hang 住的问题。
修复当前端连接突然关闭无法释放后端连接的 Bug。
项目主页：https://gitee.com/csudata/zqpool