User-Agent: Microsoft-Entourage/11.2.5.060620
Date: Wed, 25 Oct 2006 08:06:36 -0700
Subject: Re: Best COPY Performance
From: "Luke Lonergan" <llonergan@greenplum.com>
To: "Worky Workerson" <worky.workerson@gmail.com>,
	"Craig A. James" <cjames@modgraph-usa.com>
cc: "Jim C. Nasby" <jim@nasby.net>, "Merlin Moncure" <mmoncure@gmail.com>,
	pgsql-performance@postgresql.org
Message-ID: <C164CB8C.51BD%llonergan@greenplum.com>
Thread-Topic: [PERFORM] Best COPY Performance
Thread-Index: Acb4RyojaGuCpmQ6EdujvAAWy4o9DA==
In-Reply-To: <ce4072df0610250503g26f32cb7r4e668613c8665774@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain;
 charset=us-ascii
Content-Transfer-Encoding: 7bit

Mr. Worky Workerson,

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

> However, I am still curious as to the rather slow COPYs from psql to
> local disks.  Like I mentioned previously, I was only seeing about 5.7
> MB/s (1.8 GB / 330 seconds), where it seemed like others were doing
> substantially better.  What sorts of things should I look into?

It's probable that you  have a really poor performing disk configuration.
Judging from earlier results, you may only be getting 3 x 5.7 = 17 MB/s of
write performance to your disks, which is about 1/4 of a single disk drive.

Please run this test and report the time here:

1) Calculate the size of 2x memory in 8KB blocks:
  # of blocks = 250,000 x memory_in_GB

Example:
  250,000 x 16GB = 4,000,000 blocks

2) Benchmark the time taken to write 2x RAM sequentially to your disk:
  time bash -c "dd if=/dev/zero of=bigfile bs=8k count=<# of blocks> &&
sync"

3) Benchmark the time taken to read same:
  time dd if=bigfile of=/dev/null bs=8k

- Luke