作者:王志斌,曾取得中国PostgreSQL数据库治理工程师(PGCE),是PostgreSQL官网认证讲师,盘古云课堂特邀金牌讲师。
最初再来说一下对于Pgbouncer的部署模式,包含单利用场景、多利用场景、集群场景还有多实例场景,这些形式都是根据不同的业务场景,没有孰优孰劣,合乎的才是对的。其中单利用和多利用场景来源于官网。
单利用场景:
图 9 单利用多连贯场景结构图
单利用场景次要具体为短连贯较多的场景,频繁进行数据库的连贯操作,但操作工夫较短,均为短连贯,所以将pgbouncer于应用服务器部署在同一台服务器,缩小应用服务器和pgbouncer之间的开销。
配置文件
[databases]test1 =test =[pgbouncer]listen_port = 6688listen_addr = 192.168.165.3auth_type = md5auth_file = /home/postgres/pgbouncer/bin/userlist.txtlogfile = /home/postgres/pgbouncer/pgbouncer1.logpidfile =/home/postgres/pgbouncer/pgbouncer1.pidunix_socket_dir = /tmp;;unix_socket_mode = 0777admin_users = wzbstats_users = wzbpool_mode = sessionmax_client_conn=1000default_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=testa2 = host=127.0.0.1 port=5432 dbname=test1[pgbouncer]listen_port = 6688listen_addr = *auth_type = md5auth_file = /home/postgres/pgbouncer/bin/userlist.txtlogfile = /home/postgres/pgbouncer/pgbouncer.logpidfile =/home/postgres/pgbouncer/pgbouncer.pidadmin_users = wzbstats_users = wzbpool_mode = sessionmax_client_conn=1000default_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| Cuser| pgbouncerdatabase| pgbouncerstate| activeaddr| unixport| 6432local_addr| unixlocal_port| 6432connect_time| 2020-10-09 20:41:32 CSTrequest_time| 2020-10-09 20:41:32 CSTwait| 5wait_us| 483185close_needed| 0ptr| 0x9ec340link|remote_pid| 23567tls |
pgbouncer=# show pools;
database| pgbounceruser| pgbouncercl_active| 1cl_waiting| 0sv_active|0sv_idle|0sv_used|0sv_tested|0sv_login|0maxwait|0maxwait_us|0pool_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 = 6688listen_addr = 192.168.165.3auth_type = md5auth_file = /home/postgres/pgbouncer/bin/userlist.txtlogfile = /home/postgres/pgbouncer/pgbouncer1.logpidfile =/home/postgres/pgbouncer/pgbouncer1.pidunix_socket_dir = /tmp/pg1#unix_socket_mode = 0777admin_users = wzbstats_users = wzbpool_mode = sessionmax_client_conn=1000default_pool_size=30so_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 = 6688listen_addr = 192.168.165.3auth_type = md5auth_file = /home/postgres/pgbouncer/bin/userlist.txtlogfile = /home/postgres/pgbouncer/pgbouncer2.logpidfile =/home/postgres/pgbouncer/pgbouncer2.pidunix_socket_dir = /tmp/pg2#unix_socket_mode = 0777admin_users = wzbstats_users = wzbpool_mode = sessionmax_client_conn=1000default_pool_size=30so_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官网下载网站