托管数据库连接池和PostgreSQL基准测试使用pgbench

在本教程中,我们将使用PostgreSQL的内置基准测试工具`pgbench`在DigitalOcean Managed PostgreSQL数据库上运行负载测试。我们将深入了解连接池,描述它们的工作方式,并展示如何使用Cloud Control面板创建连接池。最后,使用来自`pgbench`测试的结果,我们将演示如何使用连接池作为提高数据库吞吐量的廉价方法。

介绍

DigitalOcean托管数据库允许您使用多种方法扩展PostgreSQL数据库。 一种这样的方法是内置连接池,它允许您有效地处理大量客户端连接并减少这些打开连接的CPU和内存占用。 通过使用连接池并共享一组固定的可循环连接,您可以处理更多的并发客户端连接,并从PostgreSQL数据库中挤出额外的性能。

在本教程中,我们将使用PostgreSQL的内置基准测试工具pgbench在DigitalOcean Managed PostgreSQL数据库上运行负载测试。 我们将深入了解连接池,描述它们的工作方式,并展示如何使用Cloud Control面板创建连接池。 最后,使用pgbench测试的结果,我们将演示如何使用连接池作为提高数据库吞吐量的廉价方法。

先决条件

要完成本教程,您需要:

  • DigitalOcean Managed PostgreSQL数据库集群。 要了解如何配置和配置DigitalOcean PostgreSQL集群,请参阅Managed Database 产品文档
  • 安装了PostgreSQL的客户端计算机。 默认情况下,PostgreSQL安装将包含pgbench基准测试实用程序和psql客户端,我们将在本指南中使用它们。 参阅如何在Ubuntu 18.04上安装和使用PostgreSQL,以了解如何安装PostgreSQL。 如果您没有在客户端计算机上运行Ubuntu,则可以使用版本查找器查找相应的教程。

一旦你启动并运行了DigitalOcean PostgreSQL集群和安装了pgbench的客户端机器,你就可以开始使用本指南了。

第1步 - 创建和初始化benchmark数据库

在我们为数据库创建连接池之前,我们首先在PostgreSQL集群上创建benchmark数据库,然后使用pgbench将运行其测试的一些虚拟数据填充它。 pgbench实用程序在事务中使用多个线程和客户端重复运行一系列五个SQL命令(包括SELECTUPDATEINSERT查询),并计算一个名为T ransactions p er S econd(TPS)的有用性能指标。 TPS是数据库吞吐量的度量,计算数据库在一秒钟内处理的原子事务的数量。 要了解有关pgbench执行的特定命令的更多信息,请参阅pgbench 实际执行的“事务”是什么? 来自官方的pgbench文档。

让我们首先连接到PostgreSQL集群并创建benchmark数据库。

首先,通过导航到数据库并找到PostgreSQL集群来检索集群的连接详细信息 单击您的群集。 您应该看到包含以下“ 连接详细信息”框的群集概述页面:

PostgreSQL群集连接详细信息

从这里,我们可以解析以下配置变量:

  • 管理员用户: doadmin
  • 管理员密码: your_password
  • 群集端点: dbaas-test-do-user-3587522-0.db.ondigitalocean.com
  • 连接端口: 25060
  • 要连接的数据库: defaultdb
  • SSL模式: require (使用SSL加密连接以提高安全性)

请注意这些参数,因为在使用psql客户端和pgbench工具时您将需要它们。

单击此框上方的下拉列表,然后选择“ 连接字符串” 我们将复制此字符串并将其传递给psql以连接到此PostgreSQL节点。

使用psql和刚复制的连接字符串连接到集群:

psql postgresql://doadmin:your_password@your_cluster_endpoint:25060/defaultdb?sslmode=require

您应该看到以下PostgreSQL客户端提示符,表明您已成功连接到PostgreSQL集群:

psql (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;

您应该看到以下输出:

CREATE 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 要了解更多信息,请参阅官方pgbench文档中的这些推荐做法

运行完整的pgbench命令:

pgbench -h your_cluster_endpoint -p 25060 -U doadmin -i -s 150 benchmark

运行此命令后,系统将提示您输入指定数据库用户的密码。 输入密码,然后按ENTER

您应该看到以下输出:

dropping 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连接pooler构建在pgBouncer之上, pgBouncer是PostgreSQL的一个轻量级开源中间件连接池。 其界面可通过Cloud Control Panel UI获得。

导航到“控制面板”中的“ 数据库 ”,然后单击“进入PostgreSQL集群”。 在此处,单击“ 连接池” 然后,单击“ 创建连接池” 您应该看到以下配置窗口:

连接池配置窗口

在这里,您可以配置以下字段:

  • 池名称 :连接池的唯一名称
  • 数据库 :您要为其连接池的数据库
  • 用户 :连接池将验证为的PostgreSQL用户
  • 模式会话交易声明之一 此选项控制池为客户端分配后端连接的时间。
    • 会话 :客户端保持连接,直到它明确断开连接。
    • 事务 :客户端获取连接,直到完成事务,然后将连接返回到池。
    • 声明 :池在每个客户端语句后积极地回收连接。 在语句模式下,不允许多语句事务。 要了解更多信息,请参阅Connection Pools 产品文档
  • 池大小 :连接池在其自身与数据库之间保持打开的连接数。

在创建连接池之前,我们将运行一个基线测试,我们可以将数据库性能与连接池进行比较。

在本教程中,我们将使用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参数的多个并发连接。
  • -jpgbench将用于运行基准测试的工作线程数。 如果您使用的是多CPU计算机,则可以向上调整此计算机以跨线程分发客户端。 在双核机器上,我们将其设置为2
  • -P :每60秒显示进度和指标。
  • -T :运行基准测试600秒(10分钟)。 为了产生一致,可重复的结果,重要的是您运行基准测试几分钟,或通过一个检查点周期。

我们还将指定我们希望针对我们之前创建并填充的benchmark数据库运行基准benchmark

运行以下完整的pgbench命令:

pgbench -h your_db_endpoint -p 25060 -U doadmin -c 50 -j 2 -P 60 -T 600 benchmark

ENTER ,然后输入doadmin用户的密码以开始运行测试。 您应该看到类似于以下内容的输出(结果将取决于PostgreSQL集群的规范):

starting 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)

在这里,我们观察到,在50个并发会话的10分钟运行中,我们处理了105,256个事务,吞吐量大约为每秒175个事务。

现在,让我们运行相同的测试,这次使用150个并发客户端,一个高于此数据库的max_connections的值,以综合模拟客户端连接的大量涌入:

pgbench -h your_db_endpoint -p 25060 -U doadmin -c 150 -j 2 -P 60 -T 600 benchmark

您应该看到类似于以下内容的输出:

starting 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托管数据库中的用户抽象出来的,因此我们的主要杠杆是连接池模式大小设置。

首先,让我们在事务模式下创建一个连接池,以保持打开所有可用的后端连接。

导航到“控制面板”中的“ 数据库 ”,然后单击“进入PostgreSQL集群”。 在此处,单击“ 连接池” 然后,单击“ 创建连接池”

在显示的配置窗口中,填写以下值:

连接池配置值

这里我们将连接池测试池命名 ,并将其与基准数据库一起使用。 我们的数据库用户是doadmin ,我们将连接池设置为Transaction模式。 回想一下,对于具有4GB RAM的托管数据库集群,有97个可用的数据库连接。 因此,配置池以保持打开97数据库连接。

完成后,点击Create Pool

您现在应该在控制面板中看到此池:

控制面板中的连接池

通过单击“ 连接详细信息”获取其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 ,即连接池的名称。

测试完成后,您应该看到类似于以下内容的输出(请注意,这些结果将根据您的数据库节点的规格而有所不同):

starting 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)

请注意,我们能够将数据库的吞吐量从257 TPS增加到344 TPS,并发150个并发连接(增加33%),并且没有遇到我们之前没有连接池时遇到的max_connections限制。 通过在数据库前放置连接池,我们可以避免在具有大量同时连接的环境中断开连接并显着提高数据库吞吐量。

如果运行相同的测试,但-c值为50(指定较少数量的客户端),则使用连接池的收益变得不那么明显:

starting 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文档

注意: 池大小设置是高度特定于工作负载的。 在本指南中,我们将连接池配置为使用所有可用的后端数据库连接。 这是因为在整个基准测试中,数据库很少达到完全利用率(您可以从云控制面板中的“ 度量标准”选项卡监视数据库负载)。 根据数据库的负载,这可能不是最佳设置。 如果您注意到数据库始终处于完全饱和状态,则缩小连接池可能会通过排队其他请求而不是尝试在已加载的服务器上同时执行所有请求来提高吞吐量并提高性能。

结论

DigitalOcean托管数据库连接池是一项功能强大的功能,可帮助您快速从数据库中挤出额外的性能。 除了复制,缓存和分片等其他技术外,连接池还可以帮助您扩展数据库层以处理更大量的请求。

在本指南中,我们使用PostgreSQL内置的pgbench基准测试工具及其默认基准测试,专注于简单的综合测试场景。 在任何生产场景中,您应该在模拟生产负载的同时针对实际生产数据运行基准测试。 这将允许您根据特定的使用模式调整数据库。

pgbench ,还有其他工具可用于对数据库进行基准测试和加载。 由Percona开发的一个这样的工具是sysbench-tpcc 另一个是Apache的JMeter ,它可以加载测试数据库和Web应用程序。

要了解有关DigitalOcean托管数据库的更多信息,请参阅托管数据库产品文档 要了解有关分片的更多信息,另一种有用的缩放技术,请参阅了解数据库分片。

参考