X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 7C05ED1B578 for ; Thu, 20 Nov 2003 20:41:35 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 08991-03 for ; Thu, 20 Nov 2003 16:41:06 -0400 (AST) Received: from b0x.supernerd.com (unknown [208.186.134.90]) by svr1.postgresql.org (Postfix) with ESMTP id F3782D1C96C for ; Thu, 20 Nov 2003 16:41:03 -0400 (AST) Received: from trogdor (unknown [67.42.122.198]) by b0x.supernerd.com (Postfix) with ESMTP id C92955FFF2; Thu, 20 Nov 2003 13:37:14 -0700 (MST) Message-ID: <01a801c3afa6$a4290710$0700a8c0@trogdor> From: "Rick Gigger" To: "Doug McNaught" Cc: "scott.marlowe" , "PgSQL General ML" References: <021a01c3ae43$3b25a2f0$0700a8c0@trogdor> <8765hhqeks.fsf@asmodeus.mcnaught.org> Subject: Re: performance problem Date: Thu, 20 Nov 2003 13:41:13 -0700 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/1136 X-Sequence-Number: 52786 > > > That's VERY high. When postgresql has to manage a lot of buffers it > > > actually is slower than letting the kernel in Linux or BSD do it for you. I am confused. In this tutorial (by Bruce Momjian) http://candle.pha.pa.us/main/writings/pgsql/hw_performance/node8.html it says: "As a start for tuning, use 25% of RAM for cache size, and 2-4% for sort size." If I've got 2g of RAM then that is 2097152k. 25% of that = 524288k. So the tutorial is saying that 524288k is a good starting point for shared buffers with this amount of RAM. If each buffer is 8k (http://candle.pha.pa.us/main/writings/pgsql/hw_performance/node3.html) then that would be 65536 buffers. I'm pretty sure that that is a lot more than I need to cache every tuple in my database. Now everytime I see someone comment on this list about appropriate numbers for shared buffers they say something like "(65536 buffers is) VERY high. Now since I obviously don't need that much shared cache so I am not concerned but it seems to me that one of the following must be true. My calculations here are wrong. or The tutorial is not accurate in saying that 25% is a good starting point. or The people making comments that 65536 is "VERY high" are wrong. Am I just confused or does this make sense? > > Even if you've got the memory to spare? Does postgres actually slow down > > just because it's slower to manager a lot of them just or because you're > > taking the memory away from the kernel so the kernel has to swap more? > > The latter, mainly, I think. Also you *really* don't want your kernel > to swap out any of your shared buffers, which can happen if they take > up a significant portion of RAM... So if I'm not swapping at all and I've got over 1g of unused memory then I'm probably ok even with the very high buffer setting? (Although I will probably reduce it anyway since it is unnecessary). rg