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

>>> On Tue, Jul 3, 2007 at  5:34 PM, in message
<20070703223402.GA5491@alvh.no-ip.org>, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
> Kevin Grittner wrote:
>
>> 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.
>
> Sounds like you would be served by setting those specific tables to a
> lower vacuum scale factor (keeping a more normal default for the rest of
> the tables), and having a non-zero vacuum delay setting (to avoid
> excessive I/O consumption).  Have you tried that?
I did play with that, but it doens't seem to make sense in our environment.
We have about 100 databases, most of them scattered around the state, and
any extra maintenance like that has a cost, particularly with the daily
cluster changing the oid.  Both from doing the math and from experience,
I can say that the autovacuum only affects the small, frequently updated
tables, so I could see no benefit.  Am I missing somethign?  (I can't see
where this causes any extra I/O.)
Our tables tend to fall into one of four categories, small tables with high
update rates, medium tables (millions or tens of millions of rows) with
thousands or tens of thousands of updates per day, static tables of various
sizes that are only modified as part of a software release, and big honking
tables (100s of GB) which are either insert-only or are insert with
periodic purge of old rows.  Only the first group has a chance of being
autovacuumed in normal operations.  Event he purges don't cause it to kick
in.
>> 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
>
> Another change in 8.3 is that the naptime is per-database, i.e. the time
> between two consecutive autovac runs on a database.  So with a setting
> of 10s, if you have 10 database there will be one autovac run per
> second, whereas on 8.2 there would be one autovac each 10 seconds
> (unless you run out of worker slots).
That's fine.  We actually want it every ten seconds in a production
database.  When you can have more updates per second than there are rows
in a small table, frequent vacuums are good.  As long as the table doesn't
bloat too badly, the vacuum is typically 10 to 20 milliseconds.  I'm sure
that part of it is that the table tends to remain fully cached. When these
tables were vacuumed once per minute, we ran into performance problems.
>> 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.
>
> If you can afford the cluster then there's no problem.  I don't expect
> that to change in 8.3.
Here also we're talking 10 to 20 milliseconds.  I understand that in 8.2
that leaves a chance of an error, but we seem to have dodged that bullet
so far.  Has that gotten any safer in 8.3?
-Kevin