Re: increasing the default WAL segment size

Robert,

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> On Tue, Mar 21, 2017 at 8:10 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> >> We've already
> >> created quite a few incompatibilities in this release, and I'm not
> >> entirely eager to just keep cranking them out at top speed.
> >
> > That position would seem to imply that you're in favor of keeping the
> > current default of 16MB, but that doesn't make sense given that you
> > started this discussion advocating to make it larger. Changing your
> > position is certainly fine, but it'd be good to be more clear if that's
> > what you meant here or if you were just referring to the file naming
> > scheme but you do still want to increase the default size.
>
> To be honest, I'd sort of forgotten about the change which is the
> nominal subject of this thread - I was more focused on the patch,
> which makes it configurable. I was definitely initially in favor of
> raising the value, but I got cold feet, a bit, when Alvaro pointed out
> that going to 64MB would require a substantial increase in
> min_wal_size. I'm not sure people with small installations will
> appreciate seeing that value cranked up from 5 segments * 16MB = 80MB
> to, say, 3 segments * 64MB = 192MB. That's an extra 100+ MB of space
> that doesn't really do anything for you. And nobody's done any
> benchmarking to see whether having only 3 segments is even a workable,
> performant configuration, so maybe we'll end up with 5 * 64MB = 320MB
> by default.

The performance concern of having 3 segments is a red herring here if
we're talking about a default install because the default for
max_wal_size is 1G, not 192MB. I do think increasing the default WAL
size would be valuable to do even if it does mean a default install will
take up a bit more space.

I didn't see much discussion of it, but if this is really a concern then
couldn't we set the default to be 2 segments worth instead of 3 also?
That would mean an increase from 80MB to 128MB in the default install if
you never touch more than 128MB during a checkpoint.

> I'm a little worried that this whole question of changing the file
> naming scheme is a diversion which will result in torpedoing any
> chance of getting some kind of improvement here for v11. I don't
> think the patch is all that far from being committable but it's not
> going to get there if we start redesigning the world around it.

It's not my intent to 'torpedo' this patch but I'm pretty disappointed
that we're introducing yet another initdb-time option with, as far as I
can tell, no option to change it after the cluster has started (without
some serious hackery), and continuing to have a poor default, which is
what most users will end up with.

I really don't like these kinds of options. I'd much rather have a
reasonable default that covers most cases and is less likely to be a
problem for most systems than have a minimal setting that's impossible
to change after you've got your data in the system. As much as I'd like
everyone to talk to me before doing an initdb, that's pretty rare and
instead we end up having to break the bad news that they should have
known better and done the right thing at initdb time and, no, sorry,
there's no answer today but to dump out all of the data and load it into
a new cluster which was set up with the right initdb settings.

Thanks!

Stephen