From: Markus Schaber <schabi@logix-tt.com>
To: Worky Workerson <worky.workerson@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Best COPY Performance
Date: Wed, 25 Oct 2006 14:18:52 +0200
Message-ID: <453F562C.60609@logix-tt.com> (raw)
In-Reply-To: <ce4072df0610230840r4b3df653h3144dab06bdb9ed2@mail.gmail.com>
References: <ce4072df0610201305u7836dc7bn437ec0aaf1c3e25f@mail.gmail.com>
<453C8B0D.4070803@logix-tt.com>
<ce4072df0610230840r4b3df653h3144dab06bdb9ed2@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: schabi@logix-tt.com, worky.workerson@gmail.com
Subject: Re: Best COPY Performance
In-Reply-To: <453F562C.60609@logix-tt.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox