如何在Ubuntu18.04上使用ElasticStack分析托管PostgreSQL数据库统计信息

作为 Write for DOnations 计划的一部分，作者选择了 Free and Open Source Fund 来接受捐赠。

介绍

数据库监控是系统地跟踪各种指标的持续过程，这些指标显示了数据库的执行情况。通过观察性能数据，您可以获得有价值的见解并识别可能的瓶颈，并找到提高数据库性能的其他方法。此类系统通常会实施警报，当出现问题时会通知管理员。收集的统计信息不仅可以用来改进数据库的配置和工作流程，还可以用来改进客户端应用程序的配置和工作流程。

使用 Elastic Stack（ELK 堆栈）监控托管数据库的好处是它对搜索的出色支持和快速获取新数据的能力。它不擅长更新数据，但这种权衡对于监控和记录目的是可以接受的，因为过去的数据几乎从不改变。 Elasticsearch 提供了一种强大的数据查询方式，您可以通过 Kibana 使用它来更好地了解数据库在不同时间段的运行情况。这将允许您将数据库负载与现实事件相关联，以深入了解数据库的使用方式。

在本教程中，您将通过 Logstash 将 PostgreSQL 统计收集器生成的数据库指标导入 Elasticsearch。这需要配置 Logstash 以使用 PostgreSQL JDBC 连接器从数据库中提取数据，然后立即将其发送到 Elasticsearch 进行索引。导入的数据稍后可以在 Kibana 中进行分析和可视化。然后，如果您的数据库是全新的，您将使用 PostgreSQL 基准测试工具 pgbench 来创建更有趣的可视化。最后，您将拥有一个自动系统提取 PostgreSQL 统计信息以供以后分析。

先决条件

具有至少 8 GB RAM、root 权限和辅助非 root 帐户的 Ubuntu 18.04 服务器。您可以按照这个初始服务器设置指南进行设置。对于本教程，非 root 用户是 sammy。
您的服务器上安装了 Java 8。有关安装说明，请访问如何在 Ubuntu 18.04 上使用 apt 安装 Java。
Nginx 安装在您的服务器上。有关如何执行此操作的指南，请参阅如何在 Ubuntu 18.04 上安装 Nginx。
您的服务器上安装了 Elasticsearch 和 Kibana。完成如何在 Ubuntu 18.04 上安装 Elasticsearch、Logstash 和 Kibana (Elastic Stack) 教程的前两个步骤。
从 DigitalOcean 提供的 PostgreSQL 13 托管数据库，提供可用的连接信息。确保您的服务器的 IP 地址在白名单中。有关使用 DigitalOcean 控制面板创建 PostgreSQL 托管数据库的指南，请访问 PostgreSQL 快速入门指南。

第 1 步 — 设置 Logstash 和 PostgreSQL JDBC 驱动程序

在本节中，您将安装 Logstash 并下载 PostgreSQL JDBC 驱动程序，以便 Logstash 能够连接到您的托管数据库。

首先使用以下命令安装 Logstash：

sudo apt install logstash -y

安装 Logstash 后，启用该服务以在启动时自动启动：

sudo systemctl enable logstash

Logstash 是用 Java 编写的，因此为了连接到 PostgreSQL，它需要在运行它的系统上提供 PostgreSQL JDBC（Java 数据库连接）库。由于内部限制，Logstash 只有在存储它使用的第三方库的 /usr/share/logstash/logstash-core/lib/jars 目录下才能正确加载库。

前往 JDBC 库的下载页面并将链接复制到最新版本。然后，通过运行以下命令使用 curl 下载它：

sudo curl https://jdbc.postgresql.org/download/postgresql-42.3.3.jar -o /usr/share/logstash/logstash-core/lib/jars/postgresql-jdbc.jar

在撰写本文时，该库的最新版本是 42.3.3，支持的运行时版本是 Java 8。确保下载最新版本；将它与 JDBC 和 Logstash 都支持的正确 Java 版本配对。 Logstash 将其配置文件存储在 /etc/logstash/conf.d 下，并且它本身存储在 /usr/share/logstash/bin 下。

您已经使用 apt 安装了 Logstash 并下载了 PostgreSQL JDBC 库，以便 Logstash 可以使用它连接到您的托管数据库。在下一步中，您将配置 Logstash 以从中提取统计数据。

第 2 步 — 配置 Logstash 以获取统计信息

在本节中，您将配置 Logstash 以从托管的 PostgreSQL 数据库中提取指标。

您将配置 Logstash 以监视 PostgreSQL 中的三个系统数据库，即：

pg_stat_database：提供有关每个数据库的统计信息，包括其名称、连接数、事务、回滚、查询数据库返回的行、死锁等。它有一个 stats_reset 字段，指定上次重置统计信息的时间。
pg_stat_user_tables：提供用户创建的每个表的统计信息，例如插入、删除和更新的行数。
pg_stat_user_indexes：收集有关用户创建的表中所有索引的数据，例如特定索引已被扫描的次数。

您将在 Elasticsearch 中将用于索引 PostgreSQL 统计信息的配置存储在 /etc/logstash/conf.d 目录下名为 postgresql.conf 的文件中，Logstash 存储配置文件的位置。当作为服务启动时，它将自动在后台运行它们。

使用您喜欢的编辑器（例如 nano）创建 postgresql.conf：

sudo nano /etc/logstash/conf.d/postgresql.conf

添加以下行：

/etc/logstash/conf.d/postgresql.conf

input {
        # pg_stat_database
        jdbc {
                jdbc_driver_library => ""
                jdbc_driver_class => "org.postgresql.Driver"
                jdbc_connection_string => "jdbc:postgresql://host:port/defaultdb"
                jdbc_user => "username"
                jdbc_password => "password"
                statement => "SELECT * FROM pg_stat_database"
                schedule => "* * * * *"
                type => "pg_stat_database"
        }

        # pg_stat_user_tables
        jdbc {
                jdbc_driver_library => ""
                jdbc_driver_class => "org.postgresql.Driver"
                jdbc_connection_string => "jdbc:postgresql://host:port/defaultdb"
                jdbc_user => "username"
                jdbc_password => "password"
                statement => "SELECT * FROM pg_stat_user_tables"
                schedule => "* * * * *"
                type => "pg_stat_user_tables"
        }

        # pg_stat_user_indexes
        jdbc {
                jdbc_driver_library => ""
                jdbc_driver_class => "org.postgresql.Driver"
                jdbc_connection_string => "jdbc:postgresql://host:port/defaultdb"
                jdbc_user => "username"
                jdbc_password => "password"
                statement => "SELECT * FROM pg_stat_user_indexes"
                schedule => "* * * * *"
                type => "pg_stat_user_indexes"
        }
}

output {
        elasticsearch {
                hosts => "http://localhost:9200"
                index => "%{type}"
        }
}

请记住将 host 替换为您的主机地址，将 port 替换为您可以连接到数据库的端口，将 username 替换为数据库用户用户名，并将 [X172X ] 及其密码。所有这些值都可以在托管数据库的控制面板中找到。

在此配置中，您定义了三个 JDBC 输入和一个 Elasticsearch 输出。这三个输入分别从 pg_stat_database、pg_stat_user_tables 和 pg_stat_user_indexes 数据库中提取数据。他们都将 jdbc_driver_library 参数设置为空字符串，因为 PostgreSQL JDBC 库位于 Logstash 自动加载的文件夹中。

然后，他们设置 jdbc_driver_class，其值是特定于 JDBC 库的，并提供一个 jdbc_connection_string，详细说明如何连接到数据库。 jdbc: 部分表示它是 JDBC 连接，而 postgres:// 表示目标数据库是 PostgreSQL。接下来是数据库的主机和端口，在正斜杠之后还指定要连接的数据库；这是因为 PostgreSQL 要求您连接到数据库才能发出任何查询。在这里，它被设置为始终存在且不能被删除的默认数据库，恰当地命名为defaultdb。

接下来，他们设置访问数据库的用户的用户名和密码。 statement 参数包含一个 SQL 查询，该查询应返回您希望处理的数据——在此配置中，它从适当的数据库中选择所有行。

schedule 参数接受 cron 语法中的字符串，定义 Logstash 何时应该运行此输入；完全省略它会使 Logstash 只运行一次。正如您在此处所做的那样，指定 * * * * * 将告诉 Logstash 每分钟运行一次。如果您想以不同的时间间隔收集数据，您可以指定自己的 cron 字符串。

只有一个输出，它接受来自三个输入的数据。它们都将数据发送到 Elasticsearch，后者在本地运行，可在 http://localhost:9200 访问。 index 参数定义它将数据发送到哪个 Elasticsearch 索引，其值从输入的 type 字段传入。

完成编辑后，保存并关闭文件。

您已将 Logstash 配置为从各种 PostgreSQL 统计表中收集数据并将它们发送到 Elasticsearch 以进行存储和索引。接下来，您将运行 Logstash 来测试配置。

第 3 步 — 测试 Logstash 配置

在本节中，您将通过运行 Logstash 来测试配置，以验证它是否能够正确提取数据。然后，您将通过将其配置为 Logstash 管道来使此配置在后台运行。

Logstash 支持通过将其文件路径传递给 -f 参数来运行特定配置。运行以下命令以测试上一步的新配置：

sudo /usr/share/logstash/bin/logstash -f /etc/logstash/conf.d/postgresql.conf

它可能需要一些时间才能显示任何输出，类似于以下内容：

OutputUsing bundled JDK: /usr/share/logstash/jdk
OpenJDK 64-Bit Server VM warning: Option UseConcMarkSweepGC was deprecated in version 9.0 and will likely be removed in a future release.
WARNING: Could not find logstash.yml which is typically located in $LS_HOME/config or /etc/logstash. You can specify the path using --path.settings. Continuing using the defaults
Could not find log4j2 configuration at path /usr/share/logstash/config/log4j2.properties. Using default config which logs errors to the console
[INFO ] 2022-02-24 08:49:36.664 [main] runner - Starting Logstash {"logstash.version"=>"7.17.0", "jruby.version"=>"jruby 9.2.20.1 (2.5.8) 2021-11-30 2a2962fbd1 OpenJDK 64-Bit Server VM 11.0.13+8 on 11.0.13+8 +indy +jit [linux-x86_64]"}
[INFO ] 2022-02-24 08:49:36.671 [main] runner - JVM bootstrap flags: [-Xms1g, -Xmx1g, -XX:+UseConcMarkSweepGC, -XX:CMSInitiatingOccupancyFraction=75, -XX:+UseCMSInitiatingOccupancyOnly, -Djava.awt.headless=true, -Dfile.encoding=UTF-8, -Djruby.compile.invokedynamic=true, -Djruby.jit.threshold=0, -Djruby.regexp.interruptible=true, -XX:+HeapDumpOnOutOfMemoryError, -Djava.security.egd=file:/dev/urandom, -Dlog4j2.isThreadContextMapInheritable=true]
[INFO ] 2022-02-24 08:49:36.700 [main] settings - Creating directory {:setting=>"path.queue", :path=>"/usr/share/logstash/data/queue"}
[INFO ] 2022-02-24 08:49:36.710 [main] settings - Creating directory {:setting=>"path.dead_letter_queue", :path=>"/usr/share/logstash/data/dead_letter_queue"}
[WARN ] 2022-02-24 08:49:36.992 [LogStash::Runner] multilocal - Ignoring the 'pipelines.yml' file because modules or command line options are specified
[INFO ] 2022-02-24 08:49:37.018 [LogStash::Runner] agent - No persistent UUID file found. Generating new UUID {:uuid=>"bfd27cc5-f2d0-4b19-8870-a125586135ed", :path=>"/usr/share/logstash/data/uuid"}
[INFO ] 2022-02-24 08:49:38.085 [Api Webserver] agent - Successfully started Logstash API endpoint {:port=>9600, :ssl_enabled=>false}
[INFO ] 2022-02-24 08:49:39.284 [Converge PipelineAction::Create<main>] Reflections - Reflections took 68 ms to scan 1 urls, producing 119 keys and 417 values
...
[INFO ] 2022-02-24 08:50:03.102 [Ruby-0-Thread-34@[d39f109727b9e1a2b881639e708f21ce1d65378257869071cbed233a3946468d]<jdbc__scheduler_worker-00: /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/logstash-integration-jdbc-5.2.2/lib/logstash/plugin_mixins/jdbc/scheduler.rb:77] jdbc - (0.194969s) SELECT * FROM pg_stat_user_tables
[INFO ] 2022-02-24 08:50:03.104 [Ruby-0-Thread-32@[bd7d166b46e4ae8c53b4d498eaec7d53de881ea0f8a9bdfb08f574f9cbd3a4f6]<jdbc__scheduler_worker-00: /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/rufus-scheduler-3.0.9/lib/rufus/scheduler/jobs.rb:284] jdbc - (0.192893s) SELECT * FROM pg_stat_database
[INFO ] 2022-02-24 08:50:03.104 [Ruby-0-Thread-33@[fc2c0b0065c00ee9f942e75f35edf001a9e285c77ba7cf4ae127886e43c140fc]<jdbc__scheduler_worker-00: /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/rufus-scheduler-3.0.9/lib/rufus/scheduler/jobs.rb:284] jdbc - (0.197744s) SELECT * FROM pg_stat_user_indexes
...

如果 Logstash 没有显示任何错误并记录它已成功 SELECT 从三个数据库中获取行，则您的数据库指标将被发送到 Elasticsearch。如果出现错误，请仔细检查配置文件中的所有值，以确保运行 Logstash 的机器可以连接到托管数据库。

Logstash 将在指定时间继续导入数据。您可以按 CTRL+C 安全地停止它。

如果您仅为本教程创建了一个新数据库，则应该只存在一个用户 (doadmin)。这意味着 pg_stat_user_tables 和 pg_stat_user_indexes 表将为空（因为没有其他用户存在并且已创建表）并且不会显示在 Elasticsearch 中。

如前所述，当作为服务启动时，Logstash 会自动在后台运行它在 /etc/logstash/conf.d 下找到的所有配置文件。运行以下命令将其作为服务启动：

sudo systemctl start logstash

在这一步中，您运行了 Logstash 以检查它是否可以连接到您的数据库并收集数据。接下来，您将可视化并探索 Kibana 中的一些统计数据。

第 4 步 — 在 Kibana 中探索导入的数据

在本节中，您将探索和可视化描述您的数据库在 Kibana 中的性能的统计数据。

在您的 Web 浏览器中，导航到您在其中公开 Kibana 作为先决条件的一部分的域。您将看到默认的欢迎页面：

在探索 Logstash 发送到 Elasticsearch 的数据之前，您首先需要将 pg_stat_database 索引添加到 Kibana。为此，首先按自行探索，然后打开左上角的汉堡菜单。在 Analytics 下，单击 Discover。

Kibana 将提示您创建一个新的索引模式：

按创建索引模式。您将看到一个用于创建新的 Index Pattern 的表单。 Kibana 中的索引模式提供了一种同时从多个 Elasticsearch 索引中提取数据的方法，并且可用于仅探索一个索引。

右侧列出的是 Logstash 一直在发送统计信息的三个索引。在 Name 文本字段中输入 pg_stat_database，然后从下拉列表中选择 @timestamp 作为 Timestamp 字段。完成后，按下下方的 创建索引图案 按钮。

要创建和查看现有可视化，请打开汉堡菜单。在 Analytics 下，选择 Dashboard。当它加载时，点击创建新仪表板，然后按创建可视化开始创建一个新的：

左侧面板提供了 Kibana 可用于绘制可视化的值列表，这些值将显示在屏幕的中央部分。屏幕右上角是日期范围选择器。如果在可视化中使用了 @timestamp 字段，Kibana 将仅显示属于范围选择器中指定的时间间隔的数据。

现在，您将可视化给定时间间隔内每分钟的数据元组 INSERT 的平均数量。从页面主要部分的下拉菜单中，选择 线和区域 部分下的线。然后，从左侧列表中找到 tup_inserted 字段并将其拖到中间部分。您很快就会看到 INSERT 查询的中位数随时间变化的线条可视化。如果您的数据库是全新的且未使用过，您将看不到任何内容。但是，在所有情况下，您都会看到数据库使用情况的准确描述：

在右侧，您可以配置如何处理水平轴和垂直轴。在那里，您可以通过按下显示的轴来设置垂直轴以显示 Average 值而不是 Median：

您可以选择不同的功能，或提供您自己的功能：

图表将立即使用更新的值刷新。

在这一步中，您学习了如何使用 Kibana 可视化一些 PostgreSQL 统计数据。

第 5 步 - （可选）使用 pgbench 进行基准测试

如果您尚未在本教程之外的数据库中工作过，您可以通过使用 pgbench 对数据库进行基准测试来完成此步骤以创建更有趣的可视化。 pgbench 将一遍又一遍地运行相同的 SQL 命令，模拟实际客户端使用的真实数据库。

您首先需要通过运行以下命令来安装 pgbench：

sudo apt install postgresql-contrib -y

因为 pgbench 将插入和更新测试数据，所以您需要为它创建一个单独的数据库。为此，请前往用户和数据库管理数据库控制面板中的选项卡，然后向下滚动到数据库部分。输入 pgbench 作为新数据库的名称，然后按 Save。您将将此名称以及主机、端口和用户名信息传递给 pgbench。

在实际运行 pgbench 之前，您需要使用 -i 标志运行它以初始化其数据库：

pgbench -h host -p port -U username -i pgbench

您需要将 host 替换为您的主机地址，将 port 替换为您可以连接到数据库的端口，并将 username 替换为数据库用户用户名。您可以在托管数据库的控制面板中找到所有这些值。

注意 pgbench 没有密码参数；相反，每次运行时都会要求您提供它。

输出将如下所示：

OutputNOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
creating tables...
100000 of 100000 tuples (100%) done (elapsed 0.16 s, remaining 0.00 s)
vacuum...
set primary keys...
done.

pgbench 创建了四个表，它将用于基准测试，并用一些示例行填充它们。您现在可以运行基准测试了。

限制基准运行时间的两个最重要的参数是 -t，它指定要完成的事务数，以及 -T，它定义基准应该运行多少秒。这两个选项是相互排斥的。在每个基准测试结束时，您将收到统计信息，例如每秒事务数 (tps)。

现在，通过运行以下命令启动一个持续 30 秒的基准测试：

pgbench -h host -p port -U username pgbench -T 30

输出将类似于以下内容：

Outputstarting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 30 s
number of transactions actually processed: 11991
latency average = 2.502 ms
tps = 399.664353 (including connections establishing)
tps = 399.987202 (excluding connections establishing)

在此输出中，您会看到有关基准的一般信息，例如执行的事务总数。这些基准的效果是 Logstash 提供给 Elasticsearch 的统计数据将反映该数字，这反过来将使 Kibana 中的可视化更有趣，更接近真实世界的图表。您可以多次运行上述命令，并可能更改持续时间。

完成后，前往 Kibana 并按右上角的 Refresh。您现在将看到与以前不同的行，它显示了 INSERT 的数量。通过更改位于刷新按钮上方的选择器中的值，随意更改显示的数据的时间范围。以下是图表在不同持续时间的多个基准测试后的外观：

您已经使用 pgbench 对您的数据库进行了基准测试，并在 Kibana 中评估了结果图。

结论

您现在已在服务器上安装了 Elastic 堆栈，并配置为定期从托管的 PostgreSQL 数据库中提取统计数据。您可以使用 Kibana 或其他一些合适的软件分析和可视化数据，这将帮助您收集有关数据库性能的宝贵见解和现实世界的相关性。

有关您可以使用 PostgreSQL 托管数据库做什么的更多信息，请访问产品文档。