From: Greg Smith <gsmith@gregsmith.com>
To: Marinos Yannikos <mjy@geizhals.at>
Cc: pgsql-performance@postgresql.org
Subject: Re: Background writer underemphasized ...
Date: Wed, 16 Apr 2008 15:45:22 -0400 (EDT)
Message-ID: <Pine.GSO.4.64.0804161524410.27404@westnet.com> (raw)
In-Reply-To: <480619CA.2080904@geizhals.at>
References: <480619CA.2080904@geizhals.at>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gsmith@gregsmith.com, mjy@geizhals.at
Subject: Re: Background writer underemphasized ...
In-Reply-To: <Pine.GSO.4.64.0804161524410.27404@westnet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox