Re: background writer being lazy?

On 11/01/2011 08:58 AM, Kevin Grittner wrote:
> Brian Fehrle<brianf@consistentstate.com>  wrote:
>
>> So my main question is, where is the issue?
>
> That's my question, too.  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.

>> It doesn't seem (to me) that the background writer is having a
>> hard time keeping up, because there are simply tons of times where
>> it's doing nothing. So is it just not determining that it needs to
>> do anything because there are already enough 'clean buffers' ready
>> for use at any given time?
>
> 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?

- Brian F

>> Would increasing bgwriter_lru_multiplier to a higher value help
>> get more to be written by the bgwriter, and if so are there any
>> negative side effects I would need to consider for this?
>
> Yes.  If a buffer is written to again by PostgreSQL after it hits
> disk because your background writer was overly aggressive, you will
> hurt your overall throughput.  Now, sometimes that's worth doing to
> control latency, but you haven't described any such problem.
>
> -Kevin