一文讀懂數倉中的pg_stat

摘要： GaussDB(DWS)在SQL執行過程中，會記錄表增刪改查相關的執行時統計資訊，並在事務提交或回滾後記錄到共享的記憶體中。這些資訊可以通過 “pg_stat_all_tables檢視” 查詢，也可以通過下面函式進行查詢。

本文分享自華為雲社群《 一文讀懂pgstat 》，作者： leapdb 。

GaussDB(DWS)在SQL執行過程中，會記錄表增刪改查相關的執行時統計資訊，並在事務提交或回滾後記錄到共享的記憶體中。

這些資訊可以通過 “pg_stat_all_tables檢視” 查詢，也可以通過下面函式進行查詢。

pg_stat_get_tuples_inserted   --表累積insert條數
pg_stat_get_tuples_updated    --表累積update條數
pg_stat_get_tuples_deleted    --表累積delete條數
pg_stat_get_tuples_changed    --表自上次analyze以來，修改的條數
pg_stat_get_last_analyze_time --查詢最近一次analyze時間

因此，根據共享記憶體中 "表自上次analyze以來修改過的條數" 是否超過一定閾值，就可以判定是否需要做analyze了。

1. 事務中如何記錄統計資訊

每個session有一個PgStat_TableStatus記憶體結構，當前事務訪問過的每個表佔用一個。

其中包含每一層子事務的統計資訊，用結構體PgStat_TableXactStatus描述。這裡記錄了當前子事務增刪改查的各種資訊。

pg_stat_get_xact_numscans --當前事務在當前表上，啟動順序掃描的次數
pg_stat_get_xact_tuples_returned --當前事務在當前表上，順序掃描抓取的可見元組條數。
pg_stat_get_xact_tuples_fetched --當前事務在當前表上，抓取的可見元組條數。
pg_stat_get_xact_tuples_inserted --當前事務在當前表上，插入條數
pg_stat_get_xact_tuples_deleted --當前事務在當前表上，刪除條數
pg_stat_get_xact_tuples_updated --當前事務在當前表上，更新條數
pg_stat_get_xact_tuples_hot_updated --當前事務在當前表上，HOT更新條數
pg_stat_get_xact_blocks_fetched --當前事務在當前表上，選中的頁面數
pg_stat_get_xact_blocks_hit --當前事務在當前表上，掃描過的頁面數
pg_stat_get_xact_function_calls --當前事務在當前函式上，呼叫次數
pg_stat_get_xact_function_total_time --當前事務在當前函式上，所有呼叫的總執行時間
pg_stat_get_xact_function_self_time --當前事務在當前函式上，上次呼叫一次的執行時間
pg_stat_get_xact_partition_tuples_inserted --當前事務在當前表分割槽上，插入條數
pg_stat_get_xact_partition_tuples_deleted --當前事務在當前表分割槽上，刪除條數
pg_stat_get_xact_partition_tuples_updated --當前事務在當前表分割槽上，更新條數
pg_stat_get_xact_partition_tuples_hot_updated --當前事務在當前表分割槽上，HOT更新條數

事務提交時會把當前表，所有子事務中的資訊彙總，並轉儲到pgstat中。

2. 事務外如何記錄統計資訊

事務提交時將事務中的執行資訊通過UDP報文傳送給PgCollector後臺執行緒，由其記錄到統一的記憶體中，並在叢集正常停止時轉儲到物理檔案pg_stat_tmp/pgstat.stat中。

• 例項級資訊，PgStat_GlobalStats
• 庫級資訊，PgStat_StatDBEntry
• 表級資訊，其記憶體結構PgStat_StatTabEntry

--例項級資訊
pg_stat_get_bgwriter_timed_checkpoints --後臺寫程序開啟定時檢查點的次數
pg_stat_get_bgwriter_requested_checkpoints --後臺寫程序開啟基於後端請求的檢查點的次數
pg_stat_get_checkpoint_write_time --最近一次checkpoint寫頁面的時間
pg_stat_get_checkpoint_sync_time --最近一次checkpoint同步頁面的時間
pg_stat_get_bgwriter_buf_written_checkpoints --在檢查點期間後臺寫程序寫入的緩衝區數目。
pg_stat_get_bgwriter_buf_written_clean --為日常清理髒塊，後臺寫程序寫入的緩衝區數目。
pg_stat_get_bgwriter_maxwritten_clean --後臺寫程序停止清理掃描的時間，因為已經寫入了更多的緩衝區（相比bgwriter_lru_maxpages引數宣告的緩衝區數）。
pg_stat_get_buf_written_backend --後端程序寫入的緩衝區數，因為它們需要分配一個新的緩衝區。
pg_stat_get_buf_fsync_backend --後臺程序執行fsync的次數
pg_stat_get_buf_alloc --分配的總緩衝區數。
pg_stat_get_bgwriter_stat_reset_time --後臺bgwriter執行緒的重置時間

--當前CN記錄的庫級資訊
pg_stat_get_db_xact_commit --當前例項上指定資料庫中已提交事務的數量。
pg_stat_get_db_xact_rollback --當前例項上指定資料庫中回滾事務的數量。
pg_stat_get_db_blocks_fetched --當前例項上指定資料庫中磁碟塊抓取請求的數量。
pg_stat_get_db_blocks_hit --當前例項上指定資料庫在緩衝區中找到的請求磁碟塊的數量。
pg_stat_get_db_tuples_returned --當前例項上指定資料庫返回的元祖數量。
pg_stat_get_db_tuples_fetched --當前例項上指定資料庫中讀取的元組數量。
pg_stat_get_db_tuples_inserted --當前例項上指定資料庫中插入的元組數量。
pg_stat_get_db_tuples_updated --當前例項上指定資料庫中更新的元組數量。
pg_stat_get_db_tuples_deleted --當前例項上指定資料庫中刪除的元組數量。
pg_stat_get_db_conflict_all --當前例項上指定資料庫中發生衝突恢復的次數。
pg_stat_get_db_conflict_lock --叢集中所有CN和DN上指定資料庫鎖衝突的總數。在DN上執行該函式，返回當前例項上指定資料庫中鎖衝突數量。
pg_stat_get_db_deadlocks --當前例項上指定資料庫中死鎖的數量。
pg_stat_get_db_temp_files --當前例項上指定資料庫中建立臨時檔案的個數。
pg_stat_get_db_temp_bytes --當前例項上指定資料庫中建立臨時檔案的位元組數。
pg_stat_get_db_blk_read_time --當前例項上指定資料庫中讀資料塊所用的時間。
pg_stat_get_db_blk_write_time --當前例項上指定資料庫中寫資料塊所用的時間。

--整個叢集記錄的庫級資訊
pg_stat_get_db_total_xact_commit --在CN上執行該函式，返回叢集中所有CN上指定資料庫中已提交事務的總數。在DN上執行該函式，返回當前例項上指定資料庫中已提交事務的數量。
pg_stat_get_db_total_xact_rollback --在CN上執行該函式，返回叢集中所有CN上指定資料庫中回滾事務的總數。在DN上執行該函式，返回當前例項上指定資料庫中回滾事務的數量。
pg_stat_get_db_total_blocks_fetched --在CN上執行該函式，返回叢集中所有DN上指定資料庫中磁碟塊抓取請求的總數。在DN上執行該函式，返回當前例項上指定資料庫中磁碟塊抓取請求的數量。
pg_stat_get_db_total_blocks_hit --在CN上執行該函式，返回叢集中所有DN上指定資料庫在緩衝區中找到的請求磁碟塊的總數。在DN上執行該函式，返回當前例項上指定資料庫在緩衝區中找到的請求磁碟塊的數量。
pg_stat_get_db_total_tuples_returned --在CN上執行該函式，返回叢集中所有DN上指定資料庫返回的元組總數。在DN上執行該函式，返回當前例項上指定資料庫返回的元組數量。
pg_stat_get_db_total_tuples_fetched --在CN上執行該函式，返回叢集中所有DN上指定資料庫讀取的元組總數。在DN上執行該函式，返回當前例項上指定資料庫讀取的元組數量。
pg_stat_get_db_total_tuples_inserted --在CN上執行該函式，返回叢集中所有DN上指定資料庫插入的元組總數。在DN上執行該函式，返回當前例項上指定資料庫插入的元組數量。
pg_stat_get_db_total_tuples_updated --在CN上執行該函式，返回叢集中所有DN上指定資料庫更新的元組總數。在DN上執行該函式，返回當前例項上指定資料庫更新的元組數量。
pg_stat_get_db_total_tuples_deleted --在CN上執行該函式，返回叢集中所有DN上指定資料庫刪除的元組總數。在DN上執行該函式，返回當前例項上指定資料庫刪除的元組數量。
pg_stat_get_db_total_conflict_all --在CN上執行該函式，返回叢集中所有CN和DN上指定資料庫發生衝突恢復的總次數。在DN上執行該函式，返回當前例項上指定資料庫中發生衝突恢復的次數。
pg_stat_get_db_total_temp_files --在CN上執行該函式，返回叢集中所有DN上指定資料庫中建立臨時檔案的總個數。在DN上執行該函式，返回當前例項上指定資料庫中建立臨時檔案的個數。
pg_stat_get_db_total_temp_bytes --在CN上執行該函式，返回叢集中所有DN上指定資料庫中建立臨時檔案的總位元組數。在DN上執行該函式，返回當前例項上指定資料庫中建立臨時檔案的位元組數。
pg_stat_get_db_total_deadlocks --叢集中所有CN和DN上指定資料庫死鎖的總數。在DN上執行該函式，返回當前例項上指定資料庫中死鎖的數量。
pg_stat_get_db_total_blk_read_time --在CN上執行該函式，返回叢集中所有DN上指定資料庫中讀資料塊所用的總時間。在DN上執行該函式，返回當前例項上指定資料庫中讀資料塊所用的時間。
pg_stat_get_db_total_blk_write_time --在CN上執行該函式，返回叢集中所有DN上指定資料庫中寫資料塊所用的總時間。在DN上執行該函式，返回當前例項上指定資料庫中寫資料塊所用的時間。

--表級資訊
pg_stat_get_numscans --當前表上，啟動順序掃描的次數
pg_stat_get_tuples_returned --當前表上，順序掃描抓取的可見元組條數。
pg_stat_get_tuples_fetched --當前表上，抓取的可見元組條數。
pg_stat_get_tuples_inserted --當前表上，插入條數
pg_stat_get_tuples_deleted --當前表上，刪除條數
pg_stat_get_tuples_updated --當前表上，更新條數
pg_stat_get_tuples_hot_updated --當前表上，HOT更新條數
pg_stat_get_blocks_fetched --當前表上，選中的頁面數
pg_stat_get_blocks_hit --當前表上，掃描過的頁面數
pg_stat_get_function_calls --當前函式上，呼叫次數
pg_stat_get_function_total_time --當前函式上，所有呼叫的總執行時間
pg_stat_get_function_self_time --當前函式上，上次呼叫一次的執行時間
pg_stat_get_partition_tuples_inserted --當前表分割槽上，插入條數
pg_stat_get_partition_tuples_deleted --當前表分割槽上，刪除條數
pg_stat_get_partition_tuples_updated --當前表分割槽上，更新條數
pg_stat_get_partition_tuples_hot_updated --當前表分割槽上，HOT更新條數
pg_stat_get_tuples_changed --當前表上自上一次analyze的歷史累積變化量
pg_stat_get_partition_tuples_changed --當前表分割槽上自上一次analyze的歷史累積變化量
pg_stat_get_partition_live_tuples --當前表分割槽上可見元組數
pg_stat_get_partition_dead_tuples --當前表分割槽上刪除元組數
pg_stat_get_live_tuples --當前表上可見元組數
pg_stat_get_last_vacuum_time --當前表上最近一次vacuum的時間
pg_stat_get_last_data_changed_time --當前表上最近一次資料修改時間
pg_stat_get_last_autovacuum_time --當前表上最近一次autovacuum時間
pg_stat_get_last_autoanalyze_time --當前表上最近一次autoanalyze時間
pg_stat_get_last_analyze_time --當前表上最近一次手動analyze時間

--local表示當前節點的資訊
pg_stat_get_local_tuples_updated
pg_stat_get_local_tuples_inserted
pg_stat_get_local_tuples_hot_updated
pg_stat_get_local_tuples_deleted
pg_stat_get_local_tuples_changed
pg_stat_get_local_live_tuples
pg_stat_get_local_last_autovacuum_time
pg_stat_get_local_last_autoanalyze_time
pg_stat_get_local_dead_tuples
pg_stat_get_local_autovacuum_count
pg_stat_get_local_autoanalyze_count
pg_stat_get_local_analyze_status

函式資訊，PgStat_StatFuncEntry

pg_stat_get_function_calls --函式已被呼叫次數。
pg_stat_get_function_total_time --該函式花費的總掛鐘時間，以微秒為單位。包括花費在此函式呼叫上的時間。
pg_stat_get_function_self_time --在當前事務中僅花費在此函式上的時間。不包括花費在呼叫函式上的時間。

3. 單節點的統計資訊

每個CN和DN節點，各自記錄自己的統計資訊。可以通過上面的函式分別檢視。

另外，CN在增刪改的SQL執行結束時，將各個DN的返回條數資訊記錄到CN自己的pgstat結構中，構成了全域性的統計資訊。

4. 叢集全域性的統計資訊

各個CN各自維護自己的pgstat資訊，因此若想知道叢集全域性的，還需要向所有CN查詢並彙總。

5. 統計資訊的生命週期

pgstat中的統計資訊屬於執行時資訊，這些資訊處於事務外，不嚴格保證資料一致性。

在資料庫異常停止時會清空所有資料，正常停止會保留資料。

6. 統計資訊的維護

這些資訊在執行SQL時被自動記錄，不需要人工進行維護。

點選關注，第一時間瞭解華為雲新鮮技術~