Re: archive wal's failure and load increase. - Mailing list pgsql-performance

From Simon Riggs
Subject Re: archive wal's failure and load increase.
Date
Msg-id 1159806313.2659.214.camel@holly
Whole thread Raw
In response to Re: archive wal's failure and load increase.  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-performance
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


pgsql-performance by date:

Previous
From: Simon Riggs
Date:
Subject: Re: Optimizing queries
Next
From: "Joshua D. Drake"
Date:
Subject: Re: How much memory in 32 bits Architecture to Shared Buffers