User-Agent: Microsoft-Entourage/11.2.5.060620
Date: Mon, 30 Oct 2006 07:23:07 -0700
Subject: Re: Best COPY Performance
From: "Luke Lonergan" <llonergan@greenplum.com>
To: "Spiegelberg, Greg" <gspiegelberg@cranel.com>,
	"Worky Workerson" <worky.workerson@gmail.com>,
	"Merlin Moncure" <mmoncure@gmail.com>
cc: pgsql-performance@postgresql.org
Message-ID: <C16B58DB.56EC%llonergan@greenplum.com>
Thread-Topic: [PERFORM] Best COPY Performance
Thread-Index: Acb6Ro75zcnEF2Y5EduYOAAWy4o9DAB4rupAAAOAkH4=
In-Reply-To: 
 <82E74D266CB9B44390D3CCE44A781ED90177807C@POSTOFFICE.cranel.local>
MIME-Version: 1.0
Content-Type: text/plain;
 charset=us-ascii
Content-Transfer-Encoding: 7bit

Greg,

On 10/30/06 7:09 AM, "Spiegelberg, Greg" <gspiegelberg@cranel.com> wrote:

> I broke that file into 2 files each of 550K rows and performed 2
> simultaneous COPY's after dropping the table, recreating, issuing a sync
> on the system to be sure, &c and nearly every time both COPY's finish in
> 12 seconds.  About a 20% gain to ~91K rows/second.
> 
> Admittedly, this was a pretty rough test but a 20% savings, if it can be
> put into production, is worth exploring for us.

Did you see whether you were I/O or CPU bound in your single threaded COPY?
A 10 second "vmstat 1" snapshot would tell you/us.

With Mr. Workerson (:-) I'm thinking his benefit might be a lot better
because the bottleneck is the CPU and it *may* be the time spent in the
index building bits.

We've found that there is an ultimate bottleneck at about 12-14MB/s despite
having sequential write to disk speeds of 100s of MB/s.  I forget what the
latest bottleneck was.

- Luke