Re: increasing the default WAL segment size

On 04/06/2017 08:33 PM, David Steele wrote:
> On 4/5/17 7:29 AM, Simon Riggs wrote:
>> On 5 April 2017 at 06:04, Beena Emerson <memissemerson(at)gmail(dot)com> wrote:
>>>
>>> The WALfilename - LSN mapping disruption for higher values you mean? Is
>>> there anything else I have missed?
>>
>> I see various issues raised but not properly addressed
>>
>> 1. we would need to drop support for segment sizes < 16MB unless we
>> adopt a new incompatible filename format.
>> I think at 16MB the naming should be the same as now and that
>> WALfilename -> LSN is very important.
>> For this release, I think we should just allow >= 16MB and avoid the
>> issue thru lack of time.
>
> +1.
>
>> 2. It's not clear to me the advantage of being able to pick varying
>> filesizes. I see great disadvantage in having too many options, which
>> greatly increases the chance of incompatibility, annoyance and
>> breakage. I favour a small number of values that have been shown by
>> testing to be sweet spots in performance and usability. (1GB has been
>> suggested)
>
> I'm in favor of 16,64,256,1024.
>

I don't see a particular reason for this, TBH. The sweet spots will be
likely dependent hardware / OS configuration etc. Assuming there
actually are sweet spots - no one demonstrated that yet.

Also, I don't see how supporting additional WAL sizes increases chance
of incompatibility. We already allow that, so either the tools (e.g.
backup solutions) assume WAL segments are always 16MB (in which case are
essentially broken) or support valid file sizes (in which case they
should have no issues with the new ones).

If we're going to do this, I'm in favor of deciding some reasonable
upper limit (say, 1GB or 2GB sounds good), and allowing all 2^n values
up to that limit.

>> 3. New file allocation has been a problem raised with this patch for
>> some months now.
>
> I've been playing around with this and I don't think short tests show
> larger sizes off to advantage. Larger segments will definitely perform
> more poorly until Postgres starts recycling WAL. Once that happens I
> think performance differences should be negligible, though of course
> this needs to be verified with longer-running tests.
>

I'm willing to do some extensive performance testing on the patch. I
don't see how that could happen in the next few day (before the feature
freeze), particularly considering we're interested in long tests.

The question however is whether we need to do this testing when we don't
actually change the default (at least the patch submitted on 3/27 does
seem to keep the 16MB). I assume people specifying a custom value when
calling initdb are expected to know what they are doing (and I don't see
how we can prevent distros from choosing a bad value in their packages -
they could already do that with configure-time option).

> If archive_timeout is set then there will also be additional time taken
> to zero out potentially larger unused parts of the segment. I don't see
> this as an issue, however, because if archive_timeout is being triggered
> then the system is very likely under lower than usual load.
>
>> Lack of clarity and/or movement on these issues is very, very close to
>> getting the patch rejected now. Let's not approach this with the
>> viewpoint that I or others want it to be rejected, lets work forwards
>> and get some solid changes that will improve the world without causing
>> problems.
>
> I would definitely like to see this go in, though I agree with Peter
> that we need a lot more testing. I'd like to see some build farm
> animals testing with different sizes at the very least, even if there's
> no time to add new tests.
>

Do we actually have any infrastructure for that? Or do you plan to add
some new animals with different WAL segment sizes?

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services