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 0B298D1B535 for ; Thu, 4 Dec 2003 19:55:32 +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 06898-09 for ; Thu, 4 Dec 2003 15:55:02 -0400 (AST) Received: from luna.lyris.com (luna.lyris.com [216.91.57.116]) by svr1.postgresql.org (Postfix) with ESMTP id 9F474D1B445 for ; Thu, 4 Dec 2003 15:51:00 -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 hB4JovIt031753; Thu, 4 Dec 2003 11:50:58 -0800 Received: from luna.lyris.com ([216.91.57.116]) by luna.lyris.com (MailMonitor for SMTP v1.2.2 ) ; Thu, 4 Dec 2003 11:50:57 -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 11:50:57 -0800 Subject: Re: tuning questions From: Jack Coates To: josh@agliodbs.com Cc: pgsql-performance In-Reply-To: <200312041120.21124.josh@agliodbs.com> References: <1070553983.6498.52.camel@cletus.lyris.com> <3FCF6AEB.908@dsvr.net> <1070565411.13923.70.camel@cletus.lyris.com> <200312041120.21124.josh@agliodbs.com> Content-Type: text/plain Organization: Lyris Technologies, Inc. Message-Id: <1070567455.13923.83.camel@cletus.lyris.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.4-9mdk Date: Thu, 04 Dec 2003 11:50:55 -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,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/91 X-Sequence-Number: 4951 On Thu, 2003-12-04 at 11:20, Josh Berkus wrote: > Jack, > > > Following this, I've done: > > 2gb ram > > = > > 2,000,000,000 > > bytes > > This calculation is fun, but I really don't know where you got it from. It > seems quite baroque. What are you trying to set, exactly? Message-ID: <3FCF6AEB.908@dsvr.net> Date: Thu, 04 Dec 2003 17:12:11 +0000 From: Rob Fielding > > getting the SQL query better optimized for PG is on my todo list, but > > not something I can do right now -- this application is designed to be > > cross-platform with MS-SQL, PG, and Oracle so tweaking SQL is a touchy > > subject. > > Well, if you're queries are screwed up, no amount of .conf optimization is > going to help you much. You could criticize that PG is less adept than > some other systems at re-writing "bad queries", and you would be correct. > However, there's not much to do about that on existing systems. > > How about posting some sample code? Tracking that down in CVS and translating from C++ is going to take a while -- is there a way to get PG to log the queries it's receiving? > > > The pgavd conversation is intriguing, but I don't really understand the > > role of vacuuming. Would this be a correct statement: "PG needs to > > regularly re-evaluate the database in order to adjust itself?" I'm > > imagining that it continues to treat the table as a small one until > > vacuum informs it that the table is now large? > > Not Vacuum, Analyze. Otherwise correct. Mind you, in "regular use" where > only a small % of the table changes per hour, periodic ANALYZE is fine. > However, in "batch data transform" analyze statements need to be keyed to the > updates and/or imports. > > BTW, I send a couple of e-mails to the Lyris documentation maintainer about > updating out-of-date information about setting up PostgreSQL. I never got a > response, and I don't think my changes were made. She sits on the other side of the cube wall from me, and if I find a decent config it's going into the manual -- consider this a golden opportunity :-) -- Jack Coates, Lyris Technologies Applications Engineer 510-549-4350 x148, jack@lyris.com "Interoperability is the keyword, uniformity is a dead end." --Olivier Fourdan