Re: Maximum number of WAL files in the pg_xlog directory

On Wed, Apr 1, 2015 at 7:00 PM, Jehan-Guillaume de Rorthais
<jgdr(at)dalibo(dot)com> wrote:
> Hi,
>
> As I'm writing a doc patch for 9.4 -> 9.0, I'll discuss below on this formula
> as this is the last one accepted by most of you.
>
> On Mon, 3 Nov 2014 12:39:26 -0800
> Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>
>> It looked to me that the formula, when descending from a previously
>> stressed state, would be:
>>
>> greatest(1 + checkpoint_completion_target) * checkpoint_segments,
>> wal_keep_segments) + 1 +
>> 2 * checkpoint_segments + 1
>
> It lacks a closing parenthesis. I guess the formula is:
>
> greatest (
> (1 + checkpoint_completion_target) * checkpoint_segments,
> wal_keep_segments
> )
> + 1 + 2 * checkpoint_segments + 1
>
>> This assumes logs are filled evenly over a checkpoint cycle, which is
>> probably not true because there is a spike in full page writes right after
>> a checkpoint starts.
>>
>> But I didn't have a great deal of confidence in my analysis.
>
> The only problem I have with this formula is that considering
> checkpoint_completion_target ~ 1 and wal_keep_segments = 0, it becomes:
>
> 4 * checkpoint_segments + 2
>
> Which violate the well known, observed and default one:
>
> 3 * checkpoint_segments + 1
>
> A value above this formula means the system can not cope with the number of
> file to flush. The doc is right about that:
>
> If, due to a short-term peak of log output rate, there
> are more than 3 * <varname>checkpoint_segments</varname> + 1
> segment files, the unneeded segment files will be deleted
>
> The formula is wrong in the doc when wal_keep_segments <> 0
>
>> The first line reflects the number of WAL that will be retained as-is,
>
> I agree with this files MUST be retained: the set of checkpoint_segments WALs
> beeing flushed and the checkpoint_completion_target ones written in
> the meantime.
>
>> the second is the number that will be recycled for future use before starting
>> to delete them.
>
> disagree cause the WAL files beeing written are actually consuming recycled
> WALs in the meantime.
>
> Your formula expect written files are created and recycled ones never touched,
> leading to this checkpoint_segment + 1 difference between formulas.
>
>> My reading of the code is that wal_keep_segments is computed from the
>> current end of WAL (i.e the checkpoint record), not from the checkpoint
>> redo point. If I distribute the part outside the 'greatest' into both
>> branches of the 'greatest', I don't get the same answer as you do for
>> either branch.
>
> So The formula, using checkpoint_completion_target=1, should be:
>
> greatest (
> checkpoint_segments,
> wal_keep_segments
> )
> + 2 * checkpoint_segments + 1

No. Please imagine how many WAL files can exist at the end of checkpoint.
At the end of checkpoint, we have to leave all the WAL files which were
generated since the starting point of previous checkpoint for the future
crash recovery. The number of these WAL files is

(1 + checkpoint_completion_target) * checkpoint_segments

or

wal_keep_segments

In addition to these files, at the end of checkpoint, old WAL files which were
generated before the starting point of previous checkpoint are recycled.
The number of these WAL files is at most

2 * checkpoint_segments + 1

Note that *usually* there are not such many WAL files at the end of
checkpoint. But this can happen after the peak of WAL logging generates
too much WAL files.

So the sum of those is the right formula, i.e.,

(3 + checkpoint_completion_target) * checkpoint_segments + 1

or

wal_keep_segments + 2 * checkpoint_segments + 1

If checkpoint_completion_target is 1 and wal_keep_segments is 0,
it can become 4 * checkpoint_segments + 1.

Regards,

--
Fujii Masao