PostgreSQL pgmetrics - 多版本、健康监控指标采集、报告
背景
pgmetrics，GO写的一款PostgreSQL 多版本、健康监控指标采集、报告开源软件。
https://github.com/rapidloop/pgmetrics
结合pgdash，可以实现被监控PG实例的可视化，指标值变更告警等。
https://pgdash.io/
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的指标已经比较丰富，可以用于日常的状态和性能监控。
参考
https://bucardo.org/check_postgres/
https://github.com/rapidloop/pgmetrics
https://pgmetrics.io/
https://github.com/digoal/pgsql_admin_script/blob/master/generate_report.sh
《[未完待续] PostgreSQL 一键诊断项 - 珍藏级》
《PostgreSQL 实时健康监控 大屏 - 低频指标 - 珍藏级》
《PostgreSQL 实时健康监控 大屏 - 高频指标(服务器) - 珍藏级》
《PostgreSQL 实时健康监控 大屏 - 高频指标 - 珍藏级》
《PostgreSQL、Greenplum 日常监控 和 维护任务 - 最佳实践》
《PostgreSQL 锁等待监控 珍藏级SQL - 谁堵塞了谁》
《PostgreSQL 10.0 preview 功能增强 - 新增数十个IO等待事件监控》