PostgreSQL , track_counts , 統計資訊 , pg_stat_reset
PostgreSQL資料庫的statstic子產品有一些計數器,用于統計每個表被插入、更新、删除的記錄數。
通過這些視圖,可以檢視計數器統計到的一些計數:
例如表相關的計數:
檢視某張表的計數,例如
通過reset函數,可以對這些計數清零。
Function
Return Type
Description
pg_stat_reset()
void
Reset all statistics counters for the current database to zero (requires superuser privileges by default, but EXECUTE for this function can be granted to others.)
pg_stat_reset_shared(text)
Reset some cluster-wide statistics counters to zero, depending on the argument (requires superuser privileges by default, but EXECUTE for this function can be granted to others). Calling pg_stat_reset_shared('bgwriter') will zero all the counters shown in the pg_stat_bgwriter view. Calling pg_stat_reset_shared('archiver') will zero all the counters shown in the pg_stat_archiver view.
pg_stat_reset_single_table_counters(oid)
Reset statistics for a single table or index in the current database to zero (requires superuser privileges by default, but EXECUTE for this function can be granted to others)
pg_stat_reset_single_function_counters(oid)
Reset statistics for a single function in the current database to zero (requires superuser privileges by default, but EXECUTE for this function can be granted to others)
清零有什麼後果呢?
autovacuum launcher程序,在一個autovacuum_naptime周期内,輪詢所有的database内的計數,并根據計數以及設定的門檻值(表級、或全庫級門檻值)判斷是否需要對表實施vacuum或analyze的動作。
如果計數器被清零,可能無法及時對表進行垃圾回收或analyze。
1、配置參數,便于觀察。
vi postgresql.conf
2、生效參數:pg_ctl reload
3、建立一個測試表
4、觀察日志
5、寫入批量資料
超過自動analyze的門檻值,觀察到自動觸發了analyze。
6、更新批量資料
超過自動vacuum和analyze的門檻值,觀察到自動觸發了vacuum和analyze。
7、更新批量資料,并同時清零計數器。
計數器被清零
計數器清零後,autovacuum不會觸發vacuum和analyze。
計數器清零會影響autovacuum launcher發起vacuum和analyze,導緻一些表實際上已經超過分析或垃圾回收的門檻值,但是不會被觸發。嚴重時,可能導緻表膨脹,或統計資訊不準确。
<a href="https://github.com/digoal/blog/blob/master/201611/20161123_01.md">《PostgreSQL AWR報告》</a>
pg_awr裡面涉及到計數器的預設清理,我會在後期改掉,預設不清理。
<a href="https://github.com/digoal/blog/blob/master/201610/20161018_03.md">《PostgreSQL pg_stat_ pg_statio_ 統計資訊(scan,read,fetch,hit)源碼解讀》</a>
<a href="https://www.postgresql.org/docs/10/static/routine-vacuuming.html#autovacuum">https://www.postgresql.org/docs/10/static/routine-vacuuming.html#autovacuum</a>
<a href="https://www.postgresql.org/docs/10/static/runtime-config-statistics.html#guc-track-counts">https://www.postgresql.org/docs/10/static/runtime-config-statistics.html#guc-track-counts</a>