Thread: Windows shared_buffers limitations

Was working on some documentation today and I realized that I've taken for 
granted the lore about not using large values for shared_buffers in 
Windows without ever understanding why.  Can someone explain what the 
underlying mechanism that causes that limitation is?  From poking the 
archives I got the impression it was some page mapping issue but details 
are elusive.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Greg Smith wrote:

>Was working on some documentation today and I realized that I've taken for 
>granted the lore about not using large values for shared_buffers in 
>Windows without ever understanding why.  Can someone explain what the 
>underlying mechanism that causes that limitation is?  From poking the 
>archives I got the impression it was some page mapping issue but details 
>are elusive.

All I can offer is Magnus' explanation: "All evidence I've seen points to that
you should have shared_buffers as *small* as possible on win32, because memory
access there is slow. And leave more of the caching up to the OS."
<http://archives.postgresql.org/pgsql-general/2007-10/msg01115.php>

Heikki said something similar here:
<http://archives.postgresql.org/pgsql-performance/2007-10/msg00242.php>

Rainer

"Rainer Bauer" <usenet@munnin.com> writes:

> Greg Smith wrote:
>
>>Was working on some documentation today and I realized that I've taken for 
>>granted the lore about not using large values for shared_buffers in 
>>Windows without ever understanding why.  Can someone explain what the 
>>underlying mechanism that causes that limitation is?  From poking the 
>>archives I got the impression it was some page mapping issue but details 
>>are elusive.
>
> All I can offer is Magnus' explanation: "All evidence I've seen points to that
> you should have shared_buffers as *small* as possible on win32, because memory
> access there is slow. And leave more of the caching up to the OS."
> <http://archives.postgresql.org/pgsql-general/2007-10/msg01115.php>

Details are elusive because we have no idea *why* memory access should be
slow. If the pages are all in core and mapped in the memory map then really
accessing them should just be a hardware issue. The OS only gets involved with
a page needs to be paged in from swap or other backing store.

One thing which comes to mind is that it's possible Windows is swapping out
shared memory making having large shared memory segments dangerous on that
front.

Of course it's also possible something more subtle is going on.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication
support!

Gregory Stark <stark@enterprisedb.com> writes:
> One thing which comes to mind is that it's possible Windows is swapping out
> shared memory making having large shared memory segments dangerous on that
> front.

This is a hazard on most Unixen as well.  Windows may just be a bit more
aggressive about it.  Now that larger shared memory blocks are usually
a win, we need to put more effort into telling the kernel to lock the
shared memory block into RAM ...
        regards, tom lane