Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 518D665021C for ; Fri, 28 Nov 2008 18:28:53 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 14535-06 for ; Fri, 28 Nov 2008 18:28:50 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by mail.postgresql.org (Postfix) with ESMTP id 3DE6D64FFC0 for ; Fri, 28 Nov 2008 18:28:50 -0400 (AST) Received: from Courts-MTA by gwmta.wicourts.gov with Novell_GroupWise; Fri, 28 Nov 2008 16:28:48 -0600 Message-Id: <49301C3A.EE98.0025.0@wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 7.0.3 Date: Fri, 28 Nov 2008 16:28:42 -0600 From: "Kevin Grittner" To: "Ryan Hansen" , "pgsql-performance@postgresql.org" , "Scott Carey" Subject: Re: Memory Allocation References: <011101c95013$b6fca0a0$24f5e1e0$@hansen@brightbuilders.com> <492D82BB.EE98.0025.0@wicourts.gov> 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: 200811/300 X-Sequence-Number: 32031 I'm hoping that through compare/contrast we might help someone start closer to their own best values.... >>> Scott Carey wrote: > Tests with writes can trigger it earlier if combined with bad dirty_buffers > settings. We've never, ever modified dirty_buffers settings from defaults. > The root of the problem is that the Linux paging algorithm estimates that > I/O for file read access is as costly as I/O for paging. A reasonable > assumption for a desktop, a ridiculously false assumption for a large > database with high capacity DB file I/O and a much lower capability swap > file. Our swap file is not on lower speed drives. > If you do have enough other applications that are idle that take up RAM that > should be pushed out to disk from time to time (perhaps your programs that > are doing the bulk loading?) a higher value is useful. Bulk loading was ssh cat | psql. > The more RAM you have and the larger your postgres memory usage, the lower > the swappiness value should be. I think the test environment had 8 GB RAM with 256 MB in shared_buffers. For the conversion we had high work_mem and maintenance_work_mem settings, and we turned fsync off, along with a few other settings we would never using during production. > I currently use a value of 1, on a 32GB machine, and about 600MB of 'stuff' > gets paged out normally, 1400MB under heavy load. Outside of bulk load, we've rarely seen anything swap, even under load. > ***For a bulk load database, one is optimizing for _writes_ and extra page > cache doesn't help writes like it does reads.*** I'm thinking that it likely helps when indexing tables for which data has recently been loaded. It also might help minimize head movement and/or avoid the initial disk hit for a page which subsequently get hint bits set . > Like all of these settings, tune to your application and test. We sure seem to agree on that. -Kevin