Re: [ADMIN] 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