On Mon, Nov 7, 2016 at 5:55 AM, Amit Kapila <amit.kapil...@gmail.com> wrote:
> On Tue, Sep 20, 2016 at 8:15 AM, Tsunakawa, Takayuki > <tsunakawa.ta...@jp.fujitsu.com> wrote: > > Hello, > > > >> From: pgsql-hackers-ow...@postgresql.org > >> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Magnus > Hagander > >> On Wed, Aug 24, 2016 at 4:35 AM, Tsunakawa, Takayuki > >> <tsunakawa.ta...@jp.fujitsu.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
