Waiting for 9.5 – Replace checkpoint_segments with min_wal_size and max_wal_size.

On 23rd of February, Heikki Linnakangas committed patch:

Replace checkpoint_segments with min_wal_size and max_wal_size.
 
Instead of having a single knob (checkpoint_segments) that both triggers
checkpoints, and determines how many checkpoints to recycle, they are now
separate concerns. There is still an internal variable called
CheckpointSegments, which triggers checkpoints. But it no longer determines
how many segments to recycle at a checkpoint. That is now auto-tuned by
keeping a moving average of the distance between checkpoints (in bytes),
and trying to keep that many segments in reserve. The advantage of this is
that you can set max_wal_size very high, but the system won't actually
consume that much space if there isn't any need for it. The min_wal_size
sets a floor for that; you can effectively disable the auto-tuning behavior
by setting min_wal_size equal to max_wal_size.
 
The max_wal_size setting is now the actual target size of WAL at which a
new checkpoint is triggered, instead of the distance between checkpoints.
Previously, you could calculate the actual WAL usage with the formula
"(2 + checkpoint_completion_target) * checkpoint_segments + 1". With this
patch, you set the desired WAL usage with max_wal_size, and the system
calculates the appropriate CheckpointSegments with the reverse of that
formula. That's a lot more intuitive for administrators to set.
 
Reviewed by Amit Kapila and Venkata Balaji N.

The commit message describes pretty well what's happening, but I thought it's nice to mention it anyway.

Since ever we had “checkpoint_segments" – setting with very low default value, that could caused some problems (poor performance).

Now, the configuration is nicer, easier to read, and much easier to set depending on disk usage.

Just checked, that by default 9.5devel has these settings:

  • min_wal_size = 80MB
  • max_wal_size = 128MB

This is more or less equivalent of checkpoint_segments of 3. Which is the old default.

The thing is that now, we have easy to see that the directory (pg_xlog/) should never be larger than 128MB. (well, I would guess that wal_keep_segments, and potential failing archive commands might make it larger, but in general case, without problems, and other configuration settings, this is how much it should take.

What's really great, in my opinion, is that now we can set the max to be large if we know it will be needed, but then the disk space usage will drop when the segments will be no longer needed.

Cool stuff. I really do like it.