From owner-freebsd-performance@FreeBSD.ORG Fri Jul 25 09:26:07 2003 Return-Path: Delivered-To: freebsd-performance@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 0BB6C37B401 for ; Fri, 25 Jul 2003 09:26:07 -0700 (PDT) Received: from smtp.k12us.com (smtp.k12us.com [65.112.222.15]) by mx1.FreeBSD.org (Postfix) with SMTP id C738B43FA3 for ; Fri, 25 Jul 2003 09:26:04 -0700 (PDT) (envelope-from cweimann@k12hq.com) Received: (qmail 35443 invoked by uid 1001); 25 Jul 2003 16:26:01 -0000 Date: Fri, 25 Jul 2003 12:26:01 -0400 From: Christopher Weimann To: Tom Samplonius Message-ID: <20030725162601.GA35378@smtp.k12us.com> References: <20030724173910.GA9364@smtp.k12us.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.4i X-AntiVirus: scanned for viruses by AMaViS 0.2.1 (http://amavis.org/) cc: freebsd-database@freebsd.org cc: Christopher Weimann cc: Paul Pathiakis cc: Sean Chittenden cc: freebsd-performance@freebsd.org cc: Terry Lambert Subject: Re: Tuning for PostGreSQL Database X-BeenThere: freebsd-performance@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Performance/tuning List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Fri, 25 Jul 2003 16:26:07 -0000 On Fri 07/25/2003-12:03:32AM -0700, Tom Samplonius wrote: > > Maybe you should continue to worry. PostgreSQL isn't MySQL (or a > typical server application). It reads all database pages into its shared > memory area. It is wasteful for the DBMS and the OS to both cache this > data. You'll want the PostgreSQL shared memory size to be around 75% the > size of RAM (on a dedicated DBMS server). In fact, many commercial DBMS > systems will use raw writes to bypass the OS cache! > I was concerned about the disk cache because of this link http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html#shbuf which says... PostgreSQL counts a lot on the OS to cache data files and hence does not bother with duplicating its file caching effort. The shared buffers parameter assumes that OS is going to cache a lot of files and hence it is generally very low compared with system RAM. Even for a dataset in excess of 20GB, a setting of 128MB may be too much, if you have only 1GB RAM and an aggressive-at-caching OS like Linux. But now that I have looked a bit more I see that this link http://www.postgresql.org/docs/aw_pgsql_book/hw_performance/node6.html which says... Ideally, the POSTGRESQL shared buffer cache will be: * Large enough to hold most commonly-accessed tables * Small enough to avoid swap pagein activity So I have conflicting documentation. I have machine with 4Gig of ram. What is the maximum value of SHMMAX on FreeBSD?