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
