Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 7DFEA9FB2E9 for ; Thu, 18 Jan 2007 11:38:45 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 32871-06 for ; Thu, 18 Jan 2007 11:38:29 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from exchange.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by postgresql.org (Postfix) with ESMTP id 3F2E09FB2E8 for ; Thu, 18 Jan 2007 11:38:41 -0400 (AST) Received: from 10.10.1.37 ([10.10.1.37]) by exchange.g2switchworks.com ([10.10.1.2]) with Microsoft Exchange Server HTTP-DAV ; Thu, 18 Jan 2007 15:38:32 +0000 Received: from state.g2switchworks.com by mail.g2switchworks.com; 18 Jan 2007 09:38:32 -0600 Subject: Re: Configuration Advice From: Scott Marlowe To: Steve Cc: Benjamin Minshall , pgsql-performance@postgresql.org In-Reply-To: References: <1168982580.8275.1169588211@webmail.messagingengine.com> <26412.1168983547@sss.pgh.pa.us> <1168986053.16393.1169598917@webmail.messagingengine.com> <81961ff50701161444l3f932755m5a6e15ad2d948130@mail.gmail.com> <1169002739.19758.1169636285@webmail.messagingengine.com> <1169012767.6271.7.camel@localhost.localdomain> <1169044649.19974.1169715743@webmail.messagingengine.com> <1169050746.9586.44.camel@state.g2switchworks.com> <45AE79D5.9080000@fuzzy.cz> <45AE962A.5070408@intellicon.biz> <1169075631.9586.61.camel@state.g2switchworks.com> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1169134712.9586.63.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Thu, 18 Jan 2007 09:38:32 -0600 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/310 X-Sequence-Number: 22911 On Wed, 2007-01-17 at 18:27, Steve wrote: > > Generally speaking, once you've gotten to the point of swapping, even a > > little, you've gone too far. A better approach is to pick some > > conservative number, like 10-25% of your ram for shared_buffers, and 1 > > gig or so for maintenance work_mem, and then increase them while > > exercising the system, and measure the difference increasing them makes. > > > > If going from 1G shared buffers to 2G shared buffers gets you a 10% > > increase, then good. If going from 2G to 4G gets you a 1.2% increase, > > it's questionable. You should reach a point where throwing more > > shared_buffers stops helping before you start swapping. But you might > > not. > > > > Same goes for maintenance work mem. Incremental changes, accompanied by > > reproduceable benchmarks / behaviour measurements are the way to > > determine the settings. > > > > Note that you can also vary those during different times of the day. > > you can have maint_mem set to 1Gig during the day and crank it up to 8 > > gig or something while loading data. Shared_buffers can't be changed > > without restarting the db though. > > > > I'm currently benchmarking various configuration adjustments. Problem is > these tests take a really long time because I have to run the load > process... which is like a 9 hour deal. That's why I'm asking for advice > here, because there's a lot of variables here and it's really time costly > to test :) > > I'm still working on the benchmarkings and by Friday I should have some > interesting statistics to work with and maybe help figure out what's going > on. You can probably take a portion of what you're loading and make a benchmark of the load process that is repeatable (same data, size, etc...) each time, but only takes 30 minutes to an hour to run each time. shortens your test iteration AND makes it reliably repeatable.