Re: patch to allow disable of WAL recycling

Peter,

Thanks for taking a look a this. I have a few responses in line. I am not a
PG expert, so if there is something here that I've misunderstood, please
let me know.

On Sun, Jul 1, 2018 at 6:54 AM, Peter Eisentraut <
peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:

> On 26.06.18 15:35, Jerry Jelinek wrote:
> > Attached is a patch to provide an option to disable WAL recycling. We
> > have found that this can help performance by eliminating
> > read-modify-write behavior on old WAL files that are no longer resident
> > in the filesystem cache. The is a lot more detail on the background of
> > the motivation for this in the following thread.
>
> Your patch describes this feature as a performance feature. We would
> need to see more measurements about what this would do on other
> platforms and file systems than your particular one. Also, we need to
> be careful with user options that trade off reliability for performance
> and describe them in much more detail.
>

I don't think this change really impacts the reliability of PG, since PG
doesn't actually preallocate all of the WAL files. I think PG will allocate
WAL files as it runs, up to the wal_keep_segments limit, at which point it
would start recycling. If the filesystem fills up before that limit is
reached, PG would have to handle the filesystem being full when attempting
to allocate a new WAL file (as it would with my change if WAL recycling is
disabled). Of course once all of the WAL files have finally been allocated,
then PG won't need additional space on a non-COW filesystem. I'd be happy
to add more details to the man page change describing this new option and
the implications if the underlying filesystem fills up.

> If the problem is specifically the file system caching behavior, then we
> could also consider using the dreaded posix_fadvise().
>

I'm not sure that solves the problem for non-cached files, which is where
we've observed the performance impact of recycling, where what should be a
write intensive workload turns into a read-modify-write workload because
we're now reading an old WAL file that is many hours, or even days, old and
has thus fallen out of the memory-cached data for the filesystem. The disk
reads still have to happen.

>
> Then again, I can understand that turning off WAL recycling is sensible
> on ZFS, since there is no point in preallocating space that will never
> be used. But then we should also turn off all other preallocation of
> WAL files, including the creation of new (non-recycled) ones.
>

I don't think we'd see any benefit from that (since the newly allocated
file is certainly cached), and the change would be much more intrusive, so
I opted for the trivial change in the patch I proposed.

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

Thanks again,
Jerry