开发者社区> pg小助手> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

PostgreSQL pgmetrics - 多版本、健康监控指标采集、报告

简介: postgresql 数据库 监控
+关注继续查看

背景
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等待事件监控》
转自阿里云德哥

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
RDS PostgreSQL管控体系介绍
RDS PostgreSQL管控体系介绍
206 0
PicGo v1.6版本更新——支持阿里云OSS
PicGo v1.6版本更新——支持阿里云OSS
4647 0
PostgreSQL 9.4版本的物化视图更新
postgresql的9.4版本出来有一段时间了,也更新了很多内容,其中之一是比较感兴趣的物化视图的更新,对比原先的物化视图语法,新增了一个CONCURRENTLY参数。
1073 0
+关注
21
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载