近日發現PG官方插件列表中新收錄了一款插件 pg_enterprise_views，因為官方已經數年未添新的插件了很是新奇，找了台設備測試過後果斷上了生產，得空分享給大家。 該插件提供了數十張系統表及一個GUI工具，用以監控從操作系統到資料庫方方面面的性能情況，並支持對任意時段歷史數據的回溯，基本等 ...
近日發現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. 安裝方式
下載鏈接：
官方通道：[email protected]
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');
