Re: archive wal's failure and load increase.

On Fri, 2006-09-29 at 11:55 -0400, Tom Lane wrote:
> Simon Riggs <simon(at)2ndquadrant(dot)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