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
