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

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

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

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

ignore_startup_parameters = extra_float_digits,search_path

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

local   all  all md5

docker rm -f gpdb6270
docker run -itd --name gpdb6270 -h gpdb6270 \
  -p 5627:5432 -p 26270:28080  \
  -v /sys/fs/cgroup:/sys/fs/cgroup \
  --privileged=true lhrbest/greenplum:6.27.0_v2 \
  /usr/sbin/init

docker exec -it gpdb6270 bash

su - gpadmin


gpstart -a
gpcc start



gpcc status
gpstate 

create user pgbouncer password 'lhr' superuser;

mkdir -p /usr/local/greenplum-db/pgbouncer
cat > /usr/local/greenplum-db/pgbouncer/pgbouncer.ini <<"EOF"
[databases]
* = host=127.0.0.1 port=5432

[pgbouncer]
pool_mode = session

listen_port = 6432
listen_addr = *

auth_type = md5
auth_user = pgbouncer
auth_file = /usr/local/greenplum-db/pgbouncer/users.txt


logfile = /usr/local/greenplum-db/pgbouncer/pgbouncer.log
pidfile = /usr/local/greenplum-db/pgbouncer/pgbouncer.pid

admin_users = pgbouncer

default_pool_size=60
max_client_conn=2000


ignore_startup_parameters = extra_float_digits,search_path

so_reuseport = 1
application_name_add_host=1

EOF


psql 
copy (select '"'||usename ||'"  "' || passwd||'"' from pg_shadow order by 1) to '/usr/local/greenplum-db/pgbouncer/users.txt';



/usr/local/greenplum-db/bin/pgbouncer -d /usr/local/greenplum-db/pgbouncer/pgbouncer.ini
/usr/local/greenplum-db/bin/pgbouncer -Rd /usr/local/greenplum-db/pgbouncer/pgbouncer.ini


[gpadmin@gpdb6270 ~]$ psql -U pgbouncer -h127.0.0.1 -p 6432 -d pgbouncer
Password for user pgbouncer: 
psql (9.4.26, server 1.21.0/bouncer)
Type "help" for help.

pgbouncer=# 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 |    application_name    | prepared_statements 
------+-----------+-----------+--------+-----------+-------+------------+------------+-------------------------+-------------------------+------+---------+--------------+-----------+------+------------+-----+------------------------+---------------------
 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
(1 row)

pgbouncer=# 

docker rm -f lhrprometheus
docker run -d --name lhrprometheus -h lhrprometheus \
 -p 2222:22 -p 23389:3389 \
 -p 29090:9090 -p 29093:9093 -p 23000:3000 \
  -v /sys/fs/cgroup:/sys/fs/cgroup   --restart=always \
 --privileged=true lhrbest/lhrprometheus:2.0 \
   /usr/sbin/init

docker exec -it lhrprometheus bash

-- 1、需要有pg_config
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

sed -i 's/gpgcheck=1/gpgcheck=0/g'  /etc/yum.repos.d/pgdg-redhat-all.repo 
sed -i 's/repo_gpgcheck = 1/repo_gpgcheck = 0/g'  /etc/yum.repos.d/pgdg-redhat-all.repo

yum install -y postgresql15 postgresql15-server postgresql15-contrib postgresql15-libs
echo "export PATH=/usr/pgsql-15/bin:$PATH" >> /etc/profile



-- 2、需要先安装依赖包 ，否则报错
./psycopg/psycopg.h:36:22: fatal error: libpq-fe.h: No such file or directory
./psycopg/psycopg.h:35:20: fatal error: Python.h: No such file or directory

 yum install -y python3-devel  libpq5 libpq5-devel
 pip3 install prometheus-pgbouncer-exporter


-- 3、启动
nohup pgbouncer-exporter --config /etc/pgbouncer-exporter/config.yml &

curl http://127.0.0.1:9100/metrics


mkdir -p /etc/pgbouncer-exporter/
cat > /etc/pgbouncer-exporter/config.yml <<EOF
# The host on which the exporter should listen to (defaults to 127.0.0.1)
exporter_host: 0.0.0.0

# The port on which the exporter should listen to (defaults to 9100)
exporter_port: 9100

# The list of pgbouncer instances to monitor
pgbouncers:
  - dsn: postgresql://pgbouncer:lhr@192.16.7.163:6543/pgbouncer
    exclude_databases:
      - pgbouncer
    extra_labels:
      pool_id: 1
EOF




-- 或者使用docker直接创建也可以
docker run -d \
  --name pgbouncer-exporter \
  -v /etc/timezone:/etc/timezone \
  -v /etc/localtime:/etc/localtime \
  -p 9127:9127 \
  -v /etc/pgbouncer-exporter/config.yml:/etc/pgbouncer-exporter/config.yml \
  --restart=unless-stopped \
  spreaker/prometheus-pgbouncer-exporter:2.0.3



-- 4、修改配置文件： vi /usr/local/prometheus/prometheus.yml

  - job_name: 'pgbouncer'
    static_configs:
    - targets: ['localhost:9100']
      labels:
        instance: 192.16.7.163:6543
        node_ip: 192.16.7.163:6543

curl -XPOST http://localhost:9090/-/reload


-- 5、grafana添加dashboards
13353

-- 1、安装
wget https://github.com/prometheus-community/pgbouncer_exporter/releases/download/v0.8.0/pgbouncer_exporter-0.8.0.linux-amd64.tar.gz
tar -zxvf pgbouncer_exporter-0.8.0.linux-amd64.tar.gz
cd pgbouncer_exporter-0.8.0.linux-amd64
mv pgbouncer_exporter /usr/local/bin/

nohup pgbouncer_exporter --pgBouncer.connectionString="postgres://pgbouncer:lhr@192.16.7.163:6543/pgbouncer?sslmode=disable" &
curl http://127.0.0.1:9127/metrics




-- 2、修改配置文件： vi /usr/local/prometheus/prometheus.yml


  - job_name: 'pgbouncer1'
    static_configs:
    - targets: ['localhost:9127']
      labels:
        instance: 192.16.7.163:6543


curl -XPOST http://localhost:9090/-/reload


-- 3、grafana添加dashboards
14022