Re: Maximum number of WAL files in the pg_xlog directory

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

Please find attached to this email a documentation patch for 9.4 using this
formula.

Regards,
--
Jehan-Guillaume de Rorthais
Dalibo
http://www.dalibo.com