Date: Tue, 29 Apr 2008 15:16:22 +0000
From: John Rouillard <rouilj@renesys.com>
To: pgsql-performance@postgresql.org
Subject: Re: Very poor performance loading 100M of sql data using copy
Message-ID: <20080429151622.GP6622@renesys.com>
References: <20080428172431.GI6622@renesys.com>
	<Pine.GSO.4.64.0804281404410.14083@westnet.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <Pine.GSO.4.64.0804281404410.14083@westnet.com>
User-Agent: Mutt/1.4.1i

On Mon, Apr 28, 2008 at 02:16:02PM -0400, Greg Smith wrote:
> 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.

Ok. I can do that. I chose 30 to make the WAL logs span the 5 minute

  checkpoint_timeout = 300

so that the 30 segments wouldn't wrap over before the 5 minute
checkpoint that usually occurs. Maybe I should increase both the
timeout and the segments?
 
> >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).

I forgot to mention the raid 1/0 is on a 3ware 9550SX-4LP raid card
setup as raid 1/0. The write cache is on and autoverify is turned off.

> Theoretically the OS is caching around that situation but 
> better to avoid it. 

The system is using 6-8MB of memory for cache.

> You didn't say how much RAM you have,

16GB total, but 8GB or so is taken up with other processes.

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

So swap the memory usage from the OS cache to the postgresql process.
Using 1/4 as a guideline it sounds like 600,000 (approx 4GB) is a
better setting. So I'll try 300000 to start (1/8 of memory) and see
what it does to the other processes on the box.
 
> While not relevant to this exercise you'll need to set 
> effective_cache_size to a useful value one day as well.

This is a very lightly loaded database, a few queries/hour usually
scattered across the data set, so hopefully that won't be much of an
issue.

-- 
				-- rouilj

John Rouillard
System Administrator
Renesys Corporation
603-244-9084 (cell)
603-643-9300 x 111