怎么使用PostgreSQL的插件pgmetries

这篇文章主要介绍“怎么使用postgresql的插件pgmetries”，在日常操作中，相信很多人在怎么使用Postgresql的插件pgmetries问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”怎么使用PostgreSQL的插件pgmetries”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

严格上来说，pgmetries不能成为插件，是一个用于统计pg数据库的一个工具。

安装
安装很简单，在 GitHub上下载相应的binary文件，解压即可使用。

[pg12@localhost software]$ cd pgmetrics_1.7.1_linux_amd64
[pg12@localhost pgmetrics_1.7.1_linux_amd64]$ ls
LICENSE  pgmetrics  README.md
[pg12@localhost pgmetrics_1.7.1_linux_amd64]$ 
[pg12@localhost pgmetrics_1.7.1_linux_amd64]$ ./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)
      --only-listed            collect info only about the databases listed as
                                   command-line args (use with Heroku)
Output options:
  -f, --format=FORMAT          output format; "human", "json" or "csv" (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: "/data/run/pg12")
  -p, --port=PORT              database server port (default: 5120)
  -U, --username=USERNAME      database user name (default: "pg12")
  -w, --no-passWord            never prompt for password
For more information, visit <https://pgmetrics.io>.

体验
执行pgmetries，输入password

[pg12@localhost pgmetrics_1.7.1_linux_amd64]$ ./pgmetrics -h localhost db1
Password: 
[pg12@localhost pgmetrics_1.7.1_linux_amd64]$ ./pgmetrics -h localhost -U pg12 db1
Password: 
pgmetrics run at: 19 Dec 2019 5:24:20 PM (1 second aGo)

这是数据库Cluster的概要信息

PostgreSQL Cluster:
    Name:                
    Server Version:      12.1                                     -->版本
    Server Started:      19 Dec 2019 5:03:01 PM (21 minutes ago)  -->启动时间
    System Identifier:   6761668844610171990                      -->数据库ID
    Timeline:            1                                        -->时间线
    Last Checkpoint:     19 Dec 2019 5:18:00 PM (6 minutes ago)   -->最后一次checkpoint时间
    REDO LSN:            1A/8CF526B8                              -->LSN
    Checkpoint LSN:      1A/906502D0 (55 MiB since REDO)          -->checkpoint的LSN
    Transaction IDs:     479 to 117837591 (diff = 117837112)      -->事务ID,diff是age
    Notification Queue:  0.0% Used
    Active Backends:     14 (max 100)                             -->活动会话计数                               
    Recovery Mode?       no                                       -->生产库

主机信息

System Information:
    Hostname:            localhost.localdomain
    CPU Cores:           4 x Intel(R) Core(TM) i7-4790 CPU @ 3.60GHz
    Load Average:        7.28
    Memory:              used=332 MiB, free=121 MiB, buff=8.0 KiB, cache=3.3 GiB
    Swap:                used=4.5 MiB, free=1020 MiB
    +---------------------------------+------------------+
    |                         Setting |            Value |
    +---------------------------------+------------------+
    |                  shared_buffers | 262144 (2.0 GiB) |
    |                        work_mem |   4096 (4.0 MiB) |
    |            maintenance_work_mem |   65536 (64 MiB) |
    |                    temp_buffers |   1024 (8.0 MiB) |
    |             autovacuum_work_mem |               -1 |
    |                 temp_file_limit |               -1 |
    |            max_worker_processes |                8 |
    |          autovacuum_max_workers |                5 |
    | max_parallel_workers_per_gather |                2 |
    |        effective_io_concurrency |                1 |
    +---------------------------------+------------------+

WAL文件信息

WAL Files:
    WAL Archiving?       no
    WAL Files:           79
    +--------------------+----------------+
    |            Setting |          Value |
    +--------------------+----------------+
    |          wal_level |        replica |
    |    arcHive_timeout |              0 |
    |    wal_compression |            off |
    |       max_wal_size | 8192 (128 GiB) |
    |       min_wal_size |  128 (2.0 GiB) |
    | checkpoint_timeout |            900 |
    |   full_page_writes |             on |
    |  wal_keep_segments |              0 |
    +--------------------+----------------+
`

后台BG Writer进程

BG Writer:
    Checkpoint Rate:     0.02 per min
    Average Write:       3.3 MiB per checkpoint               -->checkpoint的平均写入大小
    Total Checkpoints:   1 sched (100.0%) + 0 req (0.0%) = 1
    Total Write:         3.3 MiB, @ 1.3 KiB per sec           -->写入统计
    Buffers Allocated:   2547 (20 MiB)
    Buffers Written:     421 chkpt (100.0%) + 0 bgw (0.0%) + 0 be (0.0%)
    Clean Scan Stops:    0
    BE fsyncs:           0
    Counts Since:        19 Dec 2019 4:42:21 PM (42 minutes ago)
    +------------------------------+--------------+
    |                      Setting |        Value |
    +------------------------------+--------------+
    |               bgwriter_delay |     200 msec |
    |         bgwriter_flush_after | 64 (512 KiB) |
    |        bgwriter_lru_maxpages |          100 |
    |      bgwriter_lru_multiplier |            2 |
    |                   block_size |         8192 |
    |           checkpoint_timeout |      900 sec |
    | checkpoint_completion_target |          0.9 |
    +------------------------------+--------------+

当前后台进程(Session)信息

Backends:
    Total Backends:      14 (14.0% of max 100)
    Problematic:         0 waiting on locks, 13 waiting on other, 0 xact too long, 0 idle in xact
    Other Waiting Backends:
      +------+------+---------+-------------+----------+-----------------------+------------------------+
      |  PID | User |     App | Client Addr | Database |                  Wait |            Query Start |
      +------+------+---------+-------------+----------+-----------------------+------------------------+
      | 5459 | pg12 | pgbench |             |      db1 | LWLock / WALWriteLock | 19 Dec 2019 5:24:21 PM |
      | 5460 | pg12 | pgbench |             |      db1 | LWLock / WALWriteLock | 19 Dec 2019 5:24:21 PM |
      | 5461 | pg12 | pgbench |             |      db1 | LWLock / WALWriteLock | 19 Dec 2019 5:24:21 PM |
      | 5462 | pg12 | pgbench |             |      db1 | LWLock / WALWriteLock | 19 Dec 2019 5:24:21 PM |
      | 5480 | pg12 | pgbench |             |      db2 | LWLock / WALWriteLock | 19 Dec 2019 5:24:21 PM |
      | 5481 | pg12 | pgbench |             |      db2 |   Client / ClientRead | 19 Dec 2019 5:24:21 PM |
      | 5482 | pg12 | pgbench |             |      db2 | LWLock / WALWriteLock | 19 Dec 2019 5:24:21 PM |
      | 5483 | pg12 | pgbench |             |      db2 | LWLock / WALWriteLock | 19 Dec 2019 5:24:21 PM |
      | 5505 | pg12 | pgbench |             |      db3 | LWLock / WALWriteLock | 19 Dec 2019 5:24:21 PM |
      | 5506 | pg12 | pgbench |             |      db3 |          IO / WALSync | 19 Dec 2019 5:24:21 PM |
      | 5507 | pg12 | pgbench |             |      db3 | LWLock / WALWriteLock | 19 Dec 2019 5:24:21 PM |
      | 5508 | pg12 | pgbench |             |      db3 | LWLock / WALWriteLock | 19 Dec 2019 5:24:21 PM |
      | 9009 | pg12 |    psql |             |      db1 |   Client / ClientRead | 19 Dec 2019 5:16:46 PM |
      +------+------+---------+-------------+----------+-----------------------+------------------------+

锁信息

Locks:
    +---------------+-------------+-------+
    |     Lock Type | Not Granted | Total |
    +---------------+-------------+-------+
    |      relation |           0 |    20 |
    | transactionid |           0 |     8 |
    |    virtualxid |           0 |    10 |
    +---------------+-------------+-------+
    |               |           0 |    38 |
    +---------------+-------------+-------+

Vacuum进程信息

Vacuum Progress:
    No manual or auto vacuum jobs in progress.
    +------------------------------+----------------+
    |                      Setting |          Value |
    +------------------------------+----------------+
    |         maintenance_work_mem | 65536 (64 MiB) |
    |                   autovacuum |             on |
    | autovacuum_analyze_threshold |             50 |
    |  autovacuum_vacuum_threshold |             50 |
    |    autovacuum_freeze_max_age |      200000000 |
    |       autovacuum_max_workers |              5 |
    |           autovacuum_naptime |         60 sec |
    |        vacuum_freeze_min_age |       50000000 |
    |      vacuum_freeze_table_age |      150000000 |
    +------------------------------+----------------+

角色

Roles:
    +---------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--
------------------------------------------------------------+
    |                      Name | Login | Repl | Super | Creat Rol | Creat DB | Bypass RLS | Inherit | Expires |  
                                                  Member Of |
    +---------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--
------------------------------------------------------------+
    |                      pg12 |   yes |  yes |   yes |       yes |      yes |        yes |     yes |         |  
                                                            |
    |                pg_monitor |       |      |       |           |          |            |     yes |         | p
g_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 |                In
ode Used |
    +------------+-------+-------------------------------+---------+---------------------------+------------------
---------+
    | pg_default |  pg12 | $PGDATA = /data/pgsql/pg121db | 3.1 GiB | 62 GiB (56.8%) of 110 GiB | 237976 (0.4%) of 
57569280 |
    |  pg_global |  pg12 | $PGDATA = /data/pgsql/pg121db | 398 KiB | 62 GiB (56.8%) of 110 GiB | 237976 (0.4%) of 
57569280 |
    +------------+-------+-------------------------------+---------+---------------------------+------------------
---------+

数据库信息

Database #1:
    Name:                postgres
    Owner:               pg12
    Tablespace:          pg_default
    Connections:         0 (no max limit)
    Frozen Xid Age:      121949293
    Transactions:        0 (0.0%) commits, 0 (0.0%) rollbacks
    Cache Hits:          0.0%
    Rows Changed:        ins 0.0%, upd 0.0%, del 0.0%
    Total Temp:          0 B in 0 files
    Problems:            0 deadlocks, 0 conflicts
    Totals Since:        
    Size:                7.6 MiB
Database #2:
    Name:                db1
    Owner:               pg12
    Tablespace:          pg_default
    Connections:         6 (no max limit)
    Frozen Xid Age:      121949293
    Transactions:        6716016 (100.0%) commits, 1 (0.0%) rollbacks
    Cache Hits:          100.0%
    Rows Changed:        ins 0.0%, upd 100.0%, del 0.0%
    Total Temp:          0 B in 0 files
    Problems:            0 deadlocks, 0 conflicts
    Totals Since:        19 Dec 2019 4:49:55 PM (34 minutes ago)
    Size:                8.9 MiB
    Installed Extensions:
      +---------+---------+------------------------------+
      |    Name | Version |                      Comment |
      +---------+---------+------------------------------+
      | plpgsql |     1.0 | PL/pgSQL procedural language |
      +---------+---------+------------------------------+
Database #3:
    Name:                db2
    Owner:               pg12
    Tablespace:          pg_default
    Connections:         4 (no max limit)
    Frozen Xid Age:      121949293
    Transactions:        6691569 (100.0%) commits, 0 (0.0%) rollbacks
    Cache Hits:          100.0%
    Rows Changed:        ins 0.0%, upd 100.0%, del 0.0%
    Total Temp:          0 B in 0 files
    Problems:            0 deadlocks, 0 conflicts
    Totals Since:        19 Dec 2019 4:51:16 PM (33 minutes ago)
    Size:                283 MiB
Database #4:
    Name:                db3
    Owner:               pg12
    Tablespace:          pg_default
    Connections:         4 (no max limit)
    Frozen Xid Age:      121949293
    Transactions:        6675090 (100.0%) commits, 0 (0.0%) rollbacks
    Cache Hits:          100.0%
    Rows Changed:        ins 0.0%, upd 100.0%, del 0.0%
    Total Temp:          0 B in 0 files
    Problems:            0 deadlocks, 0 conflicts
    Totals Since:        19 Dec 2019 4:51:29 PM (32 minutes ago)
    Size:                9.2 MiB
Database #5:
    Name:                testdb
    Owner:               pg12
    Tablespace:          pg_default
    Connections:         0 (no max limit)
    Frozen Xid Age:      121949293
    Transactions:        2533 (100.0%) commits, 1 (0.0%) rollbacks
    Cache Hits:          99.9%
    Rows Changed:        ins 0.0%, upd 0.0%, del 0.0%
    Total Temp:          0 B in 0 files
    Problems:            0 deadlocks, 0 conflicts
    Totals Since:        19 Dec 2019 4:55:12 PM (29 minutes ago)
    Size:                2.8 GiB
Table #1 in "db1":
    Name:                db1.public.t_autovacuum_db1
    Columns:             1
    Manual Vacuums:      never
    Manual Analyze:      never
    Auto Vacuums:        never
    Auto Analyze:        31, last 20 seconds ago
    Post-Analyze:        641.1% est. rows modified
    Row Estimate:        83.4% live of total 11986
    Rows Changed:        ins 0.0%, upd 100.0%, del 0.0%
    HOT Updates:         100.0% of all updates
    Seq Scans:           0, 0.0 rows/scan
    Idx Scans:           6715855, 1.0 rows/scan
    Cache Hits:          100.0% (idx=100.0%)
    Size:                664 KiB
    Bloat:               272 KiB (41.0%)
    +-----------------------+-------+---------+-----------------+------------+---------+----------------+---------
----------+
    |                 Index |  Type |    Size |           Bloat | Cache Hits |   Scans | Rows Read/Scan | Rows Fet
ched/Scan |
    +-----------------------+-------+---------+-----------------+------------+---------+----------------+---------
----------+
    | t_autovacuum_db1_pkey | btree | 456 KiB | 296 KiB (64.9%) |     100.0% | 6715855 |            1.0 |         
      1.0 |
    +-----------------------+-------+---------+-----------------+------------+---------+----------------+---------
----------+
(END)
[pg12@localhost pgmetrics_1.7.1_linux_amd64]$

未来发展
相信绝大多数人都经历过医院的体检,体检报告中列出结果指标和正常范围值指标,然后给出定性的偏高+/偏低-,按此思路来改进pgmetrics不失为一个方向.

到此，关于“怎么使用PostgreSQL的插件pgmetries”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注编程网网站，小编会继续努力为大家带来更多实用的文章！