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(dot)jelinek(at)joyent(dot)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(dot)vondra(at)2ndquadrant(dot)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
>>
>
>