PostgreSQL中pgmetrics有什么作用

本篇内容主要讲解"PostgreSQL中pgmetrics有什么作用"，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习"PostgreSQL中pgmetrics有什么作用"吧!

pgmetrics 简介

pgmetrics通过连接到数据库，获取数据库当前状态（大多数指标通过STAT系统动态视图获取）。

如果连接的是本地数据库，则同时会采集服务器的状态信息。

获取指标后，可以存储为JSON或TEXT格式。

pgmetrics --help  pgmetrics collects PostgreSQL information and metrics.    Usage:    pgmetrics [OPTION]... [DBNAME]    General options:    -t, --timeout=SECS           individual query timeout in seconds (default: 5)    -i, --input=FILE             don't connect to db, instead read and display                                     this previously saved JSON file    -V, --version                output version information, then exit    -?, --help[=options]         show this help, then exit        --help=variables         list environment variables, then exit    Collection options:    -S, --no-sizes               don't collect tablespace and relation sizes    -c, --schema=REGEXP          collect only from schema(s) matching POSIX regexp    -C, --exclude-schema=REGEXP  do NOT collect from schema(s) matching POSIX regexp    -a, --table=REGEXP           collect only from table(s) matching POSIX regexp    -A, --exclude-table=REGEXP   do NOT collect from table(s) matching POSIX regexp        --omit=WHAT              do NOT collect the items specified as a comma-separated                                     list of: "tables", "indexes", "sequences",                                     "functions", "extensions", "triggers", "statements"        --sql-length=LIMIT       collect only first LIMIT characters of all SQL                                     queries (default: 500)        --statements-limit=LIMIT collect only utmost LIMIT number of row from                                     pg_stat_statements (default: 100)    Output options:    -f, --format=FORMAT          output format; "human", or "json" (default: "human")    -l, --toolong=SECS           for human output, transactions running longer than                                     this are considered too long (default: 60)    -o, --output=FILE            write output to the specified file        --no-pager               do not invoke the pager for tty output    Connection options:    -h, --host=HOSTNAME          database server host or socket directory                                     (default: "/data01/pg11/pg_root1921")    -p, --port=PORT              database server port (default: 1921)    -U, --username=USERNAME      database user name (default: "postgres")        --no-password            never prompt for password    For more information, visit .

pgmetrics 采集维度

pgmetrics通过数据库用户连接到数据库进行采集。

1、集群

2、数据库

3、操作系统

采集代码：

https://github.com/rapidloop/pgmetrics/blob/master/collector/collect.go

当采集数据的角色不为超级用户时，可能有些指标无法被正确采集（例如权限问题），但是此时并不会报错，只是输出可以正常被采集的指标并记录下来。

pgmetrics 采集调度

可以使用crontab，定期调用。

pgmetrics 例子

建议

1、设置语句超时时间，为业务可接受时间。（因为每个指标都要调用对应的SQL来进行采集，万一因为锁堵塞导致采集不及时，可以避免长时间等待）。

例子

1、超时时间为3秒，不输入密码，不分页，输出JSON格式，输出到文件，采集实例信息、系统信息、数据库postgres与newdb信息。

pgmetrics -t 3 --no-password --no-pager -f json -o ./log_`date +%s` postgres newdb

ll log*  -rw-r--r-- 1 postgres postgres 89K Oct  1 23:14 log_1538406857

2、同上，只是输出的为TEXT格式。

pgmetrics -t 3 --no-password --no-pager -f human postgres newdb

3、使用已保留的JSON文件，生成text报告.

pgmetrics -i ./log_1538406857 --no-pager|less

TEXT报告内容示例

pgmetrics -t 3 --no-password --no-pager -f human postgres newdb

pgmetrics run at: 1 Oct 2018 11:14:17 PM (3 minutes ago)    PostgreSQL Cluster:      Name:                      Server Version:      11beta3      Server Started:      22 Sep 2018 11:31:32 PM (1 week ago)      System Identifier:   6593269818598452546      Timeline:            1      Last Checkpoint:     1 Oct 2018 10:55:00 PM (22 minutes ago)      REDO LSN:            29EE/89C3EA08      Checkpoint LSN:      29F4/B02A04F0 (25 GiB since REDO)      Transaction IDs:     4030798045 to 633147358 (diff = -3397650687)      Notification Queue:  0.0% used      Active Backends:     35 (max 2000)      Recovery Mode?       no    System Information:      Hostname:            pg11-test      CPU Cores:           64 x Intel(R) Xeon(R) Platinum 8163 CPU @ 2.50GHz      Load Average:        35.17      Memory:              used=317 GiB, free=5.4 GiB, buff=377 MiB, cache=181 GiB      Swap:                used=0 B, free=0 B      +---------------------------------+--------------------+      |                         Setting |              Value |      +---------------------------------+--------------------+      |                  shared_buffers | 39321600 (300 GiB) |      |                        work_mem |     8192 (8.0 MiB) |      |            maintenance_work_mem |  2097152 (2.0 GiB) |      |                    temp_buffers |     1024 (8.0 MiB) |      |             autovacuum_work_mem |                 -1 |      |                 temp_file_limit |                 -1 |      |            max_worker_processes |                128 |      |          autovacuum_max_workers |                 16 |      | max_parallel_workers_per_gather |                  0 |      |        effective_io_concurrency |                  0 |      +---------------------------------+--------------------+    WAL Files:      WAL Archiving?       no      WAL Files:           9600      +--------------------+------------------+      |            Setting |            Value |      +--------------------+------------------+      |          wal_level |          minimal |      |    archive_timeout |                0 |      |    wal_compression |              off |      |       max_wal_size | 614400 (9.4 TiB) |      |       min_wal_size | 153600 (2.3 TiB) |      | checkpoint_timeout |             2100 |      |   full_page_writes |              off |      |  wal_keep_segments |                0 |      +--------------------+------------------+    BG Writer:      Checkpoint Rate:     0.03 per min      Average Write:       118 GiB per checkpoint      Total Checkpoints:   355 sched (88.8%) + 45 req (11.2%) = 400      Total Write:         126 TiB, @ 153 MiB per sec      Buffers Allocated:   10825060769 (81 TiB)      Buffers Written:     6157847013 chkpt (36.3%) + 6749938129 bgw (39.8%) + 4047065773 be (23.9%)      Clean Scan Stops:    6107928      BE fsyncs:           0      Counts Since:        21 Sep 2018 10:42:07 PM (1 week ago)      +------------------------------+--------------+      |                      Setting |        Value |      +------------------------------+--------------+      |               bgwriter_delay |      10 msec |      |         bgwriter_flush_after | 64 (512 KiB) |      |        bgwriter_lru_maxpages |         1000 |      |      bgwriter_lru_multiplier |           10 |      |                   block_size |         8192 |      |           checkpoint_timeout |     2100 sec |      | checkpoint_completion_target |        1e-05 |      +------------------------------+--------------+    Backends:      Total Backends:      35 (1.8% of max 2000)      Problematic:         0 waiting on locks, 8 waiting on other, 1 xact too long, 12 idle in xact      Other Waiting Backends:        +-------+----------+---------+-------------+----------+-----------------------+------------------------+        |   PID |     User |     App | Client Addr | Database |                  Wait |            Query Start |        +-------+----------+---------+-------------+----------+-----------------------+------------------------+        | 16514 | postgres | pgbench |             | postgres |   Client / ClientRead | 1 Oct 2018 11:14:17 PM |        | 16515 | postgres | pgbench |             | postgres | IPC / ClogGroupUpdate | 1 Oct 2018 11:14:17 PM |        | 16517 | postgres | pgbench |             | postgres |   Client / ClientRead | 1 Oct 2018 11:14:17 PM |        | 16523 | postgres | pgbench |             | postgres | IPC / ClogGroupUpdate | 1 Oct 2018 11:14:17 PM |        | 16527 | postgres | pgbench |             | postgres |   Client / ClientRead | 1 Oct 2018 11:14:17 PM |        | 16533 | postgres | pgbench |             | postgres |   Client / ClientRead | 1 Oct 2018 11:14:17 PM |        | 16535 | postgres | pgbench |             | postgres |   Client / ClientRead | 1 Oct 2018 11:14:17 PM |        | 40144 | postgres |    psql |             | postgres |   Client / ClientRead | 1 Oct 2018 10:55:00 PM |        +-------+----------+---------+-------------+----------+-----------------------+------------------------+        Long Running (>60 sec) Transactions:        +-------+----------+------+-------------+----------+-----------------------------------------+        |   PID |     User |  App | Client Addr | Database |                       Transaction Start |        +-------+----------+------+-------------+----------+-----------------------------------------+        | 40259 | postgres | psql |             | postgres | 1 Oct 2018 10:54:56 PM (22 minutes ago) |        +-------+----------+------+-------------+----------+-----------------------------------------+        Idling in Transaction:        +-------+----------+---------+-------------+----------+----------+------------------------+        |   PID |     User |     App | Client Addr | Database | Aborted? |           State Change |        +-------+----------+---------+-------------+----------+----------+------------------------+        | 16507 | postgres | pgbench |             | postgres |       no | 1 Oct 2018 11:14:17 PM |        | 16508 | postgres | pgbench |             | postgres |       no | 1 Oct 2018 11:14:17 PM |        | 16512 | postgres | pgbench |             | postgres |       no | 1 Oct 2018 11:14:17 PM |        | 16513 | postgres | pgbench |             | postgres |       no | 1 Oct 2018 11:14:17 PM |        | 16518 | postgres | pgbench |             | postgres |       no | 1 Oct 2018 11:14:17 PM |        | 16523 | postgres | pgbench |             | postgres |       no | 1 Oct 2018 11:14:17 PM |        | 16524 | postgres | pgbench |             | postgres |       no | 1 Oct 2018 11:14:17 PM |        | 16528 | postgres | pgbench |             | postgres |       no | 1 Oct 2018 11:14:17 PM |        | 16530 | postgres | pgbench |             | postgres |       no | 1 Oct 2018 11:14:17 PM |        | 16534 | postgres | pgbench |             | postgres |       no | 1 Oct 2018 11:14:17 PM |        | 16535 | postgres | pgbench |             | postgres |       no | 1 Oct 2018 11:14:17 PM |        | 16537 | postgres | pgbench |             | postgres |       no | 1 Oct 2018 11:14:17 PM |        +-------+----------+---------+-------------+----------+----------+------------------------+    Vacuum Progress:      Vacuum Process #1:        Phase:             scanning heap        Database:          postgres        Table:                     Scan Progress:     201556359 of 205237504 (98.2% complete)        Heap Blks Vac'ed:  0 of 205237504        Idx Vac Cycles:    0        Dead Tuples:       0        Dead Tuples Max:   291      +------------------------------+-------------------+      |                      Setting |             Value |      +------------------------------+-------------------+      |         maintenance_work_mem | 2097152 (2.0 GiB) |      |                   autovacuum |                on |      | autovacuum_analyze_threshold |                50 |      |  autovacuum_vacuum_threshold |                50 |      |    autovacuum_freeze_max_age |        1200000000 |      |       autovacuum_max_workers |                16 |      |           autovacuum_naptime |            60 sec |      |        vacuum_freeze_min_age |          50000000 |      |      vacuum_freeze_table_age |        1150000000 |      +------------------------------+-------------------+    Roles:      +---------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+      |                      Name | Login | Repl | Super | Creat Rol | Creat DB | Bypass RLS | Inherit | Expires |                                                    Member Of |      +---------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+      |                  postgres |   yes |  yes |   yes |       yes |      yes |        yes |     yes |         |                                                              |      |                pg_monitor |       |      |       |           |          |            |     yes |         | pg_read_all_settings, pg_read_all_stats, pg_stat_scan_tables |      |      pg_read_all_settings |       |      |       |           |          |            |     yes |         |                                                              |      |         pg_read_all_stats |       |      |       |           |          |            |     yes |         |                                                              |      |       pg_stat_scan_tables |       |      |       |           |          |            |     yes |         |                                                              |      |         pg_signal_backend |       |      |       |           |          |            |     yes |         |                                                              |      |      pg_read_server_files |       |      |       |           |          |            |     yes |         |                                                              |      |     pg_write_server_files |       |      |       |           |          |            |     yes |         |                                                              |      | pg_execute_server_program |       |      |       |           |          |            |     yes |         |                                                              |      +---------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+    Tablespaces:      +------------+----------+------------------------------------+---------+----------------------------+-------------------------+      |       Name |    Owner |                           Location |    Size |                  Disk Used |              Inode Used |      +------------+----------+------------------------------------+---------+----------------------------+-------------------------+      | pg_default | postgres | $PGDATA = /data01/pg11/pg_root1921 | 337 GiB | 494 GiB (12.1%) of 4.0 TiB | 59771 (1.4%) of 4194304 |      |  pg_global | postgres | $PGDATA = /data01/pg11/pg_root1921 | 598 KiB | 494 GiB (12.1%) of 4.0 TiB | 59771 (1.4%) of 4194304 |      |       tbs1 | postgres |                  /data02/pg11/tbs1 | 1.9 TiB | 2.0 TiB (49.9%) of 4.0 TiB | 53907 (1.3%) of 4194304 |      |       tbs2 | postgres |                  /data03/pg11/tbs2 | 2.2 GiB | 550 GiB (13.4%) of 4.0 TiB | 62361 (1.5%) of 4194304 |      +------------+----------+------------------------------------+---------+----------------------------+-------------------------+    Database #1:      Name:                postgres      Owner:               postgres      Tablespace:          pg_default      Connections:         35 (no max limit)      Frozen Xid Age:      847687930      Transactions:        32236616423 (100.0%) commits, 148 (0.0%) rollbacks      Cache Hits:          100.0%      Rows Changed:        ins 25.1%, upd 74.9%, del 0.0%      Total Temp:          1.9 GiB in 4 files      Problems:            0 deadlocks, 0 conflicts      Totals Since:        21 Sep 2018 11:32:32 PM (1 week ago)      Size:                2.2 TiB      Sequences:        +------------------------+------------+        |               Sequence | Cache Hits |        +------------------------+------------+        |     part_p_partkey_seq |            |        | region_r_regionkey_seq |            |        | nation_n_nationkey_seq |            |        | supplier_s_suppkey_seq |            |        | customer_c_custkey_seq |            |        |  orders_o_orderkey_seq |            |        |           hints_id_seq |            |        |      user_order_id_seq |     100.0% |        +------------------------+------------+        Installed Extensions:        +--------------------+---------+-------------------------------------------------------------------+        |               Name | Version |                                                           Comment |        +--------------------+---------+-------------------------------------------------------------------+        |          btree_gin |     1.3 |                      support for indexing common datatypes in GIN |        |               cube |     1.4 |                              data type for multidimensional cubes |        |             dblink |     1.2 |      connect to other PostgreSQL databases from within a database |        |            imgsmlr |     1.0 |                                           image similarity module |        |       pg_hint_plan |   1.3.0 |                                                                   |        |      pg_oltp_bench |     1.0 |                            supporting function for oltp benchmark |        |         pg_prewarm |     1.2 |                                             prewarm relation data |        | pg_stat_statements |     1.5 |         track execution statistics of all SQL statements executed |        |            pg_trgm |     1.4 | text similarity measurement and index searching based on trigrams |        |           pgsocket |     1.0 |                                              TCP IP Socket client |        |            plpgsql |     1.0 |                                      PL/pgSQL procedural language |        |                rum |     1.2 |                                           RUM index access method |        +--------------------+---------+-------------------------------------------------------------------+    Database #2:      Name:                newdb      Owner:               postgres      Tablespace:          pg_default      Connections:         0 (no max limit)      Frozen Xid Age:      801198930      Transactions:        410 (97.2%) commits, 12 (2.8%) rollbacks      Cache Hits:          97.1%      Rows Changed:        ins 100.0%, upd 0.0%, del 0.0%      Total Temp:          0 B in 0 files      Problems:            0 deadlocks, 0 conflicts      Totals Since:        1 Oct 2018 10:47:44 PM (30 minutes ago)      Size:                23 MiB      Installed Extensions:        +---------+---------+------------------------------+        |    Name | Version |                      Comment |        +---------+---------+------------------------------+        | plpgsql |     1.0 | PL/pgSQL procedural language |        +---------+---------+------------------------------+    Table #1 in "postgres":      Name:                postgres.public.part      Columns:             9      Manual Vacuums:      1, last 6 hours ago      Manual Analyze:      never      Auto Vacuums:        31, last 2 hours ago      Auto Analyze:        never      Post-Analyze:        0.0% est. rows modified      Row Estimate:        100.0% live of total 39999206      Rows Changed:        ins 0.0%, upd 0.0%, del 0.0%      HOT Updates:         0.0% of all updates      Seq Scans:           0, 0.0 rows/scan      Idx Scans:           0, 0.0 rows/scan      Cache Hits:          78.2% (idx=75.0%)      Size:                6.6 GiB      Bloat:               442 MiB (6.6%)      +--------------+-------+---------+-------------------+------------+-------+----------------+-------------------+      |        Index |  Type |    Size |             Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan |      +--------------+-------+---------+-------------------+------------+-------+----------------+-------------------+      |  idx_part__6 | btree | 857 MiB |   442 MiB (51.5%) |      75.0% |     0 |            0.0 |               0.0 |      |  idx_part__5 | btree | 1.5 GiB |   442 MiB (28.1%) |      75.0% |     0 |            0.0 |               0.0 |      | idx_part_p_1 | btree | 9.3 MiB | 442 MiB (4734.9%) |      75.0% |     0 |            0.0 |               0.0 |      |   idx_part_1 | btree | 1.5 GiB |   442 MiB (28.5%) |      75.0% |     0 |            0.0 |               0.0 |      |    part_pkey | btree | 857 MiB |   442 MiB (51.5%) |      75.0% |     0 |            0.0 |               0.0 |      +--------------+-------+---------+-------------------+------------+-------+----------------+-------------------+    Table #2 in "postgres":      Name:                postgres.public.region      Columns:             3      Manual Vacuums:      1, last 6 hours ago      Manual Analyze:      never      Auto Vacuums:        31, last 2 hours ago      Auto Analyze:        never      Post-Analyze:        0.0% est. rows modified      Row Estimate:        100.0% live of total 5      Rows Changed:        ins 0.0%, upd 0.0%, del 0.0%      HOT Updates:         0.0% of all updates      Seq Scans:           0, 0.0 rows/scan      Idx Scans:           0, 0.0 rows/scan      Cache Hits:          83.0% (idx=75.0%)      Size:                40 KiB      +-------------+-------+--------+-------+------------+-------+----------------+-------------------+      |       Index |  Type |   Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan |      +-------------+-------+--------+-------+------------+-------+----------------+-------------------+      | region_pkey | btree | 16 KiB |       |      75.0% |     0 |            0.0 |               0.0 |      |        idx5 | btree | 16 KiB |       |      75.0% |     0 |            0.0 |               0.0 |      +-------------+-------+--------+-------+------------+-------+----------------+-------------------+    Table #3 in "postgres":      Name:                postgres.public.nation      Columns:             4      Manual Vacuums:      1, last 6 hours ago      Manual Analyze:      never      Auto Vacuums:        31, last 2 hours ago      Auto Analyze:        never      Post-Analyze:        0.0% est. rows modified      Row Estimate:        100.0% live of total 25      Rows Changed:        ins 0.0%, upd 0.0%, del 0.0%      HOT Updates:         0.0% of all updates      Seq Scans:           0, 0.0 rows/scan      Idx Scans:           0, 0.0 rows/scan      Cache Hits:          83.0% (idx=75.0%)      Size:                40 KiB      +----------------------+-------+--------+-------+------------+-------+----------------+-------------------+      |                Index |  Type |   Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan |      +----------------------+-------+--------+-------+------------+-------+----------------+-------------------+      |                 idx6 | btree | 16 KiB |       |      75.0% |     0 |            0.0 |               0.0 |      |          nation_pkey | btree | 16 KiB |       |      75.0% |     0 |            0.0 |               0.0 |      | idx_nation_regionkey | btree | 16 KiB |       |      75.0% |     0 |            0.0 |               0.0 |      +----------------------+-------+--------+-------+------------+-------+----------------+-------------------+    Table #4 in "postgres":      Name:                postgres.public.supplier      Columns:             7      Manual Vacuums:      1, last 6 hours ago      Manual Analyze:      never      Auto Vacuums:        31, last 2 hours ago      Auto Analyze:        never      Post-Analyze:        0.0% est. rows modified      Row Estimate:        100.0% live of total 1999109      Rows Changed:        ins 0.0%, upd 0.0%, del 0.0%      HOT Updates:         0.0% of all updates      Seq Scans:           0, 0.0 rows/scan      Idx Scans:           0, 0.0 rows/scan      Cache Hits:          79.4% (idx=75.0%)      Size:                369 MiB      Bloat:               9.5 MiB (2.6%)      +-------------------------+-------+--------+-----------------+------------+-------+----------------+-------------------+      |                   Index |  Type |   Size |           Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan |      +-------------------------+-------+--------+-----------------+------------+-------+----------------+-------------------+      |           supplier_pkey | btree | 43 MiB | 9.5 MiB (22.1%) |      75.0% |     0 |            0.0 |               0.0 |      |                    idx4 | btree | 60 MiB | 9.5 MiB (15.7%) |      75.0% |     0 |            0.0 |               0.0 |      | idx_supplier_nation_key | btree | 43 MiB | 9.5 MiB (22.1%) |      75.0% |     0 |            0.0 |               0.0 |      +-------------------------+-------+--------+-----------------+------------+-------+----------------+-------------------+    Table #5 in "postgres":      Name:                postgres.public.customer      Columns:             8      Manual Vacuums:      1, last 6 hours ago      Manual Analyze:      never      Auto Vacuums:        31, last 2 hours ago      Auto Analyze:        never      Post-Analyze:        0.0% est. rows modified      Row Estimate:        100.0% live of total 29998813      Rows Changed:        ins 0.0%, upd 0.0%, del 0.0%      HOT Updates:         0.0% of all updates      Seq Scans:           0, 0.0 rows/scan      Idx Scans:           0, 0.0 rows/scan      Cache Hits:          78.2% (idx=75.0%)      Size:                5.8 GiB      Bloat:               92 MiB (1.5%)      +---------------+-------+---------+----------------+------------+-------+----------------+-------------------+      |         Index |  Type |    Size |          Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan |      +---------------+-------+---------+----------------+------------+-------+----------------+-------------------+      | customer_pkey | btree | 643 MiB | 92 MiB (14.3%) |      75.0% |     0 |            0.0 |               0.0 |      +---------------+-------+---------+----------------+------------+-------+----------------+-------------------+    Table #6 in "postgres":      Name:                postgres.public.partsupp      Columns:             5      Manual Vacuums:      1, last 6 hours ago      Manual Analyze:      never      Auto Vacuums:        31, last 2 hours ago      Auto Analyze:        never      Post-Analyze:        0.0% est. rows modified      Row Estimate:        100.0% live of total 159999995      Rows Changed:        ins 0.0%, upd 0.0%, del 0.0%      HOT Updates:         0.0% of all updates      Seq Scans:           0, 0.0 rows/scan      Idx Scans:           0, 0.0 rows/scan      Cache Hits:          78.1% (idx=75.0%)      Size:                29 GiB      Bloat:               587 MiB (2.0%)      +----------------------+-------+---------+-----------------+------------+-------+----------------+-------------------+      |                Index |  Type |    Size |           Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan |      +----------------------+-------+---------+-----------------+------------+-------+----------------+-------------------+      | idx_partsupp_suppkey | btree | 3.3 GiB | 587 MiB (17.1%) |      75.0% |     0 |            0.0 |               0.0 |      | idx_partsupp_partkey | btree | 3.3 GiB | 587 MiB (17.1%) |      75.0% |     0 |            0.0 |               0.0 |      |                  idx | btree | 6.1 GiB |  587 MiB (9.5%) |      75.0% |     0 |            0.0 |               0.0 |      |        partsupp_pkey | btree | 4.7 GiB | 587 MiB (12.2%) |      75.0% |     0 |            0.0 |               0.0 |      |      idx_partsupp__4 | btree | 3.3 GiB | 587 MiB (17.1%) |      75.0% |     0 |            0.0 |               0.0 |      +----------------------+-------+---------+-----------------+------------+-------+----------------+-------------------+    Table #7 in "postgres":      Name:                postgres.public.orders      Columns:             9      Manual Vacuums:      1, last 6 hours ago      Manual Analyze:      never      Auto Vacuums:        31, last 2 hours ago      Auto Analyze:        never      Post-Analyze:        0.0% est. rows modified      Row Estimate:        100.0% live of total 299997901      Rows Changed:        ins 0.0%, upd 0.0%, del 0.0%      HOT Updates:         0.0% of all updates      Seq Scans:           0, 0.0 rows/scan      Idx Scans:           0, 0.0 rows/scan      Cache Hits:          78.1% (idx=75.0%)      Size:                43 GiB      Bloat:               1.8 GiB (4.2%)      +----------------------+-------+---------+-----------------+------------+-------+----------------+-------------------+      |                Index |  Type |    Size |           Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan |      +----------------------+-------+---------+-----------------+------------+-------+----------------+-------------------+      | idx_orders_orderdate | btree | 6.3 GiB | 1.8 GiB (28.7%) |      75.0% |     0 |            0.0 |               0.0 |      |        idx_orders__6 | btree | 8.8 GiB | 1.8 GiB (20.5%) |      75.0% |     0 |            0.0 |               0.0 |      |   idx_orders_custkey | btree | 6.3 GiB | 1.8 GiB (28.7%) |      75.0% |     0 |            0.0 |               0.0 |      |          orders_pkey | btree | 6.3 GiB | 1.8 GiB (28.7%) |      75.0% |     0 |            0.0 |               0.0 |      +----------------------+-------+---------+-----------------+------------+-------+----------------+-------------------+    Table #8 in "postgres":      Name:                postgres.public.lineitem      Columns:             16      Manual Vacuums:      1, last 6 hours ago      Manual Analyze:      never      Auto Vacuums:        31, last 2 hours ago      Auto Analyze:        never      Post-Analyze:        0.0% est. rows modified      Row Estimate:        100.0% live of total 1200018409      Rows Changed:        ins 0.0%, upd 0.0%, del 0.0%      HOT Updates:         0.0% of all updates      Seq Scans:           0, 0.0 rows/scan      Idx Scans:           0, 0.0 rows/scan      Cache Hits:          78.1% (idx=77.1%)      Size:                199 GiB      Bloat:               6.6 GiB (3.3%)      +-----------------------+-------+---------+------------------+------------+-------+----------------+-------------------+      |                 Index |  Type |    Size |            Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan |      +-----------------------+-------+---------+------------------+------------+-------+----------------+-------------------+      | idx_lineitem_orderkey | btree |  25 GiB |  6.6 GiB (26.4%) |      77.4% |     0 |            0.0 |               0.0 |      |                  idx1 | btree |  16 GiB |  6.6 GiB (41.8%) |      77.4% |     0 |            0.0 |               0.0 |      | idx_lineitem_shipdate | btree |  45 GiB |  6.6 GiB (14.6%) |      77.4% |     0 |            0.0 |               0.0 |      |       idx_lineitem__2 | btree |  25 GiB |  6.6 GiB (26.4%) |      77.4% |     0 |            0.0 |               0.0 |      |       idx_lineitem__3 | btree |  25 GiB |  6.6 GiB (26.4%) |      77.4% |     0 |            0.0 |               0.0 |      |      idx_lineitem__11 | btree |  25 GiB |  6.6 GiB (26.4%) |      77.4% |     0 |            0.0 |               0.0 |      |          idx_part_l_2 | btree | 5.3 GiB | 6.6 GiB (124.0%) |      77.4% |     0 |            0.0 |               0.0 |      |         lineitem_pkey | btree |  35 GiB |  6.6 GiB (18.8%) |      75.0% |     0 |            0.0 |               0.0 |      +-----------------------+-------+---------+------------------+------------+-------+----------------+-------------------+    Table #9 in "postgres":      Name:                postgres.hint_plan.hints      Columns:             4      Manual Vacuums:      1, last 6 hours ago      Manual Analyze:      never      Auto Vacuums:        31, last 2 hours ago      Auto Analyze:        never      Post-Analyze:        0.0% est. rows modified      Row Estimate:        0.0% live of total 0      Rows Changed:        ins 0.0%, upd 0.0%, del 0.0%      HOT Updates:         0.0% of all updates      Seq Scans:           0, 0.0 rows/scan      Idx Scans:           0, 0.0 rows/scan      Cache Hits:          75.0% (idx=75.0%)      Size:                8.0 KiB      +--------------------+-------+---------+-------+------------+-------+----------------+-------------------+      |              Index |  Type |    Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan |      +--------------------+-------+---------+-------+------------+-------+----------------+-------------------+      | hints_norm_and_app | btree | 8.0 KiB |       |      75.0% |     0 |            0.0 |               0.0 |      |         hints_pkey | btree | 8.0 KiB |       |      75.0% |     0 |            0.0 |               0.0 |      +--------------------+-------+---------+-------+------------+-------+----------------+-------------------+    Table #10 in "postgres":      Name:                postgres.public.pgbench_history      Tablespace:          tbs1      Columns:             6      Manual Vacuums:      5, last 6 hours ago      Manual Analyze:      1, last 4 days ago      Auto Vacuums:        26, last 2 hours ago      Auto Analyze:        70, last 7 hours ago      Post-Analyze:        7.4% est. rows modified      Row Estimate:        100.0% live of total 32165335009      Rows Changed:        ins 100.0%, upd 0.0%, del 0.0%      HOT Updates:         0.0% of all updates      Seq Scans:           0, 0.0 rows/scan      Idx Scans:           0, 0.0 rows/scan      Cache Hits:          99.4% (idx=0.0%)      Size:                1.5 TiB    Table #11 in "postgres":      Name:                postgres.public.pgbench_tellers      Tablespace:          tbs1      Columns:             4      Manual Vacuums:      4, last 6 minutes ago      Manual Analyze:      1, last 4 days ago      Auto Vacuums:        7581, last 4 minutes ago      Auto Analyze:        7581, last 4 minutes ago      Post-Analyze:        6219.9% est. rows modified      Row Estimate:        17.8% live of total 56200      Rows Changed:        ins 0.0%, upd 99.9%, del 0.0%      HOT Updates:         99.9% of all updates      Seq Scans:           1, 10000.0 rows/scan      Idx Scans:           32236503570, 1.0 rows/scan      Cache Hits:          100.0% (idx=100.0%)      Size:                174 MiB      Bloat:               253 MiB (145.3%)      +----------------------+-------+--------+------------------+------------+-------------+----------------+-------------------+      |                Index |  Type |   Size |            Bloat | Cache Hits |       Scans | Rows Read/Scan | Rows Fetched/Scan |      +----------------------+-------+--------+------------------+------------+-------------+----------------+-------------------+      | pgbench_tellers_pkey | btree | 80 MiB | 253 MiB (316.8%) |     100.0% | 32236503570 |            1.0 |               1.0 |      +----------------------+-------+--------+------------------+------------+-------------+----------------+-------------------+    Table #12 in "postgres":      Name:                postgres.public.pgbench_accounts      Tablespace:          tbs1      Columns:             4      Manual Vacuums:      5, last 6 minutes ago      Manual Analyze:      2, last 4 days ago      Auto Vacuums:        27, last 2 hours ago      Auto Analyze:        2828, last 6 minutes ago      Post-Analyze:        13.1% est. rows modified      Row Estimate:        98.3% live of total 101746633      Rows Changed:        ins 0.3%, upd 99.7%, del 0.0%      HOT Updates:         100.0% of all updates      Seq Scans:           1, 100000000.0 rows/scan      Idx Scans:           64473007140, 1.0 rows/scan      Cache Hits:          100.0% (idx=100.0%)      Size:                13 GiB      Bloat:               977 MiB (7.2%)      +-----------------------+-------+---------+-----------------+------------+-------------+----------------+-------------------+      |                 Index |  Type |    Size |           Bloat | Cache Hits |       Scans | Rows Read/Scan | Rows Fetched/Scan |      +-----------------------+-------+---------+-----------------+------------+-------------+----------------+-------------------+      | pgbench_accounts_pkey | btree | 2.1 GiB | 977 MiB (45.6%) |     100.0% | 64473007140 |            1.0 |               1.0 |      +-----------------------+-------+---------+-----------------+------------+-------------+----------------+-------------------+    Table #13 in "postgres":      Name:                postgres.public.pgbench_branches      Tablespace:          tbs1      Columns:             3      Manual Vacuums:      4, last 6 minutes ago      Manual Analyze:      1, last 4 days ago      Auto Vacuums:        7578, last 4 minutes ago      Auto Analyze:        7578, last 4 minutes ago      Post-Analyze:        22546.2% est. rows modified      Row Estimate:        6.5% live of total 15504      Rows Changed:        ins 0.0%, upd 99.9%, del 0.0%      HOT Updates:         99.9% of all updates      Seq Scans:           2, 1000.0 rows/scan      Idx Scans:           32236503570, 1.0 rows/scan      Cache Hits:          100.0% (idx=100.0%)      Size:                65 MiB      Bloat:               96 MiB (147.5%)      +-----------------------+-------+--------+-----------------+------------+-------------+----------------+-------------------+      |                 Index |  Type |   Size |           Bloat | Cache Hits |       Scans | Rows Read/Scan | Rows Fetched/Scan |      +-----------------------+-------+--------+-----------------+------------+-------------+----------------+-------------------+      | pgbench_branches_pkey | btree | 31 MiB | 96 MiB (308.9%) |     100.0% | 32236503570 |            1.1 |               1.0 |      +-----------------------+-------+--------+-----------------+------------+-------------+----------------+-------------------+    Table #14 in "postgres":      Name:                postgres.public.user_order      Columns:             11      Manual Vacuums:      2, last 22 minutes ago      Manual Analyze:      never      Auto Vacuums:        1, last 3 hours ago      Auto Analyze:        1, last 7 hours ago      Post-Analyze:        0.0% est. rows modified      Row Estimate:        100.0% live of total 999928      Rows Changed:        ins 100.0%, upd 0.0%, del 0.0%      HOT Updates:         0.0% of all updates      Seq Scans:           1, 0.0 rows/scan      Idx Scans:           0, 0.0 rows/scan      Cache Hits:          98.4% (idx=99.8%)      Size:                135 MiB      Bloat:               24 MiB (17.7%)      +-----------------+-------+--------+-----------------+------------+-------+----------------+-------------------+      |           Index |  Type |   Size |           Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan |      +-----------------+-------+--------+-----------------+------------+-------+----------------+-------------------+      | user_order_pkey | btree | 21 MiB | 24 MiB (111.0%) |      99.8% |     0 |            0.0 |               0.0 |      +-----------------+-------+--------+-----------------+------------+-------+----------------+-------------------+    Table #1 in "newdb":      Name:                newdb.public.pgbench_history      Columns:             6      Manual Vacuums:      1, last 30 minutes ago      Manual Analyze:      1, last 30 minutes ago      Auto Vacuums:        never      Auto Analyze:        never      Post-Analyze:        0.0% est. rows modified      Row Estimate:        0.0% live of total 0      Rows Changed:        ins 0.0%, upd 0.0%, del 0.0%      HOT Updates:         0.0% of all updates      Seq Scans:           0, 0.0 rows/scan      Idx Scans:           0, 0.0 rows/scan      Cache Hits:          0.0% (idx=0.0%)      Size:                0 B    Table #2 in "newdb":      Name:                newdb.public.pgbench_tellers      Columns:             4      Manual Vacuums:      1, last 30 minutes ago      Manual Analyze:      1, last 30 minutes ago      Auto Vacuums:        never      Auto Analyze:        never      Post-Analyze:        0.0% est. rows modified      Row Estimate:        100.0% live of total 10      Rows Changed:        ins 100.0%, upd 0.0%, del 0.0%      HOT Updates:         0.0% of all updates      Seq Scans:           1, 10.0 rows/scan      Idx Scans:           0, 0.0 rows/scan      Cache Hits:          76.2% (idx=0.0%)      Size:                40 KiB      +----------------------+-------+--------+-------+------------+-------+----------------+-------------------+      |                Index |  Type |   Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan |      +----------------------+-------+--------+-------+------------+-------+----------------+-------------------+      | pgbench_tellers_pkey | btree | 16 KiB |       |            |     0 |            0.0 |               0.0 |      +----------------------+-------+--------+-------+------------+-------+----------------+-------------------+    Table #3 in "newdb":      Name:                newdb.public.pgbench_accounts      Columns:             4      Manual Vacuums:      1, last 30 minutes ago      Manual Analyze:      1, last 30 minutes ago      Auto Vacuums:        never      Auto Analyze:        never      Post-Analyze:        0.0% est. rows modified      Row Estimate:        100.0% live of total 100000      Rows Changed:        ins 100.0%, upd 0.0%, del 0.0%      HOT Updates:         0.0% of all updates      Seq Scans:           1, 100000.0 rows/scan      Idx Scans:           0, 0.0 rows/scan      Cache Hits:          80.0% (idx=0.0%)      Size:                13 MiB      Bloat:               192 KiB (1.5%)      +-----------------------+-------+---------+----------------+------------+-------+----------------+-------------------+      |                 Index |  Type |    Size |          Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan |      +-----------------------+-------+---------+----------------+------------+-------+----------------+-------------------+      | pgbench_accounts_pkey | btree | 2.2 MiB | 192 KiB (8.7%) |            |     0 |            0.0 |               0.0 |      +-----------------------+-------+---------+----------------+------------+-------+----------------+-------------------+    Table #4 in "newdb":      Name:                newdb.public.pgbench_branches      Columns:             3      Manual Vacuums:      1, last 30 minutes ago      Manual Analyze:      1, last 30 minutes ago      Auto Vacuums:        never      Auto Analyze:        never      Post-Analyze:        100.0% est. rows modified      Row Estimate:        100.0% live of total 1      Rows Changed:        ins 100.0%, upd 0.0%, del 0.0%      HOT Updates:         0.0% of all updates      Seq Scans:           1, 1.0 rows/scan      Idx Scans:           0, 0.0 rows/scan      Cache Hits:          58.3% (idx=0.0%)      Size:                40 KiB      +-----------------------+-------+--------+-------+------------+-------+----------------+-------------------+      |                 Index |  Type |   Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan |      +-----------------------+-------+--------+-------+------------+-------+----------------+-------------------+      | pgbench_branches_pkey | btree | 16 KiB |       |            |     0 |            0.0 |               0.0 |      +-----------------------+-------+--------+-------+------------+-------+----------------+-------------------+

小结

1、pgmetrics用法非常简单，连接到数据库，获取内置的监测指标的内容。

pgmetrics --help  pgmetrics collects PostgreSQL information and metrics.    Usage:    pgmetrics [OPTION]... [DBNAME]    General options:    -t, --timeout=SECS           individual query timeout in seconds (default: 5)    -i, --input=FILE             don't connect to db, instead read and display                                     this previously saved JSON file    -V, --version                output version information, then exit    -?, --help[=options]         show this help, then exit        --help=variables         list environment variables, then exit    Collection options:    -S, --no-sizes               don't collect tablespace and relation sizes    -c, --schema=REGEXP          collect only from schema(s) matching POSIX regexp    -C, --exclude-schema=REGEXP  do NOT collect from schema(s) matching POSIX regexp    -a, --table=REGEXP           collect only from table(s) matching POSIX regexp    -A, --exclude-table=REGEXP   do NOT collect from table(s) matching POSIX regexp        --omit=WHAT              do NOT collect the items specified as a comma-separated                                     list of: "tables", "indexes", "sequences",                                     "functions", "extensions", "triggers", "statements"        --sql-length=LIMIT       collect only first LIMIT characters of all SQL                                     queries (default: 500)        --statements-limit=LIMIT collect only utmost LIMIT number of row from                                     pg_stat_statements (default: 100)    Output options:    -f, --format=FORMAT          output format; "human", or "json" (default: "human")    -l, --toolong=SECS           for human output, transactions running longer than                                     this are considered too long (default: 60)    -o, --output=FILE            write output to the specified file        --no-pager               do not invoke the pager for tty output    Connection options:    -h, --host=HOSTNAME          database server host or socket directory                                     (default: "/data01/pg11/pg_root1921")    -p, --port=PORT              database server port (default: 1921)    -U, --username=USERNAME      database user name (default: "postgres")        --no-password            never prompt for password    For more information, visit .

2、用户可以使用pgmetrics定义收集数据库状态信息。

3、结合pgdash，可以将pgmetrics收集的内容可视化，并提供告警能力。

4、我在之前还写过一些数据库指标监控的文档，请参考末尾。

5、如发现需要新增的指标，或者有些指标不正确，可以发ISSUE给作者。

目前pgmetrics的指标已经比较丰富，可以用于日常的状态和性能监控。

到此，相信大家对"PostgreSQL中pgmetrics有什么作用"有了更深的了解，不妨来实际操作一番吧！这里是网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！