Re: Best COPY 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