Re: patch to allow disable of WAL recycling

On Thu, Mar 28, 2019 at 6:10 PM Thomas Munro <thomas(dot)munro(at)gmail(dot)com> wrote:

> On Fri, Mar 29, 2019 at 10:47 AM Thomas Munro <thomas(dot)munro(at)gmail(dot)com>
> wrote:
> > On Fri, Mar 29, 2019 at 8:59 AM Robert Haas <robertmhaas(at)gmail(dot)com>
> wrote:
> > > On Tue, Mar 26, 2019 at 3:24 PM Jerry Jelinek <
> jerry(dot)jelinek(at)joyent(dot)com> wrote:
> > > > The latest patch is rebased, builds clean, and passes some basic
> testing. Please let me know if there is anything else I could do on this.
> > >
> > > I agree with Thomas Munro's earlier critique of the documentation.
> > > The documentation of the new parameters makes an assumption,
> > > completely unsupported in my view, about when those parameters should
> > > be set, yet at the same time gives almost no information about what
> > > they actually do. I don't like that.
> > >
> > > The patch needs a visit from pgindent, too.
> >
> > I would like to fix these problems and commit the patch. First, I'm
> > going to go and do some project-style tidying, write some proposed doc
> > tweaks, and retest these switches on the machine where I saw
> > beneficial effects from the patch before. I'll post a new version
> > shortly to see if anyone has objections.
>
> Here's a new version of the patch.
>
> Last time I ran the test, I was using FreeBSD 11.2, but now I'm on
> FreeBSD 12.0, and I suspect something changed about how it respects
> the arc size sysctls causing it to behave very badly, so this time I
> didn't change them from their defaults. Also the disks have changed
> from 7200RPM drives to 5400RPM drives since last time. The machine
> has 2 underpowered cores and 6GB of RAM. What can I say, it's a super
> low end storage/backup box. What's interesting is that it does show
> the reported problem. Actually I often test stuff relating to OS
> caching on this box precisely because the IO sticks out so much.
>
> Some OS set-up steps run as root:
>
> zfs create zroot/tmp/test
> zfs set mountpoint=/tmp/test zroot/tmp/test
> zfs set compression=off zroot/tmp/test
> zfs set recordsize=8192 zroot/tmp/test
> chown tmunro:tmunro /tmp/test
>
> Now as my regular user:
>
> initdb -D /tmp/test
> cat <<EOF >> /tmp/test/postgresql.conf
> fsync=off
> max_wal_size = 600MB
> min_wal_size = 600MB
> EOF
>
> I started postgres -D /tmp/test and I set up pgbench:
>
> pgbench -i -s 100 postgres
>
> Then I ran each test as follows:
>
> tar cvf /dev/null /tmp/test # make sure all data files are pre-warmed into
> arc
> for i in 1 2 3 ; do
> pgbench -M prepared -c 4 -j 4 -T 120 postgres
> done
>
> I did that with all 4 GUC permutations and got the following TPS numbers:
>
> wal_recycle=off, wal_init_zero=off: 2668, 1873, 2166
> wal_recycle=on, wal_init_zero=off: 1936, 1350, 1552
> wal_recycle=off, wal_init_zero=on : 2213, 1360, 1539
> wal_recycle=on, wal_init_zero=on : 1539, 1007, 1252
>
> Finally, concious that 2 minutes isn't really enough, I did a 10
> minute run with both settings on and both off, again with the tar
> command first to try to give them the same initial conditions (really
> someone should write a "drop-caches-now" patch for FreeBSD that
> affects the page cage and the ZFS ARC, but I digress) and got:
>
> wal_recycle=on, wal_init_zero=on : 1468
> wal_recycle=off, wal_init_zero=off: 2046
>
> I still don't know why exactly this happens, but it's clearly a real
> phenomenon. As for why Tomas Vondra couldn't see it, I'm guessing
> that stacks more RAM and ~500k IOPS help a lot (essentially the
> opposite end of the memory, CPU, IO spectrum from this little
> machine), and Joyent's systems may be somewhere in between?
>
> --
> Thomas Munro
> https://enterprisedb.com

Thomas,

Thanks for doing all of this work on the patch. I like your new
documentation a lot better than what I wrote. Also, sorry for messing up
the style here. I was trying to be consistent with the existing style, but
I clearly failed. Thanks for cleaning all of that up. I went through your
new version of the patch and it all looks great to me.

Thanks again,
Jerry