> -----Original Message----- > From: pgsql-performance-owner@xxxxxxxxxxxxxx > [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of > Luke Lonergan > Sent: Saturday, October 28, 2006 12:07 AM > To: Worky Workerson; Merlin Moncure > Cc: pgsql-performance@xxxxxxxxxxxxxx > Subject: Re: [PERFORM] Best COPY Performance > > Worky, > > On 10/27/06 8:47 PM, "Worky Workerson" > <worky.workerson@xxxxxxxxx> wrote: > > > Are you saying that I should be able to issue multiple COPY > commands > > because my I/O wait is low? I was under the impression > that I am I/O > > bound, so multiple simeoultaneous loads would have a detrimental > > effect ... > > ... > I agree with Merlin that you can speed things up by breaking > the file up. > Alternately you can use the OSS Bizgres java loader, which > lets you specify the number of I/O threads with the "-n" > option on a single file. As a result of this thread, and b/c I've tried this in the past but never had much success at speeding the process up, I attempted just that here except via 2 psql CLI's with access to the local file. 1.1M rows of data varying in width from 40 to 200 characters COPY'd to a table with only one text column, no keys, indexes, &c took about 15 seconds to load. ~73K rows/second. 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. B/c I'll be asked, I did this on an idle, dual 3.06GHz Xeon with 6GB of memory, U320 SCSI internal drives and PostgreSQL 8.1.4. Greg
