X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5D5CF53321 for ; Thu, 12 May 2005 12:21:04 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 60061-10 for ; Thu, 12 May 2005 15:20:55 +0000 (GMT) Received: from koolancexeon.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by svr1.postgresql.org (Postfix) with ESMTP id 9CA5053236 for ; Thu, 12 May 2005 12:20:55 -0300 (ADT) Received: mail.g2switchworks.com 10.10.1.8 from 10.10.1.37 10.10.1.37 via HTTP with MS-WebStorage 6.5.6944 Received: from state.g2switchworks.com by mail.g2switchworks.com; 12 May 2005 10:20:53 -0500 Subject: Re: memory allocation ; postgresql-8.0 From: Scott Marlowe To: "Kavan, Dan (IMS)" Cc: postgres In-Reply-To: <782D2A81EC812642B857B03B506E0B44326488@granite.omni.imsweb.com> References: <782D2A81EC812642B857B03B506E0B44326488@granite.omni.imsweb.com> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1115911253.3868.262.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Thu, 12 May 2005 10:20:53 -0500 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200505/149 X-Sequence-Number: 17578 On Thu, 2005-05-12 at 10:10, Kavan, Dan (IMS) wrote: > Hi Scott, > > Thanks again for all your tips. > > If I knock the buffer size down to 65,536 (still higher than what you > are recommending) then my shmmax becomes: > 256,000 + 550,292,685 (65536*8396.8) + 1,454,100 = 552,002,785 > > That will leave me with 3.5 GB of free memory for the system & work > memory to use. > Will those free system resources ever get used with a 10 million record, > 10 GB database? Certainly. As you access the data the kernel will cache all the data sent through it. Once the machine's been up and processing for a while you should see a top output that shows "free" memory at a few megs (8 to 30 meg is typical) and all the rest of the memory being used as kernel cache. > If I go with 65,536 as my buffer size, Would having the SHMMAX set to 1 > GB on my sysctl.conf system parameters allow me to run two seperate > instances of postgresql on 2 seperate ports? Yes, but you may want to set it just a tad higher for things like fsm and whatnot. Definitely benchmark both the 64k setting of shared_buffers and lower settings, looking for a knee with your data set. It may well be that the best performance happens at a lower number, and doesn't really increase as you bump up the shared_buffers. Be sure to test things as realistically as possible, i.e. the right amount of parallel users and all that.