User-Agent: Microsoft-Entourage/11.2.5.060620
Date: Wed, 25 Oct 2006 13:13:25 -0700
Subject: Re: Best COPY Performance
From: "Luke Lonergan" <llonergan@greenplum.com>
To: "Worky Workerson" <worky.workerson@gmail.com>
cc: "Merlin Moncure" <mmoncure@gmail.com>,
	"Markus Schaber" <schabi@logix-tt.com>, pgsql-performance@postgresql.org
Message-ID: <C1651375.523A%llonergan@greenplum.com>
Thread-Topic: [PERFORM] Best COPY Performance
Thread-Index: Acb4cgbCRWXzTGRlEduYOAAWy4o9DA==
In-Reply-To: <ce4072df0610251126w1eaca026x7751256f962b131f@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain;
 charset=us-ascii
Content-Transfer-Encoding: 7bit

Mr. Worky,

On 10/25/06 11:26 AM, "Worky Workerson" <worky.workerson@gmail.com> wrote:

> And here are the dd results for 16GB RAM, i.e. 4,000,000 8K blocks:

So, if we divide 32,000 MB by the real time, we get:

/home (WAL):
53 MB/s write
84 MB/s read

/data (data):
89 MB/s write
38 MB/s read

The write and read speeds on /home look like a single disk drive, which is
not good if you have more drives in a RAID.  OTOH, it should be sufficient
for WAL writing and you should think that the COPY speed won't be limited by
WAL.

The read speed on your /data volume is awful to the point where you should
consider it broken and find a fix.  A quick comparison: the same number on a
16 drive internal SATA array with 7200 RPM disks gets 950 MB/s read, about
25 times faster for about 1/4 the price.

But again, this may not have anything to do with the speed of your COPY
statements.

Can you provide about 10 seconds worth of "vmstat 1" while running your COPY
so we can get a global view of the I/O and CPU?

- Luke