0

We're running PostgreSQL v11.7 (upgrade is already planned) on Windows and seem to be hitting a rare dynamic shared memory bug #15749 which results in the error message:

FATAL: cannot unpin a segment that is not pinned

Ref.:https://www.postgresql.org/message-id/[email protected]

The workaround here is to either upgrade to PostgreSQL v11.9+ OR disable query parallelism with:

max_parallel_workers_per_gather = 0

However, recently a discussion popped up in our team about the right amount of shared memory for PostgreSQL specifically on Windows. The documentation for v9.1 says:

If you have a dedicated database server with 1GB or more of RAM, areasonable starting value for shared_buffers is 25% of the memory inyour system.

and

Also, on Windows, large values for shared_buffers aren't as effective.You may find better results keeping the setting relatively low andusing the operating system cache more instead. The useful range forshared_buffers on Windows systems is generally from 64MB to 512MB.

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

However, starting with v10+ the sentence aboutshared_buffers size between 64MB and 512MB on Windows has been removed from the documentation. While the statement of having 25% of available RAM forshared_buffers was retained.

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

Could someone explain what was changed in the way PostgreSQL shared memory works on Windows between v9 and v10+? Has it been unified with the way PostgreSQL shared memory works on Linux/UNIX systems so the comment about Windows has become obsolete?

Usually the reason why there was an advice to keepshared_buffers small on Windows was to avoid double-buffering between PostgreSQL and the Windows OS cache.

However, are we now OK now to safely setshared_buffers to 25% of RAM initially even if it means setting it to 4GB or higher?

Our current PostgreSQL server runs on a Windows box with 16GB of RAM with the following settings:

shared_buffers = 128MBdynamic_shared_memory_type = windows

Would greatly appreciate any insight from PostgreSQL experts or maybe PostgreSQL developers.

askedSep 11 at 19:15
andrews's user avatar
6
  • 3
    This is an exercise in archaeology that does not appeal to me. Why investigate the behavior of outdated software when the answer is to upgrade? You don't even bother to install minor updates. With a recent version, it is no problem to use 4GB for shared buffers. Perform a load test to see which value ofshared_buffers works best for you.CommentedSep 11 at 19:59
  • @LaurenzAlbe thanks for your comment and time. The question is not only about shared_buffers size. The question is about its relation to OS cache on Windows and why the comment about keeping shared_buffers size on Windows smaller was removed.CommentedSep 11 at 20:11
  • @andrews what will change for you if you learn why theshared_buffers recommendation was modified seven years ago?CommentedSep 11 at 21:54
  • 1
    You're using an unsupported version of PostgreSQL and referencing even older versions. The only problem you have is that your maintenance is a few years behind. We can't solve that for you.CommentedSep 11 at 23:38
  • @mustaccio this is just of a curiosity I guess. Don't concentrate on the unsupported version of pgsql here. We already have a solution for the race conditions bug. I'm asking what was changed in the way shared memory works on Windows. I guess only developers can answer this type of a question.CommentedSep 12 at 5:22

1 Answer1

3

Ok, so you want to know why the recommendation to use tiny values forshared_buffers on Windows was removed from the documentation. So let me demonstrate the power of open source, using the PostgreSQL Git repository.

  • usegit log -p and search for the commit that removed the text:

    commit 81c52728f82be5303ea16508255e948017f4cd87Author: Peter Eisentraut <[email protected]>Date:   Wed Nov 30 12:00:00 2016 -0500    doc: Remove claim about large shared_buffers on Windows    Testing has shown that it is no longer correct.    From: Tsunakawa, Takayuki <[email protected]>    Reviewed-by: amul sul <[email protected]>    Discussion: http://www.postgresql.org/message-id/flat/0A3221C70F24FB45833433255569204D1F5EE995@G01JPEXMBYT05/diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgmlindex bcfe2fded29..b1c5289fccc 100644--- a/doc/src/sgml/config.sgml+++ b/doc/src/sgml/config.sgml@@ -1316,11 +1316,6 @@ include_dir 'conf.d'        <para>         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 <varname>shared_buffers</varname>-        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 <varname>shared_buffers</varname> on Windows systems-        is generally from 64MB to 512MB.        </para>       </listitem>

  • follow the discussion link to themailing list archives, which will answer your question

So the claim was removed based on experiments.

answeredSep 12 at 6:05
Laurenz Albe's user avatar
1
  • Laurenz, this is exactly what I was looking for! Thank you very much. I've read the discussion thread you've referenced and there are test results showing better TPS performance with the increased shared_buffers on Windows thus making the statement of 512MB upper limit no longer relevant!CommentedSep 12 at 8:37

Your Answer

Sign up orlog in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

By clicking “Post Your Answer”, you agree to ourterms of service and acknowledge you have read ourprivacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.