关于数据库:Pgbouncer最佳实践系列四

66次阅读

共计 4524 个字符，预计需要花费 12 分钟才能阅读完成。

作者：王志斌，曾取得中国 PostgreSQL 数据库治理工程师（PGCE），是 PostgreSQL 官网认证讲师，盘古云课堂特邀金牌讲师。

最初再来说一下对于 Pgbouncer 的部署模式，包含单利用场景、多利用场景、集群场景还有多实例场景，这些形式都是根据不同的业务场景，没有孰优孰劣，合乎的才是对的。其中单利用和多利用场景来源于官网。

单利用场景：

图 9 单利用多连贯场景结构图

单利用场景次要具体为短连贯较多的场景，频繁进行数据库的连贯操作，但操作工夫较短，均为短连贯，所以将 pgbouncer 于应用服务器部署在同一台服务器，缩小应用服务器和 pgbouncer 之间的开销。

配置文件

 [databases]
test1 =
test =
[pgbouncer]
listen_port = 6688
listen_addr = 192.168.165.3
auth_type = md5
auth_file = /home/postgres/pgbouncer/bin/userlist.txt
logfile = /home/postgres/pgbouncer/pgbouncer1.log
pidfile =/home/postgres/pgbouncer/pgbouncer1.pid
unix_socket_dir = /tmp
;;unix_socket_mode = 0777
admin_users = wzb
stats_users = wzb
pool_mode = session
max_client_conn=1000
default_pool_size=30

导出数据库中用户名及明码到 userslist.txt
userslist.txt，格局为用户名明码

 "testuser" "md54d15115d8bebd3188c1ae09c4a9848af"
"testuser1" "md5f8386abbae413786661ee5a5cfb5593c"
"wzb" "md53d57c4bc9a647385e6916efd0b44db46"

启动 Pgbouncer
pgbouncer -d pgbouncer.ini

客户端连贯形式
psql -dtest1 -Utestuser1 -p6688

多利用场景:

图 10 多利用场景结构图

多利用场景，个别指多个应用服务器连贯数据库，因而能够抉择将 pgbouncer 与数据库服务部署在同一台服务器上，缩小 pgbouncer 和数据库之间的开销。

配置 PgBouncer.ini 文件

 [databases]
a1 =  host=127.0.0.1 port=5432 dbname=test
a2 =  host=127.0.0.1 port=5432 dbname=test1
[pgbouncer]
listen_port = 6688
listen_addr = *
auth_type = md5
auth_file = /home/postgres/pgbouncer/bin/userlist.txt
logfile = /home/postgres/pgbouncer/pgbouncer.log
pidfile =/home/postgres/pgbouncer/pgbouncer.pid
admin_users = wzb
stats_users = wzb
pool_mode = session
max_client_conn=1000
default_pool_size=30

导出数据库中用户名及明码到 userslist.txt
userslist.txt，格局为用户名明码

 "testuser" "md54d15115d8bebd3188c1ae09c4a9848af"
"testuser1" "md5f8386abbae413786661ee5a5cfb5593c"
"wzb" "md53d57c4bc9a647385e6916efd0b44db46"

启动 Pgbouncer
pgbouncer -d pgbouncer.ini

连贯后端数据库
$ psql -p 6688 -U testuser a1
$ psql -p 6688 -U testuser1 a2

连贯 pgbouncer 数据库
psql -p 6688 pgbouncer -U wzb
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

pgbouncer=# show clients;

 type| C
user| pgbouncer
database| pgbouncer
state| active
addr| unix
port| 6432
local_addr| unix
local_port| 6432
connect_time| 2020-10-09 20:41:32 CST
request_time| 2020-10-09 20:41:32 CST
wait| 5
wait_us| 483185
close_needed| 0
ptr| 0x9ec340
link|
remote_pid| 23567
tls |

pgbouncer=# show pools;

 database| pgbouncer
user| pgbouncer
cl_active| 1
cl_waiting| 0
sv_active|0
sv_idle|0
sv_used|0
sv_tested|0
sv_login|0
maxwait|0
maxwait_us|0
pool_mode|  transaction

集群场景（读写拆散）:

读写拆散场景下 pgbouncer 的配置与后面配置基本一致，次要区别于要针对读和写进行别离部署 pgbouncer，因为 pgbouncer 自身只是数据库连接池，不具备负载平衡，或高可用，IP 漂移等个性，须要联合其余成熟产品进行组合应用。

多实例场景：

图 11 多实例场景结构图

多实例场景次要利用 linux 零碎端口重用技术，这个个性依附 Linux 内核上的反对（Linux3.6 以上版本），并联合 pgbouncer 本身反对（设置 so_reuseport=1）联合起来造成多实例场景下的 pgbouncer 应用，能够认为是 pgbouncer 的高牢靠或者高可用，在某一个实例过程故障的状况下，其余实例集成依然能够解决来自内部的数据库连贯申请。从操作系统层面来看，属于多过程共享同一个端口。

实例配置 1

 [databases]
a2 =  host=127.0.0.1 port=5432 dbname=test1 pool_size=50
;;a1 =  host=127.0.0.1 port=5432 dbname=test pool_size=30
[pgbouncer]
listen_port = 6688
listen_addr = 192.168.165.3
auth_type = md5
auth_file = /home/postgres/pgbouncer/bin/userlist.txt
logfile = /home/postgres/pgbouncer/pgbouncer1.log
pidfile =/home/postgres/pgbouncer/pgbouncer1.pid
unix_socket_dir = /tmp/pg1
#unix_socket_mode = 0777
admin_users = wzb
stats_users = wzb
pool_mode = session
max_client_conn=1000
default_pool_size=30
so_reuseport = 1

实例配置 2

 [databases]
a2 =  host=127.0.0.1 port=5432 dbname=test1 pool_size=50
;;a1 =  host=127.0.0.1 port=5432 dbname=test pool_size=30
[pgbouncer]
listen_port = 6688
listen_addr = 192.168.165.3
auth_type = md5
auth_file = /home/postgres/pgbouncer/bin/userlist.txt
logfile = /home/postgres/pgbouncer/pgbouncer2.log
pidfile =/home/postgres/pgbouncer/pgbouncer2.pid
unix_socket_dir = /tmp/pg2
#unix_socket_mode = 0777
admin_users = wzb
stats_users = wzb
pool_mode = session
max_client_conn=1000
default_pool_size=30
so_reuseport = 1

导出数据库中用户名及明码到 userslist.txt
userslist.txt，格局为用户名明码

 "testuser" "md54d15115d8bebd3188c1ae09c4a9848af"
"testuser1" "md5f8386abbae413786661ee5a5cfb5593c"
"wzb" "md53d57c4bc9a647385e6916efd0b44db46"

启动多实例
./pgbouncer pgbouncer.ini
./pgbouncer pgbouncer1.ini

参考
[1]Pgbouncer 官网
[2]PgBouncer Configuration
[3]Tuning PostgreSQL for sysbench-tpcc
[4]understanding-user-management-in-pgbouncer
[5]performance-best-practices-for-using-azure-database-for-postgresql-connection-pooling
[6]guide-using-pgbouncer
[7]azure-database-for-postgresql/connection-handling-best-practice-with-postgresql
[8]steps-to-install-and-setup-pgbouncer-connection-pooling-proxy
[9]pg-phriday-securing-pgbouncer

理解更多 PostgreSQL 热点资讯、新闻动态、精彩流动，请拜访中国 PostgreSQL 官方网站

解决更多 PostgreSQL 相干常识、技术、工作问题，请拜访中国 PostgreSQL 官网问答社区

下载更多 PostgreSQL 相干材料、工具、插件问题，请拜访中国 PostgreSQL 官网下载网站

正文完

数据库

发表至：数据库

2021-03-04

0

关于数据库:活动速递｜-Apache-Doris-性能优化实战系列直播课程初公开诚邀您来参加

关于数据库:数字化与冠军不得不说的故事

关于数据库:面试官如何优化索引

关于数据库:唯一上榜图数据库悦数图数据库入选中国信通院高质量数字化转型产品及服务全景图2023

关于git:更新CentOS自带的-Git-版本-Git-2x

	[databases]
	test1 =
	test =
	[pgbouncer]
	listen_port = 6688
	listen_addr = 192.168.165.3
	auth_type = md5
	auth_file = /home/postgres/pgbouncer/bin/userlist.txt
	logfile = /home/postgres/pgbouncer/pgbouncer1.log
	pidfile =/home/postgres/pgbouncer/pgbouncer1.pid
	unix_socket_dir = /tmp
	;;unix_socket_mode = 0777
	admin_users = wzb
	stats_users = wzb
	pool_mode = session
	max_client_conn=1000
	default_pool_size=30

	"testuser" "md54d15115d8bebd3188c1ae09c4a9848af"
	"testuser1" "md5f8386abbae413786661ee5a5cfb5593c"
	"wzb" "md53d57c4bc9a647385e6916efd0b44db46"

	[databases]
	a1 = host=127.0.0.1 port=5432 dbname=test
	a2 = host=127.0.0.1 port=5432 dbname=test1
	[pgbouncer]
	listen_port = 6688
	listen_addr = *
	auth_type = md5
	auth_file = /home/postgres/pgbouncer/bin/userlist.txt
	logfile = /home/postgres/pgbouncer/pgbouncer.log
	pidfile =/home/postgres/pgbouncer/pgbouncer.pid
	admin_users = wzb
	stats_users = wzb
	pool_mode = session
	max_client_conn=1000
	default_pool_size=30

	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

	type\| C
	user\| pgbouncer
	database\| pgbouncer
	state\| active
	addr\| unix
	port\| 6432
	local_addr\| unix
	local_port\| 6432
	connect_time\| 2020-10-09 20:41:32 CST
	request_time\| 2020-10-09 20:41:32 CST
	wait\| 5
	wait_us\| 483185
	close_needed\| 0
	ptr\| 0x9ec340
	link\|
	remote_pid\| 23567
	tls \|

	database\| pgbouncer
	user\| pgbouncer
	cl_active\| 1
	cl_waiting\| 0
	sv_active\|0
	sv_idle\|0
	sv_used\|0
	sv_tested\|0
	sv_login\|0
	maxwait\|0
	maxwait_us\|0
	pool_mode\| transaction

	[databases]
	a2 = host=127.0.0.1 port=5432 dbname=test1 pool_size=50
	;;a1 = host=127.0.0.1 port=5432 dbname=test pool_size=30
	[pgbouncer]
	listen_port = 6688
	listen_addr = 192.168.165.3
	auth_type = md5
	auth_file = /home/postgres/pgbouncer/bin/userlist.txt
	logfile = /home/postgres/pgbouncer/pgbouncer1.log
	pidfile =/home/postgres/pgbouncer/pgbouncer1.pid
	unix_socket_dir = /tmp/pg1
	#unix_socket_mode = 0777
	admin_users = wzb
	stats_users = wzb
	pool_mode = session
	max_client_conn=1000
	default_pool_size=30
	so_reuseport = 1

关于数据库:Pgbouncer最佳实践系列四

Just My Socks（注册教程 内含优惠码）

Just My Socks（注册教程内含优惠码）