使用pgbench进行托管数据库连接池和PostgreSQL基准测试
介绍
DigitalOcean Managed Databases 允许您使用多种方法扩展 PostgreSQL 数据库。 一种这样的方法是内置连接池，它允许您有效地处理大量客户端连接并减少这些打开连接的 CPU 和内存占用。 通过使用连接池并共享一组固定的可回收连接，您可以处理更多的并发客户端连接，并从 PostgreSQL 数据库中获得额外的性能。
在本教程中，我们将使用 PostgreSQL 的内置基准测试工具 pgbench 在 DigitalOcean 托管 PostgreSQL 数据库上运行负载测试。 我们将深入研究连接池，描述它们的工作原理，并展示如何使用云控制面板创建一个。 最后，使用 pgbench 测试的结果，我们将演示如何使用连接池成为一种增加数据库吞吐量的廉价方法。
先决条件
要完成本教程，您需要：
- 一个 DigitalOcean 托管的 PostgreSQL 数据库集群。 要了解如何配置和配置 DigitalOcean PostgreSQL 集群，请参阅托管数据库 产品文档 。
- 安装了 PostgreSQL 的客户端计算机。 默认情况下，您的 PostgreSQL 安装将包含
pgbench基准测试实用程序和psql客户端，我们将在本指南中使用这两者。 参考如何在Ubuntu 18.04上安装和使用PostgreSQL，了解如何安装PostgreSQL。 如果您的客户端计算机上没有运行 Ubuntu，您可以使用版本查找器来查找相应的教程。
一旦您启动并运行了 DigitalOcean PostgreSQL 集群并安装了 pgbench 的客户端计算机，您就可以开始阅读本指南了。
第 1 步 - 创建和初始化 benchmark 数据库
在为数据库创建连接池之前，我们将首先在 PostgreSQL 集群上创建 benchmark 数据库，并使用 pgbench 将在其上运行测试的一些虚拟数据填充它。 pgbench 实用程序使用多个线程和客户端，并计算一个有用的性能指标，称为 Transactions per Second (TPS)。 TPS是衡量数据库吞吐量的指标，统计数据库在一秒内处理的原子事务数。 要详细了解 pgbench 执行的具体命令，请参阅 pgbench 中实际执行的“事务”是什么？ 来自官方 pgbench 文档。
让我们首先连接到我们的 PostgreSQL 集群并创建 benchmark 数据库。
首先，通过导航到 Databases 并找到您的 PostgreSQL 集群来检索集群的 Connection Details。 单击进入您的集群。 您应该会看到一个集群概览页面，其中包含以下 Connection Details 框：
由此，我们可以解析以下配置变量：
- 管理员用户：
doadmin - 管理员密码：your_password
- 集群端点：
dbaas-test-do-user-3587522-0.db.ondigitalocean.com - 连接端口：
25060 - 要连接的数据库：
defaultdb - SSL 模式：
require（使用 SSL 加密连接以提高安全性）
记下这些参数，因为在使用 psql 客户端和 pgbench 工具时都需要它们。
单击此框上方的下拉菜单并选择 Connection String。 我们将复制此字符串并将其传递给 psql 以连接到此 PostgreSQL 节点。
使用 psql 和您刚刚复制的连接字符串连接到您的集群：
psql postgresql://doadmin:your_password@your_cluster_endpoint:25060/defaultdb?sslmode=require
您应该会看到以下 PostgreSQL 客户端提示，表明您已成功连接到 PostgreSQL 集群：
Outputpsql (10.6 (Ubuntu 10.6-0ubuntu0.18.04.1)) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. defaultdb=>
从这里，创建 benchmark 数据库：
CREATE DATABASE benchmark;
您应该看到以下输出：
OutputCREATE DATABASE
现在，断开与集群的连接：
\q
在我们运行 pgbench 测试之前，我们需要使用一些表和运行测试所需的虚拟数据填充这个 benchmark 数据库。
为此，我们将使用以下标志运行 pgbench：
-h：PostgreSQL 集群端点-p：PostgreSQL集群连接端口-U：数据库用户名-i：表示我们想用基准测试表及其虚拟数据初始化benchmark数据库。-s：设置比例因子为 150，它将表格大小乘以 150。1的默认比例因子会生成以下大小的表格：table # of rows --------------------------------- pgbench_branches 1 pgbench_tellers 10 pgbench_accounts 100000 pgbench_history 0
使用 150 的比例因子，
pgbench_accounts表将包含 15,000,000 行。注意： 为避免过多的阻塞事务，请确保将比例因子设置为至少与您打算测试的并发客户端数量一样大的值。 在本教程中，我们将最多使用 150 个客户端进行测试，因此我们在此处将
-s设置为 150。 要了解更多信息，请参阅官方pgbench文档中的这些 推荐做法。
运行完整的 pgbench 命令：
pgbench -h your_cluster_endpoint -p 25060 -U doadmin -i -s 150 benchmark
运行此命令后，系统将提示您输入您指定的数据库用户的密码。 输入密码，然后点击ENTER。
您应该看到以下输出：
Outputdropping old tables... NOTICE: table "pgbench_accounts" does not exist, skipping NOTICE: table "pgbench_branches" does not exist, skipping NOTICE: table "pgbench_history" does not exist, skipping NOTICE: table "pgbench_tellers" does not exist, skipping creating tables... generating data... 100000 of 15000000 tuples (0%) done (elapsed 0.19 s, remaining 27.93 s) 200000 of 15000000 tuples (1%) done (elapsed 0.85 s, remaining 62.62 s) 300000 of 15000000 tuples (2%) done (elapsed 1.21 s, remaining 59.23 s) 400000 of 15000000 tuples (2%) done (elapsed 1.63 s, remaining 59.44 s) 500000 of 15000000 tuples (3%) done (elapsed 2.05 s, remaining 59.51 s) . . . 14700000 of 15000000 tuples (98%) done (elapsed 70.87 s, remaining 1.45 s) 14800000 of 15000000 tuples (98%) done (elapsed 71.39 s, remaining 0.96 s) 14900000 of 15000000 tuples (99%) done (elapsed 71.91 s, remaining 0.48 s) 15000000 of 15000000 tuples (100%) done (elapsed 72.42 s, remaining 0.00 s) vacuuming... creating primary keys... done.
此时，我们已经创建了一个基准测试数据库，其中填充了运行 pgbench 测试所需的表和数据。 我们现在可以继续运行基线测试，我们将使用它来比较启用连接池之前和之后的性能。
第 2 步 — 运行基线 pgbench 测试
在我们运行我们的第一个基准测试之前，有必要深入了解我们正在尝试使用连接池进行优化的内容。
通常，当客户端连接到 PostgreSQL 数据库时，主 PostgreSQL OS 进程将自己分叉到与此新连接对应的子进程中。 当只有几个连接时，这很少会出现问题。 但是，随着客户端和连接的扩展，创建和维护这些连接的 CPU 和内存开销开始增加，尤其是在相关应用程序不能有效使用数据库连接的情况下。 此外，max_connections PostgreSQL 设置可能会限制允许的客户端连接数，从而导致其他连接被拒绝或丢弃。
连接池保持打开固定数量的数据库连接，即 池大小 ，然后它使用它来分发和执行客户端请求。 这意味着您可以容纳更多的同时连接，有效地处理空闲或停滞的客户端，以及在流量高峰期间排队客户端请求而不是拒绝它们。 通过回收连接，您可以在连接量很大的环境中更有效地使用计算机资源，并从数据库中挤出额外的性能。
连接池可以在应用程序端实现，也可以作为数据库和应用程序之间的中间件实现。 Managed Databases 连接池建立在 pgBouncer 之上，这是一个用于 PostgreSQL 的轻量级开源中间件连接池。 其界面可通过云控制面板 UI 获得。
导航到控制面板中的 Databases，然后单击进入您的 PostgreSQL 集群。 从这里，点击进入 Connection Pools。 然后，单击创建连接池。 您应该看到以下配置窗口：
在这里，您可以配置以下字段：
- 池名称：连接池的唯一名称
- Database：您要为其建立连接池的数据库
- User：连接池将认证为的 PostgreSQL 用户
- Mode：Session、Transaction 或 Statement 之一。 此选项控制池将后端连接分配给客户端的时间。 会话：客户端保持连接，直到它显式断开连接。 事务：客户端获取连接，直到完成一个事务，然后将连接返回到池中。 语句：池在每个客户端语句之后积极回收连接。 在语句模式下，不允许多语句事务。 要了解更多信息，请参阅连接池产品文档。
- 池大小：连接池将在自身和数据库之间保持打开的连接数。
在我们创建连接池之前，我们将运行一个基线测试，我们可以将数据库性能与连接池进行比较。
在本教程中，我们将使用 4 GB RAM、2 个 vCPU、80 GB 磁盘、仅限主节点的托管数据库设置。 您可以根据您的 PostgreSQL 集群规范扩展本节中的基准测试参数。
DigitalOcean 托管数据库集群的 PostgreSQL max_connections 参数预设为每 1 GB RAM 25 个连接。 因此，4 GB RAM PostgreSQL 节点的 max_connections 设置为 100。 此外，对于所有集群，保留 3 个连接用于维护。 因此，对于这个 4 GB RAM 的 PostgreSQL 集群，有 97 个连接可用于连接池。
考虑到这一点，让我们运行我们的第一个基线 pgbench 测试。
登录到您的客户端计算机。 我们将运行 pgbench，像往常一样指定数据库端点、端口和用户。 此外，我们将提供以下标志：
-c：要模拟的并发客户端或数据库会话的数量。 我们将其设置为 50，以便为我们的 PostgreSQL 集群模拟小于max_connections参数的并发连接数。-j：工作线程数pgbench将用于运行基准测试。 如果您使用的是多 CPU 机器，则可以向上调整以跨线程分配客户端。 在双核机器上，我们将其设置为2。-P：每60秒显示进度和指标。-T：运行基准测试600秒（10 分钟）。 为了产生一致、可重现的结果，运行基准测试几分钟或通过一个检查点循环非常重要。
我们还将指定我们希望针对我们之前创建和填充的 benchmark 数据库运行基准测试。
运行以下完整的 pgbench 命令：
pgbench -h your_db_endpoint -p 25060 -U doadmin -c 50 -j 2 -P 60 -T 600 benchmark
点击 ENTER，然后输入 doadmin 用户的密码以开始运行测试。 您应该看到类似于以下的输出（结果将取决于您的 PostgreSQL 集群的规格）：
Outputstarting vacuum...end. progress: 60.0 s, 157.4 tps, lat 282.988 ms stddev 40.261 progress: 120.0 s, 176.2 tps, lat 283.726 ms stddev 38.722 progress: 180.0 s, 167.4 tps, lat 298.663 ms stddev 238.124 progress: 240.0 s, 178.9 tps, lat 279.564 ms stddev 43.619 progress: 300.0 s, 178.5 tps, lat 280.016 ms stddev 43.235 progress: 360.0 s, 178.8 tps, lat 279.737 ms stddev 43.307 progress: 420.0 s, 179.3 tps, lat 278.837 ms stddev 43.783 progress: 480.0 s, 178.5 tps, lat 280.203 ms stddev 43.921 progress: 540.0 s, 180.0 tps, lat 277.816 ms stddev 43.742 progress: 600.0 s, 178.5 tps, lat 280.044 ms stddev 43.705 transaction type: <builtin: TPC-B (sort of)> scaling factor: 150 query mode: simple number of clients: 50 number of threads: 2 duration: 600 s number of transactions actually processed: 105256 latency average = 282.039 ms latency stddev = 84.244 ms tps = 175.329321 (including connections establishing) tps = 175.404174 (excluding connections establishing)
在这里，我们观察到在 10 分钟内运行 50 个并发会话，我们处理了 105,256 个事务，吞吐量约为每秒 175 个事务。
现在，让我们运行相同的测试，这次使用 150 个并发客户端，该值高于此数据库的 max_connections，以综合模拟大量涌入的客户端连接：
pgbench -h your_db_endpoint -p 25060 -U doadmin -c 150 -j 2 -P 60 -T 600 benchmark
您应该会看到类似于以下内容的输出：
Outputstarting vacuum...end. connection to database "pgbench" failed: FATAL: remaining connection slots are reserved for non-replication superuser connections progress: 60.0 s, 182.6 tps, lat 280.069 ms stddev 42.009 progress: 120.0 s, 253.8 tps, lat 295.612 ms stddev 237.448 progress: 180.0 s, 271.3 tps, lat 276.411 ms stddev 40.643 progress: 240.0 s, 273.0 tps, lat 274.653 ms stddev 40.942 progress: 300.0 s, 272.8 tps, lat 274.977 ms stddev 41.660 progress: 360.0 s, 250.0 tps, lat 300.033 ms stddev 282.712 progress: 420.0 s, 272.1 tps, lat 275.614 ms stddev 42.901 progress: 480.0 s, 261.1 tps, lat 287.226 ms stddev 112.499 progress: 540.0 s, 272.5 tps, lat 275.309 ms stddev 41.740 progress: 600.0 s, 271.2 tps, lat 276.585 ms stddev 41.221 transaction type: <builtin: TPC-B (sort of)> scaling factor: 150 query mode: simple number of clients: 150 number of threads: 2 duration: 600 s number of transactions actually processed: 154892 latency average = 281.421 ms latency stddev = 125.929 ms tps = 257.994941 (including connections establishing) tps = 258.049251 (excluding connections establishing)
注意 FATAL 错误，表示 pgbench 达到了 max_connections 设置的 100 连接限制阈值，导致连接被拒绝。 测试仍然能够完成，TPS 大约为 257。
此时，我们可以研究连接池如何潜在地提高数据库的吞吐量。
第 3 步 — 创建和测试连接池
在这一步中，我们将创建一个连接池并重新运行之前的 pgbench 测试，看看我们是否可以提高数据库的吞吐量。
通常，max_connections 设置和连接池参数会同时调整以最大限度地提高数据库的负载。 然而，因为 max_connections 在 DigitalOcean 托管数据库中从用户那里抽象出来，我们这里的主要杠杆是连接池 Mode 和 Size 设置。
首先，让我们在 Transaction 模式下创建一个连接池，以保持打开所有可用的后端连接。
导航到控制面板中的 Databases，然后单击进入您的 PostgreSQL 集群。 从这里，点击进入 Connection Pools。 然后，单击创建连接池。
在出现的配置窗口中，填写以下值：
这里我们将连接池命名为 test-pool，并将其与 benchmark 数据库一起使用。 我们的数据库用户是 doadmin，我们将连接池设置为 Transaction 模式。 回想一下，对于具有 4GB RAM 的托管数据库集群，有 97 个可用的数据库连接。 因此，将池配置为保持打开的 97 个数据库连接。
完成后，点击 Create Pool。
您现在应该在控制面板中看到这个池：
通过单击 Connection Details 获取其 URI。 它应该如下所示
postgres://doadmin:password@pool_endpoint:pool_port/test-pool?sslmode=require
您应该注意到这里有一个不同的端口，可能还有一个不同的端点和数据库名称，对应于池名称 test-pool。
现在我们已经创建了 test-pool 连接池，我们可以重新运行上面运行的 pgbench 测试。
重新运行 pgbench
在您的客户端计算机上，运行以下 pgbench 命令（具有 150 个并发客户端），确保将突出显示的值替换为连接池 URI 中的值：
pgbench -h pool_endpoint -p pool_port -U doadmin -c 150 -j 2 -P 60 -T 600 test-pool
这里我们再次使用 150 个并发客户端，跨 2 个线程运行测试，每 60 秒打印一次进度，并运行测试 600 秒。 我们将数据库名称设置为test-pool，即连接池的名称。
测试完成后，您应该会看到类似于以下内容的输出（请注意，这些结果将根据您的数据库节点的规格而有所不同）：
Outputstarting vacuum...end. progress: 60.0 s, 240.0 tps, lat 425.251 ms stddev 59.773 progress: 120.0 s, 350.0 tps, lat 428.647 ms stddev 57.084 progress: 180.0 s, 340.3 tps, lat 440.680 ms stddev 313.631 progress: 240.0 s, 364.9 tps, lat 411.083 ms stddev 61.106 progress: 300.0 s, 366.5 tps, lat 409.367 ms stddev 60.165 progress: 360.0 s, 362.5 tps, lat 413.750 ms stddev 59.005 progress: 420.0 s, 359.5 tps, lat 417.292 ms stddev 60.395 progress: 480.0 s, 363.8 tps, lat 412.130 ms stddev 60.361 progress: 540.0 s, 351.6 tps, lat 426.661 ms stddev 62.960 progress: 600.0 s, 344.5 tps, lat 435.516 ms stddev 65.182 transaction type: <builtin: TPC-B (sort of)> scaling factor: 150 query mode: simple number of clients: 150 number of threads: 2 duration: 600 s number of transactions actually processed: 206768 latency average = 421.719 ms latency stddev = 114.676 ms tps = 344.240797 (including connections establishing) tps = 344.385646 (excluding connections establishing)
请注意，我们能够在 150 个并发连接的情况下将数据库的吞吐量从 257 TPS 增加到 344 TPS（增加 33%），并且没有达到我们之前在没有连接池的情况下达到的 max_connections 限制. 通过在数据库前面放置一个连接池，我们可以避免连接丢失，并在同时连接大量的环境中显着提高数据库吞吐量。
如果您运行相同的测试，但 -c 值为 50（指定较少数量的客户端），则使用连接池的好处变得不那么明显：
Outputstarting vacuum...end. progress: 60.0 s, 154.0 tps, lat 290.592 ms stddev 35.530 progress: 120.0 s, 162.7 tps, lat 307.168 ms stddev 241.003 progress: 180.0 s, 172.0 tps, lat 290.678 ms stddev 36.225 progress: 240.0 s, 172.4 tps, lat 290.169 ms stddev 37.603 progress: 300.0 s, 177.8 tps, lat 281.214 ms stddev 35.365 progress: 360.0 s, 177.7 tps, lat 281.402 ms stddev 35.227 progress: 420.0 s, 174.5 tps, lat 286.404 ms stddev 34.797 progress: 480.0 s, 176.1 tps, lat 284.107 ms stddev 36.540 progress: 540.0 s, 173.1 tps, lat 288.771 ms stddev 38.059 progress: 600.0 s, 174.5 tps, lat 286.508 ms stddev 59.941 transaction type: <builtin: TPC-B (sort of)> scaling factor: 150 query mode: simple number of clients: 50 number of threads: 2 duration: 600 s number of transactions actually processed: 102938 latency average = 288.509 ms latency stddev = 83.503 ms tps = 171.482966 (including connections establishing) tps = 171.553434 (excluding connections establishing)
在这里，我们看到我们无法通过使用连接池来增加吞吐量。 我们的吞吐量从 175 TPS 下降到 171 TPS。
尽管在本指南中我们使用 pgbench 及其内置基准数据集，但确定是否使用连接池的最佳测试是准确表示数据库上的生产负载的基准负载，与生产数据. 创建自定义基准测试脚本和数据超出了本指南的范围，但要了解更多信息，请参阅官方 pgbench 文档。
注意： 池大小 设置是高度特定于工作负载的。 在本指南中，我们将连接池配置为使用所有可用的后端数据库连接。 这是因为在我们的基准测试中，数据库很少达到完全利用率（您可以从云控制面板中的 Metrics 选项卡监控数据库负载）。 根据您的数据库负载，这可能不是最佳设置。 如果您注意到您的数据库一直处于完全饱和状态，那么缩小连接池可能会通过将其他请求排队而不是尝试在已加载的服务器上同时执行所有请求来增加吞吐量并提高性能。
结论
DigitalOcean Managed Databases 连接池是一项强大的功能，可以帮助您快速从数据库中提取额外的性能。 与复制、缓存和分片等其他技术一起，连接池可以帮助您扩展数据库层以处理更大数量的请求。
在本指南中，我们专注于使用 PostgreSQL 内置的 pgbench 基准测试工具及其默认基准测试的简单综合测试场景。 在任何生产场景中，您都应该在模拟生产负载的同时针对实际生产数据运行基准测试。 这将允许您针对特定的使用模式调整数据库。
除了 pgbench，还有其他工具可用于基准测试和加载数据库。 Percona 开发的此类工具之一是 sysbench-tpcc。 另一个是 Apache 的 JMeter，它可以加载测试数据库以及 Web 应用程序。
要了解有关 DigitalOcean 托管数据库的更多信息，请参阅托管数据库 产品文档 。 要了解有关分片（另一种有用的缩放技术）的更多信息，请参阅 Understanding Database Sharding。
