原文出处：Improve database performance with connection pooling
作者：Michael Aboagye
译者：ACDU翻译组（@Shawn.W潇）
我们倾向于依靠缓存解决方案来提高数据库性能。在内存中或通过数据库缓存频繁访问的查询可以优化写入/读取的性能，并降低网络延迟，特别是对于工作负载很大的应用程序，例如游戏服务和问答门户。但是，您可以通过池化用户到数据库的连接来进一步提高性能。
客户端用户需要先建立与Web服务的连接，然后才能执行CRUD操作。大多数Web服务都由关系型数据库服务器（例如PostgreSQL或MySQL）支持。使用PostgreSQL时，每个新连接最多可占用1.3MB的内存。在生产环境中，我们事实上会接收数千个乃至数百万个并发连接到后端服务，这可能会很快超出您的内存资源（或者说，如果您拥有可伸缩的云，那么它很快就会因扩容而变得非常昂贵）。
由于客户端每次尝试访问后端服务都需要OS资源来创建、维护和关闭与数据存储的连接，这会产生大量开销，从而导致数据库性能的下降。
消费者总是期望获得快速的服务响应。如果性能下降，则可能导致欠佳的用户体验、收入损失，甚至计划外的宕机。如果将后端服务公开为API，则重复的速度下降和故障可能会导致级联问题并失去客户。
连接池使用数据库连接的缓存而不是为每个请求打开和关闭连接。这些缓存可以在将来需要对数据库进行请求时重用。它可以让您的数据库随着存储的数据和访问它的客户端数量的增长而有效地扩展。流量从来都不是恒定的，因此池化可以更好地管理流量峰值而不会造成中断。生产数据库不应该成为您的瓶颈。
在本文中，我们将探讨如何使用连接池中间件（例如pgpool和pgbouncer）来减少开销和网络延迟。出于说明目的，我将使用pgpool-II和pgbouncer来解释连接池的概念，并比较哪一个在连接池中更有效，因为某些连接池甚至会影响数据库性能。
我们将研究如何使用pgbench对Postgres数据库进行基准测试，因为它是PostgreSQL提供的标准工具。
不同的硬件会根据您设置的计划提供不同的基准测试结果。对于以下测试，使用了这些规格。
测试机规格：
- Linode服务器：Ubuntu 16 – 64位（虚拟机）
- Postgres 9.5版
- 内存：2GB
- 数据库大小：800MB
- 储存空间：2GB
由于大多数组件会消耗更多的内存并影响测试结果，因此将Postgres数据库服务器与其他框架（如logstash shipper）和其他服务器隔离开来收集性能指标也同样重要。
创建池连接
连接到后端服务是一项开销很大的操作，因为它包含以下步骤：
- 使用数据库驱动程序打开与数据库的连接
- 为CRUD操作打开一个TCP socket
- 通过socket执行CRUD操作
- 关闭连接
- 关闭socket
在生产环境中，事实上有来自客户端的数以千计的并发打开和关闭连接，对每个单个连接执行上述步骤可能会导致数据库性能下降。
我们可以通过池化来自客户端的连接以解决此问题。连接池不会为每个请求创建新的连接，而是重用一些现有的连接。因此，不需要通过打开和关闭与后端服务的连接来执行多个开销大的完整数据库访问。这样可以避免在每次请求具有相同属性（即名称、数据库、协议版本）的数据库连接时创建与数据库的新连接的开销。
像pgbouncer这样的池化中间件带有一个池管理器。通常，该管理器维护一个开放数据库连接池。没有池管理器，您将无法池化连接。
池包含两种类型的连接：
- 活动连接：由应用程序使用
- 空闲连接：可供应用程序使用。
当收到来自后端服务的新的数据访问请求时，池管理器将检查池是否包含任何未使用的连接，并返回一个可用的连接。如果池中的所有连接均处于活动状态，则将创建新的连接，并由池管理器将其添加到池中。当池达到最大容量时，所有新连接都会排队，直到池中的连接可用为止。
尽管大多数数据库都没有内置的连接池系统，但是有一些中间件解决方案可用于从客户端进行池连接。
对于PostgreSQL数据库服务器，pgbouncer和pgpool-II都可以充当Web服务和Postgres数据库之间的池接口。这两个中间件使用相同的逻辑来池化来自客户端的连接。
pgpool-II提供了连接池之外的更多功能，例如复制、负载均衡和并行查询。
如何添加连接池？它与安装应用程序一样简单吗？
集成连接池的两种方法
这里讲两种实现PostgreSQL应用程序连接池的方法：
1. 作为外部服务或中间件，例如pgbouncer
诸如pgbouncer和pgpool-II之类的连接池可用于池化从客户端到PostgreSQL数据库的连接。连接池位于应用程序和数据库服务器之间。可以将pgbouncer或pgpool-II配置为将请求从应用程序中继到数据库服务器。
2. 客户端库，例如c3p0
存在诸如c3p0之类的库，这些库扩展了数据库驱动程序功能以包括连接池的支持。
然而，为应用程序实现连接池的最佳方法是利用外部服务或中间件，因为它更易于设置和管理。此外，像pgpool-II这样的外部中间件还提供了其他功能，例如除池连接之外的负载均衡。
现在，让我们更深入地研究由后端服务连接到Postgres数据库（有或没有池）时发生的情况。
无需连接池即可扩展数据库性能
我们不需要连接池来连接到后端服务。我们可以直接连接到Postgres数据库。为了检查在没有连接池的情况下执行到数据库的并发连接需要花费多长时间，我们将使用pgbench来对Postgres数据库的连接做基准测试。
Pgbench基于TPC-B。TPC-B根据系统每秒可以执行多少事务来衡量吞吐量。Pgbench每个事务执行五个SELECT，INSERT和UPDATE命令。
基于类TPC-B的事务，pgbench在多个并发数据库会话中重复运行相同的SQL命令序列，并计算平均事务速率。
在运行pgbench之前，我们需要使用以下命令对其进行初始化，以创建pgbench_history，pgbench_branches，pgbench_tellers和pgbench_accounts表。Pgbench使用下表运行事务以进行基准测试。
pgbench -i -s 50 database_name
之后，执行以下命令来测试150个客户端的数据库：
pgbench -c 10 -j 2 -t 10000 database_name
如您所见，在我们的初始基准测试中，pgbench在十个不同的客户端会话中执行。每个客户端会话将执行10,000个事务。
从这些结果来看，我们的初始基准测试似乎是每秒486个事务。
让我们看看如何利用pgbouncer和pgpool等连接池来提高交易吞吐量，并避免出现 “对不起！已经有太多客户” 这种尴尬。
使用pgbouncer扩展数据库性能
让我们看看如何使用pgbouncer来提高交易吞吐量。
Pgbouncer可以安装在几乎所有Linux发行版上。您可以在此处查看如何设置pgbouncer。另外，您可以使用apt-get或yum等软件包管理器安装pgbouncer。
如果发现使用pgbouncer对客户端进行身份验证很困难，可以查看GitHub上的方法。
Pgbouncer带有三种类型的池：
- 会话池：池中的连接之一已分配给客户端，直到超时为止。
- 事务池：类似于会话轮询，它从池获取连接，并一直保留到交易完成为止。如果同一客户端要运行另一个事务，则必须等待直到获得分配给它的另一个事务。
- 语句池：第一个查询完成后，连接将返回到池中。
我们将使用事务池模式。在pgbouncer.ini文件中，我修改了以下参数：
max_client_conn = 100
max_client_conn参数定义了允许多少个客户端连接到pgbouncer（而不是Postgres）。
default_pool_size = 25
default_pool_size参数定义了每个用户/数据库允许多少个服务器连接。
reserve_pool_size = 5
reserve_pool_size参数定义了允许连到池的附加连接数。
与之前的测试一样，我使用十个不同的客户端会话执行了pgbench。每个客户端执行1,000个事务，如下所示。
pgbench -c 10 -p -j 2 -t 1000 database_name
如您所见，事务吞吐量从每秒486个事务增加到每秒566个事务。借助pgbouncer，交易吞吐量提高了约60％。
现在，让我们看看如何使用具有连接池功能的pgpool-II来提高事务吞吐量。
与pgbouncer不同，pgpool-II提供了超出连接池的功能。该文档提供了有关pgpool-II功能以及如何从源代码或通过软件包管理器进行设置的详细信息。
我更改了pgpool.conf文件中的以下参数，以使其将客户端连接从pgpool-II路由到Postgres数据库服务器。
connection_cache = on
listen_addresses = ‘postgres_database_name’’
port = 5432
将connection_cache参数设置为on将激活pgpool-II池功能。
像之前的测试一样，pgbench执行了十个不同的客户端会话。每个客户端执行到Postgres数据库服务器的事务为1,000个。因此，我们期望所有客户端总共进行10,000次交易。
gbench -p 9999 -c 10 -C -t 1000 postgres_database
以相同的方式，我们使用pgbouncer增加了事务吞吐量，与最初的测试相比，pgpool-II似乎还增加了75％的事务吞吐量。
Pgbouncer实现了“即开即用”的连接池，而无需微调参数，而pgpool-II允许您微调参数以增强连接池。
选择连接池：pgpool-II还是pgbouncer？
选择要使用的连接池时，需要考虑多个因素。尽管pgbouncer和pgpool-II是连接池的出色解决方案，但是每种工具都有其优点和缺点。
内存/资源消耗
如果您倾向于后端服务的轻量级连接池，那么pgbouncer是适合选择。与默认情况下允许派生32个子进程的pgpool-II不同，pgbouncer仅使用一个进程。因此，pgbouncer比pgpool-II消耗更少的内存。
流复制
除了池连接之外，您还可以使用pgpool-II通过流复制来管理Postgres集群。流复制将数据从主节点复制到辅助节点。Pgpool-II支持Postgres流复制，而pgbouncer不支持。这是实现高可用性并防止数据丢失的最佳方法。
集中的密码管理
在生产环境中，您希望许多客户端/应用程序通过连接池同时并发连接到数据库，因此有必要使用集中式密码管理系统来管理客户端的凭据。
您可以使用pgbouncer中的auth_query从数据库加载客户端的凭据，而不是将客户端的凭据存储在userlist.txt文件中，并将连接字符串中的凭据与userlist.txt文件进行比较。
负载均衡与高可用性
最后，如果您想为池化连接增加负载均衡和高可用性，那么pgpool-II是可以使用的正确工具。pgpool-II通过内置的看门狗进程支持Postgres高可用性。这个pgpool-II子进程监视参与看门狗集群的pgpool-II节点的健康状况，以及在多个pgpool-II节点之间进行协调。
结论
除了连接池改善数据库性能外，复制、负载均衡和内存缓存都有助于提高数据库性能。
如果某个Web服务旨在对数据库进行大量读写查询，且您拥有多个Postgres数据库实例，可以通过pgpool-II等负载均衡器来处理来自客户端的写入查询，并用内存缓存优化读取查询。
尽管pgpool-II可以用作负载均衡器和连接池，但pgbouncer是连接池的首选中间件解决方案。因为它易于设置，管理起来不太困难，并且主要用作没有任何其他功能的连接池。
