Message-ID: <4EB03821.6090408@consistentstate.com>
Date: Tue, 01 Nov 2011 12:19:13 -0600
From: Brian Fehrle <brianf@consistentstate.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686 (x86_64); en-US;
	rv:1.9.2.18) Gecko/20110616 Lightning/1.0b2 Thunderbird/3.1.11
MIME-Version: 1.0
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
CC: pgsql-admin@postgresql.org,
 Kevin Kempter <kevink@consistentstate.com>
Subject: Re: background writer being lazy?
References: <4EAF2F20.7080205@consistentstate.com>
	<4EAFC2CA02000025000428D2@gw.wicourts.gov>
In-Reply-To: <4EAFC2CA02000025000428D2@gw.wicourts.gov>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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