Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id CE1729FB1F2 for ; Wed, 25 Oct 2006 12:22:24 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 82888-02 for ; Wed, 25 Oct 2006 12:22:15 -0300 (ADT) 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 5A0239FB1D5 for ; Wed, 25 Oct 2006 12:22:15 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id 3C17256435; Wed, 25 Oct 2006 10:22:14 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Wed, 25 Oct 2006 10:22:09 -0500 Date: Wed, 25 Oct 2006 10:22:09 -0500 From: "Jim C. Nasby" To: "Craig A. James" Cc: Worky Workerson , Merlin Moncure , pgsql-performance@postgresql.org Subject: Re: Best COPY Performance Message-ID: <20061025152209.GP26892@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: <453EF7C4.2040105@modgraph-usa.com> 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:cjames@modgraph-usa.com::680S2FMESh2T4SvT:00000000000000 0000000000000000000000000Wrd X-Hashcash: 1:20:061025:worky.workerson@gmail.com::+93mBemlkjhVUoO2:000000000000 0000000000000000000000001o7T X-Hashcash: 1:20:061025:mmoncure@gmail.com::e+whQd+jYZlsxl3n:0000000000000000000 0000000000000000000000003i0E X-Hashcash: 1:20:061025:pgsql-performance@postgresql.org::zmKswSxm/2akCfGR:00000 000000000000000000000000D4zi X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.076 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200610/515 X-Sequence-Number: 21644 On Tue, Oct 24, 2006 at 10:36:04PM -0700, Craig A. James wrote: > Jim C. Nasby wrote: > >Well, given that perl is using an entire CPU, it sounds like you should > >start looking either at ways to remove some of the overhead from perl, > >or to split that perl into multiple processes. > > I use Perl for big database copies (usually with some > processing/transformation along the way) and I've never seen 100% CPU usage > except for brief periods, even when copying BLOBS and such. My typical > copy divides operations into blocks, for example doing > > N = 0 > while (more rows to go) { > begin transaction > select ... where primary_key > N order by primary_key limit 1000 > while (fetch a row) > insert into ... > N = (highest value found in last block) > commit > } > > Doing it like this in Perl should keep Postgres busy, with Perl using only > moderate resources. If you're seeing high Perl CPU usage, I'd look first > at the Perl code. Wait... so you're using perl to copy data between two tables? And using a cursor to boot? I can't think of any way that could be more inefficient... What's wrong with a plain old INSERT INTO ... SELECT? Or if you really need to break it into multiple transaction blocks, at least don't shuffle the data from the database into perl and then back into the database; do an INSERT INTO ... SELECT with that same where clause. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)