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? Can you once do some longer read-write tests to see if we get consistent data and take the median-of-three runs data? Above benchmarks doesn't seem to indicate that increase in shared buffers upto 25% of RAM shows significant win on Windows systems, because the performance of writes increase upto 2GB and then starts decreasing. -- With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers