破繭成蝶：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