Re: Redesigning checkpoint_segments

On 02/05/2015 11:28 PM, Robert Haas wrote:
> On Thu, Feb 5, 2015 at 2:11 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> Default of 4 for min_wal_size?
>
> I assume you mean 4 segments; why not 3 as currently? As long as the
> system has the latitude to ratchet it up when needed, there seems to
> be little advantage to raising the minimum. Of course I guess there
> must be some advantage or Heikki wouldn't have made it configurable,
> but I'd err on the side of keeping this one small. Hopefully the
> system that automatically adjusts this is really smart, and a large
> min_wal_size is superfluous for most people.

There are a few reasons for making the minimum configurable:

1. Creating new segments when you need them is not free, so if you have
a workload with occasional very large spikes, you might want to prepare
for them. The auto-tuning will accommodate for the peak usage, but it's
a moving average so if the peaks are infrequent enough, it will shrink
the size down between the spikes.

2. To avoid running out of disk space on write to WAL (which leads to a
PANIC). In particular, if you have the WAL on the same filesystem as the
data, pre-reserving all the space required for WAL makes it much more
likely that you when you run out of disk space, you run out when writing
regular data, not WAL.

3. Unforeseen issues with the auto-tuning. It might not suite everyone,
so it's nice that you can still get the old behaviour by setting min=max.

Actually, perhaps we should have a boolean setting that just implies
min=max, instead of having a configurable minimum?. That would cover all
of those reasons pretty well. So we would have a "max_wal_size" setting,
and a boolean "preallocate_all_wal = on | off". Would anyone care for
the flexibility of setting a minimum that's different from the maximum?

- Heikki