From: Simon Riggs <simon@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: cedric@dreamgnu.com
Cc: pgsql-performance@postgresql.org
Subject: Re: archive wal's failure and load increase.
Date: Mon, 02 Oct 2006 17:25:13 +0100
Message-ID: <1159806313.2659.214.camel@holly> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: simon@2ndquadrant.com, tgl@sss.pgh.pa.us, cedric@dreamgnu.com
Subject: Re: archive wal's failure and load increase.
In-Reply-To: <1159806313.2659.214.camel@holly>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox