pgBouncer初探

pgBouncer是一个轻型的pg连接池，可以很好的管理pg的连接（对客户端连接进行限制，预防过多恶意连接；pgbouncer的每个连接仅消耗2KB的内存资源）。

PgBouncer的优点

内存消耗低(默认为2k/连接)，因为Bouncer不需要每次都接受完整的数据包

可以把不同的数据库连接到一个机器上，而对客户端保持透明

支持在线的重新配置而无须重启

pgbouncer 可以限制每一对用户+数据库 到PostgreSQL Cluster的总连接数

PgBouncer缺点

仅支持V3协议，因此后端版本须>=7.4

并没有真正实现数据库的负载均衡，如果单一的对一个数据库操作会使某个数据节点读写量增大

当某个数据库down掉后，就不能通过pgbouncer连接起来，缺少了数据库备份的功能

主要工作还是每一个数据节点对数据的存取，pgbouncer只是提供给客户端数据节点的分配和链接以及限制客户端连接的数量。

PgBouncer 当前支持三种连接池模型

1、session

会话级连接，在它的连接生命周期内，连接池分配给它一个数据库连接。客户端断开时，数据库连接会放回连接池中。

2、transaction

事务级别连接，当客户端的每个事务结束时，数据库连接就会重新释放回连接池中，再次执行一个事务时，需要再从连接池中获取一个连接。

3、statement

每执行完一个SQL时，连接就会重新释放回连接池中，再次执行一个SQL 时，需要再次从连接池中获得连接。这种模式意味着在客户端强制autocomit模式

https://pgbouncer.github.io/install.html 

https://pgbouncer.github.io/usage.html

https://pgbouncer.github.io/config.html

RPM安装： 

 python-psycopg2 is needed by pgbouncer-1.9.0-1.rhel7.x86_64

yum install python-psycopg2 -y 

rpm -ivh pgbouncer-1.9.0-1.rhel7.x86_64.rpm

 

rpm -ql pgbouncer

 

Source安装

[root@vlnx107002 ~]# tar zxvf pgbouncer-1.9.0.tar.gz

yum install libevent -y

yum install libevent-devel -y

 

./configure --prefix=/usr/local --with-libevent=libevent-prefi

make

 make install

编译安装 默认PgBouncer是安装到 /usr/local/bin 目录下的。

案例：

fsdb_paas = host=127.0.0.1 port=5432 dbname=postgres connect_query='SELECT 1'

wenzhong = host=127.0.0.1 port=5432 dbname=wenzhong connect_query='SELECT 1'

针对库 postgres 创建了一个连接池，该连接池对调用方的呈现的数据库名为  fsdb_paas，它映射到了 postgres库上，即所有访问 pgbouncer上的 fsdb_paas的请求都会转到 postgres库上完成。

 

auth_type  和 auth_file 是 pgbouncer用于完成对客户端身份认证的配置项， auth_file中保存用户名和密码，根据验证方式auth_type的不同，auth_file中的内容也不同。

md5: 基于md5的密码验证，auth_file中需要有普通文本和md5值两种形式的密码；

crypt: 基于crypt的密码验证(man 3 crypt), auth_file必须包含文本密码；

plain: 明文验证方式；

trust: 不进行验证，但auth_file依然需要保存用户名；

any: 也不进行验证，而且auth_file中不需要保存用户名了。但此种方式需要在pg_template1中明确说明用户名进行真实数据库的登录。如: pg_template1?=?host=127.0.0.1 user=exampleuser dbname=template1.否则会报错的。

 

注意：auth_file中的用户名、密码都必须使用双引号。

 

admin_users：列出哪些用户可以登录pgbouncer进行管理，以逗号进行分隔

stats_users：列出哪些用户可以登录pgbouncer进行只读操作，如可以列出服务器状态，访问链接等，但是不能执行reload。

 

max_client_conn 是对外提供数据库连接的个数，根据现场的情况调整

default_pool_size是连接到数据库个数，这个参数现场的情况调整，不宜设置过多，默认为20

 

第一种 方法 将 pg_shadow 中的 用户名和密码  导出到  userlist.txt 文档中,最后需要手动修改成标准 格式；

postgres=# copy (select usename,passwd from pg_shadow) to '/etc/pgbouncer/userlist.txt';

或

postgres=# \o /etc/pgbouncer/userlist.txt

postgres=# select usename,passwd from pg_shadow;

第二种方法使用 pgbouncer 自带的工具   mkauth.py 工具导出用户名和密码；

第二种方法需要首先安装 psycopg2这个python模块，因为mkauth是利用该模块连接postgres数据库的。

mkauth脚本使用格式方法： ./mkauth.py 密码文件名 连接参数

[root@vlnx107001 pgbouncer]# ./mkauth.py /etc/pgbouncer/userlist.txt  "host=127.0.0.1 user= zhaowz password=zhaowz dbname=postgres"

 

 编辑pgbouncer配置文件

vim /etc/pgbouncer/pgbouncer.ini

如果 auth_type  使用 hba 方式验证 则 需要 同时配置  以下俩个选项 

auth_file 

auth_hba_file

该处的 hba文件一定要和 pg所在服务器的hba文件中的一致，否则会登录失败

在 /etc/pgbouncer目录下 新建 pg_hba.conf 文件

 

开启 pgbouncer

[pgbouncer@vlnx107001 ~]$ pgbouncer -d /etc/pgbouncer/pgbouncer.ini -v

[pgbouncer@vlnx107001 ~]$ ps -ef |grep pgbou

 

 

关闭 pgbouncer

[pgbouncer@vlnx107001 ~]$ kill `cat /var/run/pgbouncer/pgbouncer.pid`

 

登录 pgbouncer虚拟库(管理员终端维护操作）

psql -p 6432 -d pgbouncer -U postgres -h 127.0.0.1

如果修改了一些配置参数，可以不用重启 pgbouncer 而是 reload 使其生效

注意：

只有在配置了参数 admin_users 或者 stats_users才会连接到控制台。

 

 

 

pgbouncer日志

/var/log/pgbouncer/pgbouncer.log

in b/s：这个比较好读懂，每秒读入字节数。

 

out b/s：和in b/s一样，表示每秒读出的字节数。

 

query us：平均每个查询话费的时间，单位微秒。us应该是used的缩写。

 

wait time : 等待耗时 微秒

 

xacts/s： 每秒多少个事务操作

 

queries/s：每秒多少次请求数

 

xact  us：每个事务耗时多少微秒

 

还可以查看 pgbouncer库上的  SHOW STATS_TOTALS

 

SHOW STATS_TOTALS;

SHOW STATS的子集显示总值（total_）。；

SHOW STATS_AVERAGES;

SHOW STATS的子集显示平均值（avg_）。

 

https://pgbouncer.github.io/usage.html

 

SHOW STATS;

database

每个数据库显示统计数据。

 

total_xact_count

pgbouncer汇总的SQL事务总数。

 

total_query_count

pgbouncer汇总的SQL查询总数。

 

total_received

pgbouncer收到的网络流量总字节数。

 

total_sent

pgbouncer发送的网络流量的总字节数。

 

total_xact_time

pgbouncer在事务中连接到PostgreSQL，在事务中空闲或执行查询时所花费的总微秒数。

 

total_query_time

pgbouncer在主动连接到PostgreSQL并执行查询时所花费的总微秒数。

 

total_wait_time

等待服务器的客户端花费的时间（以微秒为单位）。

 

avg_xact_count

上一个统计期间的平均每秒交易次数。

 

avg_query_count

上一个统计期间的每秒平均查询次数。

 

avg_recv

平均每秒接收（来自客户端）字节数。

 

avg_sent

平均每秒发送（到客户端）字节数。

 

avg_xact_time

平均事务持续时间，以微秒为单位。

 

avg_query_time

平均查询持续时间，以微秒为单位。

 

avg_wait_time

等待服务器的客户端花费的时间（以微秒为单位）

 

SHOW SERVERS;

type

S，用于服务器。

 

user

连接到服务器的pgbouncer用户名。

 

database

数据库名称。

 

state

pgbouncer服务器连接的状态， active,used,idle。

 

addr

PostgreSQL服务器的IP地址。

 

port

PostgreSQL服务器端口。

 

local_addr

本地计算机上的连接起始地址。

 

local_port

本地计算机上的连接启动端口。

 

connect_time

建立连接时。

 

request_time

上次(最后一次）发出请求时间。

 

wait

当前等待时间（秒）。

 

wait_us

当前等待时间(微秒。

 

close_needed

如果连接将尽快关闭，因为配置文件重新加载或DNS更新已更改连接信息或已发出RECONNECT。

 

ptr

此连接的内部对象的地址。用作唯一ID。

 

link

服务器与之配对的客户端连接的地址。

 

remote_pid

后端服务器进程的PID（远程客户端进程PID)。

 

tls

带有TLS连接信息的字符串，如果不使用TLS，则为空。

 

SHOW CLIENTS;

type

C，对于客户。

 

user

客户端连接用户。

 

database

数据库名称。

 

state

客户端连接状态，active,used,waiting,idle

 

addr

客户端的IP地址。

 

port

端口客户端已连接到。

 

local_addr

本地计算机上的连接结束地址。

 

local_port

本地计算机上的连接端口。

 

connect_time

连接时间的时间戳。

 

request_time

最新客户端请求的时间戳。

 

wait

当前等待时间（秒）。

 

wait_us

当前的等待时间(微秒)。

 

close_needed

不用于客户端

 

ptr

此连接的内部对象的地址。用作唯一ID。

 

link

客户端与之配对的服务器连接的地址。

 

remote_pid

进程ID，以防客户端通过Unix套接字连接并且OS支持获取它。

 

tls

带有TLS连接信息的字符串，如果不使用TLS，则为空。

 

SHOW POOLS;

为每一对（数据库,用户）都会保留一条新的连接池记录。

 

database

数据库名称。

 

user

用户名。

 

cl_active

链接到服务器连接并可以处理查询的客户端连接。

 

cl_waiting

客户端连接已发送查询但尚未建立服务器连接。

 

sv_active

链接到客户端的服务器连接。

 

sv_idle

未使用且可立即用于客户端查询的服务器连接。

 

sv_used

服务器连接的空闲时间超过了server_check_delay，因此在使用之前需要server_check_query在其上运行。

 

sv_tested

当前正在运行server_reset_query或server_check_query的服务器连接。

 

sv_login

当前正在登录的服务器连接。

 

maxwait

队列中第一个（最老的）客户端等待的时间，以秒为单位。如果这开始增加，那么当前的服务器池不能足够快地处理请求。原因可能是服务器过载或者pool_size设置太小。

 

maxwait_us

最长等待时间(微秒)。

 

pool_mode

正在使用的池模式。

 

 

SHOW LISTS;

显示下列内部信息，分字段显示（不是按行显示）：

database

数据库的数量。

 

user

用户数量。

 

pools

池数。

 

free_clients

空闲客户数量。

 

used_clients

使用过的客户数量。

 

login_clients

处于登录状态的客户端数。

 

free_servers

空闲服务器数量。

 

used_servers

使用过的服务器数量。

 

dns_names

缓存中的DNS名称计数。

 

dns_zones

在缓存中DNS区域的计数。

 

dns_queries

正在进行的DNS查询计数。

 

dns_pending

不曾用过

 

SHOW USERS;

查看有哪些账号

pool_mode

The user’s override pool_mode, or NULL if the default will be used instead.

 

SHOW MEM;

 

SHOW DATABASES;

这部分 对应  ini文件中如下部分

name

已配置数据库条目的名称。

 

host

主机pgbouncer连接到。

 

port

端口pgbouncer连接到。

 

database

实际数据库名称pgbouncer连接到。

 

force_user

当用户是连接字符串的一部分时，pgbouncer和PostgreSQL之间的连接被强制给给定用户，无论客户端用户如何。

 

pool_size

最大服务器连接数。即默认连接池大小 default_pool_size

 

pool_mode

数据库覆盖pool_mode，如果使用默认值，则为NULL。

 

MAX_CONNECTIONS

此数据库允许的最大连接数，由max_db_connections设置 ，全局或每个数据库。

 

current_connections

此数据库的当前连接数。

 

paused

如果此数据库当前暂停，则为1，否则为0。

 

disabled

如果此数据库当前已禁用，则为1，否则为0。

 

SHOW FDS;

内部命令 - 显示正在使用的文件描述符列表以及附加到它们的内部状态。

 

当连接的用户具有用户名“pgbouncer”时，通过Unix套接字连接并且具有与正在运行的进程相同的UID，实际的FD通过连接传递。该机制用于在线重启。注意：这不适用于Windows计算机。

 

此命令还会阻止内部事件循环，因此在使用PgBouncer时不应使用它。

 

FD

文件描述符数值。

 

task

其中一个缓冲池，客户端或服务器。

 

user

使用FD的连接的用户。

 

database

使用FD的连接数据库。

 

addr

使用FD的连接的IP地址，如果使用Unix套接字，则使用unix。

 

port

使用FD连接使用的端口。

 

cancel

取消此连接的密钥。

 

link

相应服务器/客户端的fd。空闲时为NULL。

 

SHOW CONFIG;

显示当前配置设置，一个配置一行，字段如下：

key

配置变量名称

 

value

配置值

 

changeable

yes 或 no，显示这个变量是否可以在运行时进行更改。如果NO，则只能在引导时更改变量。使用SET在运行时更改变量。

 

进程控制命令

 

RELOAD;

PgBouncer进程将重新加载其配置文件并更新可更改的设置。

当配置文件重新加载更改数据库定义的连接参数时，PgBouncer会注意到。下次释放服务器连接时，将关闭与旧目标的现有服务器连接（根据池模式），新服务器连接将立即使用更新的连接参数。

 

PAUSE [db];

PgBouncer尝试断开与所有服务器的连接，首先等待所有查询完成。在完成所有查询之前，该命令不会返回。在数据库重启时使用。

如果给出了数据库名称，则只会暂停该数据库。与暂停数据库的新客户端连接将一直等到 调用RESUME。

 

RESUME [db];

从先前的KILL，PAUSE或SUSPEND命令恢复工作。

 

DISABLE db;

拒绝给定数据库上的所有新客户端连接。

 

ENABLE db;

在上一个DISABLE命令之后允许新客户端连接。

 

KILL db;

立即删除给定数据库上的所有客户端和服务器连接。

与被杀死数据库的新客户端连接将一直等到 调用RESUME。

 

RECONNECT [db];

在给定数据库或所有数据库发布后（根据池模式）关闭给定数据库或所有数据库的每个打开服务器连接，即使其生命周期尚未到期。可以立即建立新的服务器连接，并根据池大小设置根据需要进行连接。

当服务器连接设置已更改时，此命令很有用，例如，逐步切换到新服务器。这是不是 当pgbouncer.ini连接字符串已更改并重新加载需要运行这个命令（见RELOAD）或当DNS解析已经改变，因为当时这个命令相当于将自动运行。仅当PgBouncer下游的某些内容路由连接时，才需要此命令。

运行此命令后，可能会有一段延长的时间段，其中某些服务器连接转到旧目标，而某些服务器连接转到新目标。只有在只读副本之间切换只读流量或在多主机复制设置的节点之间切换时，这才有意义。如果需要同时切换所有连接，建议使用PAUSE。要在不等待的情况下关闭服务器连接（例如，在紧急故障转移而不是逐步切换方案中），还要考虑KILL。

 

SUSPEND;

刷新所有套接字缓冲区，PgBouncer停止侦听它们上的数据。在所有缓冲区都为空之前，该命令不会返回。在PgBouncer在线重启时使用。

与挂起的数据库的新客户端连接将一直等到 调用RESUME。

 

SHUTDOWN;

PgBouncer 进程将会退出。