Re: Remove the comment on the countereffectiveness of large shared_buffers on Windows

On Mon, Nov 7, 2016 at 5:55 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:

> On Tue, Sep 20, 2016 at 8:15 AM, Tsunakawa, Takayuki
> <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com> wrote:
> > Hello,
> >
> >> From: pgsql-hackers-owner(at)postgresql(dot)org
> >> [mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Magnus
> Hagander
> >> On Wed, Aug 24, 2016 at 4:35 AM, Tsunakawa, Takayuki
> >> <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com> wrote:
> >> As a similar topic, I wonder whether the following still holds
> true,
> >> after many improvements on shared buffer lock contention.
> >>
> >> https://www.postgresql.org/docs/devel/static/runtime-config-re
> >> source.html
> >>
> >> "The useful range for shared_buffers on Windows systems
> >> is generally from 64MB to 512MB."
> >>
> >>
> >>
> >>
> >> Yes, that may very much be out of date as well. A good set of benchmarks
> >> around that would definitely be welcome.
> >
> >
> > I'd like to propose the above-mentioned comment from the manual. The
> patch is attached.
> >
> > I ran read-only and read-write modes of pgbench, and could not see any
> apparent decrease in performance when I increased shared_buffers. The
> scaling factor is 200, where the database size is roughly 3GB. I ran the
> benchmark on my Windows 10 PC with 6 CPU cores and 16GB of RAM. The
> database and WAL is stored on the same HDD.
> >
> > <<Test batch file>>
> > @echo off
> > for %%s in (256MB 512MB 1GB 2GB 4GB) do (
> > pg_ctl -w -o "-c shared_buffers=%%s" start
> > pgbench -c18 -j6 -T60 -S bench >> g:\b.txt 2>&1
> > pg_ctl -t 3600 stop
> > )
> >
> > <<Select-only (with -S)>>
> > shared_buffers tps
> > 256MB 63056
> > 512MB 63918
> > 1GB 65520
> > 2GB 66840
> > 4GB 68270
> >
> > <<Read-write (without -S)>>
> > shared_buffers tps
> > 256MB 1138
> > 512MB 1187
> > 1GB 1571
> > 2GB 1650
> > 4GB 1598
> >
>
> Isn't it somewhat strange that writes are showing big win whereas
> reads doesn't show much win?

I don't find that unusual, and have seen the same thing on Linux.

With small shared_buffers, you are constantly throwing dirty buffers at the
kernel in no particular order, and the kernel does a poor job of predicting
when the same buffer will be dirtied repeatedly and only needs the final
version of the data actually written to disk. With large shared_buffers,
you only send each dirty buffer to the kernel once per checkpoint.

Cheers,

Jeff