pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dawid Kuroczko <qnex42@gmail.com>
To: Chris Hoover <revoohc@gmail.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: 8.x Vaccum/Autovacuum settings
Date: Sun, 25 Jun 2006 14:01:47 +0200
Message-ID: <758d5e7f0606250501u784589e5qf2db9485ad3c8369@mail.gmail.com> (raw)
In-Reply-To: <1d219a6f0606241503k3ae3e7d6lb1675fcb8b61bbc3@mail.gmail.com>
References: <1d219a6f0606241503k3ae3e7d6lb1675fcb8b61bbc3@mail.gmail.com>

On 6/25/06, Chris Hoover <revoohc@gmail.com> wrote:
>
> Just curious,
>
> What are most of you setting your vacuum and autovacuum parameters to for
> your 8.x databases.  I just turned on autovacuuming on one of my db
> servers and went with a very conservative vacuum_cost_delay of 200 and
> vacuum_cost_limit of 50.  I am wondering if anyone else has tested to find
> out just how far you can push your vacuum/autovacuum before you start to
> feel performance hits from running it?
>

Depends how much you're UPDATing (and DELETing).  If much, then you will
hit the problem that you're VACUUMing too slow, and the residues from not
yet removed tuples will accumulate and you'll get the performance hit.

Say, if vacuum of 1mln rows take 2h, and you accumulate 0.5mln rows within
an hour, you could end up with:

t=0h    1.0mln rows,  0 dead
t=1h    1.5mln rows, 0.5mln dead, VACUUM starts (will take 3h)
t=2h    2mln rows, 1mln dead, vacuum 33%
t=3h    2.5mln rows, 1.5mln dead, vacuum 66%
t=4h    3mln rows, 2mln dead, vacuum 99%
          2.5mln rows, 1.5mln dead, after vacuum, VACUUM starts (will take
5h)
t=5h    3mln rows, 2mln dead, vacuum 20%
t=6h    3.5mln rows, 2.5mln dead, vacuum 40%
t=7h    4mln rows, 3mln dead, vacuum 60%
t=8h    4.5mln rows, 3.5mln dead, vacuum 80%
t=9h    5mln rows, 4mln dead, vacuum 99%
           3.5mln rows, 2.5mln dead, after vacuum, VACUUM starts (will take
7h)

...and so on...

so vacuum should run quick enough to remove dead tuples quicker than
they accumulate.  For one busy DB, I use cost of 8000 and delay of 150,
which makes spindles busy, but does not cause the load to soar high...

   Regards,
       Dawid

...and so on.

view thread (3+ messages)  latest in thread

Message-ID: <758d5e7f0606250501u784589e5qf2db9485ad3c8369@mail.gmail.com>
Permalink:  ../758d5e7f0606250501u784589e5qf2db9485ad3c8369@mail.gmail.com/
Also on:    postgresql.org/message-id/758d5e7f0606250501u784589e5qf2db9485ad3c8369@mail.gmail.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: qnex42@gmail.com, revoohc@gmail.com
  Subject: Re: 8.x Vaccum/Autovacuum settings
  In-Reply-To: <758d5e7f0606250501u784589e5qf2db9485ad3c8369@mail.gmail.com>
  References: <1d219a6f0606241503k3ae3e7d6lb1675fcb8b61bbc3@mail.gmail.com> <758d5e7f0606250501u784589e5qf2db9485ad3c8369@mail.gmail.com>

* 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