Re: autovacuum 'stuck' ?

On Wed, Jul 29, 2009 at 12:47 PM, Doug Hunley<doug(at)hunley(dot)homeip(dot)net> wrote:
> When reviewing the vacuum logs, I notice that on any given day
> autovacuum only seems to touch four of the tables in one of our
> schemas (not counting toast tables). However, if I look at the
> pgstatspack output for the same day, I see that there are plenty of
> other tables receiving a high number of inserts and deletes. How can I
> tell if autovacuum is accurately choosing the tables that need its
> attention (these four tables apparently) or if autovacuum is simply
> never making it to the other tables cause its too busy with these
> tables (my suspicion)? This is on 8.3.7 with the following settings in
> postgresql.conf:
> autovacuum = on
> log_autovacuum_min_duration = 0
> autovacuum_vacuum_threshold = 250
> autovacuum_analyze_threshold = 125
> autovacuum_vacuum_scale_factor = 0.2
> autovacuum_analyze_scale_factor = 0.1
> autovacuum_naptime = 5min
>
> Any/all other information can be provided as needed. TIA, again.

Disclaimer: I am not an expert on autovacuum.

If most of the activity on your other tables is UPDATEs, and given
that you are running 8.3, it is possible that they are all HOT
updates, and vacuuming isn't much needed. In terms of figuring out
what is going on with those tables, perhaps you could try any or all
of the following:

1. Lower your autovacuum_naptime (say, to the default value instead of
five times that amount) and see if it vacuums more stuff. On a
related note, does autovacuum do stuff every time it wakes up? Or
just now and then? If the latter, it's probably fine.

2. Fire off a manual VACUUM VERBOSE on one of the other tables you
think might need attention and examine (or post) the output.

3. Get Greg Sabino Mullane's check_postgres.pl script and use it to
look for bloat. Or, low tech way that I have used, compare:

SELECT COALESCE(SUM(pg_column_size(x)), 0) AS size FROM your_table_name x
vs.
SELECT pg_relation_size('your_table_name'::regclass)

(There's probably an easy way to do better than this; maybe someone
will enlighten me?)

Also, keep in mind that vacuuming is a little like dieting. No one
particularly likes it, and there's no value (and possibly some harm)
in doing more of it than you need. If you're not getting fat (i.e.
your queries aren't running slowly) then it's probably not worth
worrying about too much.

...Robert