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 9D265D1B4BE for ; Thu, 4 Dec 2003 23:17:18 +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 45489-04 for ; Thu, 4 Dec 2003 19:16:50 -0400 (AST) Received: from luna.lyris.com (luna.lyris.com [216.91.57.116]) by svr1.postgresql.org (Postfix) with ESMTP id 2652AD1B49B for ; Thu, 4 Dec 2003 19:16:47 -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 hB4NGEIt006141; Thu, 4 Dec 2003 15:16:14 -0800 Received: from luna.lyris.com ([216.91.57.116]) by luna.lyris.com (MailMonitor for SMTP v1.2.2 ) ; Thu, 4 Dec 2003 15:16:13 -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 15:16:13 -0800 Subject: Re: tuning questions From: Jack Coates To: josh@agliodbs.com Cc: Richard Huxton , pgsql-performance In-Reply-To: <200312041324.37889.josh@agliodbs.com> References: <1070553983.6498.52.camel@cletus.lyris.com> <200312042027.22223.dev@archonet.com> <1070570264.13923.88.camel@cletus.lyris.com> <200312041324.37889.josh@agliodbs.com> Content-Type: text/plain Organization: Lyris Technologies, Inc. Message-Id: <1070579771.18838.187.camel@cletus.lyris.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.4-9mdk Date: Thu, 04 Dec 2003 15:16:11 -0800 Content-Transfer-Encoding: 7bit X-SMTP-HELO: cletus.lyris.com X-SMTP-MAIL-FROM: jack@lyris.com X-SMTP-RCPT-TO: josh@agliodbs.com, dev@archonet.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/109 X-Sequence-Number: 4969 On Thu, 2003-12-04 at 13:24, Josh Berkus wrote: > Jack, > > > latest changes: > > shared_buffers = 35642 > > This is fine, it's about 14% of available RAM. Though the way you calculated > it still confuses me. It's not complicated; it should be between 6% and 15% > of available RAM; since you're doing a data-transformation DB, yours should > be toward the high end. > > > max_fsm_relations = 1000 > > max_fsm_pages = 10000 > > You want to raise this a whole lot if your data transformations involve large > delete or update batches. I'd suggest running "vacuum analyze verbose" > between steps to see how many dead pages you're accumulating. This looks really difficult to tune, and based on the load I'm giving it, it looks really important. I've tried the verbose analyze and I've looked at the rules of thumb, neither approach seems good for the pattern of "hammer the system for a day or two, then leave it alone for a week." I'm setting it to 500000 (half of the biggest table size divided by a 6k page size), but I'll keep tweaking this. > > > wal_buffers = 64 > > sort_mem = 32768 > > vacuum_mem = 32768 > > 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). 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 13 24 63 1 2 1 2804 10808 40808 1899848 0 0 21748 60 1143 3655 9 22 69 still high cpu (3-ish load) though, and there's no noticeable improvement in query speed. > > > IO is active, but hardly saturated. CPU load is hefty though, load > > average is at 4 now. > > Unless you're doing huge statistical aggregates (like radar charts), or heavy > numerical calculations-by-query, high CPU and idle I/O usually indicates a > really bad query, like badly mismatched data types on a join or unconstrained > joins or overblown formatting-by-query. Ran that by the programmer responsible for this area and watched the statements go by with tcpdump -X. Looks like really simple stuff to me: select a handful of values, then insert into one table and delete from another. -- Jack Coates, Lyris Technologies Applications Engineer 510-549-4350 x148, jack@lyris.com "Interoperability is the keyword, uniformity is a dead end." --Olivier Fourdan