Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id DF1DE9F98F8 for ; Wed, 25 Oct 2006 12:28:33 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 18381-05 for ; Wed, 25 Oct 2006 15:28:19 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 061269FA344 for ; Wed, 25 Oct 2006 12:28:18 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id 01D5E56467; Wed, 25 Oct 2006 10:28:18 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Wed, 25 Oct 2006 10:28:13 -0500 Date: Wed, 25 Oct 2006 10:28:13 -0500 From: "Jim C. Nasby" To: Worky Workerson Cc: "Craig A. James" , Merlin Moncure , pgsql-performance@postgresql.org Subject: Re: Best COPY Performance Message-ID: <20061025152812.GQ26892@nasby.net> References: <20061023215913.GW26892@nasby.net> <20061025001709.GA26892@nasby.net> <453EF7C4.2040105@modgraph-usa.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: X-Operating-System: FreeBSD 6.1-RELEASE-p10 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.13 (2006-08-11) X-Hashcash: 1:20:061025:worky.workerson@gmail.com::rTx6E3cDS4oH2uQc:000000000000 0000000000000000000000002B4e X-Hashcash: 1:20:061025:cjames@modgraph-usa.com::t7drlb+s5NDqRQKV:00000000000000 0000000000000000000000005jsn X-Hashcash: 1:20:061025:mmoncure@gmail.com::/B1gZo9lrS8g5GtJ:0000000000000000000 0000000000000000000000009b/7 X-Hashcash: 1:20:061025:pgsql-performance@postgresql.org::I3cDvMol9+Y+ApF7:00000 0000000000000000000000000+Jq X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200610/516 X-Sequence-Number: 21645 On Wed, Oct 25, 2006 at 08:03:38AM -0400, Worky Workerson wrote: > I'm just doing CSV style transformations (and calling a lot of > functions along the way), but the end result is a straight bulk load > of data into a blank database. And we've established that Postgres > can do *way* better than what I am seeing, so its not suprising that > perl is using 100% of a CPU. If you're loading into an empty database, there's a number of tricks that will help you: Turn off fsync Add constraints and indexes *after* you've loaded the data (best to add as much of them as possible on a per-table basis right after the table is loaded so that it's hopefully still in cache) Crank up maintenance_work_mem, especially for tables that won't fit into cache anyway Bump up checkpoint segments and wal_buffers. Disable PITR Create a table and load it's data in a single transaction (8.2 will avoid writing any WAL data if you do this and PITR is turned off) -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)