Date: Wed, 16 Apr 2008 15:45:22 -0400 (EDT)
From: Greg Smith <gsmith@gregsmith.com>
To: Marinos Yannikos <mjy@geizhals.at>
cc: pgsql-performance@postgresql.org
Subject: Re: Background writer underemphasized ... 
In-Reply-To: <480619CA.2080904@geizhals.at>
Message-ID: <Pine.GSO.4.64.0804161524410.27404@westnet.com>
References: <480619CA.2080904@geizhals.at>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed

On Wed, 16 Apr 2008, Marinos Yannikos wrote:

> to save some people a headache or two: I believe we just solved our 
> performance problem in the following scenario:

I was about to ask your PostgreSQL version but since I see you mention 
wal_writer_delay it must be 8.3.  Knowing your settings for shared_buffers 
and checkpoint_segments in particular would make this easier to 
understand.

You also didn't mention what disk controller you have, or how much write 
cache it has (if any).

> This helped with our configuration:
> bgwriter_delay = 10000ms         # 10-10000ms between rounds
> bgwriter_lru_maxpages = 1000     # 0-1000 max buffers written/round

The default for bgwriter_delay is 200ms = 5 passes/second.  You're 
increasing that to 10000ms means one pass every 10 seconds instead. 
That's almost turning the background writer off.  If that's what improved 
your situation, you might as well as turn it off altogether by setting all 
the bgwriter_lru_maxpages parameters to be 0.  The combination you 
describe here, running very infrequently but with lru_maxpages set to its 
maximum, is a bit odd.

> Other options we have tried/used were shared_buffers between 200MB and 
> 20GB, wal_buffers = 256MB, wal_writer_delay=5000ms ...

The useful range for wal_buffers tops at around 1MB, so no need to get 
extreme there.  wal_writer_delay shouldn't matter here unless you turned 
on asyncronous commit.

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