怎么使用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]$ lsLICENSE  pgmetrics  README.md[pg12@localhost pgmetrics_1.7.1_linux_amd64]$ [pg12@localhost pgmetrics_1.7.1_linux_amd64]$ ./pgmetrics --helppgmetrics 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 exitCollection 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 outputConnection 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 passwordFor more information, visit .

体验
执行pgmetries，输入password

[pg12@localhost pgmetrics_1.7.1_linux_amd64]$ ./pgmetrics -h localhost db1Password: [pg12@localhost pgmetrics_1.7.1_linux_amd64]$ ./pgmetrics -h localhost -U pg12 db1Password: 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 |         | 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 |  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 MiBDatabase #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 MiBDatabase #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 MiBDatabase #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 GiBTable #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 Fetched/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"的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注网站，小编会继续努力为大家带来更多实用的文章！