Redesigning checkpoint_segments

checkpoint_segments is awkward. From an admin's point of view, it 
controls two things:

1. it limits the amount of disk space needed for pg_xlog. (it's a soft 
limit, but still)
2. it limits the time required to recover after a crash.

For limiting the disk space needed for pg_xlog, checkpoint_segments is 
awkward because it's defined in terms of 16MB segments between 
checkpoints. It takes a fair amount of arithmetic to calculate the disk 
space required to hold the specified number of segments. The manual 
gives the formula: (2 + checkpoint_completion_target) * 
checkpoint_segments + 1, which amounts to about 1GB per 20 segments as a 
rule of thumb. We shouldn't impose that calculation on the user. It 
should be possible to just specify "checkpoint_segments=512MB", and the 
system would initiate checkpoints so that the total size of WAL in 
pg_xlog stays below 512MB.

For limiting the time required to recover after crash, 
checkpoint_segments is awkward because it's difficult to calculate how 
long recovery will take, given checkpoint_segments=X. A bulk load can 
use up segments really fast, and recovery will be fast, while segments 
full of random deletions can need a lot of random I/O to replay, and 
take a long time. IMO checkpoint_timeout is a much better way to control 
that, although it's not perfect either.

A third point is that even if you have 10 GB of disk space reserved for 
WAL, you don't want to actually consume all that 10 GB, if it's not 
required to run the database smoothly. There are several reasons for 
that: backups based on a filesystem-level snapshot are larger than 
necessary, if there are a lot of preallocated WAL segments and in a 
virtualized or shared system, there might be other VMs or applications 
that could make use of the disk space. On the other hand, you don't want 
to run out of disk space while writing WAL - that can lead to a PANIC in 
the worst case.


In VMware's vPostgres fork, we've hacked the way that works, so that 
there is a new setting, checkpoint_segments_max that can be set by the 
user, but checkpoint_segments is adjusted automatically, on the fly. The 
system counts how many segments were consumed during the last checkpoint 
cycle, and that becomes the checkpoint_segments setting for the next 
cycle. That means that in a system with a steady load, checkpoints are 
triggered by checkpoint_timeout, and the effective checkpoint_segments 
value converges at the exact number of segments needed for that. That's 
simple but very effective. It doesn't behave too well with bursty load, 
however; during quiet times, checkpoint_segments is dialed way down, and 
when the next burst comes along, you get several checkpoints in quick 
succession, until checkpoint_segments is dialed back up again.


I propose that we do something similar, but not exactly the same. Let's 
have a setting, max_wal_size, to control the max. disk space reserved 
for WAL. Once that's reached (or you get close enough, so that there are 
still some segments left to consume while the checkpoint runs), a 
checkpoint is triggered.

In this proposal, the number of segments preallocated is controlled 
separately from max_wal_size, so that you can set max_wal_size high, 
without actually consuming that much space in normal operation. It's 
just a backstop, to avoid completely filling the disk, if there's a 
sudden burst of activity. The number of segments preallocated is 
auto-tuned, based on the number of segments used in previous checkpoint 
cycles.

I'll write up a patch to do that, but before I do, does anyone disagree 
on those tuning principles? How do you typically tune 
checkpoint_segments on your servers? If the system was to tune it 
automatically, what formula should it use?

- Heikki