本文共 43118 字，大约阅读时间需要 143 分钟。
背景
pgmetrics，GO写的一款PostgreSQL 多版本、健康监控指标采集、报告开源软件。结合pgdash，可以实现被监控PG实例的可视化，指标值变更告警等。
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 displaythis 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 thanthis 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 .
pgmetrics 采集维度pgmetrics通过数据库用户连接到数据库进行采集。1、集群
2、数据库
3、操作系统
采集代码：
当采集数据的角色不为超级用户时，可能有些指标无法被正确采集（例如权限问题），但是此时并不会报错，只是输出可以正常被采集的指标并记录下来。
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
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 displaythis 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 thanthis 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 .
2、用户可以使用pgmetrics定义收集数据库状态信息。3、结合pgdash，可以将pgmetrics收集的内容可视化，并提供告警能力。
4、我在之前还写过一些数据库指标监控的文档，请参考末尾。
5、如发现需要新增的指标，或者有些指标不正确，可以发ISSUE给作者。
目前pgmetrics的指标已经比较丰富，可以用于日常的状态和性能监控。
参考
《[未完待续] PostgreSQL 一键诊断项 - 珍藏级》
《PostgreSQL 实时健康监控 大屏 - 低频指标 - 珍藏级》
《PostgreSQL 实时健康监控 大屏 - 高频指标(服务器) - 珍藏级》
《PostgreSQL 实时健康监控 大屏 - 高频指标 - 珍藏级》
《PostgreSQL、Greenplum 日常监控 和 维护任务 - 最佳实践》
《PostgreSQL 锁等待监控 珍藏级SQL - 谁堵塞了谁》
《PostgreSQL 10.0 preview 功能增强 - 新增数十个IO等待事件监控》
转自阿里云德哥转载地址：http://rlizo.baihongyu.com/