PostgreSQL 数据库性能 PgBouncer
1 本章背景知识
1、在数据库中，经常会有频繁的短连接链接到数据库上，造成较大的性能开销。
2、解决办法还是计算机历史上最通用的方法，加入中间层。
3、这个中间层就是连接池 [PgBouncer](PgBouncer - lightweight connection pooler for PostgreSQL。
1.1 安装方法
1、对于RHEL/Centos 平台使用 yum 命令。
2、对于Debian/Ubuntu 平台使用 apt 命令。
3、源代码编译。
1.2 环境准备
CREATE USER pgbench WITH password '123456';
CREATE DATABASE pgbench OWNER pgbench;
pgbench -U pgbench -i pgbench -p 5432
2 PgBouncer 配置文件详解
2.1 通用描述符号
1、配置文件采用 ini 格式。
2、每个功能模块的设置用 [] 分割。
3、注释以 ; 和 #。
2.2 pgbouncer 的参数详解
PgBouncer 通用选项
PgBouncer 认证参数
PgBouncer userlist.txt
未完，更多请参考官方文档。
3 YUM 安装与配置
yum install pgbouncer -y
4 源代码安装与配置
1、上传 PgBouncer 源码到 /soft 文件夹。
2、解压 PgBouncer 源码。
su - root
tar -zxf pgbouncer-1.20.1.tar.gz
cd pgbouncer*/
3、配置 PgBouncer 环境包。
su - root
yum install libevent-devel -y
4、配置编辑环境
su - root
./configure --with-libevent=libevent-prefi --with-pam --with-systemd
5、编译并安装
su - root
make -j 8
make install
//屏幕输出：
INSTALL pgbouncer /usr/local/bin
INSTALL README.md /usr/local/share/doc/pgbouncer
INSTALL NEWS.md /usr/local/share/doc/pgbouncer
INSTALL etc/pgbouncer-minimal.ini /usr/local/share/doc/pgbouncer
INSTALL etc/pgbouncer.ini /usr/local/share/doc/pgbouncer
INSTALL etc/pgbouncer.service /usr/local/share/doc/pgbouncer
INSTALL etc/pgbouncer.socket /usr/local/share/doc/pgbouncer
INSTALL etc/userlist.txt /usr/local/share/doc/pgbouncer
INSTALL doc/pgbouncer.1 /usr/local/share/man/man1
INSTALL doc/pgbouncer.5 /usr/local/share/man/man5
4.1 配置 PgBouncer systemd 管理
1、将服务管理文件放置在系统中。
cp /usr/local/share/doc/pgbouncer/pgbouncer.service /etc/systemd/system/
2、编辑 systemd 服务管理文件
vi /etc/systemd/system/pgbouncer.service
--------------------input------------------------------
# Example systemd service unit for PgBouncer
#
# - Adjust the paths in ExecStart for your installation.
#
# 5 The User setting requires careful consideration. PgBouncer needs
# to be able to place a Unix-domain socket file where PostgreSQL
# clients will look for it. In the olden days, this was in /tmp,
# but systems using systemd now prefer something like
# /var/run/postgresql/. But then some systems also lock down that
# directory so that only the postgres user can write to it. That
# means you need to either
#
# - run PgBouncer as the postgres user, or
#
# - create a separate user and add it to the postgres group and
# make /var/run/postgresql/ group-writable, or
#
# - use systemd to create the sockets; see pgbouncer.socket nearby.
#
# For packagers and deployment systems, this requires some
# coordination between the PgBouncer and the PostgreSQL
# packages/components.
#
[Unit]
Description=connection pooler for PostgreSQL
Documentation=man:pgbouncer(1)
Documentation=https://www.pgbouncer.org/
After=network.target
#Requires=pgbouncer.socket
[Service]
Type=notify
User=postgres
ExecStart=/usr/local/bin/pgbouncer /etc/pgbouncer/pgbouncer.ini
ExecReload=/bin/kill -HUP $MAINPID
KillSignal=SIGINT
#LimitNOFILE=1024
[Install]
WantedBy=multi-user.target
systemctl daemon-reload
4.2 配置pgbouncer 的启动环境
mkdir -p /var/log/pgbouncer/p
mkdir -p /var/log/pgbouncer/
chown postgres:postgres /var/log/pgbouncer/
mkdir -p /var/run/pgbouncer/
chown postgres:postgres /var/run/pgbouncer/
4.3 配置文件相关路径
mkdir -p /etc/pgbouncer
cp /usr/local/share/doc/pgbouncer/pgbouncer.ini /etc/pgbouncer/pgbouncer.ini
cp /usr/local/share/doc/pgbouncer/userlist.txt /etc/pgbouncer/userlist.txt
cp /soft/pgbouncer-1.20.1/etc/mkauth.py /etc/pgbouncer/mkauth.py
4.4 配置 pgbouncer.ini
vi /etc/pgbouncer/pgbouncer.ini
--------------------input------------------------------
;;;
;;; PgBouncer configuration file
;;;
;; database name = connect string
;;
;; connect string params:
;; dbname= host= port= user= password= auth_user=
;; client_encoding= datestyle= timezone=
;; pool_size= reserve_pool= max_db_connections=
;; pool_mode= connect_query= application_name=
[databases]
* = user=postgres
[pgbouncer]
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
listen_addr = *
listen_port = 6432
auth_type = md5
max_client_conn =150
auth_file = /etc/pgbouncer/userlist.txt
admin_users = postgres
stats_users = stats, postgres
4.5 配置 userlist.txt
1、安装python 连接驱动
su - root
pip3 install psycopg2-binary
su - root
/etc/pgbouncer/mkauth.py /etc/pgbouncer/userlist.txt "host=127.0.0.1 user=postgres password=postgres"
chmod +r /etc/pgbouncer/userlist.txt
4.6 启动 pgbouncer 服务
su - root
systemctl start pgbouncer ;
4.7 验证是否能够登录
1、登录数据库。
psql -p 6432 -U postgres -d postrges
// 可以进行数据的正常操作
2、登录 pgbouncer 虚拟库。
psql -p 6432 -d pgbouncer -U postgres -h 127.0.0.1
// 对pgbouncer 虚拟机配置。
show help;
show nodes;
show pools;
5 性能优化
5.1 不使用连接池
pgbench -M extended -v -r -P 1 -S -C -c 100 -j 100 -T 60 -p 5432 -Upostgres pgbench
//屏幕输出：
starting vacuum...end.
starting vacuum pgbench_accounts...end.
progress: 1.0 s, 2101.5 tps, lat 3.628 ms stddev 3.776
... ... ... ...
progress: 60.0 s, 2055.2 tps, lat 3.280 ms stddev 3.661
transaction type: <builtin: select only>
scaling factor: 1
query mode: extended
number of clients: 100
number of threads: 100
duration: 60 s
number of transactions actually processed: 124563
latency average = 3.253 ms
latency stddev = 3.463 ms
tps = 2074.563646 (including connections establishing)
tps = 30296.505074 (excluding connections establishing)
statement latencies in milliseconds:
0.002 \set aid random(1, 100000 * :scale)
3.252 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
5.2 使用连接池
pgbench -M extended -v -r -P 1 -S -C -c 100 -j 100 -T 60 -p 6432 -Upostgres pgbench
Password:
starting vacuum...end.
starting vacuum pgbench_accounts...end.
progress: 1.0 s, 1456.1 tps, lat 47.392 ms stddev 8.435
... ... ... ...
progress: 60.0 s, 1604.0 tps, lat 46.241 ms stddev 4.681
transaction type: <builtin: select only>
scaling factor: 1
query mode: extended
number of clients: 100
number of threads: 100
duration: 60 s
number of transactions actually processed: 96140
latency average = 45.566 ms
latency stddev = 5.104 ms
tps = 1601.821974 (including connections establishing)
tps = 2193.650591 (excluding connections establishing)
statement latencies in milliseconds:
0.009 \set aid random(1, 100000 * :scale)
45.557 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;