Re: background writer being lazy?

 version                       | PostgreSQL 8.4.1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (SUSE Linux) 4.3.2 [gcc-4_3-branch revision 141291], 64-bit
 bgwriter_delay                | 50ms
 bgwriter_lru_maxpages         | 500
 bgwriter_lru_multiplier       | 4
 checkpoint_segments           | 100
 checkpoint_timeout            | 40min
 checkpoint_warning            | 30min
 checkpoint_completion_target  | 0.5
 effective_cache_size          | 16GB
 effective_io_concurrency      | 4
 extra_float_digits            | 3
 max_connections               | 2000
 max_stack_depth               | 7MB
 shared_buffers                | 16GB
 synchronous_commit            | off
 temp_buffers                  | 8192
 TimeZone                      | US/Pacific
 wal_buffers                   | 8MB
 work_mem                      | 64MB


Since our checkpoint_completion_target is 0.5, we complete a checkpoint around 20 mins after it started, and checkpoints occur like clockwork every 40 minutes, no extra's are forced.

Performance overall is fairly good, but of course we're trying to squeeze as much out of it as we can. One main thing is trying to lower 'spikey' disk IO so that performance is more consistent at any given time.

- Brian F

On 11/01/2011 12:44 PM, Kevin Grittner wrote:

Brian Fehrle <brianf@consistentstate.com> wrote:

On 11/01/2011 08:58 AM, Kevin Grittner wrote:

What problem are you trying to solve?

We're trying to offload dirty buffer writes from checkpoints and
from backends (not the bgwriter) to the background writer, I
believe with the hope that it's the same amount of disk IO, but
spread out evenly rather than just when a checkpoint is occurring.

 
What version of PostgreSQL are you using?  Recent versions spread
out the checkpoint activity using the same process which does the
background writing, so there is no benefit from moving writes from
its background writing phase to its distributed checkpoint phase. 
Depending on your setting of checkpoint_completion_target you are
probably spending as much or more time spreading the checkpoint as
doing background writing between checkpoints.  Each of the last few
major releases has made this much better, so if you're spending time
tweaking something prior to 9.1, you'd probably be better served
putting that time into upgrading.

Yes.  Writing dirty buffers when there are enough buffers
available to service requests would tend to increase overall disk
writes and degrade performance.  You don't have a problem unless
you have a high percentage of writes from normal backends which
need to flush a buffer in order to get one.

This seems to be the case, as buffers_backend is between 
checkpoint_buffers and clean_buffers from pg_stat_bgwriter.
For example, on 2011-10-19:
checkpoint buffers: 622,519
clean_buffers:  65,879
clean_max_written: 56
backend_buffers: 460,471

Am I reading these right in wanting to reduce backend_buffers and 
checkpoint_buffers?

 
Hmm.  That is a higher percentage of backend writes than I would
like to see.  What is your shared_memory setting?  Actually, please
show the results of the query here:
http://wiki.postgresql.org/wiki/Server_Configuration
How is your performance?
-Kevin