怎么使用PostgreSQL的插件pgmetries

技术怎么使用PostgreSQL的插件pgmetries这篇文章主要介绍“怎么使用PostgreSQL的插件pgmetries”,在日常操作中,相信很多人在怎么使用PostgreSQL的插件pgmetries问题上存在疑

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

严格上来说,pgmetries不能成为插件,是一个用于统计程序制导数据库的一个工具。

安装

安装很简单,在

开源代码库上下载相应的二进制的文件,解压即可使用。

[pg12 @ localhostsoftware]$ cdpgmetrics _ 1。7 .1 _ Linux _ amd64

[pg12 @ localhostpgmetrics _ 1。7 .1 _ Linux _ amd64]$ ls

LICENSEpgmetricsREADME.md

[pg12 @ localhostpgmetrics _ 1。7 .1 _ Linux _ amd64]$

[pg12 @ localhostpgmetrics _ 1。7 .1 _ Linux _ amd64]$ ./pgmetrics -帮助

pgmetricccscoletspostgresqlinformationand metrics .

Usage:

pgmetrics[OPTION].[DBNAME]

一般选项3360

-t,-time out=secsindividequerytimeutinses(默认值:5)

-i,- input=FILEdon'tconnecttodb,insteadreadanddisplay

这是一个预配置文件

版本信息,退出

-?-帮助[=选项]显示此帮助,退出

-help=变量列表环境变量,新退出

集合选项:

-S,-无大小sdon不收集表空间和关系大小

-c,-schema=regexcolletlyFromschema匹配positingxRegeexp

-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”的学习就结束了,希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习,快去试试吧!若想继续学习更多相关知识,请继续关注网站,小编会继续努力为大家带来更多实用的文章!

内容来源网络,如有侵权,联系删除,本文地址:https://www.230890.com/zhan/76888.html

(0)

相关推荐

  • 如何使用mysqldump对mysql进行备份和恢复

    技术如何使用mysqldump对mysql进行备份和恢复这篇文章给大家分享的是有关如何使用mysqldump对mysql进行备份和恢复的内容。小编觉得挺实用的,因此分享给大家做个参考,一起跟随小编过来看看吧。mysqld

    攻略 2021年11月3日
  • 偶数的概念,奇数和偶数的概念0是什么数

    技术偶数的概念,奇数和偶数的概念0是什么数1993年我国颁布的《中华人民共和国国家标准》(GB3100-3102-93)《量和单位》(11-2.9)P311页,规定自然数包括0。在以往的教学中,关于自然数的组成,有两种情

    生活 2021年10月24日
  • 鲫鱼的做法大全家常菜,鲫鱼怎么做好吃

    技术鲫鱼的做法大全家常菜,鲫鱼怎么做好吃鲫鱼豆腐汤鲫鱼的做法大全家常菜:材料:
    鲫鱼1条、豆腐1盒、姜3片、葱3段、油、盐、胡椒、料酒、鸡精做法:
    1、鲫鱼开膛去内脏,去鳞去鳃(鱼档工作人员完成),洗净,抹干,用盐和

    生活 2021年10月20日
  • 如何理解解WCF契约应用

    技术如何理解解WCF契约应用本篇文章给大家分享的是有关如何理解解WCF契约应用,小编觉得挺实用的,因此分享给大家学习,希望大家阅读完这篇文章后可以有所收获,话不多说,跟着小编一起来看看吧。现在我们来具体实现一个WCF契约

    攻略 2021年11月16日
  • mysql遇到不小心误删表怎么办

    技术mysql遇到不小心误删表怎么办这篇文章主要介绍了mysql遇到不小心误删表怎么办,具有一定借鉴价值,感兴趣的朋友可以参考下,希望大家阅读完这篇文章之后大有收获,下面让小编带着大家一起了解一下。测试环境:5.6.33

    攻略 2021年11月2日
  • Vue后台管理系统怎么实现分页功能

    技术Vue后台管理系统怎么实现分页功能这篇文章主要介绍“Vue后台管理系统怎么实现分页功能”,在日常操作中,相信很多人在Vue后台管理系统怎么实现分页功能问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望

    攻略 2021年12月9日