Skip site navigation (1) Skip section navigation (2)

Re: Best COPY Performance

From: Markus Schaber <schabi(at)logix-tt(dot)com>
To: Worky Workerson <worky(dot)workerson(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Best COPY Performance
Date: 2006-10-25 12:18:52
Message-ID: 453F562C.60609@logix-tt.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi, Worky,

Worky Workerson wrote:

> $ psql -c "COPY my_table TO STDOUT" > my_data
> $ ls my_data
> 2018792 edgescape_pg_load
> $ time cat my_data | psql -c "COPY mytable FROM STDIN"
> real    5m43.194s
> user    0m35.412s
> sys     0m9.567s

That's via PSQL, and you get about 5 MB/Sec.

>> On a table with no indices, triggers and contstraints, we managed to
>> COPY about 7-8 megabytes/second with psql over our 100 MBit network, so
>> here the network was the bottleneck.
> 
> hmm, this makes me think that either my PG config is really lacking,
> or that the SAN is badly misconfigured, as I would expect it to
> outperform a 100Mb network.  As it is, with a straight pipe to psql
> COPY, I'm only working with a little over 5.5 MB/s.  Could this be due
> to the primary key index updates?

Yes, index updates cause both CPU load, and random disk access (which is
slow by nature).


HTH,
Markus
-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org

In response to

pgsql-performance by date

Next:From: Merlin MoncureDate: 2006-10-25 13:20:02
Subject: Re: Best COPY Performance
Previous:From: Alex StapletonDate: 2006-10-25 12:16:15
Subject: Re: Best COPY Performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group