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 C53B3D1B444 for ; Thu, 4 Dec 2003 17:00:14 +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 74130-08 for ; Thu, 4 Dec 2003 12:59:43 -0400 (AST) Received: from davinci.ethosmedia.com (server228.ethosmedia.com [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id 0A207D1B446 for ; Thu, 4 Dec 2003 12:59:37 -0400 (AST) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 4022070; Thu, 04 Dec 2003 09:00:25 -0800 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: Jack Coates , pgsql-performance@postgresql.org Subject: Re: tuning questions Date: Thu, 4 Dec 2003 08:59:06 -0800 User-Agent: KMail/1.4.3 References: <1070553983.6498.52.camel@cletus.lyris.com> In-Reply-To: <1070553983.6498.52.camel@cletus.lyris.com> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-Id: <200312040859.06963.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/74 X-Sequence-Number: 4933 Jack, > The application is on another server, and does this torture test: it > builds a large table (~6 million rows in one test, ~18 million in > another). Rows are then pulled in chunks of 4 to 6 thousand, acted on, > and inserted back into another table (which will of course eventually > grow to the full size of the first). >e 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. Have you read this? http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html Actually, your situation is not "worst case". For one thing, your process is effectively single-user; this allows you to throw all of your resources at one user. The problem is that your settings have effectively throttled PG at a level appropriate to a many-user and/or multi-purpose system. You need to "open them up". For something involving massive updating/transformation like this, once you've done the basics (see that URL above) the main settings which will affect you are sort_mem and checkpoint_segments, both of which I'd advise jacking way up (test by increments). Raising wal_buffers wouldn't hurt either. Also, give some thought to running VACUUM and/or ANALYZE between segments of your procedure. Particularly if you do updates to many rows of a table and then query based on the changed data, it is vital to run an ANALYZE first, and usually a good idea to run a VACUUM if it was an UPDATE or DELETE and not an INSERT. -- Josh Berkus Aglio Database Solutions San Francisco