Re: patch to allow disable of WAL recycling

I've setup FreeBSD 11.1 in a VM and I setup a ZFS filesystem to use for the Postgres DB. I ran the following simple benchmark.

pgbench -M prepared -c 4 -j 4 -T 60 postgres

Since it is in a VM and I can't control what else might be happening on the box, I ran this several times at different times of the day and averaged the results. Here is the average TPS and latency with WAL recycling on (the default) and off.

recycling on
avg tps: 407.4
avg lat: 9.8

recycling off
avg tps: 425.7
avg lat: 9.4 ms

Given my uncertainty about what else is running on the box, I think it is reasonable to say these are essentially equal, but I can collect more data across more different times if necessary. I'm also happy to collect more data if people have suggestions for different parameters on the pgbench run.

Thanks,

Jerry

On Fri, Jul 20, 2018 at 4:04 PM, Jerry Jelinek <jerry.jelinek@joyent.com> wrote:

Thomas,

Thanks for your offer to run some tests on different OSes and filesystems that you have. Anything you can provide here would be much appreciated. I don't have anything other than our native SmartOS/ZFS based configurations, but I might be able to setup some VMs and get results that way. I should be able to setup a VM running FreeBSD. If you have a chance to collect some data, just let me know the exact benchmarks you ran and I'll run the same things on the FreeBSD VM. Obviously you're under no obligation to do any of this, so if you don't have time, just let me know and I'll see what I can do on my own.

Thanks again,

Jerry

On Tue, Jul 17, 2018 at 2:47 PM, Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:

On 07/17/2018 09:12 PM, Peter Eisentraut wrote:
> On 17.07.18 00:04, Jerry Jelinek wrote:
>> There have been quite a few comments since last week, so at this point I
>> am uncertain how to proceed with this change. I don't think I saw
>> anything concrete in the recent emails that I can act upon.
>
> The outcome of this could be multiple orthogonal patches that affect the
> WAL file allocation behavior somehow.  I think your original idea of
> skipping recycling on a COW file system is sound.  But I would rather
> frame the option as "preallocating files is obviously useless on a COW
> file system" rather than "this will make things mysteriously faster with
> uncertain trade-offs".
>

Makes sense, I guess. But I think many claims made in this thread are
mostly just assumptions at this point, based on our beliefs how CoW or
non-CoW filesystems work. The results from ZFS (showing positive impact)
are an exception, but that's about it. I'm sure those claims are based
on real-world experience and are likely true, but it'd be good to have
data from a wider range of filesystems / configurations etc. so that we
can give better recommendations to users, for example.

That's something I can help with, assuming we agree on what tests we
want to do. I'd say the usual batter of write-only pgbench tests with
different scales (fits into s_b, fits into RAM, larger then RAM) on
common Linux filesystems (ext4, xfs, btrfs) and zfsonlinux, and
different types of storage would be enough. I don't have any freebsd box
available, unfortunately.


regards

--
Tomas Vondra                  http://www.2ndQuadrant.com

PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services