暂无图片
暂无图片
2
暂无图片
暂无图片
暂无图片

PG Bouncer 构建轻量级连接池

原创 大表哥 2022-08-22
1668

image.png
大家好, 今天大表哥和大家分享一下 PG bouncer 构建轻量级连接池。

相对于业界著名的连接池PG POOL, PG bouncer 是一款配置简单,运维成本更低的轻量级数据库连接池的解决方案。

PG bouncer 目前在各大PG 的厂商的产品中使用广泛。

业界著名的公司 EDB 也在社区版的基础上,进行了二次开发, 形成了 EDB pgBouncer. https://www.enterprisedb.com/docs/pgbouncer/latest/

PG Bouncer 也是 EDB 提供的faliover 的方案之一。

Image.png
Image.png
微软的Azure 上,直接集成了 pg bouncer 作为 Flexible server 的解决方案。

https://docs.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-pgbouncer

Image.png

上面科普一下PG bouncer 之后, 下面我们来安装和测试一下:

1)下载社区版 http://www.pgbouncer.org/

Image.png

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 的密码生成方式:

官网文档上给出的例子

Image.png

我们来给账户: 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 进行一下性能测试 :

image.png
综合来看: 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;
复制

Image.png
连接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;
复制

Image.png

Have a fun 🙂 !

「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论