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 BF9C0D1B446 for ; Thu, 4 Dec 2003 19:17:25 +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 98924-02 for ; Thu, 4 Dec 2003 15:16:55 -0400 (AST) Received: from luna.lyris.com (luna.lyris.com [216.91.57.116]) by svr1.postgresql.org (Postfix) with ESMTP id 537A2D1B48E for ; Thu, 4 Dec 2003 15:16:53 -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 hB4JGrIt025290 for ; Thu, 4 Dec 2003 11:16:53 -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:16:53 -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:16:53 -0800 Subject: Re: tuning questions From: Jack Coates To: pgsql-performance In-Reply-To: <3FCF6AEB.908@dsvr.net> References: <1070553983.6498.52.camel@cletus.lyris.com> <3FCF6AEB.908@dsvr.net> Content-Type: text/plain Organization: Lyris Technologies, Inc. Message-Id: <1070565411.13923.70.camel@cletus.lyris.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.4-9mdk Date: Thu, 04 Dec 2003 11:16:51 -0800 Content-Transfer-Encoding: 7bit X-SMTP-HELO: cletus.lyris.com X-SMTP-MAIL-FROM: jack@lyris.com X-SMTP-RCPT-TO: 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/85 X-Sequence-Number: 4945 On Thu, 2003-12-04 at 09:12, Rob Fielding wrote: > > > > I've tweaked shared buffers to 8192, pushed sort memory to 2048, vacuum > > memory to 8192, and effective cache size to 10000. > > /proc/sys/kernel/shmmax is set to 1600000000 and /proc/sys/fs/file-max > > is set to 65536. Ulimit -n 3192. > > Your sharedmemory is too high, and not even being used effectivey. Your > other settings are too low. > > Ball park guessing here, but I'd say first read (and understand) this: > > http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html I've read it many times, understanding is slower :-) > > Then make shared memory about 10-20% available ram, and set: > > ((shmmax/1024) - ( 14.2 * max_connections ) - 250 ) / 8.2 = shared_buffers > > decrease random_page_cost to 0.3 and wack up sort mem by 16 times, > effective cache size to about 50% RAM (depending on your other settings) > and try that for starters. Following this, I've done: 2gb ram = 2,000,000,000 bytes 15 % of that = 300,000,000 bytes divided by 1024 = 292,969 kbytes max_conn * 14.2 = 454 kbytes subtract c4 = 292,514 kbytes subtract 250 = 292,264 kbytes divide by 8.2 = 35,642 shared_buffers performance is unchanged for the 18M job -- pg continues to use ~ 285-300M, system load and memory usage stay the same. I killed that, deleted from the affected tables, inserted a 6M job, and started a vacuumdb --anaylze. It's been running for 20 minutes now... 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. 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? -- Jack Coates, Lyris Technologies Applications Engineer 510-549-4350 x148, jack@lyris.com "Interoperability is the keyword, uniformity is a dead end." --Olivier Fourdan