PostgreSQL統計資訊的幾個重要檢視

1、pg_stat_database

yzs=# select *from pg_stat_database;
-[ RECORD 1 ]--+------------------------------
datid          | 13156                            #資料庫的oid
datname        | postgres                         #資料庫名
numbackends    | 0                                #訪問當前資料庫的連線數量
xact_commit    | 2357                             #該資料庫事務提交總量：和下面的rollback和作為TPS統計
xact_rollback  | 17                               #該資料庫事務rollback總量，如果特別多，需要看業務是否有問題了
blks_read      | 1946                             #總磁碟物理讀的塊數，這裡的read可能是從 cache中讀取，如果很高需要結合blk_read_time看是否真的存在從磁碟讀取的情況
blks_hit       | 103625                           #從shared buffer命中塊數
tup_returned   | 1413113                          #對於表來說，是全表掃描的行數；對於索引是透過索引返回的索引行數，如果這個值明顯大於tup_fetched，說明當前資料庫存在大量的全表掃描。檢視執行計劃，這個是databas全域性級別的
tup_fetched    | 36041                            #指透過索引返回的行數
tup_inserted   | 104                              #插入的行數
tup_updated    | 0                                #更新的行數
tup_deleted    | 19                               #刪除的行數
conflicts      | 0                                #與恢復衝突取消的查詢次數，只會在備機上發生
temp_files     | 0                                #產生臨時檔案的數量，如果這個值很高，需要調大work_mem
temp_bytes     | 0                                #臨時檔案的大小
deadlocks      | 0                                #死鎖的數量，如果這個值很大說明業務邏輯有問題
blk_read_time  | 0                                #資料庫中花費在讀取檔案的時間，這個值很高說明記憶體較小，需要頻繁從磁碟讀入資料檔案
blk_write_time | 0                                #資料庫中花費在寫資料檔案的時間，pg中髒頁一般寫入page cache，如果這個值較高，則說明cache較小，作業系統的cache需要更積極的寫入
stats_reset    | 2019-02-11 23:42:37.526743-08    #統計資訊重置的時間

透過pg_stat_database可以大概瞭解資料庫的歷史情況。

比如tup_returned值明顯大於tup_fetched，歷史SQL語句很多是全表掃描，存在沒有使用索引的SQL，可結合pg_stat_statments查詢慢SQL，也可結合pg_stat_user_table找全表掃描次數和行數最多的表；

透過看tup_updated很高，可以說明資料庫有頻繁的更新，這個時候需要關注vaccum相關的指標和長事務，如果沒有及時進行垃圾回收，會引起表膨脹；

temp_files較高說明存在很多排序，hash,或者聚合這種操作，可以增大work_mem減少臨時檔案的產生，並且同時這些操作的效能也會有較大的提升。

2、pg_stat_user_tables

yzs=# select *from pg_stat_user_tables;
-[ RECORD 1 ]-------+------------------------------
relid               | 16440        #表oid
schemaname          | public       #模式名
relname             | t1           #表名
seq_scan            | 50           #這個表進行全表掃描的次數
seq_tup_read        | 1867763      #全表掃描的資料行數，如果這個值很大說明操作這個表的SQL語句很可能是全表掃描，需要結合執行計劃分析
idx_scan            |              #索引掃描的次數
idx_tup_fetch       |              #透過索引掃描返回的行數
n_tup_ins           | 1130502      #插入的資料行數
n_tup_upd           | 0            #更新的資料行數
n_tup_del           | 81920        #刪除的資料行數
n_tup_hot_upd       | 0            #hot update的資料行數，這個值與n_tup_upd接近說明更新效能較好，不需要更新索引
n_live_tup          | 655366       #活的行數量
n_dead_tup          | 0            #死記錄個數
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次數
autovacuum_count    | 0            #自動vacuum次數
analyze_count       | 0            #analyze次數
autoanalyze_count   | 10           #自動analyze次數

透過查詢pg_stat_user_tables，可以基本清除哪些表的全表掃描次數較多，表中DML哪種操作多，也可以瞭解垃圾資料的數量。

3、pg_stat_user_indexes

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

可以知道當前哪些索引頻繁使用，哪些是無效索引。無效索引可以刪除掉，減少磁碟空間的使用和提升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或磁碟讀取表的塊數
heap_blks_hit   | 1    #從shared buffer命中的塊數
idx_blks_read   | 0    #從page cache或磁碟讀取的索引的塊數
idx_blks_hit    | 0    #從shared buffer命中的索引塊數
toast_blks_read |      #從page cache或磁碟讀取的toast表的塊數
toast_blks_hit  |      #在shared buffer中命中toast表的塊數
tidx_blks_read  |      #從page cache或者磁碟中讀入的toast表索引的塊數
tidx_blks_hit   |      #在shared buffer中命中toast表索引的塊數

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

5、 pg_stat_bgwriter

yzs=# select *from pg_stat_bgwriter;
-[ RECORD 1 ]---------+------------------------------
checkpoints_timed     | 206     #指超過checkpoint_timeout的時間後觸發的檢查點次數
checkpoints_req       | 8       #手動觸發checkpoint或者因為WAL檔案數量達到max_wal_size時也會增加，如果這個值大於checkpoints_req說明checkpoint_timeout設定的不合理
checkpoint_write_time | 306582  #從shared buffer 中write到page cache花費的時間
checkpoint_sync_time  | 367     #checkpoint呼叫fsync將髒資料刷到磁碟花費的時間，如果這個值很長，容易造成IO抖動，需要增加checkpoint_timeout或者checkpoint_completion_target
buffers_checkpoint    | 6671    #透過checkpoint寫入髒塊的數量
buffers_clean         | 0       #透過bgwriter寫入塊的數量
maxwritten_clean      | 0       #bgwriter超過bgwriter_lru_maxpages時停止的次數，如果這個值很高，需要增加bgwriter_lru_maxpages
buffers_backend       | 7953    #透過backend寫入的塊數量
buffers_backend_fsync | 0       #backend需要fsync的次數
buffers_alloc         | 11613   #被分配的緩衝區數量
stats_reset           | 2019-02-11 23:42:35.273758-08

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