PostgreSQL教程

PostgreSQL一站式插件推荐 -- pg_enterprise_views

本文主要是介绍PostgreSQL一站式插件推荐 -- pg_enterprise_views,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

    近日发现PG官方插件列表中新收录了一款插件 pg_enterprise_views,因为官方已经数年未添新的插件了很是新奇,找了台设备测试过后果断上了生产,得空分享给大家。

    该插件提供了数十张系统表及一个GUI工具,用以监控从操作系统到数据库方方面面的性能情况,并支持对任意时段历史数据的回溯,基本等同于以往所有监控类插件整合后的超集。

1. 系统表

本质上而言,官方有意提供GUI工具意在降低学习成本,一般运维人员无需关注系统表内容,了解GUI工具的使用即可,在此仅作简要说明。

    完成安装后,所有相关结构会被安放在 postgres 库下,这正是其优秀之处,PG的数据库之间是相对独立的,并不提供跨库的数据访问,因此大部分的插件作用域仅为单库,而 PEV(即 pg_enterprise_views,后文简称 PEV)从单库即可完成对整个数据库簇的实例级监控。先来看看提供了哪些表与视图,由名称可见其内容应包含负载指标、活跃会话、等待事件、超时锁、长事务、SQL及执行计划、SQL统计信息、数据库、表、索引、序列、函数、后台写进程及归档进程,可以说是相当全面的。

postgres=# \dt pev.*
                 List of relations
 Schema |          Name          | Type  |  Owner   
--------+------------------------+-------+----------
 pev    | pev_active_session_his | table | postgres
 pev    | pev_archiver_his       | table | postgres
 pev    | pev_bgwriter_his       | table | postgres
 pev    | pev_database_his       | table | postgres
 pev    | pev_functions_his      | table | postgres
 pev    | pev_indexes_his        | table | postgres
 pev    | pev_long_locks_his     | table | postgres
 pev    | pev_long_trxs_his      | table | postgres
 pev    | pev_metrics_his        | table | postgres
 pev    | pev_sequences_his      | table | postgres
 pev    | pev_setting            | table | postgres
 pev    | pev_sql                | table | postgres
 pev    | pev_sql_plan           | table | postgres
 pev    | pev_sql_stats_his      | table | postgres
 pev    | pev_tables_his         | table | postgres
 pev    | pev_wait_events_his    | table | postgres
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 postgres=# \dv pev.*
               List of relations
 Schema |        Name        | Type |  Owner   
--------+--------------------+------+----------
 pev    | pev_active_session | view | postgres
 pev    | pev_long_locks     | view | postgres
 pev    | pev_long_trxs      | view | postgres
 pev    | pev_metrics        | view | postgres
 pev    | pev_sql_stats      | view | postgres
 pev    | pev_wait_events    | view | postgres

1.1. pev_metrics && pev_metrics_his

  视图 pev_metrics 提供数十项从操作系统到数据库的实时负载指标,表 pev_metrics_his 周期性拍摄指标快照并计算增量。

  这极大的弥补了 PG 在这方面的缺陷,如 Oracle、SQL Server 等商业数据库甚至是 MySQL 这种同样的开源产品都内置有丰富的性能视图,而 PG 迭代的侧重点可能更多的聚焦于功能层面。

postgres=# \d pev.pev_metrics
              View "pev.pev_metrics"
    Column    |          Type          | Modifiers 
--------------+------------------------+-----------
 metric_group | text                   | 
 metric_id    | text                   | 
 metric_name  | text                   | 
 value        | character varying(200) | 
 units        | text                   | 
 desp         | text                   | 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
postgres=# \d pev.pev_metrics_his
              Table "pev.pev_metrics_his"
    Column    |            Type             | Modifiers 
--------------+-----------------------------+-----------
 snap_id      | bigint                      | 
 sample_time  | timestamp without time zone | 
 metric_group | character varying(2000)     | 
 metric_id    | integer                     | 
 metric_name  | character varying(2000)     | 
 value        | character varying(2000)     | 
 value_ps     | double precision            | 
 units        | character varying(2000)     | 
 desp         | character varying(2000)     | 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
postgres=# select * from pev.pev_metrics;
 metric_group | metric_id |     metric_name      |                   value                   | units  |                                                     desp                                                     
--------------+-----------+----------------------+-------------------------------------------+--------+--------------------------------------------------------------------------------------------------------------
 OS(CPU)      | 1001      | CPU_USER_NORMAL_PCT  | 0                                         | %      | Percentage of CPU time spent processing user-mode processes
 OS(CPU)      | 1002      | CPU_USER_NICED_PCT   | 0                                         | %      | Percentage of time spent by CPU processing the priority of user-mode scheduling process
 OS(CPU)      | 1003      | CPU_KERNEL_PCT       | 0                                         | %      | Percentage of CPU time spent processing kernel processes
 OS(CPU)      | 1004      | CPU_IDLE_PCT         | 100                                       | %      | Percentage of CPU idle time
 OS(CPU)      | 1005      | CPU_IO_PCT           | 0                                         | %      | Percentage of time spent by CPU processing I/O
 OS(CPU)      | 1006      | CPU_MODEL            | Intel(R) Xeon(R) CPU E5-2640 v4 @ 2.40GHz | -      | CPU model name
 OS(CPU)      | 1007      | CPU_PRO_LOGICAL_CNT  | 0                                         | number | Number of CPU logical processors
 OS(CPU)      | 1008      | CPU_PRO_PHYSICAL_CNT | 8                                         | number | Number of CPU physical processors
 OS(CPU)      | 1009      | CPU_CORE_CNT         | 1                                         | number | Number of CPU cores
 OS(CPU)      | 1010      | CPU_ARCH             | x86_64                                    | -      | CPU architecture
 OS(CPU)      | 1011      | CPU_L1D_CACHE_SIZE   | 32                                        | KB     | CPU L1 data cache size
 OS(CPU)      | 1012      | CPU_L1I_CACHE_SIZE   | 32                                        | KB     | CPU L1 instruction cache size
 OS(CPU)      | 1013      | CPU_L2_CACHE_SIZE    | 256                                       | KB     | CPU L2 cache size
 OS(CPU)      | 1014      | CPU_L3_CACHE_SIZE    | 25600                                     | KB     | CPU L3 cache size
 OS(MEMORY)   | 1015      | MEM_TOTAL_MB         | 15773.6                                   | MB     | Total memory capacity size
 OS(MEMORY)   | 1016      | MEM_USED_MB          | 798.37                                    | MB     | Current memory capacity usage
 OS(MEMORY)   | 1017      | MEM_FREE_MB          | 14975.2                                   | MB     | Current memory capacity free size
 OS(MEMORY)   | 1018      | MEM_SWAP_TOTAL_MB    | 32768                                     | MB     | Swap partition size
 OS(MEMORY)   | 1019      | MEM_SWAP_USED_MB     | 0                                         | MB     | Used size of swap partition
 OS(MEMORY)   | 1020      | MEM_SWAP_FREE_MB     | 32768                                     | MB     | Free size of swap partition
 OS(DISK)     | 1021      | DISK_TOTAL_MB        | 205714                                    | MB     | Total disk capacity
 OS(DISK)     | 1022      | DISK_USED_MB         | 17995.5                                   | MB     | Disk used size
 OS(DISK)     | 1023      | DISK_FREE_MB         | 187719                                    | MB     | Disk free size
 OS(DISK)     | 1024      | DISK_RD_CNT          | 90                                        | number | Number of disk reads, ps means per seconed of delta
 OS(DISK)     | 1025      | DISK_WT_CNT          | 0                                         | number | Number of disk writes, ps means per seconed of delta
 OS(DISK)     | 1026      | DISK_RD_KB           | 544249                                    | KB     | Disk read data size, ps means per seconed of delta
 OS(DISK)     | 1027      | DISK_WT_KB           | 55032636                                  | KB     | Disk write data size, ps means per seconed of delta
 OS(PROCESS)  | 1028      | PRO_TOTAL_CNT        | 230                                       | number | Total number of current processes
 OS(PROCESS)  | 1029      | PRO_ACTIVE_CNT       | 1                                         | number | Total number of current active processes
 OS(PROCESS)  | 1030      | PRO_SLEEP_CNT        | 115                                       | number | Total number of current sleep processes
 OS(PROCESS)  | 1031      | PRO_STOPPED_CNT      | 0                                         | number | Total number of current stopped processes
 OS(PROCESS)  | 1032      | PRO_ZOMBIE_CNT       | 0                                         | number | Total number of current zombiz processes
 OS(NETWORK)  | 1033      | NET_DATA_SEND_KB     | 0                                         | KB     | Network data transmission size, ps means per seconed of delta
 OS(NETWORK)  | 1034      | NET_PACKAGES_SEND    | 0                                         | number | Number of network packets sent, ps means per seconed of delta
 OS(NETWORK)  | 1035      | NET_ERR_SEND         | 0                                         | number | Number of network data transmission errors, ps means per seconed of delta
 OS(NETWORK)  | 1036      | NET_PACKAGES_SDROP   | 0                                         | number | Number of packets lost in network data transmission, ps means per seconed of delta
 OS(NETWORK)  | 1037      | NET_DATA_RECEIVE_KB  | 0                                         | KB     | Network data receive size, ps means per seconed of delta
 OS(NETWORK)  | 1038      | NET_PACKAGES_RECEIVE | 0                                         | number | Number of network packets receive, ps means per seconed of delta
 OS(NETWORK)  | 1039      | NET_ERR_RECEIVE      | 0                                         | number | Number of network data receive errors, ps means per seconed of delta
 OS(NETWORK)  | 1040      | NET_PACKAGES_RDROP   | 0                                         | number | Number of packets lost in network data receive, ps means per seconed of delta
 DB           | 2001      | CONN_TOTAL           | 4                                         | number | Total current connections
 DB           | 2002      | CONN_ACTIVE          | 1                                         | number | Current active connections
 DB           | 2003      | SESS_BG_TOTAL        | 3                                         | number | Number of current background sessions
 DB           | 2004      | SESS_BG_ACTIVE       | 1                                         | number | Number of current background active sessions
 DB           | 2005      | TX_CNT               | 2581359                                   | number | Total number of transactions since the server was started, ps means per seconed of delta
 DB           | 2006      | TX_COMMIT_CNT        | 2581321                                   | number | Total number of transactions submitted since the server was started, ps means per seconed of delta
 DB           | 2007      | TX_ROLLBACK_CNT      | 38                                        | number | Total number of transactions rollbacked since the server was started, ps means per seconed of delta
 DB           | 2008      | TEMP_KB              | 0.00                                      | KB     | Total size of temporary space occupation size since the server was started
 DB           | 2009      | FETCHED_CNT          | 16423761                                  | number | Total number of rows scanned since the server was started, ps means per seconed of delta
 DB           | 2010      | INSERT_CNT           | 3081659                                   | number | Total number of rows inserted since the server was started, ps means per seconed of delta
 DB           | 2011      | UPDATE_CNT           | 411174                                    | number | Total number of rows updated since the server was started, ps means per seconed of delta
 DB           | 2012      | DELETE_CNT           | 3092713                                   | number | Total number of rows deleted since the server was started, ps means per seconed of delta
 DB           | 2013      | WAL_KB               | 81920.00                                  | KB     | The size of the WAL generated since the server was started, ps means per seconed of delta
 DB           | 2014      | LOGICAL_RD_CNT       | 66268944                                  | number | Number of logical reads since server startup, ps means per seconed of delta
 DB           | 2015      | PHYSICAL_RD_CNT      | 14919                                     | number | Number of physical reads since server startup, ps means per seconed of delta
 DB           | 2016      | DBSIZE_MB            | 144.70                                    | MB     | Total size of the current databases
 DB           | 2017      | CONFLICTS_CNT        | 0                                         | number | The number of queries cancelled in this database due to conflicts with recovery since the server was started
 DB           | 2018      | DEADLOCKS_CNT        | 0                                         | number | Number of deadlocks since the server was started

 1.2. pev_active_session && pev_active_session_his

  视图 pev_active_session 提供实时的会话信息,表 pev_active_session_his 周期性拍摄会话快照。

  其结构大致等同与内置视图 pg_stat_activity 但附加了 queryid、planid 及 ssl 信息,这也就意味着对于系统内的任意会话都可实时获取其 SQL 及执行计划文本,并支持在对任意时段进行故障溯源时定位到具体的 SQL、执行计划、客户端等。

postgres=# \d pev.pev_active_session
              View "pev.pev_active_session"
      Column      |           Type           | Modifiers 
------------------+--------------------------+-----------
 datid            | oid                      | 
 datname          | name                     | 
 pid              | integer                  | 
 usesysid         | oid                      | 
 application_name | text                     | 
 backend_type     | text                     | 
 backend_start    | timestamp with time zone | 
 state            | text                     | 
 state_change     | timestamp with time zone | 
 backend_xid      | xid                      | 
 backend_xmin     | xid                      | 
 queryid          | bigint                   | 
 planid           | bigint                   | 
 query            | text                     | 
 query_start      | timestamp with time zone | 
 xact_start       | timestamp with time zone | 
 wait_event_type  | text                     | 
 wait_event       | text                     | 
 client_addr      | inet                     | 
 client_port      | bigint                   | 
 client_hostname  | text                     | 
 ssl              | boolean                  | 
 sslcompression   | boolean                  | 
 sslversion       | text                     | 
 sslcipher        | text                     | 
 sslbits          | bigint                   | 
 sslclientdn      | text                     | 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
postgres=# \d pev.pev_active_session_his
             Table "pev.pev_active_session_his"
      Column      |            Type             | Modifiers 
------------------+-----------------------------+-----------
 snap_id          | bigint                      | 
 sample_time      | timestamp without time zone | 
 datid            | oid                         | 
 datname          | character varying(2000)     | 
 pid              | integer                     | 
 usesysid         | oid                         | 
 application_name | character varying(2000)     | 
 client_addr      | character varying(2000)     | 
 client_hostname  | character varying(2000)     | 
 client_port      | integer                     | 
 backend_type     | character varying(2000)     | 
 backend_start    | timestamp with time zone    | 
 backend_xid      | xid                         | 
 backend_xmin     | xid                         | 
 xact_start       | timestamp with time zone    | 
 query_start      | timestamp with time zone    | 
 queryid          | bigint                      | 
 planid           | bigint                      | 
 state            | character varying(2000)     | 
 state_change     | timestamp with time zone    | 
 wait_event_type  | character varying(2000)     | 
 wait_event       | character varying(2000)     | 
 ssl              | boolean                     | 
 sslcompression   | boolean                     | 
 sslversion       | text                        | 
 sslcipher        | text                        | 
 sslbits          | bigint                      | 
 sslclientdn      | text                        | 

1.3. pev_wait_events && pev_wait_events_his 

  视图 pev_wait_events 提供实时的等待事件汇总信息,表 pev_wait_events_his 周期性拍摄等待事件快照。

  使运维人员或DBA能清晰的观测到数据库实时及历史的时间分配情况。

postgres=# \d pev.pev_wait_events
      View "pev.pev_wait_events"
     Column      |  Type   | Modifiers 
-----------------+---------+-----------
 wait_event_type | text    | 
 wait_event      | text    | 
 wait_count      | bigint  | 
 dura_ms         | numeric | 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
postgres=# \d pev.pev_wait_events_his
              Table "pev.pev_wait_events_his"
     Column      |            Type             | Modifiers 
-----------------+-----------------------------+-----------
 snap_id         | bigint                      | 
 sample_time     | timestamp without time zone | 
 wait_event_type | character varying(2000)     | 
 wait_event      | character varying(2000)     | 
 wait_count      | integer                     | 
 dura_ms         | bigint                      | 
 dura_ms_delta   | bigint                      | 

1.4. pev_sql_stats && pev_sql_stats_his

  视图 pev_sql_stats 提供实时的 SQL 统计信息,表 pev_sql_stats_his 周期性拍摄 SQL 统计信息快照。

  能够清晰的洞察任意 SQL 于指定时段内的统计信息变化趋势,而如原生拓展 pg_stat_statements 仅包含实时信息其实并不直观,也不具备很强的参考价值。

postgres=# \d pev.pev_sql_stats
         View "pev.pev_sql_stats"
       Column        |  Type   | Modifiers 
---------------------+---------+-----------
 userid              | oid     | 
 dbid                | oid     | 
 queryid             | bigint  | 
 calls               | bigint  | 
 total_time_ms       | numeric | 
 min_time_ms         | numeric | 
 max_time_ms         | numeric | 
 mean_time_ms        | numeric | 
 stddev_time_ms      | numeric | 
 rows                | bigint  | 
 shared_blks_hit     | bigint  | 
 shared_blks_read    | bigint  | 
 shared_blks_dirtied | bigint  | 
 shared_blks_written | bigint  | 
 local_blks_hit      | bigint  | 
 local_blks_read     | bigint  | 
 local_blks_dirtied  | bigint  | 
 local_blks_written  | bigint  | 
 temp_blks_read      | bigint  | 
 temp_blks_written   | bigint  | 
 blk_read_time_ms    | numeric | 
 blk_write_time_ms   | numeric | 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
postgres=# \d pev.pev_sql_stats_his
                    Table "pev.pev_sql_stats_his"
          Column           |            Type             | Modifiers 
---------------------------+-----------------------------+-----------
 snap_id                   | bigint                      | 
 sample_time               | timestamp without time zone | 
 userid                    | oid                         | 
 dbid                      | oid                         | 
 queryid                   | bigint                      | 
 calls                     | bigint                      | 
 total_time_ms             | double precision            | 
 min_time_ms               | double precision            | 
 max_time_ms               | double precision            | 
 mean_time_ms              | double precision            | 
 stddev_time_ms            | double precision            | 
 rows                      | bigint                      | 
 shared_blks_hit           | bigint                      | 
 shared_blks_read          | bigint                      | 
 shared_blks_dirtied       | bigint                      | 
 shared_blks_written       | bigint                      | 
 local_blks_hit            | bigint                      | 
 local_blks_read           | bigint                      | 
 local_blks_dirtied        | bigint                      | 
 local_blks_written        | bigint                      | 
 temp_blks_read            | bigint                      | 
 temp_blks_written         | bigint                      | 
 blk_read_time_ms          | double precision            | 
 blk_write_time_ms         | double precision            | 
 calls_delta               | bigint                      | 
 total_time_ms_delta       | double precision            | 
 min_time_ms_delta         | double precision            | 
 max_time_ms_delta         | double precision            | 
 mean_time_ms_delta        | double precision            | 
 stddev_time_ms_delta      | double precision            | 
 rows_delta                | bigint                      | 
 shared_blks_hit_delta     | bigint                      | 
 shared_blks_read_delta    | bigint                      | 
 shared_blks_dirtied_delta | bigint                      | 
 shared_blks_written_delta | bigint                      | 
 local_blks_hit_delta      | bigint                      | 
 local_blks_read_delta     | bigint                      | 
 local_blks_dirtied_delta  | bigint                      | 
 local_blks_written_delta  | bigint                      | 
 temp_blks_read_delta      | bigint                      | 
 temp_blks_written_delta   | bigint                      | 
 blk_read_time_ms_delta    | double precision            | 
 blk_write_time_ms_delta   | double precision            | 

1.5. pev_long_locks & pev_long_locks_his

  视图 pev_long_locks 提供实时的超20秒的锁等待信息,表 pev_long_locks_his 周期性拍摄超时锁快照。

  PG原生的锁信息相关系统表非常晦涩,不具备易用性,而通过 PEV 的锁等待视图可以轻松查看到阻塞者以及被阻塞者的进程、客户端、SQL、执行计划、被锁定的目标结构等等,更加贴合实际的运维需求。

postgres=# \d pev.pev_long_locks
        View "pev.pev_long_locks"
      Column      |   Type   | Modifiers 
------------------+----------+-----------
 blocker_pid      | integer  | 
 blocker_user     | name     | 
 blocker_client   | text     | 
 blocker_queryid  | bigint   | 
 blocker_planid   | bigint   | 
 blocker_state    | text     | 
 blocked_pid      | integer  | 
 blocked_user     | name     | 
 blocked_client   | text     | 
 blocked_queryid  | bigint   | 
 blocked_planid   | bigint   | 
 blocked_state    | text     | 
 blocked_dura_sec | bigint   | 
 lock_type        | text     | 
 lock_db          | name     | 
 lock_table       | regclass | 
 lock_row_num     | smallint | 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
postgres=# \d pev.pev_long_locks_his
               Table "pev.pev_long_locks_his"
      Column      |            Type             | Modifiers 
------------------+-----------------------------+-----------
 snap_id          | bigint                      | 
 sample_time      | timestamp without time zone | 
 blocker_pid      | integer                     | 
 blocker_user     | character varying(2000)     | 
 blocker_client   | character varying(2000)     | 
 blocker_queryid  | bigint                      | 
 blocker_planid   | bigint                      | 
 blocked_pid      | integer                     | 
 blocked_user     | character varying(2000)     | 
 blocked_client   | character varying(2000)     | 
 blocked_queryid  | bigint                      | 
 blocked_planid   | bigint                      | 
 blocked_dura_sec | bigint                      | 
 lock_type        | character varying(2000)     | 
 lock_db          | character varying(2000)     | 
 lock_table       | character varying(2000)     | 
 lock_row_num     | bigint                      | 

1.6. pev_long_trxs && pev_long_trxs_his

  视图 pev_long_trxs 提供实时的超20秒的长事务信息,表pev_long_trxs_his 周期性拍摄长事务快照。

  事务往往没有锁更加引人重视,因为所等待将直接导致业务阻塞,而事务则不会。其实不然,当事务长时间不释放时将影响到 auto vacuum 进程回收元组,对系统的性能影响是潜移默化的,运维人员有必要实时关注超长事务并进行必要的处理。

postgres=# \d pev.pev_long_trxs
                  View "pev.pev_long_trxs"
      Column      |            Type             | Modifiers 
------------------+-----------------------------+-----------
 datname          | name                        | 
 pid              | integer                     | 
 usesysid         | oid                         | 
 application_name | text                        | 
 client_addr      | inet                        | 
 client_port      | bigint                      | 
 backend_start    | timestamp without time zone | 
 state            | text                        | 
 xact_start       | timestamp without time zone | 
 query_start      | timestamp without time zone | 
 state_dura_ms    | bigint                      | 
 trx_dura_ms      | bigint                      | 
 query_dura_ms    | bigint                      | 
 wait_event_type  | text                        | 
 wait_event       | text                        | 
 queryid          | bigint                      | 
 planid           | bigint                      | 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
postgres=# \d pev.pev_long_trxs_his
               Table "pev.pev_long_trxs_his"
      Column      |            Type             | Modifiers 
------------------+-----------------------------+-----------
 snap_id          | bigint                      | 
 sample_time      | timestamp without time zone | 
 datname          | character varying(2000)     | 
 pid              | integer                     | 
 usesysid         | oid                         | 
 application_name | character varying(2000)     | 
 client_addr      | character varying(2000)     | 
 client_port      | integer                     | 
 backend_start    | timestamp without time zone | 
 state            | character varying(2000)     | 
 xact_start       | timestamp without time zone | 
 query_start      | timestamp without time zone | 
 state_dura_ms    | bigint                      | 
 trx_dura_ms      | bigint                      | 
 query_dura_ms    | bigint                      | 
 wait_event_type  | character varying(2000)     | 
 wait_event       | character varying(2000)     | 
 queryid          | bigint                      | 
 planid           | bigint                      | 

1.7. pev_database_his

  表 pev_database_his 周期性拍摄全数据库簇的数据库统计信息快照并进行增量计算。

postgres=# \d pev.pev_database_his
                   Table "pev.pev_database_his"
         Column          |            Type             | Modifiers 
-------------------------+-----------------------------+-----------
 snap_id                 | bigint                      | 
 sample_time             | timestamp without time zone | 
 database_oid            | oid                         | 
 database_name           | character varying(2000)     | 
 current_backends        | bigint                      | 
 xact_commit             | bigint                      | 
 xact_rollback           | bigint                      | 
 blks_read               | bigint                      | 
 blks_hit                | bigint                      | 
 tup_returned            | bigint                      | 
 tup_fetched             | bigint                      | 
 tup_inserted            | bigint                      | 
 tup_updated             | bigint                      | 
 tup_deleted             | bigint                      | 
 conflicts               | bigint                      | 
 temp_files              | bigint                      | 
 temp_bytes              | bigint                      | 
 deadlocks               | bigint                      | 
 blk_read_time_ms        | bigint                      | 
 blk_write_time_ms       | bigint                      | 
 current_backends_delta  | bigint                      | 
 xact_commit_delta       | bigint                      | 
 xact_rollback_delta     | bigint                      | 
 blks_read_delta         | bigint                      | 
 blks_hit_delta          | bigint                      | 
 tup_returned_delta      | bigint                      | 
 tup_fetched_delta       | bigint                      | 
 tup_inserted_delta      | bigint                      | 
 tup_updated_delta       | bigint                      | 
 tup_deleted_delta       | bigint                      | 
 conflicts_delta         | bigint                      | 
 temp_files_delta        | bigint                      | 
 temp_bytes_delta        | bigint                      | 
 deadlocks_delta         | bigint                      | 
 blk_read_time_ms_delta  | bigint                      | 
 blk_write_time_ms_delta | bigint                      | 

1.8. pev_tables_his

  表 pev_tables_his 周期性拍摄全数据库簇的表统计信息快照并进行增量计算。

postgres=# \d pev.pev_tables_his
                     Table "pev.pev_tables_his"
          Column           |            Type             | Modifiers 
---------------------------+-----------------------------+-----------
 snap_id                   | bigint                      | 
 sample_time               | timestamp without time zone | 
 dbname                    | character varying(2000)     | 
 table_oid                 | oid                         | 
 schema_name               | character varying(2000)     | 
 table_name                | character varying(2000)     | 
 seq_scan                  | bigint                      | 
 seq_tup_read              | bigint                      | 
 idx_scan                  | bigint                      | 
 idx_tup_fetch             | bigint                      | 
 n_tup_ins                 | bigint                      | 
 n_tup_upd                 | bigint                      | 
 n_tup_del                 | bigint                      | 
 n_tup_hot_upd             | bigint                      | 
 n_live_tup                | bigint                      | 
 n_dead_tup                | bigint                      | 
 n_mod_since_analyze       | bigint                      | 
 heap_blks_read            | bigint                      | 
 heap_blks_hit             | bigint                      | 
 idx_blks_read             | bigint                      | 
 idx_blks_hit              | bigint                      | 
 toast_blks_read           | bigint                      | 
 toast_blks_hit            | bigint                      | 
 tidx_blks_read            | bigint                      | 
 tidx_blks_hit             | bigint                      | 
 vacuum_count              | bigint                      | 
 autovacuum_count          | bigint                      | 
 analyze_count             | bigint                      | 
 autoanalyze_count         | bigint                      | 
 last_vacuum               | timestamp without time zone | 
 last_autovacuum           | timestamp without time zone | 
 last_analyze              | timestamp without time zone | 
 last_autoanalyze          | timestamp without time zone | 
 seq_scan_delta            | bigint                      | 
 seq_tup_read_delta        | bigint                      | 
 idx_scan_delta            | bigint                      | 
 idx_tup_fetch_delta       | bigint                      | 
 n_tup_ins_delta           | bigint                      | 
 n_tup_upd_delta           | bigint                      | 
 n_tup_del_delta           | bigint                      | 
 n_tup_hot_upd_delta       | bigint                      | 
 n_live_tup_delta          | bigint                      | 
 n_dead_tup_delta          | bigint                      | 
 n_mod_since_analyze_delta | bigint                      | 
 heap_blks_read_delta      | bigint                      | 
 heap_blks_hit_delta       | bigint                      | 
 idx_blks_read_delta       | bigint                      | 
 idx_blks_hit_delta        | bigint                      | 
 toast_blks_read_delta     | bigint                      | 
 toast_blks_hit_delta      | bigint                      | 
 tidx_blks_read_delta      | bigint                      | 
 tidx_blks_hit_delta       | bigint                      | 
 vacuum_count_delta        | bigint                      | 
 autovacuum_count_delta    | bigint                      | 
 analyze_count_delta       | bigint                      | 
 autoanalyze_count_delta   | bigint                      | 

1.9. pev_indexes_his

  表 pev_indexes_his 周期性拍摄全数据库簇的索引统计信息快照并进行增量计算。

postgres=# \d pev.pev_indexes_his
                  Table "pev.pev_indexes_his"
       Column        |            Type             | Modifiers 
---------------------+-----------------------------+-----------
 snap_id             | bigint                      | 
 sample_time         | timestamp without time zone | 
 table_oid           | oid                         | 
 index_oid           | oid                         | 
 schema_name         | character varying(2000)     | 
 table_name          | character varying(2000)     | 
 index_name          | character varying(2000)     | 
 idx_scan            | bigint                      | 
 idx_tup_read        | bigint                      | 
 idx_tup_fetch       | bigint                      | 
 idx_blks_read       | bigint                      | 
 idx_blks_hit        | bigint                      | 
 idx_scan_delta      | bigint                      | 
 idx_tup_read_delta  | bigint                      | 
 idx_tup_fetch_delta | bigint                      | 
 idx_blks_read_delta | bigint                      | 
 idx_blks_hit_delta  | bigint                      | 

1.10. pev_sequences_his

  表 pev_sequences_his 周期性拍摄全数据库簇的序列统计信息快照并进行增量计算。

postgres=# \d pev.pev_sequences_his
               Table "pev.pev_sequences_his"
     Column      |            Type             | Modifiers 
-----------------+-----------------------------+-----------
 snap_id         | bigint                      | 
 sample_time     | timestamp without time zone | 
 sequence_oid    | oid                         | 
 schema_name     | character varying(2000)     | 
 sequence_name   | character varying(2000)     | 
 blks_read       | bigint                      | 
 blks_hit        | bigint                      | 
 blks_read_delta | bigint                      | 
 blks_hit_delta  | bigint                      | 

1.11. pev_functions_his

  表 pev_functions_his 周期性拍摄全数据库簇的函数统计信息快照并进行增量计算。

postgres=# \d pev.pev_functions_his
                 Table "pev.pev_functions_his"
       Column        |            Type             | Modifiers 
---------------------+-----------------------------+-----------
 snap_id             | bigint                      | 
 sample_time         | timestamp without time zone | 
 function_oid        | oid                         | 
 schema_name         | character varying(2000)     | 
 function_name       | character varying(2000)     | 
 calls               | bigint                      | 
 total_time_ms       | bigint                      | 
 self_time_ms        | bigint                      | 
 calls_delta         | bigint                      | 
 total_time_ms_delta | bigint                      | 
 self_time_ms_delta  | bigint                      | 

1.12. pev_bgwriter_his

  表 pev_bgwriter_his 周期性拍摄后台写进程统计信息快照并进行增量计算。

postgres=# \d pev.pev_bgwriter_his
                       Table "pev.pev_bgwriter_his"
             Column             |            Type             | Modifiers 
--------------------------------+-----------------------------+-----------
 snap_id                        | bigint                      | 
 sample_time                    | timestamp without time zone | 
 checkpoints_timed              | bigint                      | 
 checkpoints_req                | bigint                      | 
 checkpoint_write_time_ms       | bigint                      | 
 checkpoint_sync_time_ms        | bigint                      | 
 buffers_checkpoint             | bigint                      | 
 buffers_clean                  | bigint                      | 
 maxwritten_clean               | bigint                      | 
 buffers_backend                | bigint                      | 
 buffers_backend_fsync          | bigint                      | 
 buffers_alloc                  | bigint                      | 
 checkpoints_timed_delta        | bigint                      | 
 checkpoints_req_delta          | bigint                      | 
 checkpoint_write_time_ms_delta | bigint                      | 
 checkpoint_sync_time_ms_delta  | bigint                      | 
 buffers_checkpoint_delta       | bigint                      | 
 buffers_clean_delta            | bigint                      | 
 maxwritten_clean_delta         | bigint                      | 
 buffers_backend_delta          | bigint                      | 
 buffers_backend_fsync_delta    | bigint                      | 
 buffers_alloc_delta            | bigint                      | 

1.13. pev_archiver_his

  表 pev_archiver_his 周期性拍摄归档进程统计信息快照并进行增量计算。

postgres=# \d pev.pev_archiver_his
                  Table "pev.pev_archiver_his"
        Column        |            Type             | Modifiers 
----------------------+-----------------------------+-----------
 snap_id              | bigint                      | 
 sample_time          | timestamp without time zone | 
 archived_count       | bigint                      | 
 last_archived_wal    | character varying(2000)     | 
 last_archived_time   | timestamp without time zone | 
 failed_count         | bigint                      | 
 last_failed_wal      | character varying(2000)     | 
 last_failed_time     | timestamp without time zone | 
 archived_count_delta | bigint                      | 
 failed_count_delta   | bigint                      | 

1.14. pev_sql && pev_sql_plan

  表 pev_sql 及 pev_sql_plan 记录了所有执行过的 SQL 及其执行计划信息,用于关联使用。

postgres=# \d pev.pev_sql
      Table "pev.pev_sql"
 Column  |  Type   | Modifiers 
---------+---------+-----------
 queryid | bigint  | 
 dbid    | integer | 
 query   | text    | 
 iftemp  | boolean | 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
postgres=# \d pev.pev_sql_plan
   Table "pev.pev_sql_plan"
 Column  |  Type  | Modifiers 
---------+--------+-----------
 queryid | bigint | 
 planid  | bigint | 
 plan    | text   | 

1.15. pev_setting

  表 pev_setting 用于进行整个模块的参数配置,修改后10秒内自动生效。

postgres=# select * from pev.pev_setting;
           name            | value |  unit   |                                                                     desp                                                                      
---------------------------+-------+---------+-----------------------------------------------------------------------------------------------------------------------------------------------
 PEV_MAX_SQL               | 10000 | NUMBER  | Max sql stats info to hold, the value should not be less than 1000
 PEV_MAX_SIZE              |  5120 | MB      | Maximum data size held by pev module, automatic cleaning when exceeding, the value should not be less than 1024
 PEV_TRACK_UTILITY_SQL     |     0 | -       | Whether the PEV module tracks utility SQL, Non-0 means yes
 PEV_SQL_TRACK_LEVEL       |     1 | -       | PEV module track level of SQL statements, 0: no tracking, 1: track top level SQL, 2 track all SQL, including inner nested statements
 PEV_ASH_FREQUENCY         |    30 | SECONED | PEV_ACTIVE_SESSION_HIS gather interval, normal value should not be less than 10 when opening, -1 for close
 PEV_METRICS_FREQUENCY     |    60 | SECONED | PEV_METRICS_HIS gather interval, normal value should not be less than 10 when opening, -1 for close
 PEV_WAIT_EVENTS_FREQUENCY |    60 | SECONED | PEV_WAIT_EVENTS_HIS gather interval, normal value should not be less than 10 when opening, -1 for close
 PEV_LONG_TRXS_FREQUENCY   |    60 | SECONED | PEV_LONG_TRXS_HIS gather interval, normal value should not be less than 10 when opening, -1 for close
 PEV_LONG_LOCKS_FREQUENCY  |    60 | SECONED | PEV_LONG_LOCKS_HIS gather interval, normal value should not be less than 10 when opening, -1 for close
 PEV_DATABASE_FREQUENCY    |   600 | SECONED | PEV_DATABASE_HIS gather interval, normal value should not be less than 60 when opening, -1 for close
 PEV_TABLES_FREQUENCY      |   600 | SECONED | PEV_TABLES_HIS gather interval, normal value should not be less than 60 when opening, -1 for close
 PEV_INDEXES_FREQUENCY     |   600 | SECONED | PEV_INDEXES_HIS gather interval, normal value should not be less than 60 when opening, -1 for close
 PEV_SEQUENCES_FREQUENCY   |   600 | SECONED | PEV_SEQUENCES_HIS gather interval, normal value should not be less than 60 when opening, -1 for close
 PEV_FUNCTIONS_FREQUENCY   |   600 | SECONED | PEV_FUNCTIONS_HIS gather interval, normal value should not be less than 60 when opening, -1 for close
 PEV_BGWRITER_FREQUENCY    |   600 | SECONED | PEV_BGWRITER_HIS gather interval, normal value should not be less than 60 when opening, -1 for close
 PEV_ARCHIVER_FREQUENCY    |   600 | SECONED | PEV_ARCHIVER_HIS gather interval, normal value should not be less than 60 when opening, -1 for close
 PEV_SQL_STATS_FREQUENCY   |   600 | SECONED | PEV_SQL_STATS_HIS gather interval, normal value should not be less than 60 when opening, -1 for close
 PEV_MIN_TIME              |     1 | DAY     | Minimum data dwell time held by the PEV module, priority is higher than PEV_MAX_SIZE in garbage cleaning, the value should not be less than 1

2. GUI工具

  官方提供了名为 pev.exe 的客户端 GUI 工具,对上述系统表的使用方法进行了封装,这也是最方便快捷的使用方式。在一台可连接到数据库的 windows 设备上可直接运行使用。

  可见其涵盖了所有需要关注的重点信息,从上至下分别是:负载指标、历史等待事件、实时等待事件、活跃进程信息(可切换超时锁、长事务、TOP SQL、TOP 客户端、历史超时锁、历史长事务、TOP 数据库、TOP 表、TOP 索引、写进程状态、归档进程状态等)以及曲线图。

  • 双击 QUERYID 或 PLANID 进入 SQL 详情页,可查看其于指定时段所使用的执行计划、等待事件、客户端信息。
  • 双击 PID 进入进程详情页,可查看其当前的具体动作及状态,并可一键 KILL 该进程。
  • 双击任意蓝色项可在底部曲线图处显示其在指定时间段内的变化曲线

3. 安装方式

下载链接:

    官方通道:pev@catinfo.com.cn

    PG官网通道:https://www.postgresql.org/download/products/6-postgresql-extensions/

安装步骤:

    1. 下载对应自身PG版本的压缩包并解压(如 pg_enterprise_views_pg10.x_v20230423_linux_x64(64-bit).zip)

    2. 将 pg_enterprise_views.so 文件放入PG安装目录的 lib 目录下(一般为:/usr/local/pgsql/lib/)

    3. 将 pg_enterprise_views--1.0.sql 及 pg_enterprise_views.control 文件放置于PG安装目录的 extension 目录下(一般为:/usr/local/pgsql/share/extension)

    4. 修改配置文件 postgresql.conf:shared_preload_libraries = 'pg_enterprise_views'

    5. 重启 PG 数据库

    6. psql 执行命令(必须是 postgres 库):create extension pg_enterprise_views;

免费许可:

    免费许可至2024年5月1日(支持24小时历史数据回溯)。

    查看许可信息:select pev.pev_register_info();

    企业版激活(需激活码):select pev.pev_register('Activation Code');

这篇关于PostgreSQL一站式插件推荐 -- pg_enterprise_views的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!