On Sun, Jun 20, 2010 at 11:44 AM, Jesper Krogh <jesper@krogh.cc> wrote:
> Hi.
>
> I have been wondering if anyone has been experimenting with "really
> agressive"
> autovacuuming.
I have been using moderately aggressive autovac, with 6 or more
threads running with 1ms sleep, then keeping track of them to see if
they're being too aggresive. Basically as long as io utilization
doesn't hit 100% it doesn't seem to have any negative or even
noticeable effect.
I head more in the direction of running a few more threads than I
absolutely need to keep up with bloat. If I'm set for 5 threads and I
always have five threads running, I go to 6, 7, 8 or wherever they're
never all active.
But you need the IO capability to use aggresive vacuuming.
> The database I'm adminstrating rarely have "long running"
> transactions
> (over several minutes). And a fair amount of buffercache and an OS cache of
> (at best 64GB). A lot of the OS cache is being used for read-caching.
>
> My thought was that if I tuned autovacuum to be "really aggressive" then
> I could get autovacuum to actually vacuum the tuples before they
> get evicted from the OS cache thus effectively "saving" the IO-overhead
> of vacuuming.
But vacuuming by design has to write out and that's the real resource
you're likely to use up first.
> The largest consequence I can see at the moment is that when I get a
> full vacuum (for preventing transaction-id wraparound) it would be
I assume you mean the automatic database wide vacuum. I don't think
8.4 and above need that anymore. I thnk 8.3 does that too, but I'm
not 100% sure.
> run with the same aggressive settings, thus giving a real performance
> hit in that situation.
>
> Has anyone tried to do similar? What is your experience?
> Is the idea totally bogus?
Cranking up autovacuum is not a bogus idea, but it directly impacts
your IO subsystem, and if you get too aggressive (zero naptime is way
aggressive) you have to back off on the number of threads to keep
things sane. If your IO subsystem is one 7200RPM SATA drive with
write cache disabled / fsync properly working, you're not gonna be
able to get very aggresive before you make your IO subsystem bog down.
