合 PG连接池工具之pgbouncer

简介

通常，连接池的目的是降低打开新的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内存 。

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/

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表里的，如下面所示：

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

验证码：

获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。