PostgreSQL pg_stat_reset清除track_counts的隐患

4 minute read

背景

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。

小结

计数器清零会影响autovacuum launcher发起vacuum和analyze,导致一些表实际上已经超过分析或垃圾回收的阈值,但是不会被触发。严重时,可能导致表膨胀,或统计信息不准确。

《PostgreSQL AWR报告》

pg_awr里面涉及到计数器的默认清理,我会在后期改掉,默认不清理。

参考

《PostgreSQL pg_stat_ pg_statio_ 统计信息(scan,read,fetch,hit)源码解读》

https://www.postgresql.org/docs/10/static/routine-vacuuming.html#autovacuum

https://www.postgresql.org/docs/10/static/runtime-config-statistics.html#guc-track-counts

Flag Counter

digoal’s 大量PostgreSQL文章入口