From: Jim C. Nasby <jim@nasby.net>
To: Craig A. James <cjames@modgraph-usa.com>
Cc: Worky Workerson <worky.workerson@gmail.com>
Cc: Merlin Moncure <mmoncure@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Best COPY Performance
Date: Wed, 25 Oct 2006 10:22:09 -0500
Message-ID: <20061025152209.GP26892@nasby.net> (raw)
In-Reply-To: <453EF7C4.2040105@modgraph-usa.com>
References: <ce4072df0610201305u7836dc7bn437ec0aaf1c3e25f@mail.gmail.com>
<b42b73150610201811m84369f7kc7d58b39aed3ad73@mail.gmail.com>
<ce4072df0610230810t5d66bbb8xf1e9c0ed71b6b6c0@mail.gmail.com>
<20061023215913.GW26892@nasby.net>
<ce4072df0610240617s72f35540odd3d4e0903313b63@mail.gmail.com>
<20061025001709.GA26892@nasby.net>
<453EF7C4.2040105@modgraph-usa.com>
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jim@nasby.net, cjames@modgraph-usa.com, worky.workerson@gmail.com, mmoncure@gmail.com
Subject: Re: Best COPY Performance
In-Reply-To: <20061025152209.GP26892@nasby.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox