pgbouncer简单介绍

一、介绍

pgbouncer是为PostgreSQL提供的一个轻量级连接池工具主要作用
* 应用程序如果直接与PostgreSQL连接,每次连接pg都会克隆出一个服务进程来为应用程序提供服务,关闭连接后pg会自动将服务进程给停掉.频繁的创建和销毁进程,会消耗比较多的系统资源.pgbouncer会将与pg的连接缓存住,当有请求进来,只是分配一个空闲的连接,这样降低了系统资源的消耗.
* 准许应用创建多个连接,如果直连数据库.当连接数远大于CPU核数,会造成CPU切换,效率降低,使用pgbouncer则可以控制住数据库上的活动连接,一般配置为CPU核数的1.5-2.5倍之间.
* pgbouncer使用libevent进行socket通信,效率很高
* pgbouncer比较轻巧,每个连接仅仅消耗2KB内存

二、安装

• GNU Make 3.81+
• libevent 2.0
• (optional) OpenSSL 1.0.1 for TLS support
• (optional) c-ares as alternative to libevent’s evdns

./configure —prefix=/usr/local/pgbouncer —with-libevent=libevent-prefix make make install

启动

pgbouncer –d /usr/local/pgbouncer/conf/pgbouncer.ini

停止

kill  -9 `cat /home/postgres/pgbouncer/pgbouncer.pid`

三、日常使用

Pgbouncer提供了类似连接到虚拟数据库pgbouncer,然后执行一些特殊命令的功能,这些命令就像是执行一个真正的SQL命令,让管理者能查询和管理pgbouncer的连接池信息,这个界面为pgbouncer的Console控制界面.一般使用psql命令连接到这个虚拟数据库上,如下:

[postgres@node3 pgbouncer]$ psql -p 6432 -d pgbouncer
psql (9.6.3, server 1.7.2/bouncer)
Type "help" for help.

pgbouncer=#

3.1 show help;

查看管理命令的帮助信息,这些命令都必须以分号结尾

pgbouncer=# show help;
NOTICE:  Console usage
DETAIL:  
    SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|VERSION
    SHOW STATS|FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM
    SHOW DNS_HOSTS|DNS_ZONES
    SET key = arg
    RELOAD
    PAUSE [<db>]
    RESUME [<db>]
    DISABLE <db>
    ENABLE <db>
    KILL <db>
    SUSPEND
    SHUTDOWN
SHOW

3.2 show config;

显示当前配置设置,一个配置一行,字段如下：
* key：配置变量名称
* value：配置值
* changeable：yes 或 no,显示这个变量是否可以在运行时修改如果为 no,那么这个变量只能在启动的时候修改

3.3 show pools;

列出连接池
* database：数据库名
* user：用户名
* cl_active：当前 active (活跃)的客户端连接的个数
* cl_waiting：当前 waiting (等待)的客户端连接个数
* sv_active：当前 active (活跃)的服务器连接个数
* sv_idle：当前 idle (空闲) 的服务器连接个数
* sv_used：当前 used (在使用)的服务器连接个数
* sv_tested：当前 tested (测试过)的服务器连接个数
* sv_login：当前 login (登录)到 PostgreSQL 服务器的个数
* maxwait：队列中第一个(最老的那个)客户端等待的时间长度,单位是秒.如果这个数值开始上升,那么就意味着当前的连接池中的服务器处理请求的速度不够快.原因可能是服务器过载,也可能只是 pool_size 太小

3.4 show stats;

• database：统计是根据每个数据库分比例的
• total_requests：连接池处理的SQL请求的总数
• total_received：接收到的网络流量的总字节数
• total_sent：发出的网络流量的总字节数
• total_query_time：活跃在与数据库上面的时间开销总数,单位是微秒
• avg_req：在最后一次统计过程中的每秒平均请求数
• avg_recv：每秒(从客户端)接收到的平均数据量
• avg_sent：每秒发送(给客户端)的平均数据量
• avg_query：平均的查询时间,单位是微秒

3.5 show servers;

列出数据库与pgbouncer之间连接
* type：S,表示服务器
* user：gbouncer用于连接服务器的用户名
* database：服务器端的数据库名
* state：pgbouncer 服务器连接的状态 active、used、idle
* addr：PostgreSQL服务器的IP地址
* port：PostgreSQL服务器的端口
* local_addr：本地机器上的发起连接地址
* local_port：本地机器上的发起连接端口
* connect_time：连接建立的时间
* request_time：请求发出的时间
* ptr：这个连接的内部对象地址,用做唯一 ID
* link：这个服务器对应的客户端地址

3.6 show clients;

列出客户端及客户端连接状态
* type：C,表示客户端
* user：客户端连接的用户
* database：数据库名
* state：客户端连接的状态 active、used、waiting或者idle之一
* addr：客户端的 IP 地址
* port：客户端连接去的端口
* local_addr：本地机器上连接到的对端地址
* local_port：本地机器上的连接到的对端端口
* connect_time：最后的客户端连接的时间戳
* request_time：最后的客户端请求的时间戳
* ptr：这个连接的内部对象的地址,用做唯一 ID
* link：这个客户端连接对应的服务器的地址

3.7 show lists;

显示连接池的计数信息
* databases：数据库的个数
* users：用户的个数
* pools：连接池的个数
* free_clients：空闲客户端的个数
* used_clients：已用的客户端的个数
* login_clients：处于已登录状态的客户端个数
* free_servers：空闲服务器个数
* used_servers：已用服务器个数

3.8 show databases;

列出pgbouncer数据库别名及相关数据库
* name：已配置的数据库名字记录
* host：pgbouncer 连接到的主机名
* port：pgbouncer 连接到的端口号
* database：pgbouncer 实际连接的数据库名
* force_user：当用户是连接字串的一部分的时候,在 pgbouncer 和 PostgreSQL 之间的连接会强制成给出的用户,不管 client user 是什么
* pool_size：最大的服务器端连接数目

3.9 show fds;

显示正在使用的 fd 列表如果连接的用户的用户名是 “pgbouncer”,那么通过 unix socket 连接,并且和运行的进程有同样的 UID,实际的 fd 列表是通过这个连接传递的这个机制用于做在线重启
- fd：文件描述符的数字值
- task：pooler,client 或 server 之一
- user：使用该 FD 的连接用户
- database：使用该 FD 的连接的数据库
- addr：使用该 FD 的连接的 IP 地址,如果使用的是 unix socket,就是 unix
- port：使用该 FD 的连接的端口号
- cancel：这个连接的取消键字
- link：对应的服务器/客户端的 fd如果为 idle (空闲)则为 NULL

3.10 DISABLE db;

拒绝指定数据库上所有新客户端连接

3.11 ENALBLE db;

准许之前DISABLE命令之后的新客户端连接

3.12 PAUSE [db];

尝试从所有服务器断开连接(等待query完成),在所有query完成之前,此命令不会返回,在数据库重新启动时使用.如果给出了数据库名字则只对该数据库有用

3.13 KILL db;

立即删除给定数据库上所有客户端以及数据库连接

3.14 SUPEND;

刷新所有socket缓存,并且停止监听,在缓存flush之前此命令不会有任何返回.使用场景:pgbouncer在线重新启动时使用

3.15 RESUME [db]

从之前PAUSE或者SUPEND命令恢复之前状态

3.16 SHUTDOWN;

pgbouncer进程退出

3.17 RELOAD;

重新加载其配置文件并更新可更改的配置

四、配置文件

4.1 databases配置项

databases配置比较简单,每行由key=value对组成,其中key为对外数据库名称,value由多个以空格隔开的key=value对的连接串及相关参数对组成,实例:

postgres = host=127.0.0.1 dbname=postgres port=5432 user=postgres password=postgres client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'

连接串的各个参数说明:

• dbname: 后端数据库名称
• host: 后端数据库的主机名或者IP地址
• port: 后端数据库监听端口
• user: 连接后端数据库的用户名
• password: 连接后端数据库的密码
• pool_size: 配置连接池的大小,如果没有配置此项,连接池大小将使用[pgbouncer]部分中default_pool_size配置的值
• connect_query: 在连接使用之前执行一个SQL语句,用于探测此连接是否正常.如果执行该语句出错,则选择另外一个连接
• max_db_connections: 配置数据库范围的最大值（即数据库中的所有池都不会有这么多的服务器连接
• client_encoding: 制定客户端字符集编码
• datestyle: 指定日志类型参数
• timezone: 指定时区
  注意:如果在连接串中没有指定user和password,那么pgbouncer将使用给客户端连接pgbouncer时的用户名和密码来连接后端数据库,并为每个不同的用户建立一个连接池;如果连接中指定了user和password,pgbouncer将使用这里设置的用户名和密码来连接后端数据库,这样对使用这项配置的数据库来说,就只有一个连接池了.

4.2 pgbouncer配置项

4.2.1 通用配置项

4.2.1.1 logfile

指定日志文件

4.2.1.2 pidfile

指定pidfile,文件中记录了pgbouncer的进程ID,如果是加-d启动则此项是必须配置,如果未配置启动报错如下

2017-07-25 12:26:27.875 1672 FATAL @src/main.c:517 in function go_daemon(): daemon needs pidfile configured

4.2.1.3 listen_addr

监听的IP地址

4.2.1.4 listen_port

监听的IP端口

4.2.1.5 unix_socket_dir

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

4.2.1.6 unix_socket_mode

指定unix socket文件属性,默认 0777

4.2.1.7 unix_socket_group

指定unix socket文件的组,默认没有设置

4.2.1.8 admin_users

指定启动登录console的用户名

4.2.1.9 auth_file

指定连接pgbouncer的用户名和密码认证文件.

4.2.1.10 auth_type

认证方法,可设置为any, trust, plain, crypt, md5

4.2.1.11 pool_mode

连接池模式,可为session, transaction, statement

4.2.1.12 max_client_conn

准许连接到pgbouncer上最大客户端数

4.2.1.13 default_pool_size

连接池与数据库默认大小,不同的用户或者数据库会有不同的连接池

4.2.1.14 min_pool_size

连接池最小大小,即每个连接池和数据库保持的连接数

4.2.1.15 reserve_pool_size

连接池保留连接数

4.2.1.16 reserve_pool_timeout

保留连接的超时时间

4.2.1.17 server_round_robin

负载均衡模式是否为 round robin,默认是关闭,采用LIFO(后进先出)

4.2.1.18 ignore_startup_parameters

默认pgbouncer会跟踪一些数据库参数,如client_encoding,datestyle,timezone,standard_conforming_strings,application_name等,pgbouncer能检车出这几个参数的变化并与客户端保持一致,所以默认情况下设置其他参数会导致pgbouncer抛出错误.设置此项,指定一些数据库参数,pgbouncer就可以忽略对这些参数的检查,不同参数之间用逗号隔开

4.2.1.19 disable_pqexec

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

4.2.2 日志配置项

4.2.2.1 syslog

是否打开syslog,window下没有syslog,则使用eventlog.默认为0,表示不打开

4.2.2.2 syslog_facility

可配置为auth, authpriv, daemon, user, local0-7默认是daemon

4.2.2.3 syslog_ident

以什么名称发送日志到syslog,默认是pgbouncer

4.2.2.4 log_connections

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

4.2.2.5 log_disconnections

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

4.2.2.6 log_pooler_errors

连接池发往客户端的错误是否记录在日志中,默认值是1,记录

4.2.2.7 stats_period

将汇总的统计信息写入日志的时间周期,默认60

4.2.2.8 verbose

日志记录的详细程度,在启动命令行中 –v –v 与verbose=2是同样的含义

4.2.3 console访问控制配置项

4.2.3.1 admin_users

准许在console端执行一些管理命令的用户列表,多个用户之间以逗号隔开.当设置auth_mod=any时,此配置可忽略,默认为空.

4.2.3.2 stats_users

准许连接到console上查看连接池只读信息的用户列表.这些用户可以执行除show fds命令之外的其他show命令.

4.2.4 健康检查和超时配置项

4.2.4.1 server_reset_query

当一个后端数据库连接会话被某一个客户端使用时,它的属性可能会改变,所以当这个后端数据端连接呗第二个客户端使用时,就可能会产生问题.因此一个连接被使用后重新放回连接池时,需要对这个连接的属性进行复位.默认设置为DISCARD ALL.需要注意在连接池为事务模式是,此配置项应该为空,因为在事务模式下,客户端不应该设置连接会话的属性

4.2.4.2 server_check_delay

空闲连接需要多长时间进行一次健康检查,查看其是否可用.如果设置为0则立即检查,默认设置为30s

4.2.4.3 server_check_query

健康检查的SQL,如果为空则禁止健康检查.默认为SELECT 1

4.2.4.4 server_lifetime

连接存活时间.当一个连接存活时间超过此值时,就会被关闭,然后新建一个连接.默认为3600s,模板配置文件中是1200s与默认值有冲突.如果设置为0,表示此连接只是用一次,使用后就关闭

4.2.4.5 server_idle_timeout

连接池中连接的idle时间,超过此时间,连接会被关闭,默认值为600s

4.2.4.6 server_connect_timeout

到后端数据库的login时间超过此值后,连接就会被关闭.默认为15s

4.2.4.7 server_login_retry

指定创建到后端数据库连接失败后,等待多长时间重试,默认值为15s

4.2.4.8 client_login_timeout

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

4.2.4.9 autodb_idle_timeout

如果自动创建的数据库池已经使用用了这个时间值,那么他们会被释放,不好的方面是响应的统计数据也会丢掉,默认3600s

4.2.4.10 suspend_timeout

在SUSPEND命令暂停或者用-R重新启动期间等待缓冲区刷新的秒数,如果在此时间内flush不成功，连接将被丢弃

4.2.5 危险超时配置项

为了防止一些未知原因导致系统hang住,而设置的超时配置

4.2.5.1 query_timeout

运行时间超过该时间值的SQL会被终止.此值应该设置得比SQL的实际运行时间长一些,也应该比数据库的statement_timeout参数配置的值大一些.这个参数主要是为了便于应付一些未知网络问题.设置此值可防止查询被长时间hang住.默认值为0,表示禁止此功能
测试:设置query_timeout=1,在客户端运行超过1s的SQL报错如下

2017-07-25 22:16:08.601 2985 LOG S-0xda6430: postgres/postgres@127.0.0.1:5432 closing because: query timeout (age=749)
2017-07-25 22:16:08.601 2985 LOG C-0xd9d960: postgres/postgres@192.168.42.102:46114 closing because: query timeout (age=749)
2017-07-25 22:16:08.601 2985 WARNING C-0xd9d960: postgres/postgres@192.168.42.102:46114 Pooler Error: query timeout

4.2.5.2 query_wait_timeout

一个请求在队列中等待被执行的最长时间,如果超过此时间还没有被分配到连接,则此客户端连接将会被断开.这主要为了防止数据库hang住后,客户端到pgbouncer的连接也一直被hang住,默认值为120s,如果设置为0则客户端无线排队等待

4.2.5.3 client_idle_timeout

如果客户端空闲该时间值后,一直不发送命令,则断开与此客户端的连接.这一般是为了防止客户端上的TCP连接实际上因为网络问题关闭,但是pgbouncer上相应的连接没有检测到客户端已经不存在而一直存在.默认值为0,表示禁止此功能.

4.2.5.4 idle_transaction_timeout

客户端启动事务后,超过此时间值还不提交事务,则关闭这个客户端连接,防止客户端消耗pgbouncer及数据库的资源,默认为0,表示禁止此功能.

4.2.6 底层网络配置项

4.2.6.1 pkt_buf

用于指定网络包的内部缓冲区大小,该值会影响发出的TCP包大小即内存使用大小.实际的libpq数据包可以比这个大,所以没有必要设置的太大.默认值为4096,一般保值这个值即可

4.2.6.2 max_packet_size

通过pgbouncer的最大包大小,这个包可以是一个SQL,也可以是一个SQL的返回结果集,有可能这个结果集非常大.默认为2147483647

4.2.6.3 listen_backlog

TCP监听函数listen的Backlog参数,默认是128,通过man 2 listen可查看backlog的含义
backlog参数定义sockfd的挂起连接队列可能增长的最大长度。如果在队列已满时连接请求到达，则客户端可能会收到带有ECONNREFUSED指示的错误，或者如果底层协议支持重传，则可能会忽略该请求，以便连接中的后续重新尝试成功

4.2.6.4 sbuf_loopcnt

在处理过程中,每个连接处理多少数据后切换到下一个连接.如果没有这个限制那么有可能会出现一个连接发送或者接收大量数据时,可能会导致其他连接饿死.如果设置为0表示不限制.默认值是5

4.2.6.5 tcp_defer_accept

此选项值的详细说明从linux下 man 7 tcp中获取.在linux下次默认值为45,其他平台为0

4.2.6.6 tcp_socket_buffer

默认没有设置

4.2.6.7 tcp_keepalive

是否以操作系统的默认值打开基本的keepalive设置.在linux操作系统下探活的相关默认设置为net.ipv4.tcp_keepalive_time = 7200, net.ipv4.tcp_keepalive_intvl = 75, net.ipv4.tcp_keepalive_probes = 9这些值默认偏大,一般根据实际情况调整

4.2.6.8 tcp_keepcnt

默认未设置

4.2.6.9 tcp_keepidle

默认未设置

4.2.6.10 tcp_keepintvl

默认未设置