DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=ijNPL7vlwh9UKwfe5d+9ezx2Tk+zm3Wl3k5L2C3PQPJ8+h3q0hXtA+aXFa5vQ3E9x1HCvlVnPpQhYGDKZIrcKg8Am2Y6mCZB8+VlNDrXoZotUi0CDJjglp6NZeNrfLQGyFzuWpX7fznmXwSOo/KSM8Whnq2Qdke0zVc4sLb/VGA=
Message-ID: <ce4072df0610230840r4b3df653h3144dab06bdb9ed2@mail.gmail.com>
Date: Mon, 23 Oct 2006 11:40:57 -0400
From: "Worky Workerson" <worky.workerson@gmail.com>
To: "Markus Schaber" <schabi@logix-tt.com>
Subject: Re: Best COPY Performance
Cc: pgsql-performance@postgresql.org
In-Reply-To: <453C8B0D.4070803@logix-tt.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <ce4072df0610201305u7836dc7bn437ec0aaf1c3e25f@mail.gmail.com>
	<453C8B0D.4070803@logix-tt.com>

Markus,

> Could you COPY one of your tables out to disk via psql, and then COPY it
> back into the database, to reproduce this measurement with your real data?

$ 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

> Also, how much is the disk load, and CPU usage?

  When I am loading via the perl (which I've established is a
bottleneck), the one CPU core is at 99% for the perl and another is at
30% for a postmaster, vs about 90% for the postmaster when going
through psql.

The disk load is where I start to get a little fuzzy, as I haven't
played with iostat to figure what is "normal".  The local drives
contain PG_DATA as well as all the log files, but there is a
tablespace on the FibreChannel SAN that contains the destination
table.  The disk usage pattern that I see is that there is a ton of
consistent activity on the local disk, with iostat reporting an
average of 30K Blk_wrtn/s, which I assume is the log files.  Every
several seconds there is a massive burst of activity on the FC
partition, to the tune of 250K Blk_wrtn/s.

> 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?

Thanks!