Re: Redesigning checkpoint_segments

On 01/01/2015 03:24 AM, Josh Berkus wrote:

Please remind me because I'm having trouble finding this in the
archives: how does wal_keep_segments interact with the new settings?

It's not very straightforward. First of all, min_recycle_wal_size has a different effect than wal_keep_segments. Raising min_recycle_wal_size causes more segments to be recycled rather than deleted, while wal_keep_segments causes old segments to be retained as old segments, so that they can be used for streaming replication. If you raise min_recycle_wal_size, it will not do any good for streaming replication.

wal_keep_segments does not affect the calculation of CheckPointSegments. If you set wal_keep_segments high enough, checkpoint_wal_size will be exceeded. The other alternative would be to force a checkpoint earlier, i.e. lower CheckPointSegments, so that checkpoint_wal_size would be honored. However, if you set wal_keep_segments high enough, higher than checkpoint_wal_size, it's impossible to honor checkpoint_wal_size no matter how frequently you checkpoint.

It's not totally straightforward to calculate what maximum size of WAL a given wal_keep_segments settings will force. wal_keep_segments is taken into account at a checkpoint, when we recycle old WAL segments. For example, imagine that prior checkpoint started at segment 95, a new checkpoint finishes at segment 100, and wal_keep_segments=10. Because of wal_keep_segments, we have to retain segments 90-95, which could otherwise be recycled. So that forces a WAL size of 10 segments, while otherwise 5 would be enough. However, before we reach the next checkpoint, let's assume it will complete at segment 105, we will consume five more segments, so the actual max WAL size is 15 segments. However, we could start recycling the segments 90-95 before we reach the next checkpoint, because wal_keep_segments stipulates how many segments from the current *insert* location needs to be retained, with not regard to checkpoints. But we only attempt to recycle segments at checkpoints.

So that could be made more straightforward if we recycled old segments in the background, between checkpoints. That might allow merging wal_keep_segments and min_recycle_wal_size settings, too: instead of renaming all old recycleable segments at a checkpoint, you could keep them around as old segments until they're actually needed for reuse, so they could be used for streaming replication up to that point.