From: Craig A. James <cjames@modgraph-usa.com>
To: Jim C. Nasby <jim@nasby.net>
Cc: Worky Workerson <worky.workerson@gmail.com>
Cc: Merlin Moncure <mmoncure@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Best COPY Performance
Date: Tue, 24 Oct 2006 22:36:04 -0700
Message-ID: <453EF7C4.2040105@modgraph-usa.com> (raw)
In-Reply-To: <20061025001709.GA26892@nasby.net>
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>
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.
Craig
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: cjames@modgraph-usa.com, jim@nasby.net, worky.workerson@gmail.com, mmoncure@gmail.com
Subject: Re: Best COPY Performance
In-Reply-To: <453EF7C4.2040105@modgraph-usa.com>
* 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