X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 950279FB1C0 for ; Sun, 25 Jun 2006 09:01:53 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 51682-01 for ; Sun, 25 Jun 2006 09:01:48 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.174]) by postgresql.org (Postfix) with ESMTP id EE5A09FA5CD for ; Sun, 25 Jun 2006 09:01:48 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id a2so1670557ugf for ; Sun, 25 Jun 2006 05:01:47 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=HWr+L22CTq3yVGEckcA318X+tH9Nq6TIQonX2gpaArE/oeTf3wXLBT7asYt5M+PzohOdDGIISAgSv/uMt/kwH0pcOwtTADIZtgmb27cjaPF3Oxctemgeqap/FlLiFbz9F7+CzgpCnhIHRaUmvMvMRDDeVhJioxcXj35Mvk5Wsu4= Received: by 10.78.185.7 with SMTP id i7mr1754071huf; Sun, 25 Jun 2006 05:01:47 -0700 (PDT) Received: by 10.78.100.16 with HTTP; Sun, 25 Jun 2006 05:01:47 -0700 (PDT) Message-ID: <758d5e7f0606250501u784589e5qf2db9485ad3c8369@mail.gmail.com> Date: Sun, 25 Jun 2006 14:01:47 +0200 From: "Dawid Kuroczko" To: "Chris Hoover" Subject: Re: 8.x Vaccum/Autovacuum settings Cc: "pgsql-admin@postgresql.org" In-Reply-To: <1d219a6f0606241503k3ae3e7d6lb1675fcb8b61bbc3@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_54530_24282349.1151236907097" References: <1d219a6f0606241503k3ae3e7d6lb1675fcb8b61bbc3@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/219 X-Sequence-Number: 22181 ------=_Part_54530_24282349.1151236907097 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 6/25/06, Chris Hoover 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. ------=_Part_54530_24282349.1151236907097 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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.
------=_Part_54530_24282349.1151236907097--