Re: Why is my table not autovacuuming?

On 08/28/2017 09:49 AM, Don Seiler wrote:
> I just find it very curious how pg_stat_all_tables.n_live_tups can be so
> very very far off in this case unless stats tracking was somehow
> disabled during a large bulk load or something. I'll keep an eye on things.

If you have a dev machine where you can run an experiment, try the
following:

(warning: sorry but this is quite long...)

8<--------------
createdb test
psql test
8<--------------

create table test(id int);
insert into test select g.i from generate_series(1,1000000) as g(i);

-- wait a while for autovacuum to run
select pg_sleep(90);

\x
select reltuples from pg_class where relname = 'test';
-[ RECORD 1 ]----
reltuples | 1e+06

select * from pg_stat_all_tables where relname = 'test';
-[ RECORD 1 ]-------+------------------------------
relid | 16385
schemaname | public
relname | test
seq_scan | 0
seq_tup_read | 0
idx_scan |
idx_tup_fetch |
n_tup_ins | 1000000
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
n_live_tup | 1000000
n_dead_tup | 0
n_mod_since_analyze | 0
last_vacuum |
last_autovacuum |
last_analyze |
last_autoanalyze | 2017-08-28 10:10:55.046554-07
vacuum_count | 0
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 1

8<--------------
-- quit postgres
-- do an *immediate* shutdown
-- can be done a few ways, but for example see
-- https://www.postgresql.org/docs/9.6/static/app-pg-ctl.html

-- restart postgres
8<--------------
psql test
8<--------------

\x
select reltuples from pg_class where relname = 'test';
-[ RECORD 1 ]----
reltuples | 1e+06

select * from pg_stat_all_tables where relname = 'test';
-[ RECORD 1 ]-------+-------
relid | 16385
schemaname | public
relname | test
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

update test set id = id + 10000000 where id < 50000;
-- wait a while for autovacuum to run
select pg_sleep(90);
select * from pg_stat_all_tables where relname = 'test';
-[ RECORD 1 ]-------+--------
relid | 16385
schemaname | public
relname | test
seq_scan | 1
seq_tup_read | 1000000
idx_scan |
idx_tup_fetch |
n_tup_ins | 0
n_tup_upd | 49999
n_tup_del | 0
n_tup_hot_upd | 0
n_live_tup | 0
n_dead_tup | 49999
n_mod_since_analyze | 49999
last_vacuum |
last_autovacuum |
last_analyze |
last_autoanalyze |
vacuum_count | 0
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 0

update test set id = id + 10000000 where id < 100000;
-- wait a while for autovacuum to run
select pg_sleep(90);
-- will still not trigger autoanalyze because defaults are
-- 50 + .1 x 1000000 = 100050
-- but we have only changed 100000
select * from pg_stat_all_tables where relname = 'test';
-[ RECORD 1 ]-------+--------
relid | 16385
schemaname | public
relname | test
seq_scan | 2
seq_tup_read | 2000000
idx_scan |
idx_tup_fetch |
n_tup_ins | 0
n_tup_upd | 99999
n_tup_del | 0
n_tup_hot_upd | 48
n_live_tup | 0
n_dead_tup | 99999
n_mod_since_analyze | 99999
last_vacuum |
last_autovacuum |
last_analyze |
last_autoanalyze |
vacuum_count | 0
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 0

-- one more time should trigger autoanalyze
-- however not enough to trigger autovac which will be
-- 50 + .2 x 1000000 = 200050
update test set id = id + 10000000 where id < 150000;
-- wait a while for autovacuum to run
select pg_sleep(90);
-- no we have changed 150000
select * from pg_stat_all_tables where relname = 'test';
-[ RECORD 1 ]-------+------------------------------
relid | 16385
schemaname | public
relname | test
seq_scan | 3
seq_tup_read | 3000000
idx_scan |
idx_tup_fetch |
n_tup_ins | 0
n_tup_upd | 149999
n_tup_del | 0
n_tup_hot_upd | 113
n_live_tup | 1000000
n_dead_tup | 149951
n_mod_since_analyze | 0
last_vacuum |
last_autovacuum |
last_analyze |
last_autoanalyze | 2017-08-28 10:26:28.691209-07
vacuum_count | 0
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 1

-- one more time should trigger autovac
-- 50 + .2 x 1000000 = 200050
update test set id = id + 10000000 where id < 250000;
-- wait a while for autovacuum to run
select pg_sleep(90);
-- no we have changed 150000
select * from pg_stat_all_tables where relname = 'test';
-[ RECORD 1 ]-------+------------------------------
relid | 16385
schemaname | public
relname | test
seq_scan | 4
seq_tup_read | 4000000
idx_scan |
idx_tup_fetch |
n_tup_ins | 0
n_tup_upd | 249999
n_tup_del | 0
n_tup_hot_upd | 178
n_live_tup | 1000000
n_dead_tup | 0
n_mod_since_analyze | 100000
last_vacuum |
last_autovacuum | 2017-08-28 10:29:37.164542-07
last_analyze |
last_autoanalyze | 2017-08-28 10:26:28.691209-07
vacuum_count | 0
autovacuum_count | 1
analyze_count | 0
autoanalyze_count | 1

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development