DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=Wet59KSsvoZNijjw//7eoiNWSSkV+VtAz6sAmb9uJSZFABhUitaVky3hYQCSrZWd/rwkzMx4LRP6VxvrjB8kV9SlBPrHZLjytHSZcTZqmlZALchTk8pR9x1jws8m/TV3i7Rv9EfmyDdYWAgYYQIYCVQqDmA6iJelX89lGswQ9O0=
Message-ID: <ce4072df0610250503g26f32cb7r4e668613c8665774@mail.gmail.com>
Date: Wed, 25 Oct 2006 08:03:38 -0400
From: "Worky Workerson" <worky.workerson@gmail.com>
To: "Craig A. James" <cjames@modgraph-usa.com>
Subject: Re: Best COPY Performance
Cc: "Jim C. Nasby" <jim@nasby.net>, "Merlin Moncure" <mmoncure@gmail.com>,
	pgsql-performance@postgresql.org
In-Reply-To: <453EF7C4.2040105@modgraph-usa.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
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 10/25/06, Craig A. James <cjames@modgraph-usa.com> 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

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.

However, I am still curious as to the rather slow COPYs from psql to
local disks.  Like I mentioned previously, I was only seeing about 5.7
MB/s (1.8 GB / 330 seconds), where it seemed like others were doing
substantially better.  What sorts of things should I look into?

Thanks!