Robert Haas <robertmhaas@gmail.com> writes:
> On Thu, Jul 5, 2018 at 4:39 PM, Andres Freund <andres@anarazel.de> wrote:
>> This is formulated *WAY* too positive. It'll have dramatic *NEGATIVE*
>> performance impact of non COW filesystems, and very likely even negative
>> impacts in a number of COWed scenarios (when there's enough memory to
>> keep all WAL files in memory).
>>
>> I still think that fixing this another way would be preferrable. This'll
>> be too much of a magic knob that depends on the fs, hardware and
>> workload.
> I tend to agree with you, but unless we have a pretty good idea what
> that other way would be, I think we should probably accept the patch.
> Could we somehow make this self-tuning? On any given
> filesystem/hardware/workload, either creating a new 16MB file is
> faster, or recycling an old file is faster.
That's not the way to think about it. On a COW file system, we don't
want to "create 16MB files" at all --- we should just fill WAL files
on-the-fly, because the pre-fill activity isn't actually serving the
intended purpose of reserving disk space. It's just completely useless
overhead :-(. So we can't really make a direct comparison between the
two approaches; there's no good way to net out the cost of constructing
the WAL data we need to write.
Moreover, a raw speed comparison isn't the whole story; a DBA might
choose write-without-prefill because it's faster for him, even though
he's taking a bigger chance of trouble on out-of-disk-space.
I think that the right basic idea is to have a GUC that chooses between
the two implementations, but whether it can be set automatically is not
clear to me. Can initdb perhaps investigate what kind of filesystem the
WAL directory is sitting on, and set the default value from hard-wired
knowledge about that?
regards, tom lane