本篇内容主要讲解“PostgreSQL中pgmetrics有什么作用”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“PostgreSQL中pgmetrics有什么作用”吧!
pgmetrics 简介
pgmetrics通过连接到数据库，获取数据库当前状态（大多数指标通过STAT系统动态视图获取）。
如果连接的是本地数据库，则同时会采集服务器的状态信息。
获取指标后，可以存储为JSON或TEXT格式。
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) Output options: -f, --format=FORMAT output format; "human", or "json" (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: "/data01/pg11/pg_root1921") -p, --port=PORT database server port (default: 1921) -U, --username=USERNAME database user name (default: "postgres") --no-password never prompt for password For more information, visit <https://pgmetrics.io>.
pgmetrics 采集维度
pgmetrics通过数据库用户连接到数据库进行采集。
1、集群
2、数据库
3、操作系统
采集代码：
https://github.com/rapidloop/pgmetrics/blob/master/collector/collect.go
当采集数据的角色不为超级用户时，可能有些指标无法被正确采集（例如权限问题），但是此时并不会报错，只是输出可以正常被采集的指标并记录下来。
pgmetrics 采集调度
可以使用crontab，定期调用。
pgmetrics 例子
建议
1、设置语句超时时间，为业务可接受时间。（因为每个指标都要调用对应的SQL来进行采集，万一因为锁堵塞导致采集不及时，可以避免长时间等待）。
例子
1、超时时间为3秒，不输入密码，不分页，输出JSON格式，输出到文件，采集实例信息、系统信息、数据库postgres与newdb信息。
pgmetrics -t 3 --no-password --no-pager -f json -o ./log_`date +%s` postgres newdb
ll log* -rw-r--r-- 1 postgres postgres 89K Oct 1 23:14 log_1538406857
2、同上，只是输出的为TEXT格式。
pgmetrics -t 3 --no-password --no-pager -f human postgres newdb
3、使用已保留的JSON文件，生成text报告.
pgmetrics -i ./log_1538406857 --no-pager|less
TEXT报告内容示例
pgmetrics -t 3 --no-password --no-pager -f human postgres newdb
pgmetrics run at: 1 Oct 2018 11:14:17 PM (3 minutes ago) PostgreSQL Cluster: Name: Server Version: 11beta3 Server Started: 22 Sep 2018 11:31:32 PM (1 week ago) System Identifier: 6593269818598452546 Timeline: 1 Last Checkpoint: 1 Oct 2018 10:55:00 PM (22 minutes ago) REDO LSN: 29EE/89C3EA08 Checkpoint LSN: 29F4/B02A04F0 (25 GiB since REDO) Transaction IDs: 4030798045 to 633147358 (diff = -3397650687) Notification Queue: 0.0% used Active Backends: 35 (max 2000) Recovery Mode? no System Information: Hostname: pg11-test CPU Cores: 64 x Intel(R) Xeon(R) Platinum 8163 CPU @ 2.50GHz Load Average: 35.17 Memory: used=317 GiB, free=5.4 GiB, buff=377 MiB, cache=181 GiB Swap: used=0 B, free=0 B +---------------------------------+--------------------+ | Setting | Value | +---------------------------------+--------------------+ | shared_buffers | 39321600 (300 GiB) | | work_mem | 8192 (8.0 MiB) | | maintenance_work_mem | 2097152 (2.0 GiB) | | temp_buffers | 1024 (8.0 MiB) | | autovacuum_work_mem | -1 | | temp_file_limit | -1 | | max_worker_processes | 128 | | autovacuum_max_workers | 16 | | max_parallel_workers_per_gather | 0 | | effective_io_concurrency | 0 | +---------------------------------+--------------------+ WAL Files: WAL Archiving? no WAL Files: 9600 +--------------------+------------------+ | Setting | Value | +--------------------+------------------+ | wal_level | minimal | | archive_timeout | 0 | | wal_compression | off | | max_wal_size | 614400 (9.4 TiB) | | min_wal_size | 153600 (2.3 TiB) | | checkpoint_timeout | 2100 | | full_page_writes | off | | wal_keep_segments | 0 | +--------------------+------------------+ BG Writer: Checkpoint Rate: 0.03 per min Average Write: 118 GiB per checkpoint Total Checkpoints: 355 sched (88.8%) + 45 req (11.2%) = 400 Total Write: 126 TiB, @ 153 MiB per sec Buffers Allocated: 10825060769 (81 TiB) Buffers Written: 6157847013 chkpt (36.3%) + 6749938129 bgw (39.8%) + 4047065773 be (23.9%) Clean Scan Stops: 6107928 BE fsyncs: 0 Counts Since: 21 Sep 2018 10:42:07 PM (1 week ago) +------------------------------+--------------+ | Setting | Value | +------------------------------+--------------+ | bgwriter_delay | 10 msec | | bgwriter_flush_after | 64 (512 KiB) | | bgwriter_lru_maxpages | 1000 | | bgwriter_lru_multiplier | 10 | | block_size | 8192 | | checkpoint_timeout | 2100 sec | | checkpoint_completion_target | 1e-05 | +------------------------------+--------------+ Backends: Total Backends: 35 (1.8% of max 2000) Problematic: 0 waiting on locks, 8 waiting on other, 1 xact too long, 12 idle in xact Other Waiting Backends: +-------+----------+---------+-------------+----------+-----------------------+------------------------+ | PID | User | App | Client Addr | Database | Wait | Query Start | +-------+----------+---------+-------------+----------+-----------------------+------------------------+ | 16514 | postgres | pgbench | | postgres | Client / ClientRead | 1 Oct 2018 11:14:17 PM | | 16515 | postgres | pgbench | | postgres | IPC / ClogGroupUpdate | 1 Oct 2018 11:14:17 PM | | 16517 | postgres | pgbench | | postgres | Client / ClientRead | 1 Oct 2018 11:14:17 PM | | 16523 | postgres | pgbench | | postgres | IPC / ClogGroupUpdate | 1 Oct 2018 11:14:17 PM | | 16527 | postgres | pgbench | | postgres | Client / ClientRead | 1 Oct 2018 11:14:17 PM | | 16533 | postgres | pgbench | | postgres | Client / ClientRead | 1 Oct 2018 11:14:17 PM | | 16535 | postgres | pgbench | | postgres | Client / ClientRead | 1 Oct 2018 11:14:17 PM | | 40144 | postgres | psql | | postgres | Client / ClientRead | 1 Oct 2018 10:55:00 PM | +-------+----------+---------+-------------+----------+-----------------------+------------------------+ Long Running (>60 sec) Transactions: +-------+----------+------+-------------+----------+-----------------------------------------+ | PID | User | App | Client Addr | Database | Transaction Start | +-------+----------+------+-------------+----------+-----------------------------------------+ | 40259 | postgres | psql | | postgres | 1 Oct 2018 10:54:56 PM (22 minutes ago) | +-------+----------+------+-------------+----------+-----------------------------------------+ Idling in Transaction: +-------+----------+---------+-------------+----------+----------+------------------------+ | PID | User | App | Client Addr | Database | Aborted? | State Change | +-------+----------+---------+-------------+----------+----------+------------------------+ | 16507 | postgres | pgbench | | postgres | no | 1 Oct 2018 11:14:17 PM | | 16508 | postgres | pgbench | | postgres | no | 1 Oct 2018 11:14:17 PM | | 16512 | postgres | pgbench | | postgres | no | 1 Oct 2018 11:14:17 PM | | 16513 | postgres | pgbench | | postgres | no | 1 Oct 2018 11:14:17 PM | | 16518 | postgres | pgbench | | postgres | no | 1 Oct 2018 11:14:17 PM | | 16523 | postgres | pgbench | | postgres | no | 1 Oct 2018 11:14:17 PM | | 16524 | postgres | pgbench | | postgres | no | 1 Oct 2018 11:14:17 PM | | 16528 | postgres | pgbench | | postgres | no | 1 Oct 2018 11:14:17 PM | | 16530 | postgres | pgbench | | postgres | no | 1 Oct 2018 11:14:17 PM | | 16534 | postgres | pgbench | | postgres | no | 1 Oct 2018 11:14:17 PM | | 16535 | postgres | pgbench | | postgres | no | 1 Oct 2018 11:14:17 PM | | 16537 | postgres | pgbench | | postgres | no | 1 Oct 2018 11:14:17 PM | +-------+----------+---------+-------------+----------+----------+------------------------+ Vacuum Progress: Vacuum Process #1: Phase: scanning heap Database: postgres Table: Scan Progress: 201556359 of 205237504 (98.2% complete) Heap Blks Vac'ed: 0 of 205237504 Idx Vac Cycles: 0 Dead Tuples: 0 Dead Tuples Max: 291 +------------------------------+-------------------+ | Setting | Value | +------------------------------+-------------------+ | maintenance_work_mem | 2097152 (2.0 GiB) | | autovacuum | on | | autovacuum_analyze_threshold | 50 | | autovacuum_vacuum_threshold | 50 | | autovacuum_freeze_max_age | 1200000000 | | autovacuum_max_workers | 16 | | autovacuum_naptime | 60 sec | | vacuum_freeze_min_age | 50000000 | | vacuum_freeze_table_age | 1150000000 | +------------------------------+-------------------+ 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_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 | postgres | $PGDATA = /data01/pg11/pg_root1921 | 337 GiB | 494 GiB (12.1%) of 4.0 TiB | 59771 (1.4%) of 4194304 | | pg_global | postgres | $PGDATA = /data01/pg11/pg_root1921 | 598 KiB | 494 GiB (12.1%) of 4.0 TiB | 59771 (1.4%) of 4194304 | | tbs1 | postgres | /data02/pg11/tbs1 | 1.9 TiB | 2.0 TiB (49.9%) of 4.0 TiB | 53907 (1.3%) of 4194304 | | tbs2 | postgres | /data03/pg11/tbs2 | 2.2 GiB | 550 GiB (13.4%) of 4.0 TiB | 62361 (1.5%) of 4194304 | +------------+----------+------------------------------------+---------+----------------------------+-------------------------+ Database #1: Name: postgres Owner: postgres Tablespace: pg_default Connections: 35 (no max limit) Frozen Xid Age: 847687930 Transactions: 32236616423 (100.0%) commits, 148 (0.0%) rollbacks Cache Hits: 100.0% Rows Changed: ins 25.1%, upd 74.9%, del 0.0% Total Temp: 1.9 GiB in 4 files Problems: 0 deadlocks, 0 conflicts Totals Since: 21 Sep 2018 11:32:32 PM (1 week ago) Size: 2.2 TiB Sequences: +------------------------+------------+ | Sequence | Cache Hits | +------------------------+------------+ | part_p_partkey_seq | | | region_r_regionkey_seq | | | nation_n_nationkey_seq | | | supplier_s_suppkey_seq | | | customer_c_custkey_seq | | | orders_o_orderkey_seq | | | hints_id_seq | | | user_order_id_seq | 100.0% | +------------------------+------------+ Installed Extensions: +--------------------+---------+-------------------------------------------------------------------+ | Name | Version | Comment | +--------------------+---------+-------------------------------------------------------------------+ | btree_gin | 1.3 | support for indexing common datatypes in GIN | | cube | 1.4 | data type for multidimensional cubes | | dblink | 1.2 | connect to other PostgreSQL databases from within a database | | imgsmlr | 1.0 | image similarity module | | pg_hint_plan | 1.3.0 | | | pg_oltp_bench | 1.0 | supporting function for oltp benchmark | | pg_prewarm | 1.2 | prewarm relation data | | pg_stat_statements | 1.5 | track execution statistics of all SQL statements executed | | pg_trgm | 1.4 | text similarity measurement and index searching based on trigrams | | pgsocket | 1.0 | TCP IP Socket client | | plpgsql | 1.0 | PL/pgSQL procedural language | | rum | 1.2 | RUM index access method | +--------------------+---------+-------------------------------------------------------------------+ Database #2: Name: newdb Owner: postgres Tablespace: pg_default Connections: 0 (no max limit) Frozen Xid Age: 801198930 Transactions: 410 (97.2%) commits, 12 (2.8%) rollbacks Cache Hits: 97.1% Rows Changed: ins 100.0%, upd 0.0%, del 0.0% Total Temp: 0 B in 0 files Problems: 0 deadlocks, 0 conflicts Totals Since: 1 Oct 2018 10:47:44 PM (30 minutes ago) Size: 23 MiB Installed Extensions: +---------+---------+------------------------------+ | Name | Version | Comment | +---------+---------+------------------------------+ | plpgsql | 1.0 | PL/pgSQL procedural language | +---------+---------+------------------------------+ Table #1 in "postgres": Name: postgres.public.part Columns: 9 Manual Vacuums: 1, last 6 hours ago Manual Analyze: never Auto Vacuums: 31, last 2 hours ago Auto Analyze: never Post-Analyze: 0.0% est. rows modified Row Estimate: 100.0% live of total 39999206 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: 78.2% (idx=75.0%) Size: 6.6 GiB Bloat: 442 MiB (6.6%) +--------------+-------+---------+-------------------+------------+-------+----------------+-------------------+ | Index | Type | Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan | +--------------+-------+---------+-------------------+------------+-------+----------------+-------------------+ | idx_part__6 | btree | 857 MiB | 442 MiB (51.5%) | 75.0% | 0 | 0.0 | 0.0 | | idx_part__5 | btree | 1.5 GiB | 442 MiB (28.1%) | 75.0% | 0 | 0.0 | 0.0 | | idx_part_p_1 | btree | 9.3 MiB | 442 MiB (4734.9%) | 75.0% | 0 | 0.0 | 0.0 | | idx_part_1 | btree | 1.5 GiB | 442 MiB (28.5%) | 75.0% | 0 | 0.0 | 0.0 | | part_pkey | btree | 857 MiB | 442 MiB (51.5%) | 75.0% | 0 | 0.0 | 0.0 | +--------------+-------+---------+-------------------+------------+-------+----------------+-------------------+ Table #2 in "postgres": Name: postgres.public.region Columns: 3 Manual Vacuums: 1, last 6 hours ago Manual Analyze: never Auto Vacuums: 31, last 2 hours ago Auto Analyze: never Post-Analyze: 0.0% est. rows modified Row Estimate: 100.0% live of total 5 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: 83.0% (idx=75.0%) Size: 40 KiB +-------------+-------+--------+-------+------------+-------+----------------+-------------------+ | Index | Type | Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan | +-------------+-------+--------+-------+------------+-------+----------------+-------------------+ | region_pkey | btree | 16 KiB | | 75.0% | 0 | 0.0 | 0.0 | | idx5 | btree | 16 KiB | | 75.0% | 0 | 0.0 | 0.0 | +-------------+-------+--------+-------+------------+-------+----------------+-------------------+ Table #3 in "postgres": Name: postgres.public.nation Columns: 4 Manual Vacuums: 1, last 6 hours ago Manual Analyze: never Auto Vacuums: 31, last 2 hours ago Auto Analyze: never Post-Analyze: 0.0% est. rows modified Row Estimate: 100.0% live of total 25 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: 83.0% (idx=75.0%) Size: 40 KiB +----------------------+-------+--------+-------+------------+-------+----------------+-------------------+ | Index | Type | Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan | +----------------------+-------+--------+-------+------------+-------+----------------+-------------------+ | idx6 | btree | 16 KiB | | 75.0% | 0 | 0.0 | 0.0 | | nation_pkey | btree | 16 KiB | | 75.0% | 0 | 0.0 | 0.0 | | idx_nation_regionkey | btree | 16 KiB | | 75.0% | 0 | 0.0 | 0.0 | +----------------------+-------+--------+-------+------------+-------+----------------+-------------------+ Table #4 in "postgres": Name: postgres.public.supplier Columns: 7 Manual Vacuums: 1, last 6 hours ago Manual Analyze: never Auto Vacuums: 31, last 2 hours ago Auto Analyze: never Post-Analyze: 0.0% est. rows modified Row Estimate: 100.0% live of total 1999109 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: 79.4% (idx=75.0%) Size: 369 MiB Bloat: 9.5 MiB (2.6%) +-------------------------+-------+--------+-----------------+------------+-------+----------------+-------------------+ | Index | Type | Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan | +-------------------------+-------+--------+-----------------+------------+-------+----------------+-------------------+ | supplier_pkey | btree | 43 MiB | 9.5 MiB (22.1%) | 75.0% | 0 | 0.0 | 0.0 | | idx4 | btree | 60 MiB | 9.5 MiB (15.7%) | 75.0% | 0 | 0.0 | 0.0 | | idx_supplier_nation_key | btree | 43 MiB | 9.5 MiB (22.1%) | 75.0% | 0 | 0.0 | 0.0 | +-------------------------+-------+--------+-----------------+------------+-------+----------------+-------------------+ Table #5 in "postgres": Name: postgres.public.customer Columns: 8 Manual Vacuums: 1, last 6 hours ago Manual Analyze: never Auto Vacuums: 31, last 2 hours ago Auto Analyze: never Post-Analyze: 0.0% est. rows modified Row Estimate: 100.0% live of total 29998813 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: 78.2% (idx=75.0%) Size: 5.8 GiB Bloat: 92 MiB (1.5%) +---------------+-------+---------+----------------+------------+-------+----------------+-------------------+ | Index | Type | Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan | +---------------+-------+---------+----------------+------------+-------+----------------+-------------------+ | customer_pkey | btree | 643 MiB | 92 MiB (14.3%) | 75.0% | 0 | 0.0 | 0.0 | +---------------+-------+---------+----------------+------------+-------+----------------+-------------------+ Table #6 in "postgres": Name: postgres.public.partsupp Columns: 5 Manual Vacuums: 1, last 6 hours ago Manual Analyze: never Auto Vacuums: 31, last 2 hours ago Auto Analyze: never Post-Analyze: 0.0% est. rows modified Row Estimate: 100.0% live of total 159999995 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: 78.1% (idx=75.0%) Size: 29 GiB Bloat: 587 MiB (2.0%) +----------------------+-------+---------+-----------------+------------+-------+----------------+-------------------+ | Index | Type | Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan | +----------------------+-------+---------+-----------------+------------+-------+----------------+-------------------+ | idx_partsupp_suppkey | btree | 3.3 GiB | 587 MiB (17.1%) | 75.0% | 0 | 0.0 | 0.0 | | idx_partsupp_partkey | btree | 3.3 GiB | 587 MiB (17.1%) | 75.0% | 0 | 0.0 | 0.0 | | idx | btree | 6.1 GiB | 587 MiB (9.5%) | 75.0% | 0 | 0.0 | 0.0 | | partsupp_pkey | btree | 4.7 GiB | 587 MiB (12.2%) | 75.0% | 0 | 0.0 | 0.0 | | idx_partsupp__4 | btree | 3.3 GiB | 587 MiB (17.1%) | 75.0% | 0 | 0.0 | 0.0 | +----------------------+-------+---------+-----------------+------------+-------+----------------+-------------------+ Table #7 in "postgres": Name: postgres.public.orders Columns: 9 Manual Vacuums: 1, last 6 hours ago Manual Analyze: never Auto Vacuums: 31, last 2 hours ago Auto Analyze: never Post-Analyze: 0.0% est. rows modified Row Estimate: 100.0% live of total 299997901 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: 78.1% (idx=75.0%) Size: 43 GiB Bloat: 1.8 GiB (4.2%) +----------------------+-------+---------+-----------------+------------+-------+----------------+-------------------+ | Index | Type | Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan | +----------------------+-------+---------+-----------------+------------+-------+----------------+-------------------+ | idx_orders_orderdate | btree | 6.3 GiB | 1.8 GiB (28.7%) | 75.0% | 0 | 0.0 | 0.0 | | idx_orders__6 | btree | 8.8 GiB | 1.8 GiB (20.5%) | 75.0% | 0 | 0.0 | 0.0 | | idx_orders_custkey | btree | 6.3 GiB | 1.8 GiB (28.7%) | 75.0% | 0 | 0.0 | 0.0 | | orders_pkey | btree | 6.3 GiB | 1.8 GiB (28.7%) | 75.0% | 0 | 0.0 | 0.0 | +----------------------+-------+---------+-----------------+------------+-------+----------------+-------------------+ Table #8 in "postgres": Name: postgres.public.lineitem Columns: 16 Manual Vacuums: 1, last 6 hours ago Manual Analyze: never Auto Vacuums: 31, last 2 hours ago Auto Analyze: never Post-Analyze: 0.0% est. rows modified Row Estimate: 100.0% live of total 1200018409 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: 78.1% (idx=77.1%) Size: 199 GiB Bloat: 6.6 GiB (3.3%) +-----------------------+-------+---------+------------------+------------+-------+----------------+-------------------+ | Index | Type | Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan | +-----------------------+-------+---------+------------------+------------+-------+----------------+-------------------+ | idx_lineitem_orderkey | btree | 25 GiB | 6.6 GiB (26.4%) | 77.4% | 0 | 0.0 | 0.0 | | idx1 | btree | 16 GiB | 6.6 GiB (41.8%) | 77.4% | 0 | 0.0 | 0.0 | | idx_lineitem_shipdate | btree | 45 GiB | 6.6 GiB (14.6%) | 77.4% | 0 | 0.0 | 0.0 | | idx_lineitem__2 | btree | 25 GiB | 6.6 GiB (26.4%) | 77.4% | 0 | 0.0 | 0.0 | | idx_lineitem__3 | btree | 25 GiB | 6.6 GiB (26.4%) | 77.4% | 0 | 0.0 | 0.0 | | idx_lineitem__11 | btree | 25 GiB | 6.6 GiB (26.4%) | 77.4% | 0 | 0.0 | 0.0 | | idx_part_l_2 | btree | 5.3 GiB | 6.6 GiB (124.0%) | 77.4% | 0 | 0.0 | 0.0 | | lineitem_pkey | btree | 35 GiB | 6.6 GiB (18.8%) | 75.0% | 0 | 0.0 | 0.0 | +-----------------------+-------+---------+------------------+------------+-------+----------------+-------------------+ Table #9 in "postgres": Name: postgres.hint_plan.hints Columns: 4 Manual Vacuums: 1, last 6 hours ago Manual Analyze: never Auto Vacuums: 31, last 2 hours ago 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: 75.0% (idx=75.0%) Size: 8.0 KiB +--------------------+-------+---------+-------+------------+-------+----------------+-------------------+ | Index | Type | Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan | +--------------------+-------+---------+-------+------------+-------+----------------+-------------------+ | hints_norm_and_app | btree | 8.0 KiB | | 75.0% | 0 | 0.0 | 0.0 | | hints_pkey | btree | 8.0 KiB | | 75.0% | 0 | 0.0 | 0.0 | +--------------------+-------+---------+-------+------------+-------+----------------+-------------------+ Table #10 in "postgres": Name: postgres.public.pgbench_history Tablespace: tbs1 Columns: 6 Manual Vacuums: 5, last 6 hours ago Manual Analyze: 1, last 4 days ago Auto Vacuums: 26, last 2 hours ago Auto Analyze: 70, last 7 hours ago Post-Analyze: 7.4% est. rows modified Row Estimate: 100.0% live of total 32165335009 Rows Changed: ins 100.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: 99.4% (idx=0.0%) Size: 1.5 TiB Table #11 in "postgres": Name: postgres.public.pgbench_tellers Tablespace: tbs1 Columns: 4 Manual Vacuums: 4, last 6 minutes ago Manual Analyze: 1, last 4 days ago Auto Vacuums: 7581, last 4 minutes ago Auto Analyze: 7581, last 4 minutes ago Post-Analyze: 6219.9% est. rows modified Row Estimate: 17.8% live of total 56200 Rows Changed: ins 0.0%, upd 99.9%, del 0.0% HOT Updates: 99.9% of all updates Seq Scans: 1, 10000.0 rows/scan Idx Scans: 32236503570, 1.0 rows/scan Cache Hits: 100.0% (idx=100.0%) Size: 174 MiB Bloat: 253 MiB (145.3%) +----------------------+-------+--------+------------------+------------+-------------+----------------+-------------------+ | Index | Type | Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan | +----------------------+-------+--------+------------------+------------+-------------+----------------+-------------------+ | pgbench_tellers_pkey | btree | 80 MiB | 253 MiB (316.8%) | 100.0% | 32236503570 | 1.0 | 1.0 | +----------------------+-------+--------+------------------+------------+-------------+----------------+-------------------+ Table #12 in "postgres": Name: postgres.public.pgbench_accounts Tablespace: tbs1 Columns: 4 Manual Vacuums: 5, last 6 minutes ago Manual Analyze: 2, last 4 days ago Auto Vacuums: 27, last 2 hours ago Auto Analyze: 2828, last 6 minutes ago Post-Analyze: 13.1% est. rows modified Row Estimate: 98.3% live of total 101746633 Rows Changed: ins 0.3%, upd 99.7%, del 0.0% HOT Updates: 100.0% of all updates Seq Scans: 1, 100000000.0 rows/scan Idx Scans: 64473007140, 1.0 rows/scan Cache Hits: 100.0% (idx=100.0%) Size: 13 GiB Bloat: 977 MiB (7.2%) +-----------------------+-------+---------+-----------------+------------+-------------+----------------+-------------------+ | Index | Type | Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan | +-----------------------+-------+---------+-----------------+------------+-------------+----------------+-------------------+ | pgbench_accounts_pkey | btree | 2.1 GiB | 977 MiB (45.6%) | 100.0% | 64473007140 | 1.0 | 1.0 | +-----------------------+-------+---------+-----------------+------------+-------------+----------------+-------------------+ Table #13 in "postgres": Name: postgres.public.pgbench_branches Tablespace: tbs1 Columns: 3 Manual Vacuums: 4, last 6 minutes ago Manual Analyze: 1, last 4 days ago Auto Vacuums: 7578, last 4 minutes ago Auto Analyze: 7578, last 4 minutes ago Post-Analyze: 22546.2% est. rows modified Row Estimate: 6.5% live of total 15504 Rows Changed: ins 0.0%, upd 99.9%, del 0.0% HOT Updates: 99.9% of all updates Seq Scans: 2, 1000.0 rows/scan Idx Scans: 32236503570, 1.0 rows/scan Cache Hits: 100.0% (idx=100.0%) Size: 65 MiB Bloat: 96 MiB (147.5%) +-----------------------+-------+--------+-----------------+------------+-------------+----------------+-------------------+ | Index | Type | Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan | +-----------------------+-------+--------+-----------------+------------+-------------+----------------+-------------------+ | pgbench_branches_pkey | btree | 31 MiB | 96 MiB (308.9%) | 100.0% | 32236503570 | 1.1 | 1.0 | +-----------------------+-------+--------+-----------------+------------+-------------+----------------+-------------------+ Table #14 in "postgres": Name: postgres.public.user_order Columns: 11 Manual Vacuums: 2, last 22 minutes ago Manual Analyze: never Auto Vacuums: 1, last 3 hours ago Auto Analyze: 1, last 7 hours ago Post-Analyze: 0.0% est. rows modified Row Estimate: 100.0% live of total 999928 Rows Changed: ins 100.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: 98.4% (idx=99.8%) Size: 135 MiB Bloat: 24 MiB (17.7%) +-----------------+-------+--------+-----------------+------------+-------+----------------+-------------------+ | Index | Type | Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan | +-----------------+-------+--------+-----------------+------------+-------+----------------+-------------------+ | user_order_pkey | btree | 21 MiB | 24 MiB (111.0%) | 99.8% | 0 | 0.0 | 0.0 | +-----------------+-------+--------+-----------------+------------+-------+----------------+-------------------+ Table #1 in "newdb": Name: newdb.public.pgbench_history Columns: 6 Manual Vacuums: 1, last 30 minutes ago Manual Analyze: 1, last 30 minutes ago 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 "newdb": Name: newdb.public.pgbench_tellers Columns: 4 Manual Vacuums: 1, last 30 minutes ago Manual Analyze: 1, last 30 minutes ago Auto Vacuums: never Auto Analyze: never Post-Analyze: 0.0% est. rows modified Row Estimate: 100.0% live of total 10 Rows Changed: ins 100.0%, upd 0.0%, del 0.0% HOT Updates: 0.0% of all updates Seq Scans: 1, 10.0 rows/scan Idx Scans: 0, 0.0 rows/scan Cache Hits: 76.2% (idx=0.0%) Size: 40 KiB +----------------------+-------+--------+-------+------------+-------+----------------+-------------------+ | Index | Type | Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan | +----------------------+-------+--------+-------+------------+-------+----------------+-------------------+ | pgbench_tellers_pkey | btree | 16 KiB | | | 0 | 0.0 | 0.0 | +----------------------+-------+--------+-------+------------+-------+----------------+-------------------+ Table #3 in "newdb": Name: newdb.public.pgbench_accounts Columns: 4 Manual Vacuums: 1, last 30 minutes ago Manual Analyze: 1, last 30 minutes ago Auto Vacuums: never Auto Analyze: never Post-Analyze: 0.0% est. rows modified Row Estimate: 100.0% live of total 100000 Rows Changed: ins 100.0%, upd 0.0%, del 0.0% HOT Updates: 0.0% of all updates Seq Scans: 1, 100000.0 rows/scan Idx Scans: 0, 0.0 rows/scan Cache Hits: 80.0% (idx=0.0%) Size: 13 MiB Bloat: 192 KiB (1.5%) +-----------------------+-------+---------+----------------+------------+-------+----------------+-------------------+ | Index | Type | Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan | +-----------------------+-------+---------+----------------+------------+-------+----------------+-------------------+ | pgbench_accounts_pkey | btree | 2.2 MiB | 192 KiB (8.7%) | | 0 | 0.0 | 0.0 | +-----------------------+-------+---------+----------------+------------+-------+----------------+-------------------+ Table #4 in "newdb": Name: newdb.public.pgbench_branches Columns: 3 Manual Vacuums: 1, last 30 minutes ago Manual Analyze: 1, last 30 minutes ago Auto Vacuums: never Auto Analyze: never Post-Analyze: 100.0% est. rows modified Row Estimate: 100.0% live of total 1 Rows Changed: ins 100.0%, upd 0.0%, del 0.0% HOT Updates: 0.0% of all updates Seq Scans: 1, 1.0 rows/scan Idx Scans: 0, 0.0 rows/scan Cache Hits: 58.3% (idx=0.0%) Size: 40 KiB +-----------------------+-------+--------+-------+------------+-------+----------------+-------------------+ | Index | Type | Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan | +-----------------------+-------+--------+-------+------------+-------+----------------+-------------------+ | pgbench_branches_pkey | btree | 16 KiB | | | 0 | 0.0 | 0.0 | +-----------------------+-------+--------+-------+------------+-------+----------------+-------------------+
小结
1、pgmetrics用法非常简单，连接到数据库，获取内置的监测指标的内容。
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) Output options: -f, --format=FORMAT output format; "human", or "json" (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: "/data01/pg11/pg_root1921") -p, --port=PORT database server port (default: 1921) -U, --username=USERNAME database user name (default: "postgres") --no-password never prompt for password For more information, visit <https://pgmetrics.io>.
2、用户可以使用pgmetrics定义收集数据库状态信息。
3、结合pgdash，可以将pgmetrics收集的内容可视化，并提供告警能力。
4、我在之前还写过一些数据库指标监控的文档，请参考末尾。
5、如发现需要新增的指标，或者有些指标不正确，可以发ISSUE给作者。
目前pgmetrics的指标已经比较丰富，可以用于日常的状态和性能监控。
到此，相信大家对“PostgreSQL中pgmetrics有什么作用”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/205104.html