Label
PostgreSQL, track_counts, statistics, pg_stat_reset
background
The statstic module of PostgreSQL database has some counters for counting the number of records inserted, updated and deleted in each table.
From these views, you can see some of the counts counted by the counter:
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
For example, table-related counts:
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 | | |
Look at the count of a table, for example
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
By reset function, these counts can be nullified.
| 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) |
What are the consequences of zero?
autovacuum launcher process dependency counter
The autovacuum launcher process polls all the counts in the database during an autovacuum_naptime cycle, and determines whether vacuum or analyze actions are required on the table based on the counts and the threshold set (table level or full library level threshold).
vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuples analyze threshold = analyze base threshold + analyze scale factor * number of tuples
If the counter is cleared, the table may not be garbage collected or analyze d in time.
Example
1. Configure parameters for easy observation.
vi postgresql.conf
autovacuum = on log_autovacuum_min_duration = 0 autovacuum_max_workers = 5 autovacuum_naptime = 5s
2. Effective parameters: pg_ctl reload
3. Establish a test table
create table test1(id int);
4. Observation log
tail -f -n 1 postgresql-Wed.csv
5. Writing batch data
postgres=# insert into test1 select generate_series(1,100000);
Over the threshold of automatic analysis, it was observed that automatic analysis was triggered.
2017-11-01 13:39:02.853 CST,,,25591,,59f95df6.63f7,1,,2017-11-01 13:39:02 CST,4/1074,1912083,Journal,00000,"Automatic analysis table ""postgres.public.test1""System usage: CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.02 s",,,,,,,,"do_analyze_rel, analyze.c:688",""
6. Update batch data
postgres=# update test1 set id=1;
Beyond the threshold of automatic vacuum and analysis, it was observed that automatic triggering of vacuum and analysis occurred.
2017-11-01 13:39:32.972 CST, 25599, 59f95e14.63ff, 1, 2017-11-01 13:39:32 CST, 4/1088,0, logs, 00000, "automatic cleaning table", "postgres.public.test1", "index scanning: 0:0:00"
Page: 0 removed, 885 retained, 0 skipped due to occupancy, 0 skipped frozen
tuples: 100003 removed, 100003 remain, 0 are dead but not yet removable, oldest xmin: 1912085
Buffer usage: 1795 hits, 2 failures, 4 dirty
Average read rate: 0.835 MB/s, average write rate: 1.670 MB/s
System Usage: CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s ",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
2017-11-01 13:39:32.989 CST, 25599, 59f95e14.63ff, 2, 2017-11-01 13:39:32 CST, 4/1089, 1912085, logs, 00000, "automatic analysis table", "postgres.public.test1", "system usage: CPU: user: 0.01 s, system: 0.00 s, pselated: 0.01 s,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
7. Update batch data and null the counter at the same time.
postgres=# update test1 set id=1;select pg_stat_reset();
The counter is cleared.
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
After the counter is cleared, autovacuum does not trigger vacuum and analysis.
Summary
Counter zeroing can affect autovacuum launcher to initiate vacuum and analysis, resulting in tables that actually exceed the threshold for analysis or garbage collection, but will not be triggered. Serious cases may lead to table expansion or inaccurate statistical information.
The default cleaning of the counter is involved in pg_awr. I will change it later and the default cleaning is not clear.
Reference resources
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