Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id A09019FA463 for ; Mon, 30 Jul 2007 13:04:13 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 48550-05 for ; Mon, 30 Jul 2007 13:04:09 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.235]) by postgresql.org (Postfix) with ESMTP id B475E9FA508 for ; Mon, 30 Jul 2007 13:04:09 -0300 (ADT) Received: by wx-out-0506.google.com with SMTP id h29so1347869wxd for ; Mon, 30 Jul 2007 09:04:09 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=MGMIPXWI06N3HPVkanAK2PAqVnBf+xrpv+VX+a5WKUB3+ItbaJJcFf3b8LORbt+p2TkRJAFjb7wJwm0uSUTVwBINJYaPRItDVFh4AtbAx2NTM4XTE2zkoqKHiRJ2jHrVDO0ZqbgjXxv56U2wY0R3bpVxq3xkiqKe65N9ett2sbU= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=OUXn3pb1+357IyX0ExKSantnyWgkfW2ZWsYq2cWNz35tJZRs34SsJPJYhMS25uBsK7yEHlELxU6KRcwE9ABRQElgAEfniwuZlAGL/YHVR8xZD9a1fu/GzecZAukW6TCrFAE7j63qw/R5V0cMfPB/rrAX6mClbDwbx8eYrg2BV7s= Received: by 10.70.51.17 with SMTP id y17mr10226911wxy.1185811448798; Mon, 30 Jul 2007 09:04:08 -0700 (PDT) Received: by 10.70.39.13 with HTTP; Mon, 30 Jul 2007 09:04:08 -0700 (PDT) Message-ID: <357fa7590707300904u4c2eb219p87793b5d19730638@mail.gmail.com> Date: Mon, 30 Jul 2007 12:04:08 -0400 From: "Steven Flatt" To: "Jim C. Nasby" Subject: Re: Vacuum looping? Cc: pgsql-performance@postgresql.org In-Reply-To: <20070728163624.GI39272@nasby.net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_27749_1070367.1185811448758" References: <357fa7590707271432l709da359n68deea693d2c5fdf@mail.gmail.com> <20070728163624.GI39272@nasby.net> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200707/424 X-Sequence-Number: 26108 ------=_Part_27749_1070367.1185811448758 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/28/07, Jim C. Nasby wrote: > > What are your vacuum_cost_* settings? If you set those too aggressively > you'll be in big trouble. autovacuum_vacuum_cost_delay = 100 autovacuum_vacuum_cost_limit = 200 These are generally fine, autovacuum keeps up, and there is minimal impact on the system. vacuum_cost_delay = 100 vacuum_cost_limit = 1000 We set this cost_limit a little higher so that, in the few cases where we have to intervene manually, vacuum runs faster. The second pass on the vacuum means that maintenance_work_memory isn't > large enough. maintenance_work_mem is set to 256MB and I don't think we want to make this any bigger by default. Like I say above, generally autovacuum runs fine. If we do run into this situation again (lots of OOM queries and lots to cleanup), we'll probably increase maintenance_work_mem locally and run a vacuum in that session. Good to know that vacuum was doing the right thing. Thanks, Steve ------=_Part_27749_1070367.1185811448758 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
On 7/28/07, Jim C. Nasby <decibel@decibel.org> wrote:
What are your vacuum_cost_* settings? If you set those too aggressively
you'll be in big trouble.
 
autovacuum_vacuum_cost_delay = 100
autovacuum_vacuum_cost_limit = 200
 
These are generally fine, autovacuum keeps up, and there is minimal impact on the system.
 
vacuum_cost_delay = 100
vacuum_cost_limit = 1000
 
We set this cost_limit a little higher so that, in the few cases where we have to intervene manually, vacuum runs faster.
 

The second pass on the vacuum means that maintenance_work_memory isn't
large enough.
 
maintenance_work_mem is set to 256MB and I don't think we want to make this any bigger by default.  Like I say above, generally autovacuum runs fine.  If we do run into this situation again (lots of OOM queries and lots to cleanup), we'll probably increase maintenance_work_mem locally and run a vacuum in that session.
 
Good to know that vacuum was doing the right thing.
 
Thanks,
Steve
 
------=_Part_27749_1070367.1185811448758--