From: Tom Lane <tgl@sss.pgh.pa.us>
To: Royce Ausburn <royce.ml@inomial.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Ineffective autovacuum
Date: Tue, 27 Sep 2011 09:49:14 -0400
Message-ID: <22112.1317131354@sss.pgh.pa.us> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, royce.ml@inomial.com
Subject: Re: Ineffective autovacuum
In-Reply-To: <22112.1317131354@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox