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

1sudo apt install logstash -y

1sudo systemctl enable logstash

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

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

 1[label /etc/logstash/conf.d/postgresql.conf]
 2input {
 3        # pg_stat_database
 4        jdbc {
 5                jdbc_driver_library => ""
 6                jdbc_driver_class => "org.postgresql.Driver"
 7                jdbc_connection_string => "jdbc:postgresql://host:port/defaultdb"
 8                jdbc_user => "username"
 9                jdbc_password => "password"
10                statement => "SELECT * FROM pg_stat_database"
11                schedule => "* * * * *"
12                type => "pg_stat_database"
13        }
14
15        # pg_stat_user_tables
16        jdbc {
17                jdbc_driver_library => ""
18                jdbc_driver_class => "org.postgresql.Driver"
19                jdbc_connection_string => "jdbc:postgresql://host:port/defaultdb"
20                jdbc_user => "username"
21                jdbc_password => "password"
22                statement => "SELECT * FROM pg_stat_user_tables"
23                schedule => "* * * * *"
24                type => "pg_stat_user_tables"
25        }
26
27        # pg_stat_user_indexes
28        jdbc {
29                jdbc_driver_library => ""
30                jdbc_driver_class => "org.postgresql.Driver"
31                jdbc_connection_string => "jdbc:postgresql://host:port/defaultdb"
32                jdbc_user => "username"
33                jdbc_password => "password"
34                statement => "SELECT * FROM pg_stat_user_indexes"
35                schedule => "* * * * *"
36                type => "pg_stat_user_indexes"
37        }
38}
39
40output {
41        elasticsearch {
42                hosts => "http://localhost:9200"
43                index => "%{type}"
44        }
45}

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

 1[secondary_label Output]
 2Using bundled JDK: /usr/share/logstash/jdk
 3OpenJDK 64-Bit Server VM warning: Option UseConcMarkSweepGC was deprecated in version 9.0 and will likely be removed in a future release.
 4WARNING: 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
 5Could not find log4j2 configuration at path /usr/share/logstash/config/log4j2.properties. Using default config which logs errors to the console
 6[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]"}
 7[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]
 8[INFO ] 2022-02-24 08:49:36.700 [main] settings - Creating directory {:setting=>"path.queue", :path=>"/usr/share/logstash/data/queue"}
 9[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"}
10[WARN ] 2022-02-24 08:49:36.992 [LogStash::Runner] multilocal - Ignoring the 'pipelines.yml' file because modules or command line options are specified
11[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"}
12[INFO ] 2022-02-24 08:49:38.085 [Api Webserver] agent - Successfully started Logstash API endpoint {:port=>9600, :ssl_enabled=>false}
13[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
14...
15[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
16[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
17[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
18...

1sudo systemctl start logstash

1sudo apt install postgresql-contrib -y

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

 1[secondary_label Output]
 2NOTICE:  table "pgbench_history" does not exist, skipping
 3NOTICE:  table "pgbench_tellers" does not exist, skipping
 4NOTICE:  table "pgbench_accounts" does not exist, skipping
 5NOTICE:  table "pgbench_branches" does not exist, skipping
 6creating tables...
 7100000 of 100000 tuples (100%) done (elapsed 0.16 s, remaining 0.00 s)
 8vacuum...
 9set primary keys...
10done.

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

 1[secondary_label Output]
 2starting vacuum...end.
 3transaction type: <builtin: TPC-B (sort of)>
 4scaling factor: 1
 5query mode: simple
 6number of clients: 1
 7number of threads: 1
 8duration: 30 s
 9number of transactions actually processed: 11991
10latency average = 2.502 ms
11tps = 399.664353 (including connections establishing)
12tps = 399.987202 (excluding connections establishing)