Re: patch to allow disable of WAL recycling

After I posted my previous FreeBSD results, I had a private request to run
the test for a longer period and on a larger VM.

I setup a new 8 CPU, 16 GB VM. This is the largest I can create and is on a
different machine from the previous VM, so the results cannot be directly
compared. I reran the same pgbench run but for an hour. Here are the
aggregated results

recycling on
avg tps: 470.3
avg lat: 8.5

recycling off
avg tps: 472.4
avg lat: 8.5

I think this still shows that there is no regression on FreeBSD/ZFS with
WAL recycling off.

Thanks,
Jerry

On Fri, Jul 27, 2018 at 1:32 PM, Jerry Jelinek <jerry(dot)jelinek(at)joyent(dot)com>
wrote:

> 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
>>>
>>
>>
>