通过连接池提高数据库性能

我们倾向于依靠缓存解决方案来提高数据库性能。在内存中或通过数据库缓存频繁访问的查询可以优化写/读性能并减少网络延迟，特别是对于工作量大的应用程序，如游戏服务和Q&a门户。但是，您可以通过将用户连接到数据库来进一步提高性能。

客户端用户需要先创建到web服务的连接，然后才能执行CRUD操作。大多数web服务都由Postgres或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数据库服务器与其他框架（如logstashshipper）以及收集性能指标的其他服务器隔离开来也很重要，因为这些组件中的大多数会消耗更多内存，并会影响测试结果。

创建池连接

连接到后端服务是一项昂贵的操作，因为它包括以下步骤：

• 使用数据库驱动程序打开与数据库的连接。

• 为CRUD操作打开TCP套接字。

• 通过套接字执行CRUD操作。

• 关闭连接。

• 关闭套接字。

在一个生产环境中，我们期望来自客户机的数千个并发的打开和关闭连接，对每个连接执行上述步骤可能会导致数据库性能不佳。

我们可以通过汇集来自客户端的连接来解决这个问题。连接池不会为每个请求创建新的连接，而是重用一些现有的连接。因此，不需要通过打开和关闭到后端服务的连接来执行多个昂贵的完整数据库访问。它可以防止每次请求具有相同属性（即名称、数据库、协议版本）的数据库连接时创建到数据库的新连接的开销。

像pgbouncer这样的池中间件附带了一个池管理器。通常，连接池管理器维护一个开放数据库连接池。如果没有池管理器，则不能将连接池化。

池包含两种类型的连接：

• 活动连接：由应用程序使用。

• 空闲连接：可由应用程序使用。

当从后端服务访问数据的新请求传入时，池管理器检查池是否包含任何未使用的连接，如果可用，则返回一个连接。如果池中的所有连接都处于活动状态，则池管理器将创建一个新连接并将其添加到池中。当池达到其最大大小时，所有新连接都将排队，直到池中的连接可用为止。

尽管大多数数据库都没有内置的连接池系统，但是我们可以使用中间件解决方案来汇集来自客户端的连接。

对于PostgreSQL数据库服务器，pgbouncer和pgpool II都可以作为web服务和Postgres数据库之间的池接口。两个实用程序使用相同的逻辑来汇集来自客户端的连接。

pgpool II提供了连接池之外的更多特性，如复制、负载平衡和并行查询特性。

如何添加连接池？它和安装实用程序一样简单吗？

集成连接池的两种方法

有两种方法可以实现PostgreSQL应用程序的连接池：

1.作为外部服务或中间件，如pgbouncer

连接池（如pgbouncer和pgpool II）可用于将客户机到PostgreSQL数据库的连接池化。连接池位于应用程序和数据库服务器之间。Pgbouncer或pgpool II可以配置为将来自应用程序的请求中继到数据库服务器。

2.客户端库，如c3p0

存在一些库，例如c3p0，它们扩展了数据库驱动程序功能，以包括连接池支持。

但是，为应用程序实现连接池的最佳方法是使用外部服务或中间件，因为它更易于设置和管理。此外，像pgpool2这样的外部中间件除了池连接之外还提供了负载平衡等其他特性。

现在，让我们更深入地了解一下当后端服务连接到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使用十个不同的客户端会话执行。每个客户端会话将执行10000个事务。

从这些结果来看，我们的初始基线测试似乎是每秒486个事务。

让我们看看如何使用连接池（如pgbouncer和pgpool）来提高事务吞吐量并避免“抱歉！，已经有太多客户端“错误。

使用pgbouncer扩展数据库性能

让我们看看如何使用pgbouncer来提高事务吞吐量。

Pgbouncer几乎可以安装在所有Linux发行版上。您可以在这里查看如何设置pgbouncer。或者，可以使用包管理器安装pgbouncer，如apt-get或yum。

如果您发现很难用pgbouncer对客户机进行身份验证，您可以查看GitHub以了解如何进行身份验。

Pgbouncer提供三种类型的池：

1. 会话池：池中的一个连接被分配给客户端，直到达到超时。

2. 事务池：与会话轮询类似，它从池中获取连接。它会一直保存到交易完成。如果同一个客户端要运行另一个事务，它必须等待，直到它获得分配给它的另一个事务。

3. 语句池：第一个查询完成后，连接就返回到池中。

我们将使用事务池模式。在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参数定义允许到池的附加连接数。 

在上一个测试中，我使用10个不同的客户端会话执行pgbench。每个客户机执行1000个事务，如下所示

pgbench  -c 10  -p -j 2  -t 1000 database_name    

如您所见，事务吞吐量从每秒486个事务增加到每秒566个事务。在pgbouncer的帮助下，事务吞吐量提高了约60%。

现在让我们看看如何使用pgpool II提高事务吞吐量，因为它具有连接池特性。

与pgbouncer不同，pgpool II提供了连接池以外的功能。该文档提供了有关pgpool II特性的详细信息，以及如何从源代码或通过包管理器进行设置。

我更改了pgpool.conf文件中的以下参数，使其将客户端连接从pgpool2路由到Postgres数据库服务器。

connection_cache  = on  listen_addresses  = ‘postgres_database_name’’  port  = 5432 

将connection_cache参数设置为on将激活pgpool2池功能。

与前面的测试一样，pgbench执行了10个不同的客户端会话。每个客户机向Postgres数据库服务器执行1000个事务。因此，我们预计所有客户共有10000笔交易。

gbench  -p 9999  -c  10  -C  -t 1000  postgres_database  

与我们使用pgbouncer增加事务吞吐量的方式相同，与初始测试相比，pgpool2似乎也将事务吞吐量增加了75%。

Pgbouncer实现“开箱即用”的连接池，而无需微调参数，而pgpool2允许您微调参数以增强连接池。

选择连接池：pgpool II还是pgbouncer？

在选择要使用的连接池时，有几个因素需要考虑。尽管pgbouncer和pgpool II是连接池的优秀解决方案，但每种工具都有其优缺点

内存/资源消耗

如果您对后端服务的轻量级连接池感兴趣，那么pgbouncer就是您的合适工具。与pgpool II不同，pgbouncer默认允许分叉32个子进程，而pgpool II只使用一个进程。因此，pgbouncer比pgpool2消耗更少的内存。

流式复制

除了池连接之外，您还可以使用pgpool II通过流复制管理Postgres集群。流复制将数据从主节点复制到辅助节点。Pgpool II支持Postgres流复制，而pgbouncer不支持。这是实现高可用性和防止数据丢失的最佳方法。

集中密码管理

在希望许多客户机/应用程序同时通过连接池连接到数据库的生产环境中，有必要使用集中式密码管理系统来管理客户机的凭据。

您可以使用pgbouncer中的authu query从数据库加载客户端凭据，而不是将客户端凭据存储在userlist.txt文件中，并将连接字符串中的凭据与userlist.txt文件进行比较。

负载平衡和高可用性

最后，如果您想将负载平衡和高可用性添加到池连接中，那么pgpool2是合适的工具。pgpool2通过内置的看门狗进程支持Postgres的高可用性。此pgpool2子进程监视参与监视程序群集的pgpool2节点的运行状况以及多个pgpool2节点之间的协调。

结论

除了连接池之外，还可以提高数据库性能。复制、负载平衡和内存缓存有助于提高数据库性能。

如果一个web服务被设计为对一个数据库进行大量的读写查询，那么您就有多个Postgres数据库实例，通过负载均衡器（如pgpool II）处理来自客户端的写查询，而内存缓存可以用来优化读查询。

尽管pgpool II能够充当加载程序平衡器和连接池，但pgbouncer是连接池的首选中间件解决方案，因为它易于设置，不太难管理，主要用作连接池，不需要任何其他功能。