Re: Bgwriter strategies - Mailing list pgsql-hackers

On Fri, 2007-07-06 at 10:55 +0100, Heikki Linnakangas wrote:

> We need to get the requirements straight.
> 
> One goal of bgwriter is clearly to keep just enough buffers clean in 
> front of the clock hand so that backends don't need to do writes 
> themselves until the next bgwriter iteration. But not any more than 
> that, otherwise we might end up doing more writes than necessary if some 
> of the buffers are redirtied.

The purpose of the WAL/shared buffer cache is to avoid having to write
all of the data blocks touched by a transaction to disk before end of
transaction, thus increasing request response time. That purpose is only
fulfilled iff using the shared buffer cache does not require us to write
out someone else's dirty buffers, while avoiding our own. The bgwriter
exists specifically to clean the dirty buffers, so that users do not
have to clean theirs or anybody else's dirty buffers.

> To deal with bursty workloads, for example a batch of 2 GB worth of 
> inserts coming in every 10 minutes, it seems we want to keep doing a 
> little bit of cleaning even when the system is idle, to prepare for the 
> next burst. The idea is to smoothen the physical I/O bursts; if we don't 
> clean the dirty buffers left over from the previous burst during the 
> idle period, the I/O system will be bottlenecked during the bursts, and 
> sit idle otherwise.

In short, bursty workloads are the normal situation.

When capacity is not saturated the bgwriter can utilise the additional
capacity to reduce statement response times.

It is standard industry practice to avoid running a system at peak
throughout for long periods of time, so DBT-2 does not represent a
normal situation. This is because the response times are only
predictable on a non-saturated system and most apps have some implicit
or explicit service level objective. 

However, the server needs to cope with periods of saturation, so must be
able to perform efficiently during those times. 

So I see there are two modes of operation:

i) dirty block write offload when capacity is available
ii) efficient operation when server is saturated.

DBT-2 represents only the second mode of operation; the two modes are
equally important, yet mode i) is the ideal situation. 

> To strike a balance between cleaning buffers ahead of possible bursts in 
> the future and not doing unnecessary I/O when no such bursts come, I 
> think a reasonable strategy is to write buffers with usage_count=0 at a 
> slow pace when there's no buffer allocations happening.

Agreed.

--  Simon Riggs EnterpriseDB  http://www.enterprisedb.com