侧边栏壁纸
博主头像
波哥

大龄程序猿&自由职业者&副业达人
爱好:敲代码、搞副业
12+年开发经验,热衷自动化网络推广!

  • 累计撰写 1,810 篇文章
  • 累计创建 77 个标签
  • 累计收到 13 条评论

目 录CONTENT

文章目录

PG连接中间件PgBouncer快速入门

波哥
2022-01-03 / 0 评论 / 0 点赞 / 512 阅读 / 0 字

PG连接中间件PgBouncer快速入门

一、介绍

  • PgBouncer主要目标是为PostgreSQL提供轻量级的连接池
  • PgBouncer支持在不停机的情况下修改配置(大部分)
  • PgBouncer支持在不断开客户端连接的情况下升级或重启
  • 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

```Plain Text ./configure --prefix=/usr/local/pgbouncer --with-libevent=libevent-prefix make make install

启动

```Plain Text
pgbouncer -d usr/local/pgbouncer/conf/pgbouncer.ini

停止

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

三、日常使用

> PgBouncer采用了基于Console的类似PG数据库的管理方式,当使用psql命令行连接到PgBouncer后就好像连接到虚拟的PG数据库,在PgBouncer上执行命令就像是在PG上执行SQL命令,这样PG的管理员就可以通过类似PG的方式监控和管理PgBouncer,具体操作如下:

```Plain Text
[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;

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

```Plain Text 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 []    RESUME []    DISABLE    ENABLE    KILL    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 太小
> * pool\_mode:连接池模式

### 3.4 show stats;
> 显示每个PG数据库的统计信息

> * database:统计是根据每个数据库分比例的
> * total\_requests:连接池处理的SQL请求的总数
> * total\_received:接收到的网络流量的总字节数
> * total\_sent:发出的网络流量的总字节数
> * total\_query\_time:活跃在与数据库上面的时间开销总数,单位是微秒
> * avg\_req:在最后一次统计过程中的每秒平均请求数
> * avg\_recv:每秒(从客户端)接收到的平均数据量
> * avg\_sent:每秒发送(给客户端)的平均数据量
> * avg\_query:平均的查询时间,单位是微秒

### 3.5 show servers;
> 显示与PgBouncer建立连接的PG数据库信息

> * type:S,表示服务器
> * user:PgBouncer用于连接服务器的用户名
> * 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:PgBouncer实例的IP地址
> * local\_port:PgBouncer实例的端口
> * 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之前此命令不会有任何返回

### 3.15 RESUME \[db\];
> 从之前PAUSE或者SUPEND命令恢复之前状态

### 3.16 SHUTDOWN;
> PgBouncer进程退出

### 3.17 RELOAD;
> 重新加载其配置文件并更新可更改的配置

四、配置文件

4.1 **databases配置项**

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

```Plain Text
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: 连接池的大小,如果没有设置,连接池大小将使用中的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启动则此项是必须配置,如果未配置启动报错如下

```Plain Text 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,Windows下没有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,表示禁止此功能

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
0

评论区