Shared_buffers for Windows

[sylvainbeo]

Hi,

First of all, thank for this very usefull tool.

When choosing Windows for the OS, the value for the Shared_buffers is always 512Mb.

From version 9.6 of PG, this rule is not valid anymore i think (verified with a PG expert), and the rule of 1/4 of the RAM should be applied.

What do you think ?

Thanks.

[le0pard]

Hello @sylvainbeo

Can you provide more info about this changes? Based in what information bigger settings for Windows will take effect (better provide numbers as a proof)?

Here info in wiki: https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

[le0pard]

BTW, I did not found any information in pg 9.6 or 10 changelog about shared_buffers for windows

[blogh]

I will start a discussion on hackers to ask for an update of the wiki.

[blogh]

here is the link : https://www.postgresql.org/message-id/CAPE8EZ5Oyph17snTSf-kwYR9Wt-14%3DXL5GyQrEtGon3EK_ZRbg%40mail.gmail.com

[sylvainbeo]

@le0pard Wiki is up to date.

[le0pard]

Thanks for links @blogh

Based on this discussion https://www.postgresql.org/message-id/flat/0A3221C70F24FB45833433255569204D1F5EE995@G01JPEXMBYT05 looks like best read-write was with 2GB, not 4GB for 16GB machine. Does this mean, that for windows not 25% should be, but only 12.5%?

[blogh]

In this message, the result seem different : https://www.postgresql.org/message-id/0A3221C70F24FB45833433255569204D1F64F955%40G01JPEXMBYT05

I think it's safe to remove the limitation.
Theses settings are a starting point for the configuration as stated in the how it works section.
There might be variation from installations to installations.

[le0pard]

Also still not get when changes happend - from pg 9.6 included, or from pg 10 included? @sylvainbeo @blogh

[blogh]

They removed it in the pg 10 docs.

https://www.postgresql.org/docs/10/runtime-config-resource.html

On systems with less than 1GB of RAM, a smaller percentage of RAM is appropriate, so as to leave adequate space for the operating system.

https://www.postgresql.org/docs/9.6/runtime-config-resource.html

On systems with less than 1GB of RAM, a smaller percentage of RAM is appropriate, so as to leave adequate space for the operating system. Also, on Windows, large values for shared_buffers aren't as effective. You may find better results keeping the setting relatively low and using the operating system cache more instead. The useful range for shared_buffers on Windows systems is generally from 64MB to 512MB.

[le0pard]

Yep, everything pointed to doc changes. But I am more interesting what change(s) in pg codebase make this happend, that now it better work with shared memory on Windows (so I understand reason too). Do you know this info @sylvainbeo @blogh ?

[blogh]

It looks like the dev dont really know. (And I certainely don't, I work on Linux all the time).

Extracted from the mailing list :

many improvements on shared buffer lock contention

Well, that advice goes way back. Many things have changed since then -
and just look at things like the updating of the stats target. For one
thing, we're in 64-bit now, not 32, for the majority of users. We reserve
the shared memory on startup which was done for address probability issues,
but may have had side effects. And Windows itself has changed in those 10
or so years.

I think that the crux of the problem is that bench are rarely made on windows. The example of the huge performance hit caused by "update_process_title" on windows is a great example of that (discovered a few years ago).

[le0pard]

Should be fixed by 187578a

[blogh]

thanks a lot !

[sylvainbeo]

Thanks.