On Tue, Sep 20, 2016 at 8:15 AM, Tsunakawa, Takayuki
<tsunakawa.takay@jp.fujitsu.com> wrote: > Hello, > >> From: pgsql-hackers-owner@postgresql.org >> [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Magnus Hagander >> On Wed, Aug 24, 2016 at 4:35 AM, Tsunakawa, Takayuki >> <tsunakawa.takay@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.