这篇文章主要介绍“怎么使用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”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！
