当前位置: 首页 > 工具软件 > pgbouncer > 使用案例 >

PostgreSQL系列- 6 - PgBouncer连接池

尚棋
2023-12-01

目录

PgBouncer连接池... 1

1、为什么要使用连接池... 1

2、软件安装... 1

3、连接池的配置文件... 1

4、PgBouncer启动和管理... 7

1、为什么要使用连接池

1.1 能够缓存和PostgreSQL的连接,当有连接请求进来的时候,直接分配空闲进程,而不需要PostgreSQL fork出新进程来建立连接,以节省创建新进程,创建连接的资源消耗。

1.2 能够有效提高连接的利用率,避免过多的无效连接,导致数据库消耗资源过大,CPU占用过高。

1.3 对客户端连接进行限制,预防过多或恶意的连接请求。

2、软件安装

yum install pgbouncer

安装完成以后,修改配置:

3、连接池的配置文件

PgBouncer连接池需要两个配置文件, pgbouncer.ini auth_file文件。

3.1 pgbouncer.ini

文件分为三部分内容:

a. 需要连接到的数据库信息,包括数据库主机、端口、数据库名称等

b. 连接池信息,包括连接池模式、监听端口、监听主机、用户连接认证类型、日志文件、管理用户等。(

支持三种连接池模型:

session,会话级连接,连接生命周期里,连接池分配一个数据库连接,客户端断开连接时,连接放回连接池。

transaction 事务级连接,客户端的每个事务结束时,数据库连接就会重新释放回连接池,在执行一个事务时,就需要从连接池重新获得一个连接。

statement,语句级别连接 ,执行完一个SQL语句时,连接就会释放回连接池,再次执行一个SQL语句时,需要重新从连接池里获取连接,这种模式意味客户端需要强制“autocommit”模式。

c. 用户认证信信息,指定用户认证文件的名称和用户信息加密方式


详细配置信息
使用系统自带工具安装的PgBouncer的配置文件路径是/etc/pgbouncer/pgbouncer.ini,默认的配置和含义如下:

[databases] 配置 ,添加需要代理的数据库链接

代理的数据库名称 =  host=127.0.0.1  port=5432 user=db_admin_user dbname=test_db  password=123456 connect_query='SELECT 1' pool_size=50

如:

dctadb = host=master port=5432 dbname=dbname

dctsdb = host=master port=5432 dbname=dbname user=xx_xx

[pgbouncer] 配置

这个部分的配置项比较多,主要分为下面几类:

通用配置项

日志配置项

控制界面访问控制配置项

连接健康检查和超时配置项

危险的超时配置项

底层网络配置项

【通用配置】

logfile 指定日志文件,默认值是/var/log/pgbouncer/pgbouncer.log

pidfile 指定pid文件位置,默认值是/var/run/pgbouncer/pgbouncer.pid

listen_addr  监听的地址,默认值是127.0.0.1,可以使用*号表示监听所有IP地址。

listen_port 监听的端口,默认值是6432

unix_socket_dir 指定unix socket文件的目录,默认为/tmp目录

unix_socket_mode 指定unix socket文件的权限,默认值为0777

unix_socket_group 指定unix socket文件的组,默认无

user  指定启动PgBouncer的用户名,windows系统不支持此设置

auth_type 认证的类型,默认是trust,其他值包括md5cryptplainany。用的较多的是md5

auth_file 认证文件的位置,默认值是/etc/pgbouncer/userlist.txt

pool_mode 指定池的模式,默认是session模式,还可以是transactionstatement

max_client_conn  允许的最大连接数

default_pool_size  默认的池大小,默认值20

min_pool_size  最小的池大小,每个连接池至少会向后端数据库保持多少个连接

reserve_pool_size  连接池的保留连接数

reserve_pool_timeout  保留连接的超时时间

server_round_robin  负载均衡的方式是否设置为“round robin”,默认为关闭,即后进先出

ignore_startup_parameters 默认情况PgBouncer只会跟踪一些默认参数,并且能检测这些参数的变化,保持这些参数和客户端的一致。在这个配置后面跟的配置后被PgBouncer忽略,不会被检查。

disable_pqexec  是否禁止简单查询协议,默认值为0。简单查询协议允许一个请求发送多个SQL,容易导致SQL注入攻击。

【日志配置项】

syslog  是否打开syslogwindows下打开eventlog,默认值为0,表示不打开。

syslog_ident  默认为PgBouncer

syslog_facility

log_connections  是否记录连接成功的日志,默认值为1,表示记录

log_disconnections 是否记录断开连接的日志,默认值为1,表示记录

log_pooler_errors 连接池法网客户端的错误是否记录在日志中,默认值为1,表示记录

stats_period 把汇总的统计信息写入日志的时间周期,默认是60s

【控制界面访问控制配置项】

admin_users  管理用户名,默认值是postgres

stats_users  允许连接到控制界面,查看连接池只读信息的用户列表。可以执行除“SHOW FDS”以外的其他“SHOW”命令

server_reset_query

server_check_delay 空闲的连接多长时间进行一次健康检测,判断连接是否可用。如果设置为0,则立即检测,默认值为30s

server_check_query,进行健康检查的SQL语句,如果为0,表示不检测,默认值为“select 1;”

server_lifetime  连接的存活时间,连接超过这个时间就会被关闭,默认为3600,设置为0表示只使用一次。

server_idle_timeout  连接的idle时间,超过此时间,连接会被关闭。默认为600

server_connect_timeout  后端数据库的login时间超过这个值就会被关闭。默认为15s

server_login_retry  传教到后端数据库的连接失败后,等多长时间后重试,默认为15s

client_login_timeout  客户端与PgBouncer建立连接后,如果无法在这段时间内完成登录,那么连接会断开,默认为60s

【危险超时配置项】

指的是为防止一些未知错误或者原因导致系统卡住的针对性配置。

query_timeout  允许超过该时间值的SQL会被断开,应该比SQL实际的执行时间稍长,也需要比数据库的statement_timeout参数值更大。为了应付一些未知的网络问题。默认为0.0,禁止使用

query_wait_timeout  请求在队列中等待被执行的最长时间,如果超过该时间还没有分配到连接,就会断开。默认为0,禁止使用。

client_idle_timeout  客户端连接空闲超过该时间,则断开连接。默认值为0,禁止使用

idle_transaction_timeout 客户端启动事务后,超过这个时间没有结束事务,则关闭这个客户端连接。默认值为0,禁止使用

【底层网络连接配置】

pkt_buf  用于网络包的内部缓冲区大小,会影响发出的TCP包的大小即内存的使用,默认值为2048,一般保持默认值

max_packet_size  通过PgBouncer最大的包大小,包可以是一个SQL,也可以是返回的结果,默认值是2147483647

listen_backlog  TCP监听函数listenBacklog参数,默认值为128

sbuf_loopcnt   处理过程中,每个连接处理多少数据就切换到下一个连接。默认为5,如果设置为0,表示不限制。不限制时,一个连接发送大量数据,另外的连接可能就会空闲,导致被结束掉。

tcp_defer_accept linux下,默认为45,其他平台为0。详细解释用man 7 tcp来查看

tcp_socket_buffer  默认没有设置

tcp_keepalive 是否以操作系统的默认值打开基本的keepalive 设置,在linux下,操作系统的keepalive里,默认值时tcp_keepidle=7200, tcp_keepintvl-75,tcp_keepcnt=9,其他操作系统类型,默认值为1

tcp_keepcnt  默认未设置

tcp_keepidle 默认未设置

tcp_keepintvl 默认未设置

上面的这些参数,大部分都是采用默认设置,常用的几个配置也就是pgbouncer.ini文件的基本默认设置:
listen_addr = 127.0.0.1

listen_port = 6432

auth_type = trust

auth_file = /etc/pgbouncer/userlist.txt

admin_users = postgres

stats_users = stats, postgres

pool_mode = session

server_reset_query = DISCARD ALL

max_client_conn = 100

default_pool_size = 20

3.2 auth_file

修改/etc/pgbouncer/userlist.txt 
首先去数据库获得数据库相关用户的帐号以及密码(md5值)

postgres=# select usename, passwd from pg_shadow order by 1; 
vim /etc/pgbouncer/userlist.txt
“dbuser” “md5baa6c789c82005eb54a19”

注意,双引号是不可少的,里面分别为帐号和密码。

3.3检查pg数据库是否开启md5认证

检查pg_hba.conf 然后看到:

这是默认的配置,没有开启md5认证,刚才我们的认证都是md5的,请在:

将下面的该一改

host    all       all             127.0.0.1/32            ident

改为:

host    all       all             127.0.0.1/32            md5

 

3.4 示例

pgbouncer.ini示例

[databases]

dctadb = host=master port=5432 dbname=dbname

dctsdb = host=master port=5432 dbname=dbname user=xx_xx

​

[pgbouncer]

pool_mode = session

listen_port = 7000

listen_addr = smaster

auth_type = md5

auth_file = /etc/pgbouncer/userlist.txt

logfile = pgbouncer.log

pidfile = pgbouncer.pid

admin_users = dbuser

max_client_conn =  100
 

auth_file 示例

"xx_xx" "md50159db6e66x35f2ffd59"

"dbuser" "123456"

4PgBouncer启动和管理

4.1 启动

$ $GPHOME/bin/pgbouncer -d pgbouncer.ini 

#热加载

  $ pgbouncer -R -d /etc/pgbouncer/pgbouncer.ini

 

4.2 连接测试

连接时指定的是PgBouncer的服务器和监听端口,数据库要使用pgbouncer中配置的数据库名称

$ psql -h smaster -p 7000 -d dbname -U xx_xx

Password for user xx_xx: 

psql (9.4.24)

Type "help" for help.

dctsdb=> \q

4.3 管理

使用配置中指定的管理用户,登录虚拟的pgbouncer数据库,可以进行一系列管理工作

pgbouncer=# SHOW help;

NOTICE:  Console usage

DETAIL:

    SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|VERSION

    SHOW FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM

    SHOW DNS_HOSTS|DNS_ZONES

    SHOW STATS|STATS_TOTALS|STATS_AVERAGES

    SET key = arg

    RELOAD

    PAUSE [<db>]

    RESUME [<db>]

    DISABLE <db>

    ENABLE <db>

    KILL <db>

    SUSPEND

    SHUTDOWN

    

# 重载配置文件    

pgbouncer=# RELOAD;

RELOAD

pgbouncer=# 

​

#查看当前里连接池里的连接

pgbouncer=# show CLIENTS;

 类似资料: