Re: Still recommending daily vacuum... - Mailing list pgsql-hackers

>>> On Tue, Jul 3, 2007 at  3:36 PM, in message <13153.1183494983@sss.pgh.pa.us>,
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Jim C. Nasby" <decibel@decibel.org> writes:
>> On Mon, Jul 02, 2007 at 11:19:12PM -0400, Tom Lane wrote:
>>> Is there a reason to say anything beyond "use autovac"?
>
>> There is; I know that things like web session tables aren't handled very
>> well by autovacuum if there are any moderately large tables (anything
>> that will take more than a few minutes to vacuum). Eventually we should
>> be able to accommodate that case with multiple workers, but we'll need a
>> mechanism to ensure that at least one worker doesn't get tied up in
>> large vacuums.
>
> And which part of that do you think isn't resolved in 8.3?
We have a 406GB table where 304GB is in one table.  The next two tables
are 57GB and 40GB.  Inserts to these three tables are constant during the
business day, along with inserts, updates, and very few deletes to the
other tables.  Database modifications are few and scattered at night and
on weekends.  Virtually all queries are during the business day.  The
large tables are "insert only" except for a weekend delete of the oldest
one week of data, to keep a rolling set of just over a year.  (No, we
really don't want to go to weekly partitions, if it can be avoided.)
Autovacuum is enabled with very aggressive settings, to cover small
tables, including one with about 75 rows that can be updated 100 or more
times per second.  Even with these settings there is zero chance of any
table of even moderate size hitting the autovacuum threshold between our
scheduled vacuums.  When we tried doing a nightly vacuum analyze starting
at the end of business day, it ran well into the next day, and the users
complained of slowness until it stopped.  We changed to a weeknight vacuum
analyze of the volatile tables which aren't in the big three, and a vacuum
analyze of the entire database right after the weekly delete.
Isn't this a use case where we don't want to count on autovacuum, both
from a table bloat perspective and the user impact perspective, even under
8.3?
In terms of our autovacuum settings, we have several different types of
databases, and in all of them we seem to do well with these changes from
the 8.2 defaults, combined with (except for the above configuration) a
nightly database vacuum:
autovacuum_naptime = 10s
autovacuum_vacuum_threshold = 1
autovacuum_analyze_threshold = 1
Oh, the tiny, high-update tables occasionally bloat to hundreds or
thousands of pages because of long-running transactions, so we schedule
a daily cluster on those, just to keep things tidy.
-Kevin