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(at)consistentstate(dot)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