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

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? 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