PG连接池工具之Pgbouncer

0    405    3

👉 本文共约6175个字,系统预计阅读时间或需24分钟。

通常,连接池的目的是降低打开新的PostgreSQL(或其他)数据库连接的性能影响。使用连接池能够降低服务器的内存开销,并且有效复用数据库连接,提供了良好的数据库连接性能管理。

在我们进行Postgresql入门的时候,通常会看到这段介绍“PostgreSQL服务器可以处理来自客户端的多个并发连接。为此,它为每个连接启动(“fork”)新进程,从那时起,客户端和新的服务器进程进行通信,而无需原始postgres进程进行干预。因此,主服务器进程始终在运行,等待客户端连接,而客户端及关联的服务器进程来来往往。”但是,这意味着每个新连接都会分叉一个新进程,保留在内存中,并可能在多个会话中变得过分繁忙。在业务量较小的情况下,这种方式基本可以满足要求,但是当业务量迅速激增,我们可能就需要不断去更改max_connections来满足客户端的需求。当时同样也带来了很大的问题,如频繁的关闭和创建连接造成的内存开销,管理已产生的大量连接等等,最终导致服务器响应缓慢而无法对外提供数据库服务。在这样一个背景下,数据库连接池就被提出来了,对于使用Postgresql数据库来说,一般分为客户端连接池,比如c3p0、druid等等;另外一种则是服务器端连接池,例如pgbouncer、odyssey、pgpoolII等。

Pgbouncer简介

http://www.pgbouncer.org

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

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

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

https://github.com/pgbouncer/pgbouncer

Pgbouncer是一个针对PostgreSQL数据库的轻量级连接池,

任何目标应用都可以把 pgbouncer 当作一个 PostgreSQL 服务器来连接,

然后pgbouncer 会处理与服务器连接,或者是重用已存在的连接。

pgbouncer 的目标是降低因为新建到 PostgreSQL 的连接而导致的性能损失。

PgBouncer的作用

  • PgBouncer可以在后端数据库和前端应用间建立连接桥梁,由PgBouncer去处理和后端数据库的连接关系。
  • 对客户端连接进行限制,预防过多或者恶意的连接请求。
  • 主要工作还是每一个数据节点对数据的存取,pgbouncer只是提供给客户端数据节点的分配和链接以及限制客户端连接的数量。整体上实现了对数据的负载均衡。
  • 能够缓存和PostgreSQL的连接,当有连接请求进来的时候,直接分配空闲进程,而不需要PostgreSQL fork出新进程来建立连接,以节省创建新进程,创建连接的资源消耗。
  • 能够有效提高连接的利用率,避免过多的无效连接,导致数据库消耗资源过大,CPU占用过高。

轻量级体现在:使用libevent进行socket通信、C语言编写,效率高,每个连接仅消耗2kB内存 。

PG连接池工具之Pgbouncer

PgBouncer的优点

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

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

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

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

PgBouncer的缺点

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

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

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

PgBouncer 支持三种连接池模型

1.session(会话连接池)

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

2.transaction(事务连接池)

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

3.statement(语句连接池)

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

连接池模式对比**

在这里插入图片描述

从上述对比情况来看,在连接池的选择上,需要依据业务环境特点来进行选择,默认情况下推荐使用事务连接池,它兼顾了执行事务的特性,尤其多语句的支持,并且不会像会话连接池那样,尝尝处于等待状态。当然事务模式并不支持预编译语句。而根据具体业务场景的特殊需要,有些时候需要客户端与服务器端保持连接,或者支持预编译语句,这样只能选择会话池模式。还有一些特例情况,某些业务场景只是单语句执行,那么语句池模式可能更适合。因此对比这三种模式,可以发现从对客户端操作的支持程度来讲,会话池支持度最高,其次是事务池,最后是语句池模式。但是从支持的连接数来讲,可能刚好是相反的顺序。

SQL特性对照表

在这里插入图片描述

上表为会话连接池和事务连接池的SQL特性对比情况,可以通过对比具体业务场景与SQL特性的符合度,来对连接池模式进行选型。

下面列举了一些示例场景:

  • 有些只运行快速查询,因此在没有事务的情况下可以共享一个会话来处理上百个并发查询。
  • 一些角色成员对于会话级并发是安全的,并且总是使用事务。因此,他们可以安全地共享数百个并发事务的多个会话。
  • 有些角色过于复杂,无法与其他人共享会话。因此,您对它们使用会话池模式可以避免当所有“插槽”都已占用时连接错误。
  • 不要使用它代替HAProxy或其他负载均衡器。尽管pgbouncer具有一些可配置的功能来解决负载均衡器要解决的问题,例如dns_max_ttl,并且可以为其设置DNS配置,但是大多数产品环境都使用HAProxy或其他用于HA的负载均衡器。这是因为HAProxy确实擅长以循环方式在服务器之间实现负载平衡,而不是pgbouncer。尽管pgbouncer对于postgres连接池更好,但最好使用一个小型守护程序来完美地执行一项任务,而不是使用较大的守护程序来完成两项任务,那样效果更糟。

下载安装

http://www.pgbouncer.org/downloads/

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

http://www.pgbouncer.org/install.html

yum安装

在Linux发行版中,包含已经编译好的PgBouncer,可以直接安装。

RHEL/CentOS平台直接使用yum命令来安装。

Debian/Ubuntu平台下,直接使用apt-get命令安装。

编译安装

在官网http://www.pgbouncer.org/downloads/上下载源码编译安装,需要先安装libevent-devel这个rpm包。

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

安装完成:

配置文件

使用系统自带工具(yum或apt-get)安装的PgBouncer的配置文件路径是/etc/pgbouncer/pgbouncer.ini

源码安装后,在/usr/local/share/doc/pgbouncer目录下有一个示例的配置文件pgbouncer.ini,可作为配置文件的模板。

默认的配置和含义如下:

默认情况下不配置任何数据库信息,从上面还可以看到,配置主要分为两部分:

第一部分是[databases]区域,是用来配置数据库连接相关信息的。

第二部分是[pgbouncer],是pgbouncer自身的配置。

其中,

本句话说明该pgbouncer创建了针对172.72.6.30的lhrdb的一个连接池,该链接池对调用方的呈现的数据库名称是appdb,它映射到本机的lhrdb数据库上。所有访问pgbouncer上的appdb的请求都会转到lhrdb上完成。

这意味着,客户端通过pgbouncer单次连接时,只能连接到某台主机上的某个数据库。

配置示例

官方配置示例:http://www.pgbouncer.org/config.html

配置密码文件

[pgbouncer]区域使用默认配置,补充说明以下两个配置:

第一行是用于配置登录pgbouncer的认证方式,和PostgreSQL认证方式相同,默认是trust,即所有的都信任(不需要密码即可登录),还可以使用md5加密的形式。注意:这里的trust表示只有参数“auth_file”中配置的用户才会trust认证,其它用户登陆均会报错“psql: error: ERROR: "trust" authentication failed”

第二行是用于配置认证用户的,即连接pgbouncer的用户名都保存在该文件中。

当第一行设置为md5加密时,则加密的密码也必须保存在第二行配置的文件中。

如果这个文件不存在,那么登录的时候,无论是哪个用户,都会提示下面的错误:

而这个认证文件默认情况下是没有的,因此需要手动生成。

从PostgreSQL的9.x开始,所有的用户密码都是保存在pg_shadow表里。

PostgreSQL 8.x版本则是保存在数据库目录下,可以直接复制过来使用。

生成这个认证文件有两种方式,如下:

1)SQL语句生成认证文件

之前我们说过,用户密码默认是保存在pg_shadow表里的,如下面所示:

usename和passwd两列里面保存的就是我们需要的账号和密码

我们使用copy命令将它们导出来(或者手动编辑也可以):

我们打开这个auth_file文件,内容如下:

里面保存有postgres的账号,一般不要使用这个超级管理员的身份,最好删掉。

然后保留可以连接数据库的用户账号和加密后的密码,将这个文件转移到上面配置项指定的位置。并且文件名称要和上面变量里定义的文件名一致,否则会提示找不到这个文件。

最后还要注意的一点是,默认导出的文件里用户名和密码的格式pgbouncer无法识别,需要用双引号引起来才能正确识别

如下所示:

2)使用mkauth.py来生成文件

这个文件是使用python编写的一个脚本,已经赋予了可执行权限。

执行的时候需要两个参数,基本格式是:

/etc/pgbouncer/mkauth.py 用户列表文件 "数据库连接参数"

示例:

这里比较重要的是后面那一段参数,=号两边不能有空格,两个键值对之间要用空格隔开,不能用逗号,否则会报错。用户必须是有查询pg_shadow表权限的用户请记住这里的限制条件。

如果没有错误的话,就会在/etc/pgbouncer/目录下生成userlist.txt文件。

文件内容如下所示:

默认会备份出PostgreSQL数据库的pg_shadow表里的所有数据库,包括postgres用户。所有的用户名和密码都会用双引号引起来,比手动备份更方便。这里唯一麻烦的就是脚本后面的连接字符串。

3)手动生成文件

配置密码文件,参考:http://www.pgbouncer.org/config.html#authentication-file-format ,密码需要以双引号引起来,然后用户名和密码之间使用空格隔开:

这样就是明文,不建议。

启动

不能以root启动,会报错:“FATAL PgBouncer should not run as root”。

当用户文件配置好以后,就可以启动pgbouncer来使用了。
使用linux发行版自带的包管理工具安装pgbouncer的时候,它会自动创建一个pgbouncer用户

如果是自己编译的话,则需要手动创建这个用户。创建完成以后。

需要切换到这个用户下来启动pgbouncer,pgbouncer是不允许在root用户下启动的。

停止

可以通过systemctl来操作,也可以通过kill命令来停止。

kill命令格式是:

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

pgbouncer对外提供了一个虚拟数据库pgbouncer,之所以称为虚拟数据库,是因为它可以提供像PostgreSQL那样的数据库操作界面,但是这个数据库却并不是真实存在的,而是pgbouncer虚拟出来的一个命令行界面。

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

注意:

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

登录命令是:

登录以后可以使用:

示例:

日志信息

in B/s:每秒读入字节数。

out B/s:和in B/s一样,表示每秒读出的字节数。

query us:平均每个查询花费的时间,单位微秒(1S(秒)等于1000ms(毫秒),1ms等于1000us(微秒)。)。

wait us : 等待耗时 微秒

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

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

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

登陆验证

本地登陆验证

远程登陆验证

多个客户端登陆

登陆多个客户端,然后查看数据库服务器的后台进程,发现只有1个(postgres: postgres lhrdb 172.72.6.2(59578) idle):

报错

配置pgbouncer的时候,遇到这个错误“unsupported startup parameter: extra_float_digits”。用数据库客户端图形管理工具(dbeaver)连接的时候,报错;但是,使用navicat连接或使用psql客户端连接却不报错:

PG连接池工具之Pgbouncer

解决:配置文件中添加 ignore_startup_parameters = extra_float_digits

然后重启pgbouncer即可。

PG连接池工具之Pgbouncer

PG连接池工具之Pgbouncer

Navicat的连接:

PG连接池工具之Pgbouncer

扩展文档

https://pgfans.cn/a/958

https://pgfans.cn/a/962

https://pgfans.cn/a/960

https://pgfans.cn/a/961

    头像

    小麦苗

    学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

    您可能还喜欢...

    发表评论

    您的电子邮箱地址不会被公开。

    3 × 2 =

     

    嘿,我是小麦,需要帮助随时找我哦
    • 18509239930
    • 个人微信

    • 麦老师QQ聊天
    • 个人邮箱
    • 点击加入QQ群
    • 个人微店

    • 回到顶部
    返回顶部