Hi,

As part of our monitoring work for our customers, we stumbled upon an issue with our customers' servers who have a wal_keep_segments setting higher than 0.

We have a monitoring script that checks the number of WAL files in the pg_xlog directory, according to the setting of three parameters (checkpoint_completion_target, checkpoint_segments, and wal_keep_segments). We usually add a percentage to the usual formula:

greatest(
  (2 + checkpoint_completion_target) * checkpoint_segments + 1,
  checkpoint_segments + wal_keep_segments + 1
)

And we have lots of alerts from the script for customers who set their wal_keep_segments setting higher than 0.

So we started to question this sentence of the documentation:

There will always be at least one WAL segment file, and will normally not be more than (2 + checkpoint_completion_target) * checkpoint_segments + 1 or checkpoint_segments + wal_keep_segments + 1 files.

(http://www.postgresql.org/docs/9.3/static/wal-configuration.html)

While doing some tests, it appears it would be more something like:

wal_keep_segments + (2 + checkpoint_completion_target) * checkpoint_segments + 1

But after reading the source code (src/backend/access/transam/xlog.c), the right formula seems to be:

wal_keep_segments + 2 * checkpoint_segments + 1

Here is how we went to this formula...

CreateCheckPoint(..) is responsible, among other things, for deleting and recycling old WAL files. From src/backend/access/transam/xlog.c, master branch, line 8363:

/*

 * Delete old log files (those no longer needed even for previous

 * checkpoint or the standbys in XLOG streaming).

 */

if (_logSegNo)

{

    KeepLogSeg(recptr, &_logSegNo);

    _logSegNo--;

    RemoveOldXlogFiles(_logSegNo, recptr);

}

KeepLogSeg(...) function takes care of wal_keep_segments. From src/backend/access/transam/xlog.c, master branch, line 8792:

/* compute limit for wal_keep_segments first */

if (wal_keep_segments > )

{

    /* avoid underflow, don't go below 1 */

    if (segno <= wal_keep_segments)

        segno = ;

    else

        segno = segno - wal_keep_segments;

}

IOW, the segment number (segno) is decremented according to the setting of wal_keep_segments. segno is then sent back to CreateCheckPoint(...) via _logSegNo. The RemoveOldXlogFiles() gets this segment number so that it can remove or recycle all files before this segment number. This function gets the number of WAL files to recycle with the XLOGfileslop constant, which is defined as:

/*

 * XLOGfileslop is the maximum number of preallocated future XLOG segments.

 * When we are done with an old XLOG segment file, we will recycle it as a

 * future XLOG segment as long as there aren't already XLOGfileslop future

 * segments; else we'll delete it.  This could be made a separate GUC

 * variable, but at present I think it's sufficient to hardwire it as

 * 2*CheckPointSegments+1.  Under normal conditions, a checkpoint will free

 * no more than 2*CheckPointSegments log segments, and we want to recycle all

 * of them; the +1 allows boundary cases to happen without wasting a

 * delete/create-segment cycle.

 */

#define XLOGfileslop    (2*CheckPointSegments + 1)

(in src/backend/access/transam/xlog.c, master branch, line 100)

IOW, PostgreSQL will keep wal_keep_segments WAL files before the current WAL file, and then there may be 2*CheckPointSegments + 1 recycled ones. Hence the formula:

wal_keep_segments + 2 * checkpoint_segments + 1

And this is what we usually find in our customers' servers. We may find more WAL files, depending on the write activity of the cluster, but in average, we get this number of WAL files.

AFAICT, the documentation is wrong about the usual number of WAL files in the pg_xlog directory. But I may be wrong, in which case, the documentation isn't clear enough for me, and should be fixed so that others can't misinterpret it like I may have done.

Any comments? did I miss something, or should we fix the documentation?

Thanks.

Bruce Momjian:

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.

The original calculation is summarized in this email:

http://www.postgresql.org/message-id/AANLkTi=e=oR54OuxAw88=dtV4wt0e5edMiGaeZtBVcKO@...

However, in my reading of this, it appears to be double-counting the WAL 
files during the checkpoint, e.g. the checkpoint_completion_target * 
checkpoint_segments WAL files are also part of the later 
checkpoint_segments number.

I also don't see how that can be equivalent to:

checkpoint_segments + wal_keep_segments + 1

because wal_keep_segments isn't used in the first calculation.  Is the 
user supposed to compute the maximum of those two?  Seems easier to just 
give one expression.

Is the right answer:

max(checkpoint_segments, wal_keep_segments) + checkpoint_segments + 1

or, if you want to use checkpoint_completion_target, it would be:

max(checkpoint_segments * checkpoint_completion_target, wal_keep_segments) + checkpoint_segments + 1

Is checkpoint_completion_target accurate enough to define a maximum 
number of files?

I think I need Masao Fujii's comments on this.  The fact the user is 
seeing something different from what is documented means something 
probably needs updating.

Jeff Janes:

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.