Fork me on GitHub

daydayup863

人生就像一杯茶，不会苦一辈子，但总会苦一阵子。

0%

Pgbouncer 源码安装

置顶 | 发表于 分类于 ， ， 阅读次数： Valine：

什么是pgbouncer

PgBouncer是PostgreSQL的数据库提供的一个轻量级连接池工具，PgBouncer在数据库和应用端建立连接，由PgBouncer处理与后端数据库连接关系。
通过对应用端的连接限流，减少对数据库的连接请求，避免恶意连接。其工作是每一个数据节点对数据的存取，pgbouncer只是提供给客户端数据节点的
分配和链接以及限制客户端连接的数量。整体上实现了对数据的负载均衡。能够缓存和PostgreSQL的连接，当有连接请求进来的时候，直接分配空闲进程，
而不需要PostgreSQL fork出新进程来建立连接，以节省创建新进程，创建连接的资源消耗。能够有效提高连接的利用率，避免过多的无效连接，导致数据库消耗资源过大，CPU占用过高。

目前支持以下3中连接池模型：

Session：会话级别，在session生命周期内，连接池分配给该会话一个数据库连接，断开会话后，数据库连接会放回连接池中；
Transaction：事务级别，开启一个事务，从连接池中获取一个连接，事务结束后，连接会释放到连接池中；
Statement：语句级别，执行一个SQL，开启一个连接，SQL执行完成，连接会释放到连接池中。

Pgbouncer的优劣势

优点

  1. 连接数利用率提高
    由于PostgreSQL是进程模型，连接数也是很宝贵的资源，当连接长时间空闲时, 可以释放掉，只保留部分连接即可，一定程度上提高了连接的利用率（主要表现为idle的数量减少）。

  2. 降低高并发对PostgreSQL的冲击
    客户端无连接池或弱连接池（php 或 python 脚本等，Pgbouncer 可以提供一个轻量级连接池，应用与pgbouncer建立连接的开销远小于直接与DB建立的开销, 一定程度上可以提高数据库的响应时间（主要表现为缓解大量连接时降低load）。

缺点

  1. 需要对Pgbouncer进行额外的监控和维护成本.

PostgreSQL 14版本对大量连接做了优化，如果不想额外维护Pgbouncer, 又有大量连接数，升级PostgreSQL 14是一个不错的选择。

Pgbouncer安装

编译Pgbouncer需要提前安装如下依赖包:

依赖包版本必选
GNU Make3.81+是
Libevent2.0+是
pkg-config是
OpenSSL for TLS support1.0.1+是
c-ares否
PAM libraries否

下载

Pgbouncer对应的下载地址为 https://github.com/pgbouncer/pgbouncer , 通常选择最新一个版本即可。

1
wget https://github.com/pgbouncer/pgbouncer/releases/download/pgbouncer_1_17_0/pgbouncer-1.17.0.tar.gz -P /export/

解压

1
tar -zxvf /export/pgbouncer-1.17.0.tar.gz -C /export

configure选项

除了常规选项，主要有如下可选内容：

1
2
3
4
5
6
7
--with-pam               支持PAM
--with-systemd           支持systemd
--with-cares[=PREFIX]    支持c-ares
--with-udns[=PREFIX]     支持udns
--without-openssl        不支持OpenSSL
--with-openssl[=PREFIX]  支持定制OpenSSL
--with-root-ca-file=FILE 定制根 CA 证书

通常情况下只需要选择默认方式, 定制安装路径即可:

1
/export/pgbouncer-1.17.0/configure --prefix=/pgccc/software/pgbouncer

输出如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
checking build system type... x86_64-pc-linux-gnu
checking host system type... x86_64-pc-linux-gnu
checking target host type... unix
checking for gcc... gcc
checking whether the C compiler works... yes
checking for C compiler default output file name... a.out
checking for suffix of executables...
checking whether we are cross compiling... no
...
checking for root CA certs... /etc/ssl/certs/ca-certificates.crt
checking whether to build debug binary... yes
checking whether to enable asserts... no
checking whether to fail on warnings... no
configure: creating ./config.status
config.status: creating config.mak
config.status: creating lib/usual/config.h
config.status: lib/usual/config.h is unchanged

Results:
  adns    = c-ares
  pam     = no
  systemd = no
  tls     = yes

编译 && 安装

只需执行make即可完成源码编译。

1
make

输出如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
CC       src/admin.c
CC       src/client.c
CC       src/dnslookup.c
CC       src/hba.c
CC       src/janitor.c
CC       src/loader.c
CC       src/main.c
CC       src/objects.c
CC       src/pam.c
CC       src/pktbuf.c
CC       src/pooler.c
CC       src/proto.c
CC       src/sbuf.c
CC       src/scram.c
CC       src/server.c
...
...
CC       lib/usual/tls/tls_compat.c
CC       lib/usual/tls/tls_config.c
CC       lib/usual/tls/tls_conninfo.c
CC       lib/usual/tls/tls_ocsp.c
CC       lib/usual/tls/tls_peer.c
CC       lib/usual/tls/tls_server.c
CC       lib/usual/tls/tls_util.c
CC       lib/usual/tls/tls_verify.c
CCLD     pgbouncer

执行make install 即可完成安装:

1
make install

输出如下:

1
2
3
4
5
6
7
INSTALL  pgbouncer /pgccc/software/pgbouncer/bin
INSTALL  README.md /pgccc/software/pgbouncer/share/doc/pgbouncer
INSTALL  NEWS.md /pgccc/software/pgbouncer/share/doc/pgbouncer
INSTALL  etc/pgbouncer.ini /pgccc/software/pgbouncer/share/doc/pgbouncer
INSTALL  etc/userlist.txt /pgccc/software/pgbouncer/share/doc/pgbouncer
INSTALL  doc/pgbouncer.1 /pgccc/software/pgbouncer/share/man/man1
INSTALL  doc/pgbouncer.5 /pgccc/software/pgbouncer/share/man/man5

配置

新建配置文件和日志保留路径

1
mkdir /pgccc/software/pgbouncer/{etc,log,run}

拷贝默认配置文件pgbouncer.ini,userlist.txt到新建配置文件路径下：

1
cp /export/pgbouncer-1.17.0/etc/{pgbouncer.ini,userlist.txt} /pgccc/software/pgbouncer/etc/

按需求修改配置pgbouncer.ini, 主要参数如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
[databases]
*= host=localhost port=5432 user=pgccc password=pgcccnewpassword pool_size=200

[users]
pgccc = pool_mode=transaction max_user_connections=10

[pgbouncer]

logfile = /pgccc/software/pgbouncer/log/pgbouncer.log
pidfile = /pgccc/software/pgbouncer/run/pgbouncer.pid

listen_addr = *
listen_port = 6432

auth_type = md5
auth_file = /pgccc/software/pgbouncer/etc/userlist.txt

pool_mode = session

admin_users = postgres, pgccc
stats_users = postgres

client_idle_timeout=20
server_idle_timeout=1800
idle_transaction_timeout=600
server_reset_query = DISCARD ALL;
server_check_query = select 1
server_check_delay = 10

log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
ignore_startup_parameters = extra_float_digits

tcp_keepalive = 1
tcp_keepcnt = 5
tcp_keepidle = 200
tcp_keepintvl = 20

max_client_conn = 600

default_pool_size = 100
min_pool_size = 30
reserve_pool_size = 20
reserve_pool_timeout = 5
max_db_connections = 128
max_user_connections = 0

用户密码userlist.txt配置文件修改:

1
2
3
"marko" "asdasd"
"postgres" "asdasd"
"pgbouncer" "fake"

需要注意的是如果database配置选项中没有配置数据库用户密码时，要求userlist.txt中对应的帐号密码与数据库中的保持一致。

启动

加上选项 -d ,以后台方式运行Pgbouncer

1
/pgccc/software/pgbouncer/bin/pgbouncer -d /pgccc/software/pgbouncer/etc/pgbouncer.ini

重新加载(reload)

当修改了某些参数时如pool_size等时，不需要重新启动Pgbouncer,只需reload即可, 只需增加选项 -R 即可，完整命令如下：

1
/pgccc/software/pgbouncer/bin/pgbouncer -d -R /pgccc/software/pgbouncer/etc/pgbouncer.ini

输出如下:

1
2
3
4
5
6
7
8
2022-05-20 16:21:00.740 CST [346654] LOG takeover_init: launching connection
2022-05-20 16:21:00.740 CST [346654] LOG S-0x55e8b1da3110: pgbouncer/pgbouncer@unix:6432 new connection to server
2022-05-20 16:21:00.740 CST [346654] LOG S-0x55e8b1da3110: pgbouncer/pgbouncer@unix:6432 login OK, sending SUSPEND
2022-05-20 16:21:00.740 CST [346654] LOG SUSPEND finished, sending SHOW FDS
2022-05-20 16:21:00.740 CST [346654] LOG got pooler socket: 127.0.0.1:6432
2022-05-20 16:21:00.740 CST [346654] LOG got pooler socket: unix:6432
2022-05-20 16:21:00.740 CST [346654] LOG SHOW FDS finished
2022-05-20 16:21:00.740 CST [346654] LOG disko over, going background

Pgbouncer三种pool_mode下的测试

连接池模式连接数协议tps
session 模式100simple
session 模式500simple
session 模式1000simple
session 模式100prepared
session 模式500prepared
session 模式1000prepared
transaction100simple
transaction500simple
transaction1000simple
transaction100prepared
transaction500prepared
transaction1000prepared
statement100simple
statement500simple
statement1000simple
statement100prepared
statement500prepared
statement1000prepared
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
[postgres@l-pghadb5.pgdba.cn6 ~]$ PGPASSWORD=pgcccnewpassword /opt/pg14/bin/pgbench -S -c 100 -j 100 -M prepared -T30 -p 5432 -h l-pghadb4.pgdba.cn6 -Upgccc -P 3 -n postgres
pgbench (14.2, server 11.13)
progress: 3.0 s, 413883.9 tps, lat 0.239 ms stddev 0.153
progress: 6.0 s, 459776.6 tps, lat 0.217 ms stddev 0.087
progress: 9.0 s, 475252.4 tps, lat 0.210 ms stddev 0.089
progress: 12.0 s, 483404.0 tps, lat 0.207 ms stddev 0.095
progress: 15.0 s, 485784.9 tps, lat 0.206 ms stddev 0.094
progress: 18.0 s, 489966.8 tps, lat 0.204 ms stddev 0.082
progress: 21.0 s, 488520.2 tps, lat 0.205 ms stddev 0.096
progress: 24.0 s, 493462.1 tps, lat 0.203 ms stddev 0.087
progress: 27.0 s, 492076.9 tps, lat 0.203 ms stddev 0.096
transaction type: <builtin: select only>
scaling factor: 200
query mode: prepared
number of clients: 100
number of threads: 100
duration: 30 s
number of transactions actually processed: 14327013
latency average = 0.209 ms
latency stddev = 0.099 ms
initial connection time = 41.436 ms
tps = 478180.366320 (without initial connection time)
[postgres@l-pghadb5.pgdba.cn6 ~]$
[postgres@l-pghadb5.pgdba.cn6 ~]$ PGPASSWORD=pgcccnewpassword /opt/pg14/bin/pgbench -S -c 100 -j 100 -M prepared -T30 -p 7432 -h l-pghadb4.pgdba.cn6 -Upgccc -P 3 -n postgres
pgbench (14.2, server 11.16)
progress: 3.0 s, 61590.9 tps, lat 1.620 ms stddev 0.123
progress: 6.0 s, 63417.7 tps, lat 1.577 ms stddev 0.029
progress: 9.0 s, 63149.7 tps, lat 1.583 ms stddev 0.037
progress: 12.0 s, 63803.3 tps, lat 1.567 ms stddev 0.039
progress: 15.0 s, 64319.0 tps, lat 1.555 ms stddev 0.050
progress: 18.0 s, 59858.7 tps, lat 1.671 ms stddev 0.028
progress: 21.0 s, 60102.7 tps, lat 1.664 ms stddev 0.029
progress: 24.0 s, 60390.3 tps, lat 1.656 ms stddev 0.028
progress: 27.0 s, 60558.6 tps, lat 1.651 ms stddev 0.028
transaction type: <builtin: select only>
scaling factor: 200
query mode: prepared
number of clients: 100
number of threads: 100
duration: 30 s
number of transactions actually processed: 1852727
latency average = 1.618 ms
latency stddev = 0.066 ms
initial connection time = 19.124 ms
tps = 61788.858604 (without initial connection time)
-------------本文结束感谢您的阅读-------------
听说，打赏我的人都找到了真爱

欢迎关注我的其它发布渠道