Subject: Re: archive wal's failure and load increase.
From: Simon Riggs <simon@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: cedric@dreamgnu.com, pgsql-performance@postgresql.org
In-Reply-To: <16749.1159545336@sss.pgh.pa.us>
References: <451C2572.20800@dreamgnu.com> <1159527513.2767.296.camel@holly>
	<15270.1159540162@sss.pgh.pa.us> <1159541620.2649.10.camel@holly>
	<16749.1159545336@sss.pgh.pa.us>
Content-Type: text/plain
Date: Mon, 02 Oct 2006 17:25:13 +0100
Message-Id: <1159806313.2659.214.camel@holly>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Fri, 2006-09-29 at 11:55 -0400, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > PreallocXlogFiles() adds only a *single* xlog file, sometimes.
> 
> Hm, you are right.  I wonder why it's so unaggressive ... perhaps
> because under normal circumstances we soon settle into a steady
> state where each checkpoint recycles the right number of files.

That is normally the case, yes. But only for people that have correctly
judged (or massively overestimated) what checkpoint_segments should be
set at.

Currently, when we don't have enough we add one, maybe. When we have too
many we truncate right back to checkpoint_segments as quickly as
possible.

Seems like we should try and automate that completely for 8.3:
- calculate the number required by keeping a running average which
ignores a single peak value, yet takes 5 consistently high values as the
new average
- add more segments with increasing aggressiveness 1,1,2,3,5,8 segments
at a time when required 
- handle out-of-space errors fairly gracefully by waking up the
archiver, complaining to the logs and then eventually preventing
transactions from writing to logs rather than taking server down
- shrink back more slowly by halving the difference between the
overlimit and the typical value
- get rid of checkpoint_segments GUC

That should handle peaks caused by data loads, archiving interruptions
or other peak loadings.

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