Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 5E3876515F7 for ; Mon, 23 Jun 2008 19:42:22 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 34205-07 for ; Mon, 23 Jun 2008 19:42:13 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by postgresql.org (Postfix) with ESMTP id 535226514DA for ; Mon, 23 Jun 2008 19:42:17 -0300 (ADT) Received: from Courts-MTA by gwmta.wicourts.gov with Novell_GroupWise; Mon, 23 Jun 2008 17:42:15 -0500 Message-Id: <485FE04D.EE98.0025.0@wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 7.0.3 Date: Mon, 23 Jun 2008 17:42:08 -0500 From: "Kevin Grittner" To: "Scott Marlowe" , "Greg Smith" Cc: , Subject: Re: Checkpoint tuning on 8.2.4 References: In-Reply-To: Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200806/108 X-Sequence-Number: 30458 I concur with most of what was already posted. Some additions below. >>> "Scott Marlowe" wrote: > On Fri, Jun 6, 2008 at 12:30 AM, Greg Smith wrote: > >> vacuum_cost_delay = 750 >> autovacuum = true >> autovacuum_naptime = 3600 >> autovacuum_vacuum_threshold = 1000 >> autovacuum_analyze_threshold = 500 >> autovacuum_vacuum_scale_factor = 0.4 >> autovacuum_analyze_scale_factor = 0.2 >> autovacuum_vacuum_cost_delay = -1 >> autovacuum_vacuum_cost_limit = -1 >> max_fsm_pages = 5000000 >> max_fsm_relations = 2000 > > These are terrible settings for a busy database. A cost delay > anything over 10 or 20 is usually WAY too big, and will make vacuums > take nearly forever. Naptime of 3600 is 1 hour, right? That's also > far too long to be napping between just checking to see if you should > run another vacuum. > > I'd recommend: > vacuum_cost_delay = 20 > autovacuum = true > autovacuum_naptime = 300 # 5 minutes. I would also reduce the autovacuum thresholds and scale factors; many small vacuums are more efficient than a few big ones. Also, you stand a chance to force the hint bit writing to coalesce with the initial page write if you are more aggressive here. I'd probably go all the way down to a vacuum cost delay of 10 and then see if you need to go higher. That has worked best for us in a write-heavy environment with hundreds of millions of rows. A nightly database vacuum is good if it can complete off-hours and doesn't interfere with the application; otherwise, some regular schedule, by table. It's hard to give more advice without more specifics. -Kevin