X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 73A8CD1B4A6 for ; Fri, 5 Dec 2003 00:33:15 +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 54066-09 for ; Thu, 4 Dec 2003 20:32:47 -0400 (AST) Received: from luna.lyris.com (luna.lyris.com [216.91.57.116]) by svr1.postgresql.org (Postfix) with ESMTP id 4A9D8D1B47A for ; Thu, 4 Dec 2003 20:32:43 -0400 (AST) Received: from luna.lyris.com (luna.lyris.com [216.91.57.116]) by luna.lyris.com (8.12.8/8.12.8) with ESMTP id hB50W8It019837; Thu, 4 Dec 2003 16:32:08 -0800 Received: from luna.lyris.com ([216.91.57.116]) by luna.lyris.com (MailMonitor for SMTP v1.2.2 ) ; Thu, 4 Dec 2003 16:32:08 -0800 (PST) Received: from cletus.lyris.com (cletus.lyris.com [216.91.56.155]) by luna.lyris.net with SMTP (MailShield v2.04 - LINUX Jul 17 2001 16:58:31); Thu, 04 Dec 2003 16:32:08 -0800 Subject: Re: tuning questions From: Jack Coates To: Richard Huxton Cc: josh@agliodbs.com, pgsql-performance In-Reply-To: <200312042347.54461.dev@archonet.com> References: <1070553983.6498.52.camel@cletus.lyris.com> <200312041324.37889.josh@agliodbs.com> <1070579771.18838.187.camel@cletus.lyris.com> <200312042347.54461.dev@archonet.com> Content-Type: text/plain Organization: Lyris Technologies, Inc. Message-Id: <1070584326.18838.235.camel@cletus.lyris.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.4-9mdk Date: Thu, 04 Dec 2003 16:32:06 -0800 Content-Transfer-Encoding: 7bit X-SMTP-HELO: cletus.lyris.com X-SMTP-MAIL-FROM: jack@lyris.com X-SMTP-RCPT-TO: dev@archonet.com, josh@agliodbs.com, pgsql-performance@postgresql.org X-SMTP-PEER-INFO: cletus.lyris.com [216.91.56.155] X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/112 X-Sequence-Number: 4972 On Thu, 2003-12-04 at 15:47, Richard Huxton wrote: > On Thursday 04 December 2003 23:16, Jack Coates wrote: > > > > > > effective_cache_size = 10000 > > > > > > This is way the heck too low. it's supposed to be the size of all > > > available RAM; I'd set it to 2GB*65% as a start. > > > > This makes a little bit of difference. I set it to 65% (15869 pages). > > That's still only about 127MB (15869 * 8KB). yeah, missed the final digit when I copied it into the postgresql.conf :-( Just reloaded with 158691 pages. > > > Now we have some real disk IO: > > procs memory swap io > > system cpu > > r b w swpd free buff cache si so bi bo in cs us > > sy id > > 0 3 1 2804 10740 40808 1899856 0 0 26624 0 941 4144 > > According to this your cache is currently 1,899,856 KB which in 8KB blocks is > 237,482 - be frugal and say effective_cache_size = 200000 (or even 150000 if > the trace above isn't typical). d'oh, just realized what you're telling me here. /me smacks forehead. Let's try effective_cache of 183105... (75%). Starting both servers, waiting for big fetch to start, and... procs memory swap io system cpu r b w swpd free buff cache si so bi bo in cs us sy id 0 0 0 2800 11920 40532 1906516 0 0 0 0 521 8 0 0 100 0 1 0 2800 11920 40532 1906440 0 0 356 52 611 113 1 3 97 0 1 0 2800 11920 40532 1906424 0 0 20604 0 897 808 1 18 81 0 1 0 2800 11920 40532 1906400 0 0 26112 0 927 820 1 13 87 0 1 0 2800 11920 40532 1906384 0 0 26112 0 923 812 1 12 87 0 1 0 2800 11920 40532 1906372 0 0 24592 0 921 805 1 13 87 0 1 0 2800 11920 40532 1906368 0 0 3248 48 961 1209 0 4 96 0 1 0 2800 11920 40532 1906368 0 0 2600 0 845 1631 0 2 98 0 1 0 2800 11920 40532 1906364 0 0 2728 0 871 1714 0 2 98 better in vmstat... but the query doesn't work any better unfortunately. The frustrating thing is, we also have a UP P3-500 with 512M RAM and two IDE drives with the same PG install which is doing okay with this load -- still half the speed of MS-SQL2K, but usable. I'm at a loss. -- Jack Coates, Lyris Technologies Applications Engineer 510-549-4350 x148, jack@lyris.com "Interoperability is the keyword, uniformity is a dead end." --Olivier Fourdan