如何使用Ubuntu 18.04上的弹性分析托管的PostgreSQL数据库统计信息

用户2323866

修改于 2021-06-18 10:52:48

4.2K0

修改于 2021-06-18 10:52:48

文章被收录于专栏：技术派技术派

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

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

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

先决条件

Ubuntu 18.04服务器，具有至少4 GB RAM，root权限和辅助非root帐户。您可以按照此初始服务器设置指南进行设置。对于本教程，非root用户是sammy 。
Java 8安装在您的服务器上。有关安装说明，请参阅如何在Ubuntu 18.04上使用apt安装Java 。
Nginx安装在您的服务器上。有关如何执行此操作的指南，请参阅如何在Ubuntu 18.04上安装Nginx 。
Elasticsearch和Kibana安装在您的服务器上。在Ubuntu 18.04教程中完成如何安装Elasticsearch，Logstash和Kibana（弹性）的前两个步骤。
从DigitalOcean配置的PostgreSQL托管数据库，提供连接信息。确保服务器的IP地址在白名单中。要了解有关DigitalOcean托管数据库的更多信息，请访问产品文档。

第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数据库连接）库在其运行的系统上可用。由于内部限制，只有在/usr/share/logstash/logstash-core/lib/jars目录下找到它时，Logstash才会正确加载库，它会存储它使用的第三方库。

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

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

在撰写本文时，该库的最新版本为42.2.6 ，其中Java 8为受支持的运行时版本。确保下载最新版本; 将它与JDBC和Logstash支持的正确Java版本配对。

Logstash将其配置文件存储在/etc/logstash/conf.d下，并且本身存储在/usr/share/logstash/bin 。在创建将从数据库中提取统计信息的配置之前，您需要通过运行以下命令在Logstash中启用JDBC插件：

sudo /usr/share/logstash/bin/logstash-plugin install logstash-input-jdbc

您已经使用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存储配置文件。作为服务启动时，它将自动在后台运行它们。

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

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替换用户名，使用password替换密码。所有这些值都可以在托管数据库的“控制面板”中找到。

在此配置中，您可以定义三个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: part表示它是JDBC连接，而postgres://表示目标数据库是PostgreSQL。接下来是数据库的主机和端口，在正斜杠之后，您还指定了要连接的数据库; 这是因为PostgreSQL要求您连接到数据库以便能够发出任何查询。在这里，它被设置为始终存在且无法删除的默认数据库，恰当地命名为defaultdb 。

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

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

只有一个输出，它接受来自三个输入的数据。它们都向Elasticsearch发送数据，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

显示任何输出可能需要一些时间，这看起来类似于：

OutputThread.exclusive is deprecated, use Thread::Mutex
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
[WARN ] 2019-08-02 18:29:15.123 [LogStash::Runner] multilocal - Ignoring the 'pipelines.yml' file because modules or command line options are specified
[INFO ] 2019-08-02 18:29:15.154 [LogStash::Runner] runner - Starting Logstash {"logstash.version"=>"7.3.0"}
[INFO ] 2019-08-02 18:29:18.209 [Converge PipelineAction::Create<main>] Reflections - Reflections took 77 ms to scan 1 urls, producing 19 keys and 39 values
[INFO ] 2019-08-02 18:29:20.195 [[main]-pipeline-manager] elasticsearch - Elasticsearch pool URLs updated {:changes=>{:removed=>[], :added=>[http://localhost:9200/]}}
[WARN ] 2019-08-02 18:29:20.667 [[main]-pipeline-manager] elasticsearch - Restored connection to ES instance {:url=>"http://localhost:9200/"}
[INFO ] 2019-08-02 18:29:21.221 [[main]-pipeline-manager] elasticsearch - ES Output version determined {:es_version=>7}
[WARN ] 2019-08-02 18:29:21.230 [[main]-pipeline-manager] elasticsearch - Detected a 6.x and above cluster: the `type` event field won't be used to determine the document _type {:es_version=>7}
[INFO ] 2019-08-02 18:29:21.274 [[main]-pipeline-manager] elasticsearch - New Elasticsearch output {:class=>"LogStash::Outputs::ElasticSearch", :hosts=>["http://localhost:9200"]}
[INFO ] 2019-08-02 18:29:21.337 [[main]-pipeline-manager] elasticsearch - Elasticsearch pool URLs updated {:changes=>{:removed=>[], :added=>[http://localhost:9200/]}}
[WARN ] 2019-08-02 18:29:21.369 [[main]-pipeline-manager] elasticsearch - Restored connection to ES instance {:url=>"http://localhost:9200/"}
[INFO ] 2019-08-02 18:29:21.386 [[main]-pipeline-manager] elasticsearch - ES Output version determined {:es_version=>7}
[WARN ] 2019-08-02 18:29:21.386 [[main]-pipeline-manager] elasticsearch - Detected a 6.x and above cluster: the `type` event field won't be used to determine the document _type {:es_version=>7}
[INFO ] 2019-08-02 18:29:21.409 [[main]-pipeline-manager] elasticsearch - New Elasticsearch output {:class=>"LogStash::Outputs::ElasticSearch", :hosts=>["http://localhost:9200"]}
[INFO ] 2019-08-02 18:29:21.430 [[main]-pipeline-manager] elasticsearch - Elasticsearch pool URLs updated {:changes=>{:removed=>[], :added=>[http://localhost:9200/]}}
[WARN ] 2019-08-02 18:29:21.444 [[main]-pipeline-manager] elasticsearch - Restored connection to ES instance {:url=>"http://localhost:9200/"}
[INFO ] 2019-08-02 18:29:21.465 [[main]-pipeline-manager] elasticsearch - ES Output version determined {:es_version=>7}
[WARN ] 2019-08-02 18:29:21.466 [[main]-pipeline-manager] elasticsearch - Detected a 6.x and above cluster: the `type` event field won't be used to determine the document _type {:es_version=>7}
[INFO ] 2019-08-02 18:29:21.468 [Ruby-0-Thread-7: :1] elasticsearch - Using default mapping template
[INFO ] 2019-08-02 18:29:21.538 [Ruby-0-Thread-5: :1] elasticsearch - Using default mapping template
[INFO ] 2019-08-02 18:29:21.545 [[main]-pipeline-manager] elasticsearch - New Elasticsearch output {:class=>"LogStash::Outputs::ElasticSearch", :hosts=>["http://localhost:9200"]}
[INFO ] 2019-08-02 18:29:21.589 [Ruby-0-Thread-9: :1] elasticsearch - Using default mapping template
[INFO ] 2019-08-02 18:29:21.696 [Ruby-0-Thread-5: :1] elasticsearch - Attempting to install template {:manage_template=>{"index_patterns"=>"logstash-*", "version"=>60001, "settings"=>{"index.refresh_interval"=>"5s", "number_of_shards"=>1}, "mappings"=>{"dynamic_templates"=>[{"message_field"=>{"path_match"=>"message", "match_mapping_type"=>"string", "mapping"=>{"type"=>"text", "norms"=>false}}}, {"string_fields"=>{"match"=>"*", "match_mapping_type"=>"string", "mapping"=>{"type"=>"text", "norms"=>false, "fields"=>{"keyword"=>{"type"=>"keyword", "ignore_above"=>256}}}}}], "properties"=>{"@timestamp"=>{"type"=>"date"}, "@version"=>{"type"=>"keyword"}, "geoip"=>{"dynamic"=>true, "properties"=>{"ip"=>{"type"=>"ip"}, "location"=>{"type"=>"geo_point"}, "latitude"=>{"type"=>"half_float"}, "longitude"=>{"type"=>"half_float"}}}}}}}
[INFO ] 2019-08-02 18:29:21.769 [Ruby-0-Thread-7: :1] elasticsearch - Attempting to install template {:manage_template=>{"index_patterns"=>"logstash-*", "version"=>60001, "settings"=>{"index.refresh_interval"=>"5s", "number_of_shards"=>1}, "mappings"=>{"dynamic_templates"=>[{"message_field"=>{"path_match"=>"message", "match_mapping_type"=>"string", "mapping"=>{"type"=>"text", "norms"=>false}}}, {"string_fields"=>{"match"=>"*", "match_mapping_type"=>"string", "mapping"=>{"type"=>"text", "norms"=>false, "fields"=>{"keyword"=>{"type"=>"keyword", "ignore_above"=>256}}}}}], "properties"=>{"@timestamp"=>{"type"=>"date"}, "@version"=>{"type"=>"keyword"}, "geoip"=>{"dynamic"=>true, "properties"=>{"ip"=>{"type"=>"ip"}, "location"=>{"type"=>"geo_point"}, "latitude"=>{"type"=>"half_float"}, "longitude"=>{"type"=>"half_float"}}}}}}}
[INFO ] 2019-08-02 18:29:21.771 [Ruby-0-Thread-9: :1] elasticsearch - Attempting to install template {:manage_template=>{"index_patterns"=>"logstash-*", "version"=>60001, "settings"=>{"index.refresh_interval"=>"5s", "number_of_shards"=>1}, "mappings"=>{"dynamic_templates"=>[{"message_field"=>{"path_match"=>"message", "match_mapping_type"=>"string", "mapping"=>{"type"=>"text", "norms"=>false}}}, {"string_fields"=>{"match"=>"*", "match_mapping_type"=>"string", "mapping"=>{"type"=>"text", "norms"=>false, "fields"=>{"keyword"=>{"type"=>"keyword", "ignore_above"=>256}}}}}], "properties"=>{"@timestamp"=>{"type"=>"date"}, "@version"=>{"type"=>"keyword"}, "geoip"=>{"dynamic"=>true, "properties"=>{"ip"=>{"type"=>"ip"}, "location"=>{"type"=>"geo_point"}, "latitude"=>{"type"=>"half_float"}, "longitude"=>{"type"=>"half_float"}}}}}}}
[WARN ] 2019-08-02 18:29:21.871 [[main]-pipeline-manager] LazyDelegatingGauge - A gauge metric of an unknown type (org.jruby.specialized.RubyArrayOneObject) has been create for key: cluster_uuids. This may result in invalid serialization.  It is recommended to log an issue to the responsible developer/development team.
[INFO ] 2019-08-02 18:29:21.878 [[main]-pipeline-manager] javapipeline - Starting pipeline {:pipeline_id=>"main", "pipeline.workers"=>1, "pipeline.batch.size"=>125, "pipeline.batch.delay"=>50, "pipeline.max_inflight"=>125, :thread=>"#<Thread:0x470bf1ca run>"}
[INFO ] 2019-08-02 18:29:22.351 [[main]-pipeline-manager] javapipeline - Pipeline started {"pipeline.id"=>"main"}
[INFO ] 2019-08-02 18:29:22.721 [Ruby-0-Thread-1: /usr/share/logstash/lib/bootstrap/environment.rb:6] agent - Pipelines running {:count=>1, :running_pipelines=>[:main], :non_running_pipelines=>[]}
[INFO ] 2019-08-02 18:29:23.798 [Api Webserver] agent - Successfully started Logstash API endpoint {:port=>9600}
/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/rufus-scheduler-3.0.9/lib/rufus/scheduler/cronline.rb:77: warning: constant ::Fixnum is deprecated
/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/rufus-scheduler-3.0.9/lib/rufus/scheduler/cronline.rb:77: warning: constant ::Fixnum is deprecated
/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/rufus-scheduler-3.0.9/lib/rufus/scheduler/cronline.rb:77: warning: constant ::Fixnum is deprecated
[INFO ] 2019-08-02 18:30:02.333 [Ruby-0-Thread-22: /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/rufus-scheduler-3.0.9/lib/rufus/scheduler/jobs.rb:284] jdbc - (0.042932s) SELECT * FROM pg_stat_user_indexes
[INFO ] 2019-08-02 18:30:02.340 [Ruby-0-Thread-23: /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/rufus-scheduler-3.0.9/lib/rufus/scheduler/jobs.rb:331] jdbc - (0.043178s) SELECT * FROM pg_stat_user_tables
[INFO ] 2019-08-02 18:30:02.340 [Ruby-0-Thread-24: :1] jdbc - (0.036469s) SELECT * FROM pg_stat_database
...

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

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

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

sudo systemctl start logstash

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

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

在本节中，您将了解如何探索描述数据库在Kibana中的性能的统计数据。

在浏览器中，导航到您设置为先决条件的Kibana安装。您将看到默认的欢迎页面。

要与Kibana中的Elasticsearch索引进行交互，您需要创建索引模式。 索引模式指定Kibana应该操作的索引。要创建一个，请按左侧垂直侧边栏上的最后一个图标（扳手）以打开“ 管理”页面。然后，从左侧菜单中按下Kibana下的Index Patterns 。您将看到一个用于创建索引模式的对话框。

列出的是Logstash发送统计信息的三个索引。在“ 索引模式”输入框中键入pg_stat_database ，然后按“下一步” 。系统会要求您选择一个存储时间的字段，以便稍后您可以按时间范围缩小数据范围。从下拉列表中选择@timestamp 。

按“ 创建索引模式”以完成索引模式的创建。您现在可以使用Kibana进行探索。要创建可视化，请按侧栏中的第二个图标，然后按“ 创建新可视化” 。弹出窗体时选择“ 线”可视化，然后选择刚刚创建的索引模式（ pg_stat_database ）。你会看到一个空的可视化。

在屏幕的中央部分是结果图 - 左侧面板控制其生成，您可以从中设置X和Y轴的数据。屏幕的右上角是日期范围选择器。除非您在配置数据时特别选择其他范围，否则该范围将显示在图表上。

现在，您将可视化在给定时间间隔内按分钟INSERT的数据元组的平均数量。按下左侧面板中“ 度量标准”下的“ Y轴 ”以展开它。选择Average作为聚合并选择tup_inserted作为Field 。这将使用平均值填充图的Y轴。

接下来，按下Buckets下的X-Axis 。对于聚合，请选择日期直方图 。 @timestamp应自动选为Field 。然后，按面板顶部的蓝色播放按钮生成图形。如果您的数据库是全新的而未使用，您将看不到任何内容。但是，在所有情况下，您都会看到对数据库使用情况的准确描述。

Kibana支持许多其他可视化表单 - 您可以在Kibana文档中探索其他表单。您还可以将第2步中提到的其余两个索引添加到Kibana中，以便能够将它们可视化。

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

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

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

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

sudo apt install postgresql-contrib -y

因为pgbench将插入并更新测试数据，所以您需要为它创建一个单独的数据库。为此，请转到托管数据库的“控制面板”中的“ 用户和数据库”选项卡，然后向下滚动到“ 数据库”部分。键入pgbench作为新数据库的名称，然后按“ 保存” 。您将此名称以及主机，端口和用户名信息传递给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: 7602
latency average = 3.947 ms
tps = 253.382298 (including connections establishing)
tps = 253.535257 (excluding connections establishing)

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

完成后，前往Kibana并按右上角的Refresh 。您现在将看到与之前不同的行，其中显示了INSERT的数量。您可以通过更改位于刷新按钮上方的选取器中的值来更改所显示数据的时间范围。以下是图表如何看待不同持续时间的多个基准：