Re: background writer being lazy?

From: Brian Fehrle <brianf(at)consistentstate(dot)com>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: pgsql-admin(at)postgresql(dot)org, Kevin Kempter <kevink(at)consistentstate(dot)com>
Subject: Re: background writer being lazy?
Date: 2011-11-01 18:19:13
Message-ID: 4EB03821.6090408@consistentstate.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

On 11/01/2011 08:58 AM, Kevin Grittner wrote:
> Brian Fehrle<brianf(at)consistentstate(dot)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

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Brian Fehrle 2011-11-01 19:14:59 Re: background writer being lazy?
Previous Message Vinay 2011-11-01 11:44:27 Re: streaming replication