Re: Background writer underemphasized ...

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