digoal
2017-10-31
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_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，导致一些表实际上已经超过分析或垃圾回收的阈值，但是不会被触发。严重时，可能导致表膨胀，或统计信息不准确。
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
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
