大家好, 今天大表哥和大家分享一下 PG bouncer 构建轻量级连接池。
相对于业界著名的连接池PG POOL, PG bouncer 是一款配置简单,运维成本更低的轻量级数据库连接池的解决方案。
PG bouncer 目前在各大PG 的厂商的产品中使用广泛。
业界著名的公司 EDB 也在社区版的基础上,进行了二次开发, 形成了 EDB pgBouncer. https://www.enterprisedb.com/docs/pgbouncer/latest/
PG Bouncer 也是 EDB 提供的faliover 的方案之一。
微软的Azure 上,直接集成了 pg bouncer 作为 Flexible server 的解决方案。
https://docs.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-pgbouncer
上面科普一下PG bouncer 之后, 下面我们来安装和测试一下:
1)下载社区版 http://www.pgbouncer.org/
INFRA [postgres@wqdcsrv3352 postgreSQL]# wget http://www.pgbouncer.org/downloads/files/1.17.0/pgbouncer-1.17.0.tar.gz
--2022-08-19 11:35:07-- http://www.pgbouncer.org/downloads/files/1.17.0/pgbouncer-1.17.0.tar.gz
Resolving www.pgbouncer.org (www.pgbouncer.org)... 185.199.108.153, 185.199.110.153, 185.199.109.153, ...
Connecting to www.pgbouncer.org (www.pgbouncer.org)|185.199.108.153|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 598294 (584K) [application/gzip]
Saving to: ‘pgbouncer-1.17.0.tar.gz’
100%[===================================================================================================================================================>] 598,294 2.63MB/s in 0.2s
2022-08-19 11:35:08 (2.63 MB/s) - ‘pgbouncer-1.17.0.tar.gz’ saved [598294/598294]
复制
2)解压文件
INFRA [postgres@wqdcsrv3352 postgreSQL]# tar -xvf pgbouncer-1.17.0.tar.gz
复制
3)编译安装
这个需要检查依赖的安装包: libevent-devel
INFRA [postgres@wqdcsrv3352 pgbouncer-1.17.0]# mkdir -p /opt/postgreSQL/pgbouncer
INFRA [postgres@wqdcsrv3352 pgbouncer-1.17.0]# ./configure --prefix=/opt/postgreSQL/pgbouncer
INFRA [postgres@wqdcsrv3352 pgbouncer-1.17.0]# make && make install
复制
4)安装后的版本检查
INFRA [postgres@wqdcsrv3352 pgbouncer-1.17.0]# /opt/postgreSQL/pgbouncer/bin/pgbouncer --version
PgBouncer 1.17.0
libevent 2.0.21-stable
adns: evdns2
tls: OpenSSL 1.0.2k-fips 26 Jan 2017
复制
5)编辑配置文件 pgbouncer.ini
这个配置文件我们可以从git hub 中获取完整版的文件模板和最小化配置版本的
https://github.com/pgbouncer/pgbouncer/blob/master/etc/pgbouncer.ini
https://github.com/pgbouncer/pgbouncer/blob/master/etc/pgbouncer-minimal.ini --最小化的模板
我们采用最小化的模板进行修改:
具体参数的意义可以参考文档: https://www.pgbouncer.org/config.html
;;; This is an almost minimal starter configuration file that only
;;; contains the settings that are either mandatory or almost always
;;; useful. All settings show their default value.
[databases]
pgbouncer_db = host=10.67.38.50 port=1998 dbname=postgres
;; fallback
;* =
[pgbouncer]
;; required in daemon mode unless syslog is used
logfile = /opt/postgreSQL/pgbouncer/log/pgbouncer.log
;; required in daemon mode
pidfile =/opt/postgreSQL/pgbouncer/pid/pgbouncer.pid
syslog = 0
;; set to enable TCP/IP connections
listen_addr = *
;; PgBouncer port
listen_port = 6432
;; some systems prefer /var/run/postgresql
;unix_socket_dir = /tmp
;; change to taste
auth_type = md5
;; probably need this
auth_file = /opt/postgreSQL/pgbouncer/user.txt
;; pool settings are perhaps best done per pool
pool_mode = session
default_pool_size = 20
;; should probably be raised for production
max_client_conn = 100
复制
[databases] 数据库的基本配置信息: pgbouncer_db = host=10.67.38.50 port=1998 dbname=postgres
日志文件路径信息:/opt/postgreSQL/pgbouncer/log/pgbouncer.log
进程文件路径信息: /opt/postgreSQL/pgbouncer/pid/pgbouncer.pid
监听的网络地址: * 表示所有的IP地址
监听的端口信息:6432
用户权限认证方式: md5
用户和密码文件的路径: /opt/postgreSQL/pgbouncer/user.txt
连接池的类型: session (这个是默认的) 还有 transaction, statement
每个数据库允许的连接数: 20
最大允许的客户端连接数:100
我们再来看一下 MD5 认证规则下的 user.txt 的密码生成方式:
官网文档上给出的例子
我们来给账户: app_ha_user/app_ha_user 生成密码:
postgres@[local:/tmp]:1992=#92250 select 'md5'||md5('app_ha_userapp_ha_user');
?column?
-------------------------------------
md5bea3950351c9c98b964ebb3c5a118296
(1 row)
复制
我们来编辑一下 user.txt
INFRA [postgres@wqdcsrv3352 pgbouncer]# cat user.txt
"app_ha_user" "md5bea3950351c9c98b964ebb3c5a118296"
复制
6)启动 pgbouncer
INFRA [postgres@wqdcsrv3352 pgbouncer]# /opt/postgreSQL/pgbouncer/bin/pgbouncer -d /opt/postgreSQL/pgbouncer/pgbouncer.ini
复制
7)查看启动日志以及端口占用情况
INFRA [postgres@wqdcsrv3352 pgbouncer]# tail -f /opt/postgreSQL/pgbouncer/log/pgbouncer.log
2022-08-19 15:36:51.968 CST [120399] LOG kernel file descriptor limit: 65535 (hard: 65535); max_client_conn: 100, max expected fd use: 152
2022-08-19 15:36:51.969 CST [120399] LOG listening on 0.0.0.0:6432
2022-08-19 15:36:51.969 CST [120399] LOG listening on [::]:6432
2022-08-19 15:36:51.969 CST [120399] LOG listening on unix:/tmp/.s.PGSQL.6432
2022-08-19 15:36:51.969 CST [120399] LOG process up: PgBouncer 1.17.0, libevent 2.0.21-stable (epoll), adns: evdns2, tls: OpenSSL 1.0.2k-fips 26 Jan 2017
2022-08-19 15:37:51.970 CST [120399] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
INFRA [postgres@wqdcsrv3352 pgbouncer]# netstat -pltn | grep pgbouncer
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 0.0.0.0:6432 0.0.0.0:* LISTEN 120399/pgbouncer
tcp6 0 0 :::6432 :::* LISTEN 120399/pgbouncer
复制
8)尝试连接 pgbouncer 的 6432 端口
这里需要注意的是 -d pgbouncer_db 数据库是 pgbouncer.ini 配置文件里面的数据库名字
INFRA [postgres@wqdcsrv3354 ~]# psql -h 10.67.38.50 -p 6432 -U app_ha_user -d pgbouncer_db
Password for user app_ha_user:
psql (14.3, server 12.3)
Type "help" for help.
pgbouncer_db=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
repmgr | repmgr | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
复制
9)我们启动一下 springboot 的 java 测试程序连接一下 pgbouncer
//数据库的地址以及端口号
spring.datasource.url=jdbc:postgresql://10.67.38.50:6432/pgbouncer_db
spring.datasource.username=app_ha_user
spring.datasource.password=app_ha_user
spring.datasource.driverClassName=org.postgresql.Driver
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.properties.hibernate.temp.use_jdbc_metadata_defaults=false
spring.jpa.properties.hibernate.hbm2ddl.auto=update
spring.datasource.maximum-pool-size=200
spring.datasource.min-idle=10
复制
启动程序的时候,会报一个错误: FATAL: unsupported startup parameter: extra_float_digits
org.postgresql.util.PSQLException: FATAL: unsupported startup parameter: extra_float_digits at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:613) at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:161) at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:213) at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:51) at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:223) at org.postgresql.Driver.makeConnection(Driver.java:465) at org.postgresql.Driver.connect(Driver.java:264)
复制
解决方法:配置文件中/etc/pgbouncer/pgbouncer.ini 添加
[pgbouncer]
ignore_startup_parameters = extra_float_digits
reload 一下使修改的参数生效
INFRA [postgres@wqdcsrv3352 log]# psql -h /tmp -p 6432 -d pgbouncer -U pgbouncer
psql (14.4, server 1.17.0/bouncer)
Type "help" for help.
pgbouncer@[local:/tmp]:6432=#-1456267205 reload;
RELOAD
复制
再次启动 springboot 小程序, 查看连接池:
postgres@[local:/tmp]:1998=#26695 select * from pg_stat_activity where usename = 'app_ha_user';
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start
| state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query | backend_type
-------+----------+-------+----------+-------------+------------------------+-------------+-----------------+-------------+-------------------------------+------------+---------------------
----------+-------------------------------+-----------------+------------+-------+-------------+--------------+-------------------------------------------------+----------------
13594 | postgres | 24945 | 24649 | app_ha_user | PostgreSQL JDBC Driver | 10.67.38.50 | | 41058 | 2022-08-19 16:28:37.933005+08 | | 2022-08-19 16:28:37.
973424+08 | 2022-08-19 16:28:37.973481+08 | Client | ClientRead | idle | | | SHOW TRANSACTION ISOLATION LEVEL | client backend
13594 | postgres | 24948 | 24649 | app_ha_user | PostgreSQL JDBC Driver | 10.67.38.50 | | 41066 | 2022-08-19 16:28:38.097211+08 | | 2022-08-19 16:28:38.
100256+08 | 2022-08-19 16:28:38.100272+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
13594 | postgres | 24949 | 24649 | app_ha_user | PostgreSQL JDBC Driver | 10.67.38.50 | | 41068 | 2022-08-19 16:28:38.108813+08 | | 2022-08-19 16:28:38.
111803+08 | 2022-08-19 16:28:38.111812+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
13594 | postgres | 24950 | 24649 | app_ha_user | PostgreSQL JDBC Driver | 10.67.38.50 | | 41070 | 2022-08-19 16:28:38.120795+08 | | 2022-08-19 16:28:38.
12538+08 | 2022-08-19 16:28:38.125391+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
13594 | postgres | 24951 | 24649 | app_ha_user | PostgreSQL JDBC Driver | 10.67.38.50 | | 41072 | 2022-08-19 16:28:38.135148+08 | | 2022-08-19 16:28:38.
13804+08 | 2022-08-19 16:28:38.138057+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
13594 | postgres | 24953 | 24649 | app_ha_user | PostgreSQL JDBC Driver | 10.67.38.50 | | 41076 | 2022-08-19 16:28:38.14681+08 | | 2022-08-19 16:28:38.
150348+08 | 2022-08-19 16:28:38.150362+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
13594 | postgres | 24954 | 24649 | app_ha_user | PostgreSQL JDBC Driver | 10.67.38.50 | | 41078 | 2022-08-19 16:28:38.160388+08 | | 2022-08-19 16:28:38.
163945+08 | 2022-08-19 16:28:38.163966+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
13594 | postgres | 24956 | 24649 | app_ha_user | PostgreSQL JDBC Driver | 10.67.38.50 | | 41082 | 2022-08-19 16:28:38.171793+08 | | 2022-08-19 16:28:38.
17481+08 | 2022-08-19 16:28:38.174832+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
13594 | postgres | 24957 | 24649 | app_ha_user | PostgreSQL JDBC Driver | 10.67.38.50 | | 41084 | 2022-08-19 16:28:38.183634+08 | | 2022-08-19 16:28:38.
187351+08 | 2022-08-19 16:28:38.187375+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
13594 | postgres | 24958 | 24649 | app_ha_user | PostgreSQL JDBC Driver | 10.67.38.50 | | 41086 | 2022-08-19 16:28:38.201669+08 | | 2022-08-19 16:28:38.
205084+08 | 2022-08-19 16:28:38.205095+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
(10 rows)
复制
我们查看pgbouncer 的连接池:
pgbouncer@[local:/tmp]:6432=#-1456267205 SHOW POOLS;
database | user | cl_active | cl_waiting | cl_cancel_req | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait | maxwait_us | pool_mode
--------------+-------------+-----------+------------+---------------+-----------+---------+---------+-----------+----------+---------+------------+-----------
pgbouncer | pgbouncer | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | statement
pgbouncer_db | app_ha_user | 10 | 0 | 0 | 10 | 0 | 0 | 0 | 0 | 0 | 0 | session
(2 rows)
复制
查看客户端的10个连接:
pgbouncer@[local:/tmp]:6432=#-1456267205 SHOW Clients;
type | user | database | state | addr | port | local_addr | local_port | connect_time | request_time | wait | wait_us | close_needed | ptr
| link | remote_pid | tls
------+-------------+--------------+--------+-------------+-------+-------------+------------+-------------------------+-------------------------+------+---------+--------------+-----------
+-----------+------------+-----
C | pgbouncer | pgbouncer | active | unix | 6432 | unix | 6432 | 2022-08-19 16:34:57 CST | 2022-08-19 16:40:05 CST | 298 | 419870 | 0 | 0x19e2f60
| | 29373 |
C | app_ha_user | pgbouncer_db | active | 10.64.44.74 | 49478 | 10.67.38.50 | 6432 | 2022-08-19 16:28:37 CST | 2022-08-19 16:28:37 CST | 0 | 0 | 0 | 0x19e1930
| 0x19e8840 | 0 |
C | app_ha_user | pgbouncer_db | active | 10.64.44.74 | 49481 | 10.67.38.50 | 6432 | 2022-08-19 16:28:38 CST | 2022-08-19 16:28:38 CST | 0 | 0 | 0 | 0x19e1b68
| 0x19e8a78 | 0 |
C | app_ha_user | pgbouncer_db | active | 10.64.44.74 | 49482 | 10.67.38.50 | 6432 | 2022-08-19 16:28:38 CST | 2022-08-19 16:28:38 CST | 0 | 0 | 0 | 0x19e1da0
| 0x19e8cb0 | 0 |
C | app_ha_user | pgbouncer_db | active | 10.64.44.74 | 49483 | 10.67.38.50 | 6432 | 2022-08-19 16:28:38 CST | 2022-08-19 16:28:38 CST | 0 | 0 | 0 | 0x19e1fd8
| 0x19e8ee8 | 0 |
C | app_ha_user | pgbouncer_db | active | 10.64.44.74 | 49484 | 10.67.38.50 | 6432 | 2022-08-19 16:28:38 CST | 2022-08-19 16:28:38 CST | 0 | 0 | 0 | 0x19e2210
| 0x19e9120 | 0 |
C | app_ha_user | pgbouncer_db | active | 10.64.44.74 | 49485 | 10.67.38.50 | 6432 | 2022-08-19 16:28:38 CST | 2022-08-19 16:28:38 CST | 0 | 0 | 0 | 0x19e2448
| 0x19e9358 | 0 |
C | app_ha_user | pgbouncer_db | active | 10.64.44.74 | 49486 | 10.67.38.50 | 6432 | 2022-08-19 16:28:38 CST | 2022-08-19 16:28:38 CST | 0 | 0 | 0 | 0x19e2680
| 0x19e9590 | 0 |
C | app_ha_user | pgbouncer_db | active | 10.64.44.74 | 49487 | 10.67.38.50 | 6432 | 2022-08-19 16:28:38 CST | 2022-08-19 16:28:38 CST | 0 | 0 | 0 | 0x19e28b8
| 0x19e97c8 | 0 |
C | app_ha_user | pgbouncer_db | active | 10.64.44.74 | 49488 | 10.67.38.50 | 6432 | 2022-08-19 16:28:38 CST | 2022-08-19 16:28:38 CST | 0 | 0 | 0 | 0x19e2af0
| 0x19e9a00 | 0 |
C | app_ha_user | pgbouncer_db | active | 10.64.44.74 | 49489 | 10.67.38.50 | 6432 | 2022-08-19 16:28:38 CST | 2022-08-19 16:28:38 CST | 0 | 0 | 0 | 0x19e2d28
| 0x19e9c38 | 0 |
(11 rows)
复制
以上是 spring boot 连接pgbouncer 的测试程序。
下面我们用 pgbench 对 pgbouncer 进行一下性能测试 :
综合来看: TPS 增长了 (456 - 396)/396 = 约 15% 左右,
但是每次连接的时间 从 1.237 毫秒 降到了 0.028 毫秒 大致是 下降了40多倍的样子
测试细节信息如下:
基本机器配置:
Intel® Xeon® Gold 6132 CPU @ 2.60GHz
内存 8GB
CPU 8 CORE
测试命令: pgbench -M prepared -r -c 16 -j 2 -T 600 -U postgres -p 1998 -d pgbench -l
直接连接数据库:
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 16
query mode: prepared
number of clients: 16
number of threads: 2
duration: 600 s
number of transactions actually processed: 237969
latency average = 40.370 ms
tps = 396.330320 (including connections establishing)
tps = 396.331557 (excluding connections establishing)
statement latencies in milliseconds:
0.159 \set aid random(1, 100000 * :scale)
0.126 \set bid random(1, 1 * :scale)
0.173 \set tid random(1, 10 * :scale)
0.253 \set delta random(-5000, 5000)
2.300 BEGIN;
3.572 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
2.815 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
3.722 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
9.568 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
2.883 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
12.680 END;
复制
连接pgbouncer 连接池:
ransaction type: <builtin: TPC-B (sort of)>
scaling factor: 16
query mode: prepared
number of clients: 16
number of threads: 2
duration: 600 s
number of transactions actually processed: 274146
latency average = 35.053 ms
tps = 456.457794 (including connections establishing)
tps = 456.457822 (excluding connections establishing)
statement latencies in milliseconds:
0.093 \set aid random(1, 100000 * :scale)
0.090 \set bid random(1, 1 * :scale)
0.092 \set tid random(1, 10 * :scale)
0.087 \set delta random(-5000, 5000)
0.466 BEGIN;
0.517 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
0.354 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
0.557 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
0.680 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
0.473 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
2.847 END;
复制
Have a fun 🙂 !