PostgreSQL系列- 6 - PgBouncer连接池

目录

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，其他值包括md5，crypt，plain，any。用的较多的是md5

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

pool_mode 指定池的模式，默认是session模式，还可以是transaction和statement

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  是否打开syslog，windows下打开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监听函数listen的Backlog参数，默认值为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"

4、PgBouncer启动和管理

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;