Thread: Aggressive autovacuuming ?

Aggressive autovacuuming ?

From
Jesper Krogh
Date:
Hi.

I have been wondering if anyone has been experimenting with "really
agressive"
autovacuuming. 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.

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
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?

Jesper

--
Jesper Krogh

Re: Aggressive autovacuuming ?

From
Scott Marlowe
Date:
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.

Re: Aggressive autovacuuming ?

From
Alvaro Herrera
Date:
Excerpts from Scott Marlowe's message of dom jun 20 16:13:15 -0400 2010:
> 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.

Keep in mind that autovacuum scales down the cost limit the more workers
there are.  So if you have 10ms sleeps and 1 worker, it should roughly
use a similar amount of I/O than if you have 10ms sleeps and 10 workers
(each worker would sleep 10 times more frequently).

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: Aggressive autovacuuming ?

From
"Kevin Grittner"
Date:
Jesper Krogh <jesper@krogh.cc> wrote:

> 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.

Interesting concept.  That might be a way to avoid the extra disk
I/O to set hint bits, and then some.  I haven't tried it, but I'm
going to make a note to take a look when (if???) I get some free
time.  If you give it a try, please post the results.  If you're I/O
bound (rather than CPU bound) and you choose *extremely* aggressive
settings, the multiple writes to pages *might* collapse in cache and
significantly reduce I/O.

I don't think I'd try it on a release prior to 8.4, however.  Nor
would I consider trying this in a production environment without a
good set of tests.

-Kevin

Re: Aggressive autovacuuming ?

From
Robert Haas
Date:
On Sun, Jun 20, 2010 at 4:13 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>> 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.

8.4 (and 9.0) do still need to do vacuums to freeze tuples before
transaction ID wraparound occurs.  This is not to be confused with
VACUUM FULL, which is something else altogether.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

Re: Aggressive autovacuuming ?

From
Scott Marlowe
Date:
On Wed, Jun 23, 2010 at 1:58 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Sun, Jun 20, 2010 at 4:13 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>>> 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.
>
> 8.4 (and 9.0) do still need to do vacuums to freeze tuples before
> transaction ID wraparound occurs.  This is not to be confused with
> VACUUM FULL, which is something else altogether.

My point was that modern pgsql doesn't need db wide vacuum to prevent
wrap around anymore, but can vacuum individual relations to prevent
wraparound.

Re: Aggressive autovacuuming ?

From
Robert Haas
Date:
On Wed, Jun 23, 2010 at 2:20 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Wed, Jun 23, 2010 at 1:58 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> On Sun, Jun 20, 2010 at 4:13 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>>>> 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.
>>
>> 8.4 (and 9.0) do still need to do vacuums to freeze tuples before
>> transaction ID wraparound occurs.  This is not to be confused with
>> VACUUM FULL, which is something else altogether.
>
> My point was that modern pgsql doesn't need db wide vacuum to prevent
> wrap around anymore, but can vacuum individual relations to prevent
> wraparound.

Oh, I see.  I didn't realize we used to do that.  Looks like that
change was committed 11/5/2006.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company