autovacuum ignores some tables

2011/6/23 Gábor Farkas <gabor@nekomancer.net>:

hi,

postgresql8.4.7 here.

i checked the pg_stat_user_tables table, and it have a lot of rows
there where the "last_autovacuum" and/or "last_autoanalyze" are null.
does this mean that autovacuum never worked on those tables?

roughly 70% of all the tables have null in those fields..
in those never-autovacuumed tables there are tables that are quite
big, and also have a lot of activity, so it's not that they never
needed vacuuming...

i wonder why autovacuum ignored them. i checked my settings with "SHOW
ALL" in psql, and the corresponding settings are:

autovacuum                       on
autovacuum_analyze_scale_factor  0.1
autovacuum_analyze_threshold     50
autovacuum_freeze_max_age        200000000
autovacuum_max_workers           3
autovacuum_naptime               1min
autovacuum_vacuum_cost_delay     20ms
autovacuum_vacuum_cost_limit     -1
autovacuum_vacuum_scale_factor   0.2
autovacuum_vacuum_threshold      50
track_counts on

any ideas why autovacuum ignores some of the tables?

The table may have not had enough updates or deletes to trigger a
vacuum. Are these insert-only tables? When you look at
pg_stat_user_tables, check the n_tup_upd and n_tup_del columns.

If autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor *
rows in the table) > n_dead_tup in pg_stat_user_tables, then the table
should be autovacuum'd. If it hasn't yet reached this number, it
won't yet be a candidate.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

2011/6/23 Thom Brown <thom@linux.com>:

2011/6/23 Gábor Farkas <gabor@nekomancer.net>:

hi,

postgresql8.4.7 here.

i checked the pg_stat_user_tables table, and it have a lot of rows
there where the "last_autovacuum" and/or "last_autoanalyze" are null.
does this mean that autovacuum never worked on those tables?

roughly 70% of all the tables have null in those fields..
in those never-autovacuumed tables there are tables that are quite
big, and also have a lot of activity, so it's not that they never
needed vacuuming...

i wonder why autovacuum ignored them. i checked my settings with "SHOW
ALL" in psql, and the corresponding settings are:

autovacuum                       on
autovacuum_analyze_scale_factor  0.1
autovacuum_analyze_threshold     50
autovacuum_freeze_max_age        200000000
autovacuum_max_workers           3
autovacuum_naptime               1min
autovacuum_vacuum_cost_delay     20ms
autovacuum_vacuum_cost_limit     -1
autovacuum_vacuum_scale_factor   0.2
autovacuum_vacuum_threshold      50
track_counts on

any ideas why autovacuum ignores some of the tables?

The table may have not had enough updates or deletes to trigger a
vacuum.  Are these insert-only tables?  When you look at
pg_stat_user_tables, check the n_tup_upd and n_tup_del columns.

If autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor *
rows in the table) > n_dead_tup in pg_stat_user_tables, then the table
should be autovacuum'd.  If it hasn't yet reached this number, it
won't yet be a candidate.

thanks for the explanation, now i understand. just to clarify: you
probably meant
the opposite, correct? when n_dead_tup is MORE than the threshold...

gabor

2011/6/23 Gábor Farkas <gabor@nekomancer.net>:

2011/6/23 Thom Brown <thom@linux.com>:

2011/6/23 Gábor Farkas <gabor@nekomancer.net>:

hi,

postgresql8.4.7 here.

i checked the pg_stat_user_tables table, and it have a lot of rows
there where the "last_autovacuum" and/or "last_autoanalyze" are null.
does this mean that autovacuum never worked on those tables?

roughly 70% of all the tables have null in those fields..
in those never-autovacuumed tables there are tables that are quite
big, and also have a lot of activity, so it's not that they never
needed vacuuming...

i wonder why autovacuum ignored them. i checked my settings with "SHOW
ALL" in psql, and the corresponding settings are:

autovacuum                       on
autovacuum_analyze_scale_factor  0.1
autovacuum_analyze_threshold     50
autovacuum_freeze_max_age        200000000
autovacuum_max_workers           3
autovacuum_naptime               1min
autovacuum_vacuum_cost_delay     20ms
autovacuum_vacuum_cost_limit     -1
autovacuum_vacuum_scale_factor   0.2
autovacuum_vacuum_threshold      50
track_counts on

any ideas why autovacuum ignores some of the tables?

The table may have not had enough updates or deletes to trigger a
vacuum.  Are these insert-only tables?  When you look at
pg_stat_user_tables, check the n_tup_upd and n_tup_del columns.

If autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor *
rows in the table) > n_dead_tup in pg_stat_user_tables, then the table
should be autovacuum'd.  If it hasn't yet reached this number, it
won't yet be a candidate.

thanks for the explanation, now i understand. just to clarify: you
probably meant
the opposite, correct? when n_dead_tup is MORE than the threshold...

Erk, yes, switch the > to a <.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company