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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
