Re: Redesigning checkpoint_segments

On 02/05/2015 01:42 PM, Heikki Linnakangas wrote:
> There are a few reasons for making the minimum configurable:

Any thoughts on what the default minimum should be, if the default max
is 1.1GB/64?

> 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?

I do, actually.  Here's the case I want it for:

I have a web application which gets all of its new data as uncoordinated
batch updates from customers.  Since it's possible for me to receive
several batch updates at once, I set max_wal_size to 16GB, roughtly the
side of 8 batch updates.  But I don't want the WAL that big all the time
because it slows down backup snapshots.  So I set min_wal_size to 2GB,
roughly the size of one batch update.

That's an idiosyncratic case, but I can imagine more of them out there.

I wouldn't be opposed to min_wal_size = -1 meaning "same as
max_wal_size" though.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com