To: Royce Ausburn <royce.ml@inomial.com>
cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Ineffective autovacuum 
In-reply-to: <2499AF8B-FB90-40F9-9CF7-5BDC0F33BE6E@inomial.com> 
References: <059E65EF-8C66-4DA6-835C-936D6592C94B@inomial.com>
	<14103.1317097282@sss.pgh.pa.us>
	<2499AF8B-FB90-40F9-9CF7-5BDC0F33BE6E@inomial.com>
Comments: In-reply-to Royce Ausburn <royce.ml@inomial.com>
	message dated "Tue, 27 Sep 2011 15:08:26 +1000"
Date: Tue, 27 Sep 2011 09:49:14 -0400
Message-ID: <22112.1317131354@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Royce Ausburn <royce.ml@inomial.com> writes:
> Since sending this first email I've up'd the autovacuum log level and I've noticed that the same tables seem to be auto vacuum'd over and over again… Some of the tables are a bit surprising in that they're updated semi-regularly, but not enough (I'd think) to warrant an autovacuum every few minutes… Is this unusual?

Well, that proves autovacuum isn't getting blocked anyway.  At this
point I suspect that Marti has fingered the correct issue: you likely
need to increase the FSM settings.  You should try running a manual
VACUUM VERBOSE and see if it suggests that more FSM space is needed
(there'll be some FSM stats at the end of the verbose printout).

> Perhaps unrelated: I've done some digging around and happened across a nightly task doing:
> select pg_stat_reset()
> on each of the databases in the cluster…. I've no idea why we're doing that (and our usual sysadmin / DBA has resigned, so I doubt I'll ever know).  There must have been a reason at the time, but I wonder if this might be interfering with things?

Hmm, it's not helping any.  Anything that needs vacuuming, but less
often than once a day, would get missed due to the stats getting
forgotten.

			regards, tom lane