一、pgmetrics功能概述
pgmetrics是一个开源工具,用于从正在运行的 PostgreSQL 服务器收集和报告各种统计数据和信息,以帮助进行故障排除、监控和自动化。
目前已收集 350 多个指标,请参阅 JSON 输出或源代码以获取完整列表。以下是收集的指标的概述:
- 服务器:版本、系统标识符、时间线、事务 ID 环绕、检查点滞后
- 复制:主端、备用端、物理和逻辑复制槽
- WAL 归档:归档率、WAL 和就绪文件数、上次成功和失败时间
- BG Writer:检查点速率、总检查点(sched+req)、缓冲区
- 真空相关:正在进行的自动/手动真空进度、上次分析/真空、设置
- 表空间:文件系统的位置、大小、磁盘和 inode 使用情况
- 数据库:大小、膨胀、禁用触发器、安装的扩展、临时文件、事务 ID 环绕、死锁、冲突
- 角色:用户、群组、会员
- 活跃后端:事务运行时间过长、事务空闲、等待锁
- 表格:真空、分析、行估计、idx 和 seq 扫描、缓存命中率、热更新率、大小、膨胀
- 索引:缓存命中率、扫描、读取/扫描的行数、获取/扫描的行数
- 序列:缓存命中率
- 系统指标:核心、平均负载、内存和磁盘使用情况
- 设置:当前值和默认值不同
- 慢查询:来自 pg_stat_statements(如果可用)
- 锁:已授予和等待的锁,来自 pg_locks
- 作业进度:分析、备份、集群、复制、创建索引和清理作业的进度
- 被阻止的查询:被阻止的查询以及它们正在等待的查询
- 除了从通用 PostgreSQL 服务器收集指标之外,它还可以: PgBouncer: 连接到 PgBouncer 的管理数据库时收集并报告有关PgBouncer实例的信息。 从 v1.11.0 开始也支持Odyssey 。
- 日志文件:处理并从 PostgreSQL 日志文件中提取信息。(在 v1.8.0 中添加)
- AWS RDS、Aurora:从 AWS RDS 数据库收集 RDS 指标和增强监控指标,包括 AWS RDS Aurora(在 v1.9.0 中添加)
- Citus:收集有关Citus 扩展的信息(在 v1.10.0 中添加)
- Azure:从 Azure 数据库为 PostgreSQL(单个服务器、灵活服务器和托管 Citus)收集指标。(在 v1.13.0 中添加)
- Pgpool:从 Pgpool v4.x 实例收集指标和信息(在 v1.15.0 中添加)
pgmetrics是一个独立的、无依赖的工具:不需要安装 PostgreSQL 扩展,并且工具本身也没有包或安装依赖项。
输出格式
- pgmetrics可以以人性化文本格式报告收集到的统计数据 ,也可以将其导出为JSON或CSV格式。JSON 和 CSV 格式的输出旨在用于监控和自动化脚本。 pgmetrics还可以以文本格式重新显示之前保存的 JSON 文件的内容。
PostgreSQL 版本
- pgmetrics可以与 PostgreSQL 版本9.3至16一起使用。它还可以与以下提供的托管 PostgreSQL 实例一起使用:
- 亚马逊 AWS Aurora
- 亚马逊 AWS RDS
- Microsoft Azure Database For PostgreSQL 简单服务器
- Microsoft Azure Database For PostgreSQL 灵活服务器
- 适用于 PostgreSQL 超大规模/Citus 的 Microsoft Azure 数据库
- Google GCP SQL
可用性
pgmetrics用 Go 编写,可作为零依赖静态链接命令行工具下载,适用于许多平台。它是开源的,并根据 Apache License 2.0 获得许可。
二、pgmetrics环境部署
2.1、官网下载地址
wget https://github.com/rapidloop/pgmetrics/releases/download/v1.16.0/pgmetrics_1.16.0_linux_amd64.tar.gz
2.2、解压
tar xvf pgmetrics_1.16.0_linux_amd64.tar.gz
2.3、切换目录
cd pgmetrics_1.16.0_linux_amd64
2.4、查看帮助
./pgmetrics --help
pgmetrics collects PostgreSQL information and metrics.
Usage:
pgmetrics [OPTION]... [DBNAME]
General options:
-t, --timeout=SECS individual query timeout in seconds (default: 5)
--lock-timeout=MILLIS lock timeout in milliseconds (default: 50)
-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", "log", "citus", "indexdefs",
"bloat"
--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 from the databases listed as
command-line args (use with Heroku)
--all-dbs collect info from all user databases
--log-file location of PostgreSQL log file
--log-dir read all the PostgreSQL log files in this directory
--log-span=MINS examine the last MINS minutes of logs (default: 5)
--aws-rds-dbid AWS RDS/Aurora database instance identifier
--az-resource Azure resource ID
--pgpool collect only Pgpool metrics
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: "/var/run/postgresql")
-p, --port=PORT database server port (default: 5432)
-U, --username=USERNAME database user name (default: "postgres")
-w, --no-password never prompt for password
--role=ROLE do SET ROLE before collection
For more information, visit <https://pgmetrics.io>.
三、Pgmetrics使用技巧
3.1、仅收集名称中包含“enmotech”的表:
代码如下:
./pgmetrics --no-password -a 'enmotech' -h 192.168.1.72 postgres
显示结果:
pgmetrics run at: 13 Jul 2024 10:32:13 PM (now)
PostgreSQL Cluster:
Name:
Server Version: 16.3
Server Started: 13 Jul 2024 9:17:58 PM (1 hour ago)
System Identifier: 7390392581339028604
Timeline: 1
Last Checkpoint: 13 Jul 2024 10:27:58 PM (4 minutes ago)
REDO LSN: 0/15000060
Checkpoint LSN: 0/15000098 (56 B since REDO)
Transaction IDs: oldest = 723, next = 763, range = 40
Notification Queue: 0.0% used
Active Backends: 5 (max 100)
Recovery Mode? no
WAL Files:
WAL Archiving? yes
WAL Files: 11
Ready Files: 0
Archive Rate: 0.28 per min
Last Archived: 13 Jul 2024 10:28:15 PM (3 minutes ago)
Last Failure:
Totals: 21 succeeded, 0 failed
Totals Since: 13 Jul 2024 9:17:58 PM (1 hour ago)
+--------------------+----------------+
| Setting | Value |
+--------------------+----------------+
| wal_level | replica |
| archive_timeout | 10 |
| wal_compression | off |
| max_wal_size | 1024 (1.0 GiB) |
| min_wal_size | 80 (80 MiB) |
| checkpoint_timeout | 300 |
| full_page_writes | on |
| wal_keep_size | 0 |
+--------------------+----------------+
BG Writer:
Checkpoint Rate: 0.20 per min
Average Write: 398 KiB per checkpoint
Total Checkpoints: 14 sched (93.3%) + 1 req (6.7%) = 15
Total Write: 9.2 MiB, @ 2.1 KiB per sec
Buffers Allocated: 892 (7.0 MiB)
Buffers Written: 746 chkpt (63.5%) + 0 bgw (0.0%) + 428 be (36.5%)
Clean Scan Stops: 0
BE fsyncs: 0
Counts Since: 13 Jul 2024 9:17:58 PM (1 hour 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 | 300 sec |
| checkpoint_completion_target | 0.9 |
+------------------------------+--------------+
Backends:
Total Backends: 5 (5.0% of max 100)
Problematic: 0 waiting on locks, 4 waiting on other, 0 xact too long, 0 idle in xact
Other Waiting Backends:
+------+----------+----------------------------------------+-----------------+----------+---------------------+-------------------------+
| PID | User | App | Client Addr | Database | Wait | Query Start |
+------+----------+----------------------------------------+-----------------+----------+---------------------+-------------------------+
| 2118 | postgres | psql | | postgres | Client / ClientRead | 13 Jul 2024 10:13:31 PM |
| 2137 | postgres | citus_internal gpid=999999990000001858 | 192.168.1.73/32 | postgres | Client / ClientRead | 13 Jul 2024 10:32:12 PM |
| 2139 | postgres | citus_internal gpid=999999990000001894 | 192.168.1.72/32 | postgres | Client / ClientRead | 13 Jul 2024 10:31:55 PM |
| 2150 | postgres | citus_internal gpid=999999990000002168 | 192.168.1.71/32 | postgres | Client / ClientRead | 13 Jul 2024 10:32:12 PM |
+------+----------+----------------------------------------+-----------------+----------+---------------------+-------------------------+
Locks:
+------------+-------------+-------+
| Lock Type | Not Granted | Total |
+------------+-------------+-------+
| relation | 0 | 4 |
| virtualxid | 0 | 1 |
+------------+-------------+-------+
| | 0 | 5 |
+------------+-------------+-------+
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 | 3 |
| 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 |
+-----------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+
| 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_checkpoint | | | | | | | yes | |
|
| pg_use_reserved_connections | | | | | | | yes | |
|
| pg_read_server_files | | | | | | | yes | |
|
| pg_write_server_files | | | | | | | yes | |
|
| pg_execute_server_program | | | | | | | yes | |
|
| pg_database_owner | | | | | | | yes | |
|
| pg_read_all_data | | | | | | | yes | |
|
| pg_write_all_data | | | | | | | yes | |
|
| pg_create_subscription | | | | | | | yes | |
|
+-----------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+
Tablespaces:
+------------+----------+----------+---------+
| Name | Owner | Location | Size |
+------------+----------+----------+---------+
| pg_default | postgres | | 24 MiB |
| pg_global | postgres | | 564 KiB |
+------------+----------+----------+---------+
Database #1:
Name: postgres
Owner: postgres
Tablespace: pg_default
Connections: 6 (no max limit)
Frozen Xid Age: 40
Transactions: 4219 (99.7%) commits, 13 (0.3%) rollbacks
Cache Hits: 99.5%
Rows Changed: ins 69.0%, upd 13.7%, del 17.3%
Total Temp: 0 B in 0 files
Problems: 0 deadlocks, 0 conflicts
Totals Since:
Size: 9.6 MiB
Sequences:
+----------------------+------------+
| Sequence | Cache Hits |
+----------------------+------------+
| storageid_seq | |
| enmotech_dist_id_seq | 100.0% |
| enmotech_col_id_seq | 100.0% |
| enmotech_ref_id_seq | 100.0% |
+----------------------+------------+
Installed Extensions:
+----------------+---------+------------------------------+
| Name | Version | Comment |
+----------------+---------+------------------------------+
| citus | 12.2-1 | Citus distributed database |
| citus_columnar | 12.2-1 | Citus Columnar extension |
| plpgsql | 1.0 | PL/pgSQL procedural language |
+----------------+---------+------------------------------+
Table #1 in "postgres":
Name: postgres.public.enmotech_dist
Columns: 3
Manual Vacuums: never
Manual Analyze: never
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 "postgres":
Name: postgres.public.enmotech_col
Columns: 3
Manual Vacuums: never
Manual Analyze: never
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 #3 in "postgres":
Name: postgres.public.enmotech_ref
Columns: 3
Manual Vacuums: never
Manual Analyze: never
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
3.2、仅收集名称中包含“报告”或“审计”的表:
代码如下:
./pgmetrics --no-password -a '(report|audit)' -h 192.168.1.72 postgres
显示结果:
pgmetrics run at: 14 Jul 2024 4:17:26 PM (now)
PostgreSQL Cluster:
Name:
Server Version: 16.3
Server Started: 14 Jul 2024 4:14:10 PM (3 minutes ago)
System Identifier: 7390392581339028604
Timeline: 1
Last Checkpoint: 14 Jul 2024 4:14:09 PM (3 minutes ago)
REDO LSN: 0/1F000060
Checkpoint LSN: 0/1F000060 (0 B since REDO)
Transaction IDs: oldest = 723, next = 768, range = 45
Notification Queue: 0.0% used
Active Backends: 3 (max 100)
Recovery Mode? no
WAL Files:
WAL Archiving? yes
WAL Files: 8
Ready Files: 0
Archive Rate: 0.00 per min
Last Archived:
Last Failure:
Totals: 0 succeeded, 0 failed
Totals Since: 14 Jul 2024 4:14:10 PM (3 minutes ago)
+--------------------+----------------+
| Setting | Value |
+--------------------+----------------+
| wal_level | replica |
| archive_timeout | 10 |
| wal_compression | off |
| max_wal_size | 1024 (1.0 GiB) |
| min_wal_size | 80 (80 MiB) |
| checkpoint_timeout | 300 |
| full_page_writes | on |
| wal_keep_size | 0 |
+--------------------+----------------+
BG Writer:
Checkpoint Rate: 0.30 per min
Average Write: 0 B per checkpoint
Total Checkpoints: 0 sched (0.0%) + 1 req (100.0%) = 1
Total Write: 0 B, @ 0 B per sec
Buffers Allocated: 560 (4.4 MiB)
Buffers Written: 0 chkpt (0.0%) + 0 bgw (0.0%) + 0 be (0.0%)
Clean Scan Stops: 0
BE fsyncs: 0
Counts Since: 14 Jul 2024 4:14:09 PM (3 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 | 300 sec |
| checkpoint_completion_target | 0.9 |
+------------------------------+--------------+
Backends:
Total Backends: 3 (3.0% of max 100)
Problematic: 0 waiting on locks, 2 waiting on other, 0 xact too long, 0 idle in xact
Other Waiting Backends:
+------+----------+---------------------------------+-----------------+----------+---------------------+------------------------+
| PID | User | App | Client Addr | Database | Wait | Query Start |
+------+----------+---------------------------------+-----------------+----------+---------------------+------------------------+
| 1658 | postgres | citus_internal gpid=60000001657 | 192.168.1.72/32 | postgres | Client / ClientRead | 14 Jul 2024 4:17:16 PM |
| 1659 | postgres | citus_internal gpid=70000001651 | 192.168.1.73/32 | postgres | Client / ClientRead | 14 Jul 2024 4:17:26 PM |
+------+----------+---------------------------------+-----------------+----------+---------------------+------------------------+
Locks:
+------------+-------------+-------+
| Lock Type | Not Granted | Total |
+------------+-------------+-------+
| relation | 0 | 4 |
| virtualxid | 0 | 1 |
+------------+-------------+-------+
| | 0 | 5 |
+------------+-------------+-------+
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 | 3 |
| 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 |
+-----------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+
| 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_checkpoint | | | | | | | yes | | |
| pg_use_reserved_connections | | | | | | | yes | | |
| pg_read_server_files | | | | | | | yes | | |
| pg_write_server_files | | | | | | | yes | | |
| pg_execute_server_program | | | | | | | yes | | |
| pg_database_owner | | | | | | | yes | | |
| pg_read_all_data | | | | | | | yes | | |
| pg_write_all_data | | | | | | | yes | | |
| pg_create_subscription | | | | | | | yes | | |
+-----------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+
Tablespaces:
+------------+----------+----------+---------+
| Name | Owner | Location | Size |
+------------+----------+----------+---------+
| pg_default | postgres | | 24 MiB |
| pg_global | postgres | | 564 KiB |
+------------+----------+----------+---------+
Database #1:
Name: postgres
Owner: postgres
Tablespace: pg_default
Connections: 4 (no max limit)
Frozen Xid Age: 45
Transactions: 108 (97.3%) commits, 3 (2.7%) rollbacks
Cache Hits: 98.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: 9.6 MiB
Sequences:
+----------------------+------------+
| Sequence | Cache Hits |
+----------------------+------------+
| storageid_seq | |
| enmotech_dist_id_seq | |
| enmotech_col_id_seq | |
| enmotech_ref_id_seq | |
+----------------------+------------+
Installed Extensions:
+--------------------+---------+------------------------------------------------------------------------+
| Name | Version | Comment |
+--------------------+---------+------------------------------------------------------------------------+
| citus | 12.2-1 | Citus distributed database |
| citus_columnar | 12.2-1 | Citus Columnar extension |
| pg_stat_statements | 1.10 | track planning and execution statistics of all SQL statements executed |
| plpgsql | 1.0 | PL/pgSQL procedural language |
+--------------------+---------+------------------------------------------------------------------------+
Slow Queries:
+-------+----------+------------+-----------+----------------------------------------------------+
| Calls | Avg Time | Total Time | Rows/Call | Query |
+-------+----------+------------+-----------+----------------------------------------------------+
| 1 | 67ms | 67ms | 10 | SELECT current_database() AS db, schemaname, tab |
| 1 | 59ms | 59ms | 3 | SELECT logicalrelid::oid, citus_table_size(logical |
| 2 | 8ms | 16ms | 7 | SELECT S.relid, S.schemaname, S.relname, current_d |
| 2 | 7ms | 14ms | 0 | SELECT funcid, schemaname, funcname, current_datab |
| 2 | 4ms | 9ms | 438 | SELECT name, setting, COALESCE(boot_val,$1), sourc |
| 2 | 1ms | 3ms | 5 | SELECT indexrelid, pg_get_indexdef(indexrelid) FRO |
| 4 | 0s | 3ms | 1 | SELECT pg_tablespace_size($1) |
| 1 | 3ms | 3ms | 0 | SELECT queryid, userid, dbid, query, executor, COA |
| 2 | 1ms | 3ms | 15 | SELECT R.oid, R.rolname, R.rolsuper, R.rolinherit, |
| 2 | 1ms | 2ms | 5 | SELECT S.relid, S.indexrelid, S.schemaname, S.reln |
| 2 | 0s | 1ms | 1 | SELECT (SELECT $1) + (SELECT SUM(pg_table_size(rel |
| 2 | 0s | 1ms | 1 | SELECT pg_database_size($1) |
| 36 | 0s | 1ms | 0 | SELECT waiting_pid, waiting_node_id, waiting_trans |
| 2 | 0s | 1ms | 4 | SELECT relid, schemaname, relname, current_databas |
| 2 | 0s | 1ms | 1 | SELECT EXTRACT($1 FROM pg_postmaster_start_time()) |
| 1 | 1ms | 1ms | 2 | SELECT nodeid, groupid, nodename, nodeport, COALES |
| 2 | 0s | 0s | 7 | SELECT COALESCE(D.datname, $1), L.locktype, L.mode |
| 1 | 0s | 0s | 44 | SELECT userid, dbid, queryid, LEFT(COALESCE(query, |
| 2 | 0s | 0s | 2 | SELECT COALESCE(datname, $2), COALESCE(usename, $3 |
| 2 | 0s | 0s | 0 | SELECT COALESCE(usename, $1), application_name, |
| 2 | 0s | 0s | 4 | SELECT name, current_database(), COALESCE(default_ |
| 2 | 0s | 0s | 0 | WITH P AS (SELECT DISTINCT pid FROM pg_locks WHERE |
| 2 | 0s | 0s | 8 | SELECT name FROM pg_ls_waldir() WHERE name ~ $1 |
| 2 | 0s | 0s | 0 | SELECT pid, datname, relid::int, COALESCE(command, |
| 2 | 0s | 0s | 0 | SELECT pid, datname, relid::int, index_relid::int, |
| 8 | 0s | 0s | 0 | SELECT gid FROM pg_prepared_xacts WHERE gid LIKE $ |
| 2 | 0s | 0s | 1 | SELECT checkpoint_lsn, redo_lsn, timeline_id, n |
| 2 | 0s | 0s | 8 | SELECT backend_type, count(*) FROM pg_stat_activit |
| 1 | 0s | 0s | 1 | SELECT (SELECT $1) + (SELECT SUM(pg_table_size(rel |
| 2 | 0s | 0s | 1 | SELECT current_user |
| 2 | 0s | 0s | 1 | SELECT D.oid, D.datname, D.datdba, D.dattablespace |
| 2 | 0s | 0s | 0 | SELECT pid, datname, COALESCE(relid, $1), COALESCE |
| 2 | 0s | 0s | 1 | SELECT system_identifier FROM pg_control_system() |
| 2 | 0s | 0s | 0 | SELECT pid, datname, relid::int, COALESCE(command, |
| 2 | 0s | 0s | 0 | SELECT slot_name, COALESCE(plugin, $1), slot_type, |
| 2 | 0s | 0s | 1 | SELECT COUNT(*) FROM pg_ls_archive_statusdir() WHE |
| 2 | 0s | 0s | 0 | SELECT pid, datname, COALESCE(relid::int, $1::int) |
| 2 | 0s | 0s | 1 | SELECT pg_current_wal_flush_lsn(), pg_current_ |
| 2 | 0s | 0s | 1 | SELECT archived_count, COALESCE(last_archived_ |
| 2 | 0s | 0s | 1 | SELECT COALESCE(inet_client_addr() = inet_server_a |
| 2 | 0s | 0s | 2 | SELECT oid, spcname, pg_get_userbyid(spcowner), |
| 2 | 0s | 0s | 1 | SELECT wal_records, wal_fpi, wal_bytes, wal_buffer |
| 2 | 0s | 0s | 0 | SELECT T.oid, T.tgrelid, T.tgname, P.proname F |
| 2 | 0s | 0s | 0 | SELECT pid, COALESCE(phase, $1), COALESCE(bac |
| 2 | 0s | 0s | 1 | SELECT pg_is_in_recovery(), COALESCE(pg_last_wa |
| 2 | 0s | 0s | 1 | SELECT checkpoints_timed, checkpoints_req, checkpo |
| 2 | 0s | 0s | 0 | SELECT c.oid, inhparent::regclass, COALESCE(pg_get |
| 1 | 0s | 0s | 0 | WITH sc AS (SELECT srsubid, COUNT(*) AS c FROM |
| 2 | 0s | 0s | 1 | SELECT current_database() |
| 1 | 0s | 0s | 0 | WITH pc AS (SELECT pubname, COUNT(*) AS c FROM pg_ |
| 2 | 0s | 0s | 1 | SELECT pg_notification_queue_usage() |
| 2 | 0s | 0s | 0 | SELECT status, receive_start_lsn, receive_start_tl |
| 2 | 0s | 0s | 0 | SELECT c.oid, i.inhparent::regclass FROM pg_cla |
| 1 | 0s | 0s | 1 | SELECT citus_version() |
+-------+----------+------------+-----------+----------------------------------------------------+
3.3、收集除名称中包含“enmotech”的表之外的所有表:
代码如下:
./pgmetrics --no-password -A enmotech -h 192.168.1.72 postgres
显示结果:
pgmetrics run at: 13 Jul 2024 10:51:09 PM (now)
PostgreSQL Cluster:
Name:
Server Version: 16.3
Server Started: 13 Jul 2024 9:17:58 PM (1 hour ago)
System Identifier: 7390392581339028604
Timeline: 1
Last Checkpoint: 13 Jul 2024 10:27:58 PM (23 minutes ago)
REDO LSN: 0/15000060
Checkpoint LSN: 0/15000098 (56 B since REDO)
Transaction IDs: oldest = 723, next = 763, range = 40
Notification Queue: 0.0% used
Active Backends: 5 (max 100)
Recovery Mode? no
WAL Files:
WAL Archiving? yes
WAL Files: 11
Ready Files: 0
Archive Rate: 0.23 per min
Last Archived: 13 Jul 2024 10:28:15 PM (22 minutes ago)
Last Failure:
Totals: 21 succeeded, 0 failed
Totals Since: 13 Jul 2024 9:17:58 PM (1 hour ago)
+--------------------+----------------+
| Setting | Value |
+--------------------+----------------+
| wal_level | replica |
| archive_timeout | 10 |
| wal_compression | off |
| max_wal_size | 1024 (1.0 GiB) |
| min_wal_size | 80 (80 MiB) |
| checkpoint_timeout | 300 |
| full_page_writes | on |
| wal_keep_size | 0 |
+--------------------+----------------+
BG Writer:
Checkpoint Rate: 0.20 per min
Average Write: 314 KiB per checkpoint
Total Checkpoints: 18 sched (94.7%) + 1 req (5.3%) = 19
Total Write: 9.2 MiB, @ 1.7 KiB per sec
Buffers Allocated: 895 (7.0 MiB)
Buffers Written: 746 chkpt (63.5%) + 0 bgw (0.0%) + 428 be (36.5%)
Clean Scan Stops: 0
BE fsyncs: 0
Counts Since: 13 Jul 2024 9:17:58 PM (1 hour 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 | 300 sec |
| checkpoint_completion_target | 0.9 |
+------------------------------+--------------+
Backends:
Total Backends: 5 (5.0% of max 100)
Problematic: 0 waiting on locks, 4 waiting on other, 0 xact too long, 0 idle in xact
Other Waiting Backends:
+------+----------+----------------------------------------+-----------------+----------+---------------------+-------------------------+
| PID | User | App | Client Addr | Database | Wait | Query Start |
+------+----------+----------------------------------------+-----------------+----------+---------------------+-------------------------+
| 2118 | postgres | psql | | postgres | Client / ClientRead | 13 Jul 2024 10:13:31 PM |
| 2173 | postgres | citus_internal gpid=999999990000001858 | 192.168.1.73/32 | postgres | Client / ClientRead | 13 Jul 2024 10:51:08 PM |
| 2176 | postgres | citus_internal gpid=999999990000001894 | 192.168.1.72/32 | postgres | Client / ClientRead | 13 Jul 2024 10:51:03 PM |
| 2184 | postgres | citus_internal gpid=999999990000002168 | 192.168.1.71/32 | postgres | Client / ClientRead | 13 Jul 2024 10:51:08 PM |
+------+----------+----------------------------------------+-----------------+----------+---------------------+-------------------------+
Locks:
+------------+-------------+-------+
| Lock Type | Not Granted | Total |
+------------+-------------+-------+
| relation | 0 | 4 |
| virtualxid | 0 | 1 |
+------------+-------------+-------+
| | 0 | 5 |
+------------+-------------+-------+
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 | 3 |
| 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 |
+-----------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+
| 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_checkpoint | | | | | | | yes | |
|
| pg_use_reserved_connections | | | | | | | yes | |
|
| pg_read_server_files | | | | | | | yes | |
|
| pg_write_server_files | | | | | | | yes | |
|
| pg_execute_server_program | | | | | | | yes | |
|
| pg_database_owner | | | | | | | yes | |
|
| pg_read_all_data | | | | | | | yes | |
|
| pg_write_all_data | | | | | | | yes | |
|
| pg_create_subscription | | | | | | | yes | |
|
+-----------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+
Tablespaces:
+------------+----------+----------+---------+
| Name | Owner | Location | Size |
+------------+----------+----------+---------+
| pg_default | postgres | | 24 MiB |
| pg_global | postgres | | 564 KiB |
+------------+----------+----------+---------+
Database #1:
Name: postgres
Owner: postgres
Tablespace: pg_default
Connections: 6 (no max limit)
Frozen Xid Age: 40
Transactions: 5545 (99.7%) commits, 17 (0.3%) rollbacks
Cache Hits: 99.6%
Rows Changed: ins 69.0%, upd 13.7%, del 17.3%
Total Temp: 0 B in 0 files
Problems: 0 deadlocks, 0 conflicts
Totals Since:
Size: 9.6 MiB
Sequences:
+----------------------+------------+
| Sequence | Cache Hits |
+----------------------+------------+
| storageid_seq | |
| enmotech_dist_id_seq | 100.0% |
| enmotech_col_id_seq | 100.0% |
| enmotech_ref_id_seq | 100.0% |
+----------------------+------------+
Installed Extensions:
+----------------+---------+------------------------------+
| Name | Version | Comment |
+----------------+---------+------------------------------+
| citus | 12.2-1 | Citus distributed database |
| citus_columnar | 12.2-1 | Citus Columnar extension |
| plpgsql | 1.0 | PL/pgSQL procedural language |
+----------------+---------+------------------------------+
Table #1 in "postgres":
Name: postgres.columnar_internal.options
Columns: 5
Manual Vacuums: never
Manual Analyze: never
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: 1, 0.0 rows/scan
Idx Scans: 0, 0.0 rows/scan
Cache Hits: 0.0% (idx=0.0%)
Size: 0 B
ACL:
+----------+---------------------------------------------------------------+------------+
| Role | Privileges | Granted By |
+----------+---------------------------------------------------------------+------------+
| postgres | INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER | postgres |
+----------+---------------------------------------------------------------+------------+
+--------------+-------+---------+-------+------------+-------+----------------+-------------------+
| Index | Type | Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan |
+--------------+-------+---------+-------+------------+-------+----------------+-------------------+
| options_pkey | btree | 8.0 KiB | | | 0 | 0.0 | 0.0 |
+--------------+-------+---------+-------+------------+-------+----------------+-------------------+
Table #2 in "postgres":
Name: postgres.columnar_internal.stripe
Columns: 9
Manual Vacuums: never
Manual Analyze: never
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: 4, 0.0 rows/scan
Idx Scans: 0, 0.0 rows/scan
Cache Hits: 0.0% (idx=75.0%)
Size: 0 B
ACL:
+----------+---------------------------------------------------------------+------------+
| Role | Privileges | Granted By |
+----------+---------------------------------------------------------------+------------+
| postgres | INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER | postgres |
+----------+---------------------------------------------------------------+------------+
+-----------------------------+-------+---------+-------+------------+-------+----------------+-------------------+
| Index | Type | Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan |
+-----------------------------+-------+---------+-------+------------+-------+----------------+-------------------+
| stripe_pkey | btree | 8.0 KiB | | 75.0% | 0 | 0.0 | 0.0 |
| stripe_first_row_number_idx | btree | 8.0 KiB | | 75.0% | 0 | 0.0 | 0.0 |
+-----------------------------+-------+---------+-------+------------+-------+----------------+-------------------+
Table #3 in "postgres":
Name: postgres.columnar_internal.chunk_group
Columns: 4
Manual Vacuums: never
Manual Analyze: never
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: 1, 0.0 rows/scan
Idx Scans: 0, 0.0 rows/scan
Cache Hits: 0.0% (idx=0.0%)
Size: 0 B
ACL:
+----------+---------------------------------------------------------------+------------+
| Role | Privileges | Granted By |
+----------+---------------------------------------------------------------+------------+
| postgres | INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER | postgres |
+----------+---------------------------------------------------------------+------------+
+------------------+-------+---------+-------+------------+-------+----------------+-------------------+
| Index | Type | Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan |
+------------------+-------+---------+-------+------------+-------+----------------+-------------------+
| chunk_group_pkey | btree | 8.0 KiB | | | 0 | 0.0 | 0.0 |
+------------------+-------+---------+-------+------------+-------+----------------+-------------------+
Table #4 in "postgres":
Name: postgres.columnar_internal.chunk
Columns: 14
Manual Vacuums: never
Manual Analyze: never
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: 1, 0.0 rows/scan
Idx Scans: 0, 0.0 rows/scan
Cache Hits: 0.0% (idx=0.0%)
Size: 8.0 KiB
ACL:
+----------+---------------------------------------------------------------+------------+
| Role | Privileges | Granted By |
+----------+---------------------------------------------------------------+------------+
| postgres | INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER | postgres |
+----------+---------------------------------------------------------------+------------+
+------------+-------+---------+-------+------------+-------+----------------+-------------------+
| Index | Type | Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan |
+------------+-------+---------+-------+------------+-------+----------------+-------------------+
| chunk_pkey | btree | 8.0 KiB | | | 0 | 0.0 | 0.0 |
+------------+-------+---------+-------+------------+-------+----------------+-------------------+
3.4、仅收集模式“public”和“schema_server”中的所有内容:
代码如下:
./pgmetrics --no-password -c 'public|schema_server' -h 192.168.1.72 postgres
显示结果:
pgmetrics run at: 13 Jul 2024 11:07:07 PM (now)
PostgreSQL Cluster:
Name:
Server Version: 16.3
Server Started: 13 Jul 2024 9:17:58 PM (1 hour ago)
System Identifier: 7390392581339028604
Timeline: 1
Last Checkpoint: 13 Jul 2024 10:27:58 PM (39 minutes ago)
REDO LSN: 0/15000060
Checkpoint LSN: 0/15000098 (56 B since REDO)
Transaction IDs: oldest = 723, next = 763, range = 40
Notification Queue: 0.0% used
Active Backends: 5 (max 100)
Recovery Mode? no
WAL Files:
WAL Archiving? yes
WAL Files: 11
Ready Files: 0
Archive Rate: 0.21 per min
Last Archived: 13 Jul 2024 11:05:19 PM (1 minute ago)
Last Failure:
Totals: 23 succeeded, 0 failed
Totals Since: 13 Jul 2024 9:17:58 PM (1 hour ago)
+--------------------+----------------+
| Setting | Value |
+--------------------+----------------+
| wal_level | replica |
| archive_timeout | 10 |
| wal_compression | off |
| max_wal_size | 1024 (1.0 GiB) |
| min_wal_size | 80 (80 MiB) |
| checkpoint_timeout | 300 |
| full_page_writes | on |
| wal_keep_size | 0 |
+--------------------+----------------+
BG Writer:
Checkpoint Rate: 0.20 per min
Average Write: 271 KiB per checkpoint
Total Checkpoints: 21 sched (95.5%) + 1 req (4.5%) = 22
Total Write: 9.2 MiB, @ 1.4 KiB per sec
Buffers Allocated: 902 (7.0 MiB)
Buffers Written: 746 chkpt (63.5%) + 0 bgw (0.0%) + 428 be (36.5%)
Clean Scan Stops: 0
BE fsyncs: 0
Counts Since: 13 Jul 2024 9:17:58 PM (1 hour 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 | 300 sec |
| checkpoint_completion_target | 0.9 |
+------------------------------+--------------+
Backends:
Total Backends: 5 (5.0% of max 100)
Problematic: 0 waiting on locks, 4 waiting on other, 0 xact too long, 0 idle in xact
Other Waiting Backends:
+------+----------+----------------------------------------+-----------------+----------+---------------------+-------------------------+
| PID | User | App | Client Addr | Database | Wait | Query Start |
+------+----------+----------------------------------------+-----------------+----------+---------------------+-------------------------+
| 2201 | postgres | citus_internal gpid=999999990000002168 | 192.168.1.71/32 | postgres | Client / ClientRead | 13 Jul 2024 11:07:07 PM |
| 2216 | postgres | citus_internal gpid=999999990000001858 | 192.168.1.73/32 | postgres | Client / ClientRead | 13 Jul 2024 11:07:06 PM |
| 2220 | postgres | citus_internal gpid=999999990000001894 | 192.168.1.72/32 | postgres | Client / ClientRead | 13 Jul 2024 11:06:09 PM |
| 2228 | postgres | citus_internal gpid=999999990000002355 | 192.168.1.71/32 | postgres | Client / ClientRead | 13 Jul 2024 11:05:11 PM |
+------+----------+----------------------------------------+-----------------+----------+---------------------+-------------------------+
Locks:
+------------+-------------+-------+
| Lock Type | Not Granted | Total |
+------------+-------------+-------+
| relation | 0 | 4 |
| virtualxid | 0 | 1 |
+------------+-------------+-------+
| | 0 | 5 |
+------------+-------------+-------+
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 | 3 |
| 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 |
+-----------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+
| 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_checkpoint | | | | | | | yes | |
|
| pg_use_reserved_connections | | | | | | | yes | |
|
| pg_read_server_files | | | | | | | yes | |
|
| pg_write_server_files | | | | | | | yes | |
|
| pg_execute_server_program | | | | | | | yes | |
|
| pg_database_owner | | | | | | | yes | |
|
| pg_read_all_data | | | | | | | yes | |
|
| pg_write_all_data | | | | | | | yes | |
|
| pg_create_subscription | | | | | | | yes | |
|
+-----------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+
Tablespaces:
+------------+----------+----------+---------+
| Name | Owner | Location | Size |
+------------+----------+----------+---------+
| pg_default | postgres | | 24 MiB |
| pg_global | postgres | | 564 KiB |
+------------+----------+----------+---------+
Database #1:
Name: postgres
Owner: postgres
Tablespace: pg_default
Connections: 6 (no max limit)
Frozen Xid Age: 42
Transactions: 6672 (99.7%) commits, 22 (0.3%) rollbacks
Cache Hits: 99.6%
Rows Changed: ins 69.0%, upd 13.7%, del 17.3%
Total Temp: 0 B in 0 files
Problems: 0 deadlocks, 0 conflicts
Totals Since:
Size: 9.6 MiB
Sequences:
+----------------------+------------+
| Sequence | Cache Hits |
+----------------------+------------+
| enmotech_dist_id_seq | 100.0% |
| enmotech_col_id_seq | 100.0% |
| enmotech_ref_id_seq | 100.0% |
+----------------------+------------+
Installed Extensions:
+----------------+---------+------------------------------+
| Name | Version | Comment |
+----------------+---------+------------------------------+
| citus | 12.2-1 | Citus distributed database |
| citus_columnar | 12.2-1 | Citus Columnar extension |
| plpgsql | 1.0 | PL/pgSQL procedural language |
+----------------+---------+------------------------------+
Table #1 in "postgres":
Name: postgres.public.enmotech_dist
Columns: 3
Manual Vacuums: never
Manual Analyze: never
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 "postgres":
Name: postgres.public.enmotech_col
Columns: 3
Manual Vacuums: never
Manual Analyze: never
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 #3 in "postgres":
Name: postgres.public.enmotech_ref
Columns: 3
Manual Vacuums: never
Manual Analyze: never
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
3.5、收集模式“public”中名为“enmotech”的表:
代码如下:
./pgmetrics --no-password -c public -a 'enmotech' -h 192.168.1.72 postgres
显示结果:
pgmetrics run at: 13 Jul 2024 11:11:58 PM (now)
PostgreSQL Cluster:
Name:
Server Version: 16.3
Server Started: 13 Jul 2024 9:17:58 PM (1 hour ago)
System Identifier: 7390392581339028604
Timeline: 1
Last Checkpoint: 13 Jul 2024 11:07:58 PM (4 minutes ago)
REDO LSN: 0/18000028
Checkpoint LSN: 0/18000060 (56 B since REDO)
Transaction IDs: oldest = 723, next = 765, range = 42
Notification Queue: 0.0% used
Active Backends: 5 (max 100)
Recovery Mode? no
WAL Files:
WAL Archiving? yes
WAL Files: 11
Ready Files: 0
Archive Rate: 0.21 per min
Last Archived: 13 Jul 2024 11:08:00 PM (3 minutes ago)
Last Failure:
Totals: 24 succeeded, 0 failed
Totals Since: 13 Jul 2024 9:17:58 PM (1 hour ago)
+--------------------+----------------+
| Setting | Value |
+--------------------+----------------+
| wal_level | replica |
| archive_timeout | 10 |
| wal_compression | off |
| max_wal_size | 1024 (1.0 GiB) |
| min_wal_size | 80 (80 MiB) |
| checkpoint_timeout | 300 |
| full_page_writes | on |
| wal_keep_size | 0 |
+--------------------+----------------+
BG Writer:
Checkpoint Rate: 0.20 per min
Average Write: 261 KiB per checkpoint
Total Checkpoints: 22 sched (95.7%) + 1 req (4.3%) = 23
Total Write: 9.2 MiB, @ 1.4 KiB per sec
Buffers Allocated: 902 (7.0 MiB)
Buffers Written: 751 chkpt (63.7%) + 0 bgw (0.0%) + 428 be (36.3%)
Clean Scan Stops: 0
BE fsyncs: 0
Counts Since: 13 Jul 2024 9:17:58 PM (1 hour 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 | 300 sec |
| checkpoint_completion_target | 0.9 |
+------------------------------+--------------+
Backends:
Total Backends: 5 (5.0% of max 100)
Problematic: 0 waiting on locks, 4 waiting on other, 0 xact too long, 0 idle in xact
Other Waiting Backends:
+------+----------+----------------------------------------+-----------------+----------+---------------------+-------------------------+
| PID | User | App | Client Addr | Database | Wait | Query Start |
+------+----------+----------------------------------------+-----------------+----------+---------------------+-------------------------+
| 2216 | postgres | citus_internal gpid=999999990000001858 | 192.168.1.73/32 | postgres | Client / ClientRead | 13 Jul 2024 11:11:56 PM |
| 2220 | postgres | citus_internal gpid=999999990000001894 | 192.168.1.72/32 | postgres | Client / ClientRead | 13 Jul 2024 11:11:11 PM |
| 2228 | postgres | citus_internal gpid=999999990000002355 | 192.168.1.71/32 | postgres | Client / ClientRead | 13 Jul 2024 11:05:11 PM |
| 2237 | postgres | citus_internal gpid=999999990000002168 | 192.168.1.71/32 | postgres | Client / ClientRead | 13 Jul 2024 11:11:57 PM |
+------+----------+----------------------------------------+-----------------+----------+---------------------+-------------------------+
Locks:
+------------+-------------+-------+
| Lock Type | Not Granted | Total |
+------------+-------------+-------+
| relation | 0 | 4 |
| virtualxid | 0 | 1 |
+------------+-------------+-------+
| | 0 | 5 |
+------------+-------------+-------+
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 | 3 |
| 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 |
+-----------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+
| 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_checkpoint | | | | | | | yes | |
|
| pg_use_reserved_connections | | | | | | | yes | |
|
| pg_read_server_files | | | | | | | yes | |
|
| pg_write_server_files | | | | | | | yes | |
|
| pg_execute_server_program | | | | | | | yes | |
|
| pg_database_owner | | | | | | | yes | |
|
| pg_read_all_data | | | | | | | yes | |
|
| pg_write_all_data | | | | | | | yes | |
|
| pg_create_subscription | | | | | | | yes | |
|
+-----------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+
Tablespaces:
+------------+----------+----------+---------+
| Name | Owner | Location | Size |
+------------+----------+----------+---------+
| pg_default | postgres | | 24 MiB |
| pg_global | postgres | | 564 KiB |
+------------+----------+----------+---------+
Installed Extensions:
+----------------+---------+------------------------------+
| Name | Version | Comment |
+----------------+---------+------------------------------+
| citus | 12.2-1 | Citus distributed database |
| citus_columnar | 12.2-1 | Citus Columnar extension |
| plpgsql | 1.0 | PL/pgSQL procedural language |
+----------------+---------+------------------------------+
Table #1 in "postgres":
Name: postgres.public.enmotech_dist
Columns: 3
Manual Vacuums: never
Manual Analyze: never
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 "postgres":
Name: postgres.public.enmotech_col
Columns: 3
Manual Vacuums: never
Manual Analyze: never
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 #3 in "postgres":
Name: postgres.public.enmotech_ref
Columns: 3
Manual Vacuums: never
Manual Analyze: never
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
3.6、要跳过索引和序列的收集
代码如下:
[postgres@Node1 pgmetrics_1.16.0_linux_amd64]$ ./pgmetrics --no-password --omit=indexes,sequences -h 192.168.1.72 postgres
显示结果:
pgmetrics run at: 13 Jul 2024 10:25:57 PM (now)
PostgreSQL Cluster:
Name:
Server Version: 16.3
Server Started: 13 Jul 2024 9:17:58 PM (1 hour ago)
System Identifier: 7390392581339028604
Timeline: 1
Last Checkpoint: 13 Jul 2024 10:17:58 PM (7 minutes ago)
REDO LSN: 0/13000060
Checkpoint LSN: 0/13000098 (56 B since REDO)
Transaction IDs: oldest = 723, next = 763, range = 40
Notification Queue: 0.0% used
Active Backends: 5 (max 100)
Recovery Mode? no
WAL Files:
WAL Archiving? yes
WAL Files: 11
Ready Files: 0
Archive Rate: 0.28 per min
Last Archived: 13 Jul 2024 10:18:05 PM (7 minutes ago)
Last Failure:
Totals: 19 succeeded, 0 failed
Totals Since: 13 Jul 2024 9:17:58 PM (1 hour ago)
+--------------------+----------------+
| Setting | Value |
+--------------------+----------------+
| wal_level | replica |
| archive_timeout | 10 |
| wal_compression | off |
| max_wal_size | 1024 (1.0 GiB) |
| min_wal_size | 80 (80 MiB) |
| checkpoint_timeout | 300 |
| full_page_writes | on |
| wal_keep_size | 0 |
+--------------------+----------------+
BG Writer:
Checkpoint Rate: 0.21 per min
Average Write: 393 KiB per checkpoint
Total Checkpoints: 13 sched (92.9%) + 1 req (7.1%) = 14
Total Write: 8.7 MiB, @ 2.2 KiB per sec
Buffers Allocated: 819 (6.4 MiB)
Buffers Written: 687 chkpt (61.6%) + 0 bgw (0.0%) + 428 be (38.4%)
Clean Scan Stops: 0
BE fsyncs: 0
Counts Since: 13 Jul 2024 9:17:58 PM (1 hour ago)
+------------------------------+--------------+
| Setting | Value |
+------------------------------+--------------+
3.7、 更多信息
生成JSON格式文件;
生成csv格式文件。
更多格式。。。
四、总结
综上所述, pgmetrics 是一个强大而灵活的 PostgreSQL 数据库监控工具,它不仅能够提供实时的性能监控,还能协助进行故障排除和自动化操作。它的安装简便,功能丰富,并且易于与其他监控系统集成,是 PostgreSQL 数据库管理的得力助手。
便,功能丰富,并且易于与其他监控系统集成,是 PostgreSQL 数据库管理的得力助手。
更多资料,请关注博主其他平台:
墨天轮主页:
https://www.modb.pro/topic/659255
链接二维码如下:
PGFans社区主页