From: Greg Smith <greg@2ndQuadrant.com>
To: pgsql-admin@postgresql.org
Subject: Re: background writer being lazy?
Date: Sat, 12 Nov 2011 13:51:05 -0500
Message-ID: <4EBEC019.4040903@2ndQuadrant.com> (raw)
In-Reply-To: <4EAF2F20.7080205@consistentstate.com>
References: <4EAF2F20.7080205@consistentstate.com>
On 10/31/2011 07:28 PM, Brian Fehrle wrote:
> The main thing I am currently seeing is that there are 300X or more
> buffers written by checkpoints rather than background writer.
>
Writing buffers at checkpoint time is more efficient than having the
background writer handle them. I think your efforts to space
checkpoints out may have backfired a bit on you. You're letting 40
minutes of dirty buffers accumulate before they're written out. Putting
checkpoint_timeout closer to its default of 5 minutes again may reduce
the spikes you're seeing.
The changes you've made to the background writer configuration are also
counterproductive, given that it's not really going to trigger anyway.
I would only recommend decreasing bgwriter_delay or increasing
bgwriter_lru_maxpages or you see the total_clean_max_written value get
incremented regularly. If that's not happening, making the background
writer run more often and try to do more work just adds overhead.
Also: you've set shared_buffers to 16GB. That's beyond where most
people find increases to stop being useful. I'd wager you'll get less
spiky performance just by lowering that a lot. The 256MB to 1GB range
is where I normally end up on servers where lower latency is prioritized
instead of maximum throughput.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
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-admin@postgresql.org
Cc: greg@2ndQuadrant.com
Subject: Re: background writer being lazy?
In-Reply-To: <4EBEC019.4040903@2ndQuadrant.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