Re: Maximum number of WAL files in the pg_xlog directory

On Tue, Oct 14, 2014 at 09:20:22AM -0700, Jeff Janes wrote:
> On Mon, Oct 13, 2014 at 12:11 PM, Bruce Momjian <bruce@momjian.us> wrote:
> 
> 
>     I looked into this, and came up with more questions.  Why is
>     checkpoint_completion_target involved in the total number of WAL
>     segments?  If checkpoint_completion_target is 0.5 (the default), the
>     calculation is:
> 
>             (2 + 0.5) * checkpoint_segments + 1
> 
>     while if it is 0.9, it is:
> 
>             (2 + 0.9) * checkpoint_segments + 1
> 
>     Is this trying to estimate how many WAL files are going to be created
>     during the checkpoint?  If so, wouldn't it be (1 +
>     checkpoint_completion_target), not "2 +".  My logic is you have the old
>     WAL files being checkpointed (that's the "1"), plus you have new WAL
>     files being created during the checkpoint, which would be
>     checkpoint_completion_target * checkpoint_segments, plus one for the
>     current WAL file.
> 
> 
> WAL is not eligible to be recycled until there have been 2 successful
> checkpoints.
> 
> So at the end of a checkpoint, you have 1 cycle of WAL which has just become
> eligible for recycling,
> 1 cycle of WAL which is now expendable but which is kept anyway, and
> checkpoint_completion_target worth of WAL which has occurred while the
> checkpoint was occurring and is still needed for crash recovery.

OK, so based on this analysis, what is the right calculation?  This?
(1 + checkpoint_completion_target) * checkpoint_segments + 1 +max(wal_keep_segments, checkpoint_segments)

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +