Date: Mon, 28 Apr 2008 14:16:02 -0400 (EDT)
From: Greg Smith <gsmith@gregsmith.com>
To: John Rouillard <rouilj@renesys.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Very poor performance loading 100M of sql data using
 copy
In-Reply-To: <20080428172431.GI6622@renesys.com>
Message-ID: <Pine.GSO.4.64.0804281404410.14083@westnet.com>
References: <20080428172431.GI6622@renesys.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed

On Mon, 28 Apr 2008, John Rouillard wrote:

>    2008-04-21 11:36:43 UTC @(2761)i: LOG: checkpoints ... (27 seconds apart)
> so I changed:
>   checkpoint_segments = 30
>   checkpoint_warning = 150

That's good, but you might go higher than 30 for a bulk loading operation 
like this, particularly on 8.1 where checkpoints are no fun.  Using 100 is 
not unreasonable.

> shared_buffers = 3000
> I don't see any indication in the docs that increasing shared memory
> would help speed up a copy operation.

The index blocks use buffer space, and what ends up happening if there's 
not enough memory is they are written out more than they need to be (and 
with your I/O hardware you need to avoid writes unless absolutely 
necessary).  Theoretically the OS is caching around that situation but 
better to avoid it.  You didn't say how much RAM you have, but you should 
start by a factor of 10 increase to 30,000 and see if that helps; if so, 
try making it large enough to use 1/4 of total server memory.  3000 is 
only giving the server 24MB of RAM to work with, and it's unfair to expect 
it to work well in that situation.

While not relevant to this exercise you'll need to set 
effective_cache_size to a useful value one day as well.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD