最新要闻
- 两女孩砸成人用品售卖机盗走商品，得手后大笑离去
- 把iP15 Pro Max比下去了！iPhone16 Pro Max升级苹果史上最大屏
- 段永平再评“哲库事件”：长远看不合适的、最好现在就停下来！
- 要闻速递：谷歌AI公司创始人看衰传统搜索引擎：十年内将消亡
- 天天观焦点：国产安卓手机成功超越苹果了吗？这问题其实很复杂
- 每日看点!毕业生收Offer租房后遭HR毁约拉黑：电话也不接了
- 天天速读：百超（深圳）中标“核电粮仓”激光切割机及服务项目
- 环球最新：5月18日杜甫草堂免费门票老年人要预约么?
- 被视为格力“第二个董明珠”的孟羽童被开除：人设崩了？|播报
- 今日要闻!美国上路私家车平均车龄12.5年 创20年来新高！原因现实
- 焦点消息！AMD Zen5全家泄露：192核力压Intel 144核！1.5GB缓存无敌
- 全球热点评！丰田卡罗拉高速错过出口强行变道：后方货车紧急制动轮冒青烟
- 中国移动也能捅破天 5G手机可直连卫星通信_每日观察
- 边走边拍⑨一环路烟火人间“串”起 地道成都味|观热点
- 换皮系统实锤了？Win11曝出Win8老版本残留
- 新动态：被观众喷爆！《埃及艳后》成网飞评价最差剧集之一
广告
手机
iphone11大小尺寸是多少？苹果iPhone11和iPhone13的区别是什么？
警方通报辅警执法直播中被撞飞：犯罪嫌疑人已投案
- iphone11大小尺寸是多少？苹果iPhone11和iPhone13的区别是什么？
- 警方通报辅警执法直播中被撞飞：犯罪嫌疑人已投案
- 男子被关545天申国赔：获赔18万多 驳回精神抚慰金
- 3天内26名本土感染者，辽宁确诊人数已超安徽
- 广西柳州一男子因纠纷杀害三人后自首
- 洱海坠机4名机组人员被批准为烈士 数千干部群众悼念
家电
天天即时：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 | postgres1.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 started1.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 12. 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
pg_enterprise_views是一款免费的企业级PG运维管理插件，为开源PG提供一站式成体系的运维能力。
来源： -
-
热门：Booking.com如何在毫秒内搜索数百万个地点
译自：HowBooking comSearchesThroughMillionsofLocationsinMillisecondsBooking com是一家与酒店、旅馆、
来源： -
天天即时：PostgreSQL一站式插件推荐 -- pg_enterprise_views
30Wqps+闲鱼优惠中台，如何架构的？_天天快播
热门：Booking.com如何在毫秒内搜索数百万个地点
记录--10个超级实用的Set、Map使用技巧-每日时讯
世界短讯！腾讯开源的 3 个 GitHub 项目，足够良心！
两女孩砸成人用品售卖机盗走商品，得手后大笑离去
把iP15 Pro Max比下去了！iPhone16 Pro Max升级苹果史上最大屏
段永平再评“哲库事件”：长远看不合适的、最好现在就停下来！
要闻速递：谷歌AI公司创始人看衰传统搜索引擎：十年内将消亡
天天观焦点：国产安卓手机成功超越苹果了吗？这问题其实很复杂
每日看点!毕业生收Offer租房后遭HR毁约拉黑：电话也不接了
天天速读：百超（深圳）中标“核电粮仓”激光切割机及服务项目
js深拷贝
Red Hat Linux安装CentOS的yum源
基于C#和Blazor开发的前后端分离框架
AI女友同时和1000人谈恋爱，狂赚500万
AspNet Core MVC项目接入的登录页验证码（一）
环球最新：5月18日杜甫草堂免费门票老年人要预约么?
恒生指数16日收涨0.04% 互联网科技股集体走强
被视为格力“第二个董明珠”的孟羽童被开除：人设崩了？|播报
今日要闻!美国上路私家车平均车龄12.5年 创20年来新高！原因现实
焦点消息！AMD Zen5全家泄露：192核力压Intel 144核！1.5GB缓存无敌
全球热点评！丰田卡罗拉高速错过出口强行变道：后方货车紧急制动轮冒青烟
中国移动也能捅破天 5G手机可直连卫星通信_每日观察
边走边拍⑨一环路烟火人间“串”起 地道成都味|观热点
微资讯！浅析AIGC for MMKG
全球球精选！C++ | const修饰的变量
焦点快播：谈谈Spring IOC容器的注入方式
换皮系统实锤了？Win11曝出Win8老版本残留
新动态：被观众喷爆！《埃及艳后》成网飞评价最差剧集之一
今日热讯：1032km续航全球量产车第一！极氪001千里续航套装版开启交付
天天快消息！世卫组织建议勿用非糖甜味剂控制体重：会导致2型糖尿病、甚至死亡
苹果Siri即将大升级！体验巨变|全球简讯
博主每天都在纠结选小米13 Pro还是13 Ultra！雷军给出答案
世界最新：美国“悔棋翻盘”破坏多边经贸秩序——起底美国贸易战反智本质述评之三
全球快看点丨统计学习方法笔记-感知机学习方法
今日热文：易基因：多组学关联分析及组学分子实验验证方法（表观组+转录组+微生物组）｜干货系列
AHB2APB bridge IP使用
头条：中秋节快乐贺词简短四字_中秋节快乐贺词
弯道超车？无需光刻工艺的处理器来了：成本暴降
重大突破！全国首例心脏不停跳人工心脏植入：患者恢复良好-世界视点
每日报道：21人合买 中2574万彩票摊主已转让摊位：4万就转手
iQOO Neo8系列将支持Wi-Fi 7：峰值速率高达3.6Gbps 焦点信息
网传迪士尼乐园将落户成都 官方回应：暂未收到选址情况 世界时讯
抓好消防安全 保高质量发展_热点
@Getter cheated me
关于忘记kubernetes-master节点的token以及让新node节点加入k8s集群的命令的处理方法
风靡全球20年 “最美奔驰轿车”CLS宣布停产_讯息
电源撑得住吗？AMD CPU功耗未来将达600W 比Intel还高100W 视讯
广东河源2023年1月自考延期考试成绩查询时间：5月11日15时
C#异步方法async/await的三种返回类型
PhotoShop的平替: GIMP
雪榕生物：雪榕源已于2023年第一季度推出产品，具体您可以通过抖音“雪榕膳食旗舰店”详细了解
准点下班被辞退仲裁胜诉 当事人：为讨领导开心的无效加班真没必要
用上“最黑的黑” 比亚迪汉DM-p战神版实拍：3.7秒加速的性能怪兽 世界热门
热点聚焦：喝的人越来越少！星巴克中国最贵单品更换原料但不官宣 网友炮轰降本不降价
国产Chiplet小芯片工艺稳定量产 国际客户4nm工艺产品已出货 焦点日报
微软690亿美元收购动视暴雪交易获欧盟批准
阿里云数据库ClickHouse产品和技术解读
高效联调，可靠发布！华为云推出CodeArts Release发布管理服务
玩转Zabbix智能告警：降噪、排班、认领、升级、IM协同
机器学习服务语音合成，解锁智能养娃新趋势|速看
注册会计师报考费用2023
【新要闻】年轻人越来越吃不起鸭脖？女子武汉坐高铁满车人都在啃鸭脖 直言被馋哭
出海国产手游战绩：米哈游超腾讯 《原神》第一、《崩坏：星穹铁道》第七 天天快看点
管住嘴果然有用！科学家：保持饥饿感或可延缓衰老 每日时讯
世界观点：国产电动车反攻欧洲 法国急忙出手限制：砍掉3.8万元补贴
迪卡侬双11促销虚标原价被罚！网友大赞：消费者苦假促销久矣-每日头条
史上最全 Activiti 学习教程，一文搞定最强工作流引擎！ 天天快消息
可观测性三支柱？远不止此！|全球快播报
每日观察!Linux-特殊权限设置（SUID、SGID、SBIT）
智能补全音源损失！索尼蓝牙耳机暴降130元
“北向互换通”上线 人民币国际化再提速-资讯
国家统计局：4月份社会消费品零售总额同比增长18.4%
当前时讯：罕见！抗阿尔茨海默病“第二人”出现
速降35℃！黑鲨冰封散热背夹3 Pro预售 首销199元_当前热议
世界今日讯！4000元档良心所在 Redmi Book 14处理器公布：45W标压12代酷睿
16GB大内存稳了！iQOO Neo8 Pro官宣搭载性能铁三角 最资讯
世界新动态：就看这款了！蔚来全新ES6上市定档：“高端中型SUV天花板”登场
蔚来起诉网红索赔500万！公关总监：绝不姑息造谣、污蔑行为
尘埃歌词我的爱像尘埃在线试听 尘埃歌词我的爱像尘埃
pg_enterprise_views安装及使用
JS中的偏函数
虚拟机网络配置_全球资讯
天天即时看！华东医药：5月15日融资买入1046.79万元，融资融券余额9.64亿元
暴汗燃脂！腾讯首款体感健身应用《舞动极光》发布：手机+电视就能玩
速读：一键开盖 耐热抗摔：哈尔斯Tritan材质单手开盖水杯14.9元发车
《王者荣耀》第四款敦煌皮肤、八周年限定皮肤都来了_环球热门
多地气温超36℃ 今夏还会“热哭”吗？专家称没有去年热 今日热闻
技术不成熟 无人驾驶汽车在旧金山“疯狂搞事”：还拦不住-快消息
时讯：国家网信办：集中清理仿冒新闻单位、新闻主播账号10.7万个
狂神说ngnix笔记（附md文件下载链接）
前沿资讯!Upstart (UPST.US)向私人信贷商店Castlelake出售40亿美元消费分期贷款 股价涨超20%
今晚油价或迎年内“最大跌幅”！95号汽油有望回归7元时代
天天热门:当事人回应10万元买8套房：是特列但房价确实便宜 我买房只看价格
当前快报:50TB仅398元！国产小龙盘升级：新增联网模式AI聊天
张艺谋《满江红》最终票房45.44亿 位列中国影史第6位
焦点简讯:中银香港完成首批“北向互换通”交易
【读财报】科创板一季度业绩速览：近七成公司实现盈利 机械、通信等行业表现突出 今日聚焦
天天看点：我市统计承诺实践创新获评国家优秀案例