PostgreSQL pg_stat_reset清除track_counts的隱患

標簽

PostgreSQL , track_counts , 統計信息 , pg_stat_reset

背景

PostgreSQL數據庫的statstic模塊有一些計數器，用於統計每個表被插入、更新、刪除的記錄數。

通過這些視圖，可以查看計數器統計到的一些計數：

postgres=# \dv pg_stat*    
                     List of relations    
   Schema   |            Name             | Type |  Owner       
------------+-----------------------------+------+----------    
 pg_catalog | pg_stat_activity            | view | postgres    
 pg_catalog | pg_stat_all_indexes         | view | postgres    
 pg_catalog | pg_stat_all_tables          | view | postgres    
 pg_catalog | pg_stat_archiver            | view | postgres    
 pg_catalog | pg_stat_bgwriter            | view | postgres    
 pg_catalog | pg_stat_database            | view | postgres    
 pg_catalog | pg_stat_database_conflicts  | view | postgres    
 pg_catalog | pg_stat_progress_vacuum     | view | postgres    
 pg_catalog | pg_stat_replication         | view | postgres    
 pg_catalog | pg_stat_ssl                 | view | postgres    
 pg_catalog | pg_stat_subscription        | view | postgres    
 pg_catalog | pg_stat_sys_indexes         | view | postgres    
 pg_catalog | pg_stat_sys_tables          | view | postgres    
 pg_catalog | pg_stat_user_functions      | view | postgres    
 pg_catalog | pg_stat_user_indexes        | view | postgres    
 pg_catalog | pg_stat_user_tables         | view | postgres    
 pg_catalog | pg_stat_wal_receiver        | view | postgres    
 pg_catalog | pg_stat_xact_all_tables     | view | postgres    
 pg_catalog | pg_stat_xact_sys_tables     | view | postgres    
 pg_catalog | pg_stat_xact_user_functions | view | postgres    
 pg_catalog | pg_stat_xact_user_tables    | view | postgres    
 pg_catalog | pg_statio_all_indexes       | view | postgres    
 pg_catalog | pg_statio_all_sequences     | view | postgres    
 pg_catalog | pg_statio_all_tables        | view | postgres    
 pg_catalog | pg_statio_sys_indexes       | view | postgres    
 pg_catalog | pg_statio_sys_sequences     | view | postgres    
 pg_catalog | pg_statio_sys_tables        | view | postgres    
 pg_catalog | pg_statio_user_indexes      | view | postgres    
 pg_catalog | pg_statio_user_sequences    | view | postgres    
 pg_catalog | pg_statio_user_tables       | view | postgres    
 pg_catalog | pg_stats                    | view | postgres

例如表相關的計數：

postgres=# \d pg_stat_all_tables     
                      View "pg_catalog.pg_stat_all_tables"    
       Column        |           Type           | Collation | Nullable | Default     
---------------------+--------------------------+-----------+----------+---------    
 relid               | oid                      |           |          |     
 schemaname          | name                     |           |          |     
 relname             | name                     |           |          |     
 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                   |           |          |     
 last_vacuum         | timestamp with time zone |           |          |     
 last_autovacuum     | timestamp with time zone |           |          |     
 last_analyze        | timestamp with time zone |           |          |     
 last_autoanalyze    | timestamp with time zone |           |          |     
 vacuum_count        | bigint                   |           |          |     
 autovacuum_count    | bigint                   |           |          |     
 analyze_count       | bigint                   |           |          |     
 autoanalyze_count   | bigint                   |           |          |

查看某張表的計數，例如

postgres=# select * from pg_stat_all_tables where relname='test1';    
-[ RECORD 1 ]-------+-------    
relid               | 31129    
schemaname          | public    
relname             | test1    
seq_scan            | 0    
seq_tup_read        | 0    
idx_scan            |     
idx_tup_fetch       |     
n_tup_ins           | 0    
n_tup_upd           | 0    
n_tup_del           | 0    
n_tup_hot_upd       | 0    
n_live_tup          | 0    
n_dead_tup          | 0    
n_mod_since_analyze | 0    
last_vacuum         |     
last_autovacuum     |     
last_analyze        |     
last_autoanalyze    |     
vacuum_count        | 0    
autovacuum_count    | 0    
analyze_count       | 0    
autoanalyze_count   | 0

通過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)	void	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)	void	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)	void	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 launcher進程，在一個autovacuum_naptime周期內，輪詢所有的database內的計數，並根據計數以及設置的閾值（表級、或全庫級閾值）判斷是否需要對表實施vacuum或analyze的動作。

vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuples

analyze threshold = analyze base threshold + analyze scale factor * number of tuples

如果計數器被清零，可能無法及時對表進行垃圾回收或analyze。

例子

1、配置參數，便於觀察。

vi postgresql.conf

autovacuum = on    
log_autovacuum_min_duration = 0    
autovacuum_max_workers = 5    
autovacuum_naptime = 5s

2、生效參數：pg_ctl reload

3、建立一個測試表

create table test1(id int);

4、觀察日誌

 tail -f -n 1 postgresql-Wed.csv

5、寫入批量數據

postgres=# insert into test1 select generate_series(1,100000);

超過自動analyze的閾值，觀察到自動觸發了analyze。

2017-11-01 13:39:02.853 CST,,,25591,,59f95df6.63f7,1,,2017-11-01 13:39:02 CST,4/1074,1912083,日誌,00000,"自動分析表 ""postgres.public.test1""的係統使用情況: CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.02 s",,,,,,,,"do_analyze_rel, analyze.c:688",""

6、更新批量數據

postgres=# update test1 set id=1;

超過自動vacuum和analyze的閾值，觀察到自動觸發了vacuum和analyze。

2017-11-01 13:39:32.972 CST,,,25599,,59f95e14.63ff,1,,2017-11-01 13:39:32 CST,4/1088,0,日誌,00000,"自動清理表""postgres.public.test1""：索引掃描：0    
頁麵：0 被移除，885 保留，0 由於被占用而跳過，0 被跳過的已被凍結    
tuples: 100003 removed, 100003 remain, 0 are dead but not yet removable, oldest xmin: 1912085    
緩衝區使用：1795次命中，2次失效，4次髒    
平均讀取率：0.835 MB/s，平均寫入率：1.670 MB/s    
係統用法：CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s",,,,,,,,"lazy_vacuum_rel, vacuumlazy.c:402",""    
    
2017-11-01 13:39:32.989 CST,,,25599,,59f95e14.63ff,2,,2017-11-01 13:39:32 CST,4/1089,1912085,日誌,00000,"自動分析表 ""postgres.public.test1""的係統使用情況: CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s",,,,,,,,"do_analyze_rel, analyze.c:688",""

7、更新批量數據，並同時清零計數器。

postgres=# update test1 set id=1;select pg_stat_reset();

計數器被清零

postgres=# select * from pg_stat_all_tables where relname='test1';    
-[ RECORD 1 ]-------+-------    
relid               | 31129    
schemaname          | public    
relname             | test1    
seq_scan            | 0    
seq_tup_read        | 0    
idx_scan            |     
idx_tup_fetch       |     
n_tup_ins           | 0    
n_tup_upd           | 0    
n_tup_del           | 0    
n_tup_hot_upd       | 0    
n_live_tup          | 0    
n_dead_tup          | 0    
n_mod_since_analyze | 0    
last_vacuum         |     
last_autovacuum     |     
last_analyze        |     
last_autoanalyze    |     
vacuum_count        | 0    
autovacuum_count    | 0    
analyze_count       | 0    
autoanalyze_count   | 0

計數器清零後，autovacuum不會觸發vacuum和analyze。