On Thu, Mar 28, 2019 at 6:10 PM Thomas Munro <thomas.mu...@gmail.com> wrote:
> On Fri, Mar 29, 2019 at 10:47 AM Thomas Munro <thomas.mu...@gmail.com> > wrote: > > On Fri, Mar 29, 2019 at 8:59 AM Robert Haas <robertmh...@gmail.com> > wrote: > > > On Tue, Mar 26, 2019 at 3:24 PM Jerry Jelinek < > jerry.jeli...@joyent.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