PostgreSQL統(tǒng)計信息的幾個重要視圖

1、pg_stat_database

yzs=# select *from pg_stat_database;
-[ RECORD 1 ]--+------------------------------
datid          | 13156                            #數(shù)據(jù)庫的oid
datname        | postgres                         #數(shù)據(jù)庫名
numbackends    | 0                                #訪問當(dāng)前數(shù)據(jù)庫的連接數(shù)量
xact_commit    | 2357                             #該數(shù)據(jù)庫事務(wù)提交總量：和下面的rollback和作為TPS統(tǒng)計
xact_rollback  | 17                               #該數(shù)據(jù)庫事務(wù)rollback總量，如果特別多，需要看業(yè)務(wù)是否有問題了
blks_read      | 1946                             #總磁盤物理讀的塊數(shù)，這里的read可能是從 cache中讀取，如果很高需要結(jié)合blk_read_time看是否真的存在從磁盤讀取的情況
blks_hit       | 103625                           #從shared buffer命中塊數(shù)
tup_returned   | 1413113                          #對于表來說，是全表掃描的行數(shù)；對于索引是通過索引返回的索引行數(shù)，如果這個值明顯大于tup_fetched，說明當(dāng)前數(shù)據(jù)庫存在大量的全表掃描。查看執(zhí)行計劃，這個是databas全局級別的
tup_fetched    | 36041                            #指通過索引返回的行數(shù)
tup_inserted   | 104                              #插入的行數(shù)
tup_updated    | 0                                #更新的行數(shù)
tup_deleted    | 19                               #刪除的行數(shù)
conflicts      | 0                                #與恢復(fù)沖突取消的查詢次數(shù)，只會在備機上發(fā)生
temp_files     | 0                                #產(chǎn)生臨時文件的數(shù)量，如果這個值很高，需要調(diào)大work_mem
temp_bytes     | 0                                #臨時文件的大小
deadlocks      | 0                                #死鎖的數(shù)量，如果這個值很大說明業(yè)務(wù)邏輯有問題
blk_read_time  | 0                                #數(shù)據(jù)庫中花費在讀取文件的時間，這個值很高說明內(nèi)存較小，需要頻繁從磁盤讀入數(shù)據(jù)文件
blk_write_time | 0                                #數(shù)據(jù)庫中花費在寫數(shù)據(jù)文件的時間，pg中臟頁一般寫入page cache，如果這個值較高，則說明cache較小，操作系統(tǒng)的cache需要更積極的寫入
stats_reset    | 2019-02-11 23:42:37.526743-08    #統(tǒng)計信息重置的時間

通過pg_stat_database可以大概了解數(shù)據(jù)庫的歷史情況。
比如tup_returned值明顯大于tup_fetched，歷史SQL語句很多是全表掃描，存在沒有使用索引的SQL，可結(jié)合pg_stat_statments查找慢SQL，也可結(jié)合pg_stat_user_table找全表掃描次數(shù)和行數(shù)最多的表；
通過看tup_updated很高，可以說明數(shù)據(jù)庫有頻繁的更新，這個時候需要關(guān)注vaccum相關(guān)的指標(biāo)和長事務(wù)，如果沒有及時進行垃圾回收，會引起表膨脹；
temp_files較高說明存在很多排序，hash,或者聚合這種操作，可以增大work_mem減少臨時文件的產(chǎn)生，并且同時這些操作的性能也會有較大的提升。

2、pg_stat_user_tables

yzs=# select *from pg_stat_user_tables;
-[ RECORD 1 ]-------+------------------------------
relid               | 16440        #表oid
schemaname          | public       #模式名
relname             | t1           #表名
seq_scan            | 50           #這個表進行全表掃描的次數(shù)
seq_tup_read        | 1867763      #全表掃描的數(shù)據(jù)行數(shù)，如果這個值很大說明操作這個表的SQL語句很可能是全表掃描，需要結(jié)合執(zhí)行計劃分析
idx_scan            |              #索引掃描的次數(shù)
idx_tup_fetch       |              #通過索引掃描返回的行數(shù)
n_tup_ins           | 1130502      #插入的數(shù)據(jù)行數(shù)
n_tup_upd           | 0            #更新的數(shù)據(jù)行數(shù)
n_tup_del           | 81920        #刪除的數(shù)據(jù)行數(shù)
n_tup_hot_upd       | 0            #hot update的數(shù)據(jù)行數(shù)，這個值與n_tup_upd接近說明更新性能較好，不需要更新索引
n_live_tup          | 655366       #活的行數(shù)量
n_dead_tup          | 0            #死記錄個數(shù)
n_mod_since_analyze | 6            #上次analyze的實際
last_vacuum         | 2019-04-07 00:22:00.955542-07 #上次手動vacuum的實際
last_autovacuum     |              #上次autovacuum的實際
last_analyze        |              #上次analyze時間
last_autoanalyze    | 2019-04-07 00:26:07.668391-07 #上次自動analyze時間
vacuum_count        | 2            #vacuum次數(shù)
autovacuum_count    | 0            #自動vacuum次數(shù)
analyze_count       | 0            #analyze次數(shù)
autoanalyze_count   | 10           #自動analyze次數(shù)

通過查詢pg_stat_user_tables，可以基本清除哪些表的全表掃描次數(shù)較多，表中DML哪種操作多，也可以了解垃圾數(shù)據(jù)的數(shù)量。

3、pg_stat_user_indexes

yzs=# select *from pg_stat_user_indexes;
-[ RECORD 1 ]-+----------
relid         | 16447      #相關(guān)表的oid
indexrelid    | 16450      #索引的oid
schemaname    | public     #模式名
relname       | t3         #表名
indexrelname  | t3_id_idx  #索引名
idx_scan      | 0          #通過索引掃描的次數(shù)，如果該值很小，說明該索引很少被用到，可以考慮刪除
idx_tup_read  | 0          #通過任意索引方法返回的索引行數(shù)
idx_tup_fetch | 0          #通過索引方法返回的數(shù)據(jù)行數(shù)

可以知道當(dāng)前哪些索引頻繁使用，哪些是無效索引。無效索引可以刪除掉，減少磁盤空間的使用和提升insert、delete、update的性能。

4、pg_statio_user_tables

yzs=# select *from pg_statio_user_tables;
-[ RECORD 1 ]---+--------
relid           | 16447    
schemaname      | public
relname         | t3
heap_blks_read  | 1    #從page cache或磁盤讀取表的塊數(shù)
heap_blks_hit   | 1    #從shared buffer命中的塊數(shù)
idx_blks_read   | 0    #從page cache或磁盤讀取的索引的塊數(shù)
idx_blks_hit    | 0    #從shared buffer命中的索引塊數(shù)
toast_blks_read |      #從page cache或磁盤讀取的toast表的塊數(shù)
toast_blks_hit  |      #在shared buffer中命中toast表的塊數(shù)
tidx_blks_read  |      #從page cache或者磁盤中讀入的toast表索引的塊數(shù)
tidx_blks_hit   |      #在shared buffer中命中toast表索引的塊數(shù)

如果heap_blks_read、idx_blks_read很高，說明shared buffer較小，存在頻繁從磁盤或者page cache讀取到shared buffer中命中toast表的塊數(shù)。

5、?pg_stat_bgwriter

yzs=# select *from pg_stat_bgwriter;
-[ RECORD 1 ]---------+------------------------------
checkpoints_timed     | 206     #指超過checkpoint_timeout的時間后觸發(fā)的檢查點次數(shù)
checkpoints_req       | 8       #手動觸發(fā)checkpoint或者因為WAL文件數(shù)量達到max_wal_size時也會增加，如果這個值大于checkpoints_req說明checkpoint_timeout設(shè)置的不合理
checkpoint_write_time | 306582  #從shared buffer 中write到page cache花費的時間
checkpoint_sync_time  | 367     #checkpoint調(diào)用fsync將臟數(shù)據(jù)刷到磁盤花費的時間，如果這個值很長，容易造成IO抖動，需要增加checkpoint_timeout或者checkpoint_completion_target
buffers_checkpoint    | 6671    #通過checkpoint寫入臟塊的數(shù)量
buffers_clean         | 0       #通過bgwriter寫入塊的數(shù)量
maxwritten_clean      | 0       #bgwriter超過bgwriter_lru_maxpages時停止的次數(shù)，如果這個值很高，需要增加bgwriter_lru_maxpages
buffers_backend       | 7953    #通過backend寫入的塊數(shù)量
buffers_backend_fsync | 0       #backend需要fsync的次數(shù)
buffers_alloc         | 11613   #被分配的緩沖區(qū)數(shù)量
stats_reset           | 2019-02-11 23:42:35.273758-08

通過這個視圖，可以判斷checkpoint以及max_wal_size是否合理