PostgreSQL 连接池 pgbouncer安装配置
0、概述
PostgreSQL由于是多进程模式，每个连接，有一个独立的进程与之交互，所以如果有大量的连接去连数据库时，性能会产生明显下降（pg14中显著提升了海量连接下的性能），严重时甚至会发生OOM。
为了解决该问题，建议使用连接池，比较常见的有pgbouncer，pgpool等。
PgBouncer是为PostgreSQL提供的轻量级连接池工具，其作用主要有：
- 能够缓存和PostgreSQL的连接，当有连接请求进来的时候，直接分配空闲进程，而不需要PostgreSQL
fork出新进程来建立连接，以节省创建新进程，创建连接的资源消耗。 - 能够有效提高连接的利用率，避免过多的无效连接，导致数据库消耗资源过大，CPU占用过高。
- 对客户端连接进行限制，预防过多或恶意的连接请求。
1、pgbouncer的连接池模式
Session pooling：
会话模式，当会话结束时，被会话占用的pgbouncer到PGDB的连接可以被其他会话复用。
特点：适用于短连接，或者其他模式不适用的场景，不能解决大并发场景连接打满或性能下降的问题（到后端的连接依旧会很多）。
Transaction pooling：
事务模式，当事务结束时，被会话占用的pgbouncer到PGDB的连接可以被其他会话复用。
特点：适用于大并发、未使用（游标、绑定变量、消息队列、特殊参数）的场景。
Statement pooling：
语句模式，当SQL执行完成后，被会话占用的pgbouncer到PGDB的连接可以被其他会话复用。
特点：适用于大并发、未使用（游标、绑定变量、消息队列、特殊参数），并且不需要多语句事务的场景（或者说autocommit的场景）
2、安装
首先，因为pgbouncer是使用libevent进行socket通信的，所有我们需要先安装libevent。
2.1、libevent安装
下载地址：https://github.com/downloads/libevent/libevent/libevent-2.0.21-stable.tar.gz
安装步骤比较简单，直接编译安装即可，这里我们使用pg的用户进行安装。
创建解压目录：
mkdir /home/pg13/libevent
解压安装：
tar -zxvf libevent-2.0.21-stable.tar.gz
cd libevent-2.0.21-stable
./configure --prefix=/home/pg13/libevent
make
make install
环境变量增加下面内容：
export PKG_CONFIG_PATH=/home/pg13/libevent/lib/pkgconfig
2.2、安装pgbouncer
下载地址：https://github.com/pgbouncer/pgbouncer
创建解压目录：
mkdir /home/pg13/pgbouncer
解压：
tar -xvf pgbouncer-1.15.0.tar.gz
cd pgbouncer-1.15.0/
安装：
–注意：这里编译的时候需要指定libevent的路径：
./configure --prefix=/home/pg13/pgbouncer/ --with-libevent=/home/pg13/libevent/
make
make install
2.3、配置pgbouncer
新建config目录和log目录：
mkdir -p /home/pg13/pgbouncer/config/
mkdir -p /home/pg13/pgbouncer/log
接下来我们将模板的配置文件拷贝过来：
cd /home/pg13/pgbouncer-1.15.0/etc
cp pgbouncer.ini /home/pg13/pgbouncer/config/
cp userlist.txt /home/pg13/pgbouncer/config/
配置文件修改如下：
vim pgbouncer.ini
[databases]
* = host=172.23.10.37 port=1921 pool_size=56
[pgbouncer]
reserve_pool_size=14
logfile = /home/pg13/pgbouncer/log/pgbouncer.log
pidfile = /home/pg13/pgbouncer/pgbouncer.pid
listen_addr = 0.0.0.0
listen_port = 4001
unix_socket_dir = /home/pg13/pgbouncer
unix_socket_mode = 0700
auth_type = md5
auth_file = /home/pg13/pgbouncer/config/userlist.txt
auth_user = bill
auth_query = select i_usename,i_passwd from get_shadow($1)
admin_users = bill
stats_users = bill
pool_mode = transaction
server_reset_query = DISCARD ALL
server_check_query = select 1
max_client_conn = 10000
default_pool_size = 28
然后将需要用到的用户密码配置到userlist.txt文件中
vim userlist.txt
"bill" "bill"
"postgres" "postgres"
2.4、启动pgbouncer
启动前先修改环境变量：
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH:/home/pg13/libevent/lib
export PATH=$PGHOME/bin:$PATH:/home/pg13/pgbouncer/bin/
然后启动：
./bin/pgbouncer -d /home/pg13/pgbouncer/config/pgbouncer.ini
pgbouncer对外提供了一个虚拟数据库pgbouncer，我们可以连接到该库中进行 相关的命令操作：
pg13@cnndr4pptliot-> psql -h localhost -p 4001 -U bill pgbouncer
Password for user bill:
psql (13beta3, server 1.15.0/bouncer)
Type "help" for help.
bill@pgbouncer=>
查看帮助：
bill@pgbouncer=>show help;
NOTICE: Console usage
DETAIL:
SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|USERS|VERSION
SHOW FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM
SHOW DNS_HOSTS|DNS_ZONES
SHOW STATS|STATS_TOTALS|STATS_AVERAGES|TOTALS
SET key = arg
RELOAD
PAUSE [<db>]
RESUME [<db>]
DISABLE <db>
ENABLE <db>
RECONNECT [<db>]
KILL <db>
SUSPEND
SHUTDOWN
SHOW
比较常用的命令：
- show clients： 用来查看客户端连接信息
- show pools： 用来查看连接池信息
- show databases：查看连接的数据库
- show config：查看相关配置
被会话占用的pgbouncer到PGDB的连接被复用前，（通过pgbouncer.ini配置文件）可以设置重置命令（重置一些会话状态），使用discard即可(配置，执行计划缓存，序列，临时表等)。
bill@pgbouncer=>\h discard
Command: DISCARD
Description: discard session state
Syntax:
DISCARD { ALL | PLANS | SEQUENCES | TEMPORARY | TEMP }
URL: https://www.postgresql.org/docs/13/sql-discard.html
参考链接：
https://github.com/pgbouncer/pgbouncer
https://www.jianshu.com/p/8650c3298f7f
https://github.com/digoal/blog/blob/master/201005/20100511_03.md