zqpool: ZQPool是中启乘数科技发布的PostgreSQL数据的开源连接池软件。主要解决流行的连接池软件pgbouncer的如下两个缺点： 1. 解决jdbc程序使用pgbouncer连接池无法减少数据库上连接数的问题； 2. pgbouncer不能利用到CPU的多核，当对高并发的情况下，处理能力超过了单核的能力时，性能就再也上不去了。

# ZQPool

### 介绍
ZQPool是 [中启乘数科技](http://www.csudata.com) 发布的PostgreSQL数据的开源连接池软件。主要解决PostgreSQL生态中流行的连接池软件pgbouncer软件的一些缺点。

通常使用数据库连接池的主要目的有两个：

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);”

报错的原因为：

1. 执行“prepare S_1 as select * from test01 where id=$1;”时，从连接池中取一个连接A，执行后，就释放了此连接；
2. 执行“execute S_1(1);”，再从连接池中获得一个连接，这时获得的连接可能已经不是之前的连接，这个新连接中没有Prepare语句“S_1”，所以就报错了；
3. 如果又来了另一个SQL，可能从连接池中取到的还是之前的连接A，然后再执行“prepare S_1 as select * from test02 where id=$1;”，但这个prepare SQL 的名字S_1已经被前面的SQL占用，这时就报错了。
4. 当然jdbc的实际行为比上面描述的要复杂的多，但原理大致就是上面描述的这个过程。

而ZQPool通过记录一个连接上的Prepare SQL的名字，并替换成不重复的名字的方式解决了这个问题。

pgbouncer还有一个缺点，处理SQL的转发只能用到CPU的一个核，即pgbouncer是单线程程序。对于高并发的情况下，超过单核的性能时，就会立即出现瓶颈。而ZQPool是使用golang的协程技术，可以利用了多核的性能，下面是我们在一台2颗 Intel(R) Xeon(R) Silver 4210R CPU @ 2.40GHz的物理机上做的测试：

这是pgbouncer的测试情况：

```
[postgres@csyun01 ~]$ 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测试的情况：

```
[postgres@csyun01 ~]$ 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万每秒就上不去了。

最后总结以下，ZQPool主要解决pgbouncer的两个缺点：

1. 解决java等应用使用pgbouncer连接池无法减少数据库上连接数的问题；
2.  pgbouncer不能利用到CPU的多核，当对高并发的情况下，处理能力超过了单核的能力时，性能就再也上不去了。

### 软件架构

应用程序连接到ZQPool，ZQPool连接到PostgreSQL数据库。

### 安装教程

安装方法如下：

1. 把发行版本zqpool1.0.x86_64.tar.xz拷贝到一个目录中
2. 解压：tar xf zqpool1.0.x86_64.tar.xz
3. 解压后有两个文件zqpool.conf和zqpool，其中zqpool.conf是配置文件，做相应的配置。
4. 然后启动./zqpool 即可

编译的方法：

1. git clone https://gitee.com/csudata/zqpool
2. cd zqpool
3. make
把编译生成的可执行文件zqpool和zqpool.conf也拷贝到一个目录中即可。

### 使用说明

配置zqpool.conf文件，各个配置项说明如下：

* listen_port = 5436     ： 设置zqpool的监听端口
* listen_addr = *        :  设置zqpool的监听IP，设置为*，表示在本地的所有IP地址上监听
* mgr_port = 9380        : 管理端口
* mgr_addr = *           : 管理端口监听的地址

各个连接池的配置：

* pool.1.fe_max_conns = 3000
* pool.1.fe_user=u01
* pool.1.fe_passwd=u02
* pool.1.fe_dbname=mydb
* pool.1.be_user=u01
* pool.1.be_passwd=u01
* pool.1.be_dbname=postgres
* pool.1.be_conns = 10
* pool.1.be_ipport=172.22.224.10:5432,172.22.224.10:5411
* pool.1.be_conn_life_time=60  # 指定连接的life_time，当连接超过这个时间后，会被销毁重连，主要是为了防止内存泄漏

上面的pool.1代表第一个连接池，还可以有pool.2、pool.3等多个连接池。pool.1.be_ipport后面可以配置多个IP地址端口，以逗号分隔，如果配置了多个则随机均衡到后面的多个IP地址上。

启动zqpool:

```
[codetest@pgdev zqpool]$ ./zqpool
2022/05/24 09:12:30 server.go:2188: Starting server on :5436 ...
```

在另一个窗口中，使用psql连接zqpool的5436端口：

```
[codetest@pgdev zqpool]$ /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=> 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)
```

可以为一个连接池增加一个后端数据库

```
curl -X POST -d "pool_name=u01.mydb&db_portal=172.22.224.10:5411" http://127.0.0.1:9380/api/v1/pool_add_be_db
```

或移除一个后端数据库

```
curl -X POST -d "pool_name=u01.mydb&db_portal=172.22.224.10:5432" http://127.0.0.1:9380/api/v1/pool_remove_be_db
```

查看一个连接池的后端数据库列表：

```
curl -X POST -d "pool_name=u01.mydb&db_portal=172.22.224.10:5432" http://127.0.0.1:9380/api/v1/pool_list_be_db
```

### 监控指标

**ZQPool 提供了基于 Prometheus 的 Exporter。**

在配置文件中指定 Exporter 端口:
`exporter_port=9816 # 指定 Prometheus 监控端口, 即 ZQPool 中 Exporter 暴露数据的端口`

标签：
pool_id 即配置文件中: pool.x.y 的 x <br>
比如:

```
# pool_id=1
pool.1.fe_max_conns = 3000
pool.1.fe_user=kuafu
```

| 指标名称                                      | 说明                                                 | 状态 |
| --------------------------------------------- | ---------------------------------------------------- | ---- |
| 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 | 记录了每个连接池的后端连接数被占满的次数。           | ✅    |

### PostgreSQl 14版本使用注意

目前ZQPool还不支持SCRAM-SHA-256的密码验证方式。而PostgreSQL 14版本默认使用SCRAM-SHA-256的密码验证方式，而不再是md5，所以当后端的数据库版本是14时或后端用户的验证方式SCRAM-SHA-256时，会报如下错误：

```
[codetest@pgdev src]$ ./zqpool
2023-02-20T21:27:00.509+0800    INFO    mgrhttp/mgrhttp.go:111  Manager http Listen :9380
2023-02-20T21:27:00.510+0800    INFO    poolserver/poolserver.go:254    Only support md5(type=5) and trust(type=0), can not support type = 10
```

这时我们可以手工把用户的验证方式改成md5，方法如下：

```
set password_encryption to md5;
alter user u01 password 'u01';
```

检查是否生效：

```
postgres=#  select rolname,rolpassword from pg_authid where rolname='u01';
 rolname |             rolpassword
---------+-------------------------------------
 u01     | md5a3ece78fbd8f6411afb3ae35253c960c
(1 row)
```
上面rolpassword这一列中的密码如果是以md5开头的则是md5的密码验证方式，如果是SCRAM-SHA-256开头的，说明还是SCRAM-SHA-256的验证方式。

### 参考

#### jdbc中多地址配置

可以参见：

https://jdbc.postgresql.org/documentation/use/#connection-fail-over

### 技术交流群

如需要交流或技术支持，请加扫描**乘数小助手**的二维码并加其为好友，然后由**乘数小助手**拉您进入微信交流群中 ：

![乘数小助手](csu_assis_qr_code.jpg)

在此支持群中，可以获得ZQPool的社区支持以及一些最新的消息。

### 参与贡献

1.  Fork 本仓库
2.  新建 Feat_xxx 分支
3.  提交代码
4.  新建 Pull Request

### TODO

1. 支持copy协议
2. 增加性能统计信息
3. 支持读写分离，支持后端是polardb for postgresql的读写分离功能。
4. 支持更多的配置项
5. 支持一些管理命令
6. 支持ssl协议
7. 支持SCRAM-SHA-256的密码验证

中启开源/zqpool

简介

发行版 (4)

贡献者

近期动态

中启开源/zqpool .gitee-modal { width: 500px !important; }

简介

发行版 (4)

开源评估指数源自 OSS-Compass 评估体系，评估体系围绕以下三个维度对项目展开评估：

贡献者

近期动态

搜索帮助

中启开源/zqpool