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

介绍 (Introduction)

Database monitoring is the continuous process of systematically tracking various metrics that show how the database is performing. By observing the performance data, you can gain valuable insights and identify possible bottlenecks, as well as find additional ways of improving database performance. Such systems often implement alerting, which notifies administrators when things go wrong. Gathered statistics can be used to not only improve the configuration and workflow of the database, but also those of client applications.

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

The benefit of using the Elastic Stack (ELK stack) for monitoring your managed database is its excellent support for searching and the ability to ingest new data very quickly. It does not excel at updating the data, but this trade off is acceptable for monitoring and logging purposes, where past data is almost never changed. Elasticsearch offers powerful means of querying the data, which you can use through Kibana to get a better understanding of how the database fares through different time periods. This will allow you to correlate database load with real-life events to gain insight into how the database is being used.

使用弹性堆栈 (ELK堆栈)来监视托管数据库的好处是其对搜索的出色支持以及非常快速地提取新数据的能力。 它并不擅长更新数据，但是这种权衡对于监视和记录目的是可以接受的，过去的数据几乎从未更改过。 Elasticsearch提供了强大的查询数据的方法，您可以通过Kibana使用它来更好地了解数据库在不同时间段的运行情况。 这将使您能够将数据库负载与实际事件相关联，以洞悉数据库的使用方式。

In this tutorial, you’ll import database metrics, generated by the PostgreSQL statistics collector, into Elasticsearch via Logstash. This entails configuring Logstash to pull data from the database using the PostgreSQL JDBC connector to send it to Elasticsearch for indexing immediately afterward. The imported data can later be analyzed and visualized in Kibana. Then, if your database is brand new, you’ll use pgbench, a PostgreSQL benchmarking tool, to create more interesting visualizations. In the end, you’ll have an automated system pulling in PostgreSQL statistics for later analysis.

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

先决条件 (Prerequisites)

• An Ubuntu 18.04 server with at least 4 GB RAM, root privileges, and a secondary, non-root account. You can set this up by following this initial server setup guide. For this tutorial the non-root user is sammy.

  具有至少4 GB RAM，root特权和辅助非root帐户的Ubuntu 18.04服务器。 您可以按照本初始服务器安装指南进行设置 。 在本教程中，非root用户是sammy 。

• Java 8 installed on your server. For installation instructions, visit How To Install Java with apt on Ubuntu 18.04.

  服务器上安装了Java 8。 有关安装说明，请访问如何在Ubuntu 18.04上使用apt安装Java 。

• Nginx installed on your server. For a guide on how to do that, see How To Install Nginx on Ubuntu 18.04.

  Nginx安装在您的服务器上。 有关如何执行此操作的指南，请参见如何在Ubuntu 18.04上安装Nginx 。

• Elasticsearch and Kibana installed on your server. Complete the first two steps of the How To Install Elasticsearch, Logstash, and Kibana (Elastic Stack) on Ubuntu 18.04 tutorial.

  服务器上安装了Elasticsearch和Kibana。 完成如何在Ubuntu 18.04教程上安装Elasticsearch，Logstash和Kibana(弹性堆栈)的前两个步骤。

• A PostgreSQL managed database provisioned from DigitalOcean with connection information available. Make sure that your server’s IP address is on the whitelist. To learn more about DigitalOcean Managed Databases, visit the product docs.

  由DigitalOcean提供的PostgreSQL管理的数据库，其中包含可用的连接信息。 确保服务器的IP地址在白名单上。 要了解有关DigitalOcean托管数据库的更多信息，请访问产品文档 。

步骤1 —设置Logstash和PostgreSQL JDBC驱动程序 (Step 1 — Setting up Logstash and the PostgreSQL JDBC Driver)

In this section, you will install Logstash and download the PostgreSQL JDBC driver so that Logstash will be able to connect to your managed database.

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

Start off by installing Logstash with the following command:

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

• sudo apt install logstash -y
  sudo apt安装logstash -y

Once Logstash is installed, enable the service to automatically start on boot:

一旦安装了Logstash，请启用该服务以在启动时自动启动：

• sudo systemctl enable logstash
  sudo systemctl启用logstash

Logstash is written in Java, so in order to connect to PostgreSQL it requires the PostgreSQL JDBC (Java Database Connectivity) library to be available on the system it is running on. Because of an internal limitation, Logstash will properly load the library only if it is found under the /usr/share/logstash/logstash-core/lib/jars directory, where it stores third-party libraries it uses.

Logstash用Java编写，因此为了连接到PostgreSQL，它要求PostgreSQL JDBC(Java数据库连接)库在运行它的系统上可用。 由于内部限制，仅当在/usr/share/logstash/logstash-core/lib/jars目录下找到Logstash时，Logstash才能正确加载该库，该目录存储了所使用的第三方库。

Head over to the download page of the JDBC library and copy the link to latest version. Then, download it using curl by running the following command:

转到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

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

At the time of writing, the latest version of the library was 42.2.6, with Java 8 as the supported runtime version. Ensure you download the latest version; pairing it with the correct Java version that both JDBC and Logstash support.

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

Logstash stores its configuration files under /etc/logstash/conf.d, and is itself stored under /usr/share/logstash/bin. Before you create a configuration that will pull statistics from your database, you’ll need to enable the JDBC plugin in Logstash by running the following command:

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

• sudo /usr/share/logstash/bin/logstash-plugin install logstash-input-jdbc
  须藤/ usr / share / logstash / bin / logstash-plugin安装logstash-input-jdbc

You’ve installed Logstash using apt and downloaded the PostgreSQL JDBC library so that Logstash can use it to connect to your managed database. In the next step, you will configure Logstash to pull statistical data from it.

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

步骤2 —配置Logstash提取统计信息 (Step 2 — Configuring Logstash To Pull Statistics)

In this section, you will configure Logstash to pull metrics from your managed PostgreSQL database.

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

You’ll configure Logstash to watch over three system databases in PostgreSQL, namely:

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

• pg_stat_database: provides statistics about each database, including its name, number of connections, transactions, rollbacks, rows returned by querying the database, deadlocks, and so on. It has a stats_reset field, which specifies when the statistics were last reset.

  pg_stat_database ：提供有关每个数据库的统计信息，包括其名称，连接数，事务，回滚，查询数据库返回的行，死锁等。 它具有stats_reset字段，该字段指定上次重置统计信息的时间。

• pg_stat_user_tables: provides statistics about each table created by the user, such as the number of inserted, deleted, and updated rows.

  pg_stat_user_tables ：提供有关用户创建的每个表的统计信息，例如插入，删除和更新的行数。

• pg_stat_user_indexes: collects data about all indexes in user-created tables, such as the number of times a particular index has been scanned.

  pg_stat_user_indexes ：收集有关用户创建的表中所有索引的数据，例如已扫描特定索引的次数。

You’ll store the configuration for indexing PostgreSQL statistics in Elasticsearch in a file named postgresql.conf under the /etc/logstash/conf.d directory, where Logstash stores configuration files. When started as a service, it will automatically run them in the background.

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

Create postgresql.conf using your favorite editor (for example, nano):

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

• sudo nano /etc/logstash/conf.d/postgresql.conf
  须藤nano /etc/logstash/conf.d/postgresql.conf

Add the following lines:

添加以下行：

/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}"
        }
}

Remember to replace host with your host address, port with the port to which you can connect to your database, username with the database user username, and password with its password. All these values can be found in the Control Panel of your managed database.

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

In this configuration, you define three JDBC inputs and one Elasticsearch output. The three inputs pull data from the pg_stat_database, pg_stat_user_tables, and pg_stat_user_indexes databases, respectively. They all set the jdbc_driver_library parameter to an empty string, because the PostgreSQL JDBC library is in a folder that Logstash automatically loads.

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

Then, they set the jdbc_driver_class, whose value is specific to the JDBC library, and provide a jdbc_connection_string, which details how to connect to the database. The jdbc: part signifies that it is a JDBC connection, while postgres:// indicates that the target database is PostgreSQL. Next come the host and port of the database, and after the forward slash you also specify a database to connect to; this is because PostgreSQL requires you to be connected to a database to be able to issue any queries. Here, it is set to the default database that always exists and can not be deleted, aptly named defaultdb.

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

Next, they set a username and password of the user through which the database will be accessed. The statement parameter contains a SQL query that should return the data you wish to process—in this configuration, it selects all rows from the appropriate database.

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

The schedule parameter accepts a string in cron syntax that defines when Logstash should run this input; omitting it completely will make Logstash run it only once. Specifying * * * * *, as you have done so here, will tell Logstash to run it every minute. You can specify your own cron string if you want to collect data at different intervals.

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

There is only one output, which accepts data from three inputs. They all send data to Elasticsearch, which is running locally and is reachable at http://localhost:9200. The index parameter defines to which Elasticsearch index it will send the data, and its value is passed in from the type field of the input.

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

When you are done with editing, save and close the file.

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

You’ve configured Logstash to gather data from various PostgreSQL statistical tables and send them to Elasticsearch for storage and indexing. Next, you’ll run Logstash to test the configuration.

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

步骤3 —测试Logstash配置 (Step 3 — Testing the Logstash Configuration)

In this section, you will test the configuration by running Logstash to verify it will properly pull the data. Then, you will make this configuration run in the background by configuring it as a Logstash pipeline.

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

Logstash supports running a specific configuration by passing its file path to the -f parameter. Run the following command to test your new configuration from the last step:

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

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

It may take some time before it shows any output, which will look similar to this:

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

   
   

    
    Output
   
   
Thread.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
...

If Logstash does not show any errors and logs that it has successfully SELECTed rows from the three databases, your database metrics will be shipped to Elasticsearch. If you get an error, double check all the values in the configuration file to ensure that the machine you’re running Logstash on can connect to the managed database.

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

Logstash will continue importing the data at specified times. You can safely stop it by pressing CTRL+C.

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

As previously mentioned, when started as a service, Logstash automatically runs all configuration files it finds under /etc/logstash/conf.d in the background. Run the following command to start it as a service:

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

• sudo systemctl start logstash
  须藤systemctl启动logstash

In this step, you ran Logstash to check if it can connect to your database and gather data. Next, you’ll visualize and explore some of the statistical data in Kibana.

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

步骤4 —在Kibana中浏览导入的数据 (Step 4 — Exploring Imported Data in Kibana)

In this section, you’ll see how you can explore the statistical data describing your database’s performance in Kibana.

在本节中，您将看到如何浏览描述数据库在Kibana中的性能的统计数据。

In your browser, navigate to the Kibana installation you set up as a prerequisite. You’ll see the default welcome page.

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

To interact with Elasticsearch indexes in Kibana, you’ll need to create an index pattern. Index patterns specify on which indexes Kibana should operate. To create one, press on the last icon (wrench) from the left-hand vertical sidebar to open the Management page. Then, from the left menu, press on Index Patterns under Kibana. You’ll see a dialog box for creating an index pattern.

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

Listed are the three indexes where Logstash has been sending statistics. Type in pg_stat_database in the Index Pattern input box and then press Next step. You’ll be asked to select a field that stores time, so you’ll be able to later narrow your data by a time range. From the dropdown, select @timestamp.

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

Press on Create index pattern to finish creating the index pattern. You’ll now be able to explore it using Kibana. To create a visualization, press on the second icon in the sidebar, and then on Create new visualization. Select the Line visualization when the form pops up, and then choose the index pattern you have just created (pg_stat_database). You’ll see an empty visualization.

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

On the central part of the screen is the resulting plot—the left-side panel governs its generation from which you can set the data for X and Y axis. In the upper right-hand side of the screen is the date range picker. Unless you specifically choose another range when configuring the data, that range will be shown on the plot.

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

You’ll now visualize the average number of data tuples INSERTed on minutes in the given interval. Press on Y-Axis under Metrics in the panel on the left to unfold it. Select Average as the Aggregation and select tup_inserted as the Field. This will populate the Y axis of the plot with the average values.

现在，您将可视化给定间隔中每分钟INSERT的数据元组的平均数量。 在左侧面板中“ 度量”下，按Y轴将其展开。 选择“ 平均值”作为“ 聚合”，然后选择“ tup_inserted作为“ 字段” 。 这将使用平均值填充图的Y轴。

Next, press on X-Axis under Buckets. For the Aggregation, choose Date Histogram. @timestamp should be automatically selected as the Field. Then, press on the blue play button on the top of the panel to generate your graph. If your database is brand new and not used, you won’t see anything yet. In all cases, however, you will see an accurate portrayal of database usage.

接下来，在“存储桶”下按X轴 。 对于汇总 ，选择日期直方图 。 @timestamp应该被自动选择为Field 。 然后，按面板顶部的蓝色播放按钮以生成图形。 如果您的数据库是全新的并且尚未使用，则您将看不到任何内容。 但是，在所有情况下，您都会看到数据库使用情况的准确描述。

Kibana supports many other visualization forms—you can explore other forms in the Kibana documentation. You can also add the two remaining indexes, mentioned in Step 2, into Kibana to be able to visualize them as well.

Kibana支持许多其他可视化形式-您可以在Kibana文档中探索其他形式。 您还可以将第2步中提到的其余两个索引添加到Kibana中，以使其也可视化。

In this step, you have learned how to visualize some of the PostgreSQL statistical data, using Kibana.

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

步骤5 —(可选)使用pgbench进行基准测试 (Step 5 — (Optional) Benchmarking Using pgbench)

If you haven’t yet worked in your database outside of this tutorial, you can complete this step to create more interesting visualizations by using pgbench to benchmark your database. pgbench will run the same SQL commands over and over, simulating real-world database use by an actual client.

如果您还没有在本教程之外使用数据库，则可以使用pgbench对数据库进行基准测试，从而完成此步骤以创建更有趣的可视化效果。 pgbench将一遍又一遍地运行相同SQL命令，以模拟实际客户端使用的实际数据库。

You’ll first need to install pgbench by running the following command:

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

• sudo apt install postgresql-contrib -y
  sudo apt安装postgresql-contrib -y

Because pgbench will insert and update test data, you’ll need to create a separate database for it. To do so, head over to the Users & Databases tab in the Control Panel of your managed database, and scroll down to the Databases section. Type in pgbench as the name of the new database, and then press on Save. You’ll pass this name, as well as the host, port, and username information to pgbench.

由于pgbench将插入和更新测试数据，因此您需要为其创建一个单独的数据库。 为此，请转到托管数据库的“控制面板”中的“ 用户和数据库”选项卡，然后向下滚动到“ 数据库”部分。 键入pgbench作为新数据库的名称，然后按Save 。 您将把这个名称以及主机，端口和用户名信息传递给pgbench。

Before actually running pgbench, you’ll need to run it with the -i flag to initialize its database:

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

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

  pgbench -h 主机 -p 端口 -U 用户名 -i pgbench

You’ll need to replace host with your host address, port with the port to which you can connect to your database, and username with the database user username. You can find all these values in the Control Panel of your managed database.

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

Notice that pgbench does not have a password argument; instead, you’ll be asked for it every time you run it.

注意pgbench没有密码参数。 相反，每次您运行它时都会询问您。

The output will look like the following:

输出将如下所示：

   
   

    
    Output
   
   
NOTICE:  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 created four tables, which it will use for benchmarking, and populated them with some example rows. You’ll now be able to run benchmarks.

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

The two most important arguments that limit for how long the benchmark will run are -t, which specifies the number of transactions to complete, and -T, which defines for how many seconds the benchmark should run. These two options are mutually exclusive. At the end of each benchmark, you’ll receive statistics, such as the number of transactions per second (tps).

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

Now, start a benchmark that will last for 30 seconds by running the following command:

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

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

  pgbench -h 主机 -p 端口 -U 用户名 pgbench -T 30

The output will look like:

输出将如下所示：

   
   

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

In this output, you see the general info about the benchmark, such as the total number of transactions executed. The effect of these benchmarks is that the statistics Logstash ships to Elasticsearch will reflect that number, which will in turn make visualizations in Kibana more interesting and closer to real-world graphs. You can run the preceding command a few more times, and possibly alter the duration.

在此输出中，您将看到有关基准的常规信息，例如已执行的事务总数。 这些基准的影响在于，Logstash交付给Elasticsearch的统计信息将反映该数字，从而使Kibana中的可视化更加有趣并且更接近于真实世界的图形。 您可以再运行几次前面的命令，并可能更改持续时间。

When you are done, head over to Kibana and press on Refresh in the upper right corner. You’ll now see a different line than before, which shows the number of INSERTs. Feel free to change the time range of the data shown by changing the values in the picker positioned above the refresh button. Here is how the graph may look after multiple benchmarks of varying duration:

完成后，转到Kibana，然后按右上角的“ 刷新 ”。 现在，您将看到与以前不同的行，其中显示了INSERT的数量。 可以通过更改刷新按钮上方的选择器中的值来随意更改显示的数据的时间范围。 这是图表在持续时间不同的多个基准之后的外观：

You’ve used pgbench to benchmark your database, and evaluated the resulting graphs in Kibana.

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

结论 (Conclusion)

You now have the Elastic stack installed on your server and configured to pull statistics data from your managed PostgreSQL database on a regular basis. You can analyze and visualize the data using Kibana, or some other suitable software, which will help you gather valuable insights and real-world correlations into how your database is performing.

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

For more information about what you can do with your PostgreSQL Managed Database, visit the product docs.

有关可以对PostgreSQL托管数据库执行的操作的更多信息，请访问产品文档 。

翻译自: https://www.digitalocean.com/community/tutorials/how-to-analyze-managed-postgresql-database-statistics-using-the-elastic-stack-on-ubuntu-18-04