破茧成蝶：PgBouncer在GreenPlum中的部署与优化，携手Prometheus+Grafana构建全方位性能仪表板

简介

PgBouncer工具可以用于PostgreSQL和Greenplum数据库连接的连接池。

在Greenplum数据库中使用pgBouncer作为连接池管理器是有益的，尤其是在高并发的场景下。 pgBouncer作为一个轻量级的数据库连接池管理器，它可以有效减少数据库服务器建立和销毁连接的开销，提高系统性能和稳定性。

以下是在Greenplum中使用pgBouncer的一些好处：

1. 连接复用 ：pgBouncer能够维持少量的持久连接，并让多个客户端请求共享这些连接，从而避免了频繁创建和销毁连接的开销。

2. 资源管理 ：通过限制并发连接数，可以有效地管理Greenplum数据库的资源，防止因连接数过多而导致的资源耗尽问题。

3. 性能提升 ：减少TCP连接的建立和销毁次数，可以减少网络开销和内存管理的负担，提高数据库的整体响应速度。

4. 稳定性增强 ：通过连接池，pgBouncer能够更好地处理短连接请求，避免因为连接数过多而引起的数据库崩溃风险。

5. 安全性 ：pgBouncer支持SSL加密连接，增强了数据传输过程中的安全性。

6. 易用性 ：pgBouncer配置相对简单，易于集成到现有的部署和监控体系中。

pgbouncer 官网介绍 http://www.pgbouncer.org/

配置文件：http://www.pgbouncer.org/config.html

有关使用greenplum下pgbouncer的总结

1、pgbouncer是 PostgreSQL的轻量的连接池，可以有效降低连接数，提升系统性能。Greenplum默认已经自带pgbouncer工具，无需额外安装，只是多数组织在实践中似乎并未启用此服务，也算是一种资源的浪费了。

2、pgbouncer默认存在于Greenplum数据库的bin目录下。

1[gpadmin@mdw ~]$ which pgbouncer 
2/usr/local/greenplum-db-6.26.3/bin/pgbouncer


3、pgbouncer可以配置在运行在Greenplum数据库的master上或另一台机器上，用户最好运行在Greenplum数据库的master上。

4、若配置了 [databases] 的具体值，则使用pgbouncer不能切换到其它数据库中

1postgres=# \c db1
2connection to server at "192.16.7.163", port 6432 failed: FATAL: no such database: db1
3Previous connection kept
4postgres=# \l db1
5 List of databases
6 Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
7------+-------+----------+------------+------------+------------+-----------------+-------------------
8 db1 | xxt | UTF8 | en_US.utf8 | en_US.utf8 | | libc |
9(1 row)


若想pgbouncer可以连接任意数据库，则可以在 [databases] 下配置 * =

5、 欧拉系统openEuler 22.03 LTS需要安装libevent-2.0.21包，否则会报错： /usr/local/greenplum-db/bin/pgbouncer: error while loading shared libraries: libevent-2.0.so.5: cannot open shared object file: No such file or directory

1rpm -ivh libevent-2.0.21-4.el7.x86_64.rpm --force


6、报错「pooler error: unsupported startup parameter: search_path」，或「unsupported startup parameter: extra_float_digits」

1ignore_startup_parameters = extra_float_digits,search_path


7、端口默认为6432，也可以配置6543端口。

8、我使用auth_query配置后，并不能从数据库中查询密码，可能是版本问题吧，所以 只能在创建新用户或更新用户密码后，需要先执行导出密 码 copy (select '"'||usename ||'" "' || passwd||'"' from pg_shadow order by 1) to '/usr/local/greenplum-db/pgbouncer/users.txt';

1 failed: FATAL: server login has been failing, try again later (server_login_retry)
2 failed: FATAL: bouncer config error


9、若报错「failed: FATAL: no pg_hba.conf entry for host "[local]", user "pgbouncer", database "postgres", SSL off」，可以考虑在GreenPlum的master节点的pg_hba.conf文件中配置如下内容：

1local all all md5


10、使用pgbouncer的一个缺点是：在GP库中直接查询时，只能看到客户端的IP是pgbouncer的IP。所以，若出现问题，要进行追踪是，增加了难度。但是，可以增加参数 application_name_add_host=1 用来记录客户端的真实的IP和端口，然后在的pg_stat_activity.application_name中就可以看到了。如下：

GreenPlum 环境快速部署使用并配置pgbouncer

GreenPlum 环境快速部署

1docker rm -f gpdb6270
2docker run -itd --name gpdb6270 -h gpdb6270 \
3 -p 5627:5432 -p 26270:28080 \
4 -v /sys/fs/cgroup:/sys/fs/cgroup \
5--privileged=true lhrbest/greenplum:6.27.0_v2 \
6 /usr/sbin/init
7
8docker exec -it gpdb6270 bash
9
10su - gpadmin
11
12
13gpstart -a
14gpcc start
15
16
17
18gpcc status
19gpstate 


配置pgbouncer

在mdw服务器上做如下的配置。

1createuser pgbouncer password'lhr' superuser;
2
3mkdir -p /usr/local/greenplum-db/pgbouncer
4cat > /usr/local/greenplum-db/pgbouncer/pgbouncer.ini <<"EOF"
5[databases]
6* = host=127.0.0.1 port=5432
7
8[pgbouncer]
9pool_mode = session
10
11listen_port = 6432
12listen_addr = *
13
14auth_type = md5
15auth_user = pgbouncer
16auth_file = /usr/local/greenplum-db/pgbouncer/users.txt
17
18
19logfile = /usr/local/greenplum-db/pgbouncer/pgbouncer.log
20pidfile = /usr/local/greenplum-db/pgbouncer/pgbouncer.pid
21
22admin_users = pgbouncer
23
24default_pool_size=60
25max_client_conn=2000
26
27
28ignore_startup_parameters = extra_float_digits,search_path
29
30so_reuseport = 1
31application_name_add_host=1
32
33EOF
34
35
36psql 
37copy (select'"'||usename ||'" "' || passwd||'"'from pg_shadow orderby1) to'/usr/local/greenplum-db/pgbouncer/users.txt';
38
39
40
41/usr/local/greenplum-db/bin/pgbouncer -d /usr/local/greenplum-db/pgbouncer/pgbouncer.ini
42/usr/local/greenplum-db/bin/pgbouncer -Rd /usr/local/greenplum-db/pgbouncer/pgbouncer.ini
43
44
45[gpadmin@gpdb6270 ~]$ psql -U pgbouncer -h127.0.0.1 -p 6432 -d pgbouncer
46Password for user pgbouncer: 
47psql (9.4.26, server 1.21.0/bouncer)
48Type "help" for help.
49
50pgbouncer=# show clients;
51 type | user | database | state | addr | port | local_addr | local_port | connect_time | request_time | wait | wait_us | close_needed | ptr | link | remote_pid | tls | application_name | prepared_statements 
52------+-----------+-----------+--------+-----------+-------+------------+------------+-------------------------+-------------------------+------+---------+--------------+-----------+------+------------+-----+------------------------+---------------------
53 C | pgbouncer | pgbouncer | active | 127.0.0.1 | 47030 | 127.0.0.1 | 6432 | 2024-04-19 18:18:34 CST | 2024-04-19 18:18:38 CST | 0 | 0 | 0 | 0x12cfb40 | | 0 | | psql - 127.0.0.1:47030 | 0
54(1 row)
55
56pgbouncer=# 
57


prometheus+grafana环境快速搭建

普罗米修斯+grafana的详细搭建过程，之前麦老师介绍过很多次了，这里直接使用docker来创建：

1docker rm -f lhrprometheus
2docker run -d --name lhrprometheus -h lhrprometheus \
3 -p 2222:22 -p 23389:3389 \
4 -p 29090:9090 -p 29093:9093 -p 23000:3000 \
5 -v /sys/fs/cgroup:/sys/fs/cgroup --restart=always \
6--privileged=true lhrbest/lhrprometheus:2.0 \
7 /usr/sbin/init
8
9docker exec -it lhrprometheus bash


对pgbouncer的监控

目前对pgbouncer的监控有2个工具，分别为：https://github.com/spreaker/prometheus-pgbouncer-exporter 和 https://github.com/prometheus-community/pgbouncer_exporter，下面分别介绍。

工具1：pgbouncer-exporter

缺点： 安装麻烦，2022.05.20后再没更新过

参考：https://github.com/spreaker/prometheus-pgbouncer-exporter
dashboards使用：13353 https://grafana.com/grafana/dashboards/13353-pgbouncer/

docker地址：https://hub.docker.com/r/spreaker/prometheus-pgbouncer-exporter

安装过程：

1-- 1、需要有pg_config
2yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
3
4sed -i 's/gpgcheck=1/gpgcheck=0/g' /etc/yum.repos.d/pgdg-redhat-all.repo 
5sed -i 's/repo_gpgcheck = 1/repo_gpgcheck = 0/g' /etc/yum.repos.d/pgdg-redhat-all.repo
6
7yum install -y postgresql15 postgresql15-server postgresql15-contrib postgresql15-libs
8echo "export PATH=/usr/pgsql-15/bin:$PATH" >> /etc/profile
9
10
11
12-- 2、需要先安装依赖包 ，否则报错
13./psycopg/psycopg.h:36:22: fatal error: libpq-fe.h: No such fileordirectory
14./psycopg/psycopg.h:35:20: fatal error: Python.h: No such fileordirectory
15
16 yum install -y python3-devel libpq5 libpq5-devel
17 pip3 install prometheus-pgbouncer-exporter
18
19
20-- 3、启动
21nohup pgbouncer-exporter --config /etc/pgbouncer-exporter/config.yml &
22
23curl http://127.0.0.1:9100/metrics
24
25
26mkdir -p /etc/pgbouncer-exporter/
27cat > /etc/pgbouncer-exporter/config.yml <<EOF
28# The host on which the exporter should listen to (defaults to 127.0.0.1)
29exporter_host: 0.0.0.0
30
31# The port on which the exporter should listen to (defaults to 9100)
32exporter_port: 9100
33
34# The list of pgbouncer instances to monitor
35pgbouncers:
36 - dsn: postgresql://pgbouncer:lhr@192.16.7.163:6543/pgbouncer
37 exclude_databases:
38 - pgbouncer
39 extra_labels:
40 pool_id: 1
41EOF
42
43
44
45
46-- 或者使用docker直接创建也可以
47docker run -d \
48--name pgbouncer-exporter \
49 -v /etc/timezone:/etc/timezone \
50 -v /etc/localtime:/etc/localtime \
51 -p 9127:9127 \
52 -v /etc/pgbouncer-exporter/config.yml:/etc/pgbouncer-exporter/config.yml \
53--restart=unless-stopped \
54 spreaker/prometheus-pgbouncer-exporter:2.0.3
55
56
57
58-- 4、修改配置文件： vi /usr/local/prometheus/prometheus.yml
59
60 - job_name: 'pgbouncer'
61 static_configs:
62 - targets: ['localhost:9100']
63 labels:
64instance: 192.16.7.163:6543
65 node_ip: 192.16.7.163:6543
66
67curl -XPOST http://localhost:9090/-/reload
68
69
70-- 5、grafana添加dashboards
7113353


监控结果：

监控指标：

工具2：pgbouncer_exporter

优点：安装简单，配置也简单。

参考：https://github.com/prometheus-community/pgbouncer_exporter

docker地址：https://hub.docker.com/r/prometheuscommunity/pgbouncer-exporter

1-- 1、安装
2wget https://github.com/prometheus-community/pgbouncer_exporter/releases/download/v0.8.0/pgbouncer_exporter-0.8.0.linux-amd64.tar.gz
3tar -zxvf pgbouncer_exporter-0.8.0.linux-amd64.tar.gz
4cd pgbouncer_exporter-0.8.0.linux-amd64
5mv pgbouncer_exporter /usr/local/bin/
6
7nohup pgbouncer_exporter --pgBouncer.connectionString="postgres://pgbouncer:[email protected]:6543/pgbouncer?sslmode=disable" &
8curl http://127.0.0.1:9127/metrics
9
10
11
12
13-- 2、修改配置文件： vi /usr/local/prometheus/prometheus.yml
14
15
16 - job_name: 'pgbouncer1'
17 static_configs:
18 - targets: ['localhost:9127']
19 labels:
20 instance: 192.16.7.163:6543
21
22
23curl -XPOST http://localhost:9090/-/reload
24
25
26-- 3、grafana添加dashboards
2714022


监控结果：

监控指标：

参考

GreenPlum中的pgbouncer连接池参考：https://www.xmmup.com/zaigreenplumzhongshiyongpgbouncerlianjiechi.html

PostgreSQL中的pgbouncer连接池参考：https://www.xmmup.com/pglianjiechigongjuzhipgbouncer.html