Re: Kernel cache vs shared_buffers

Harald Armin Massa wrote:
> Heikki,
>
>
>> > PostgreSQL on Windows. My current rule of thumb on Windows: set
>> > shared_buffers to minimum * 2
>> > Adjust effective_cache_size to the number given as "system cache"
>> > within the task manager.
>>
>> Why?
>
> I tried with shared_buffers = 50% of available memory, and with 30% of
> available memory, and the thoughput on complex queries stalled or got
> worse.
>
> I lowered shared_buffers to minimum, and started raising
> effective_cache_size, and performance on real world queries improved.
> pg_bench did not fully agree when simulating large numbers concurrent
> queries.
>
> So I tried setting shared_buffers between minimum and 2.5*minimum, and
> pg_bench speeds recovered and real world queries did similiar.
>
> My understanding is that shared_buffers are realised as memory mapped
> file in win32; and that they are only usually kept in memory. Maybe I
> understood that wrong.

Almost. It's a memory mapped region backed by the system pagefile.

That said, it would be good to try to figure out *why* this is
happening. It's been on my list of things to do to run checks with the
profiler (now that we can ;-) with the msvc stuff) and try to figure out
where it's slowing down. It could be as simple as that there's much more
overhead trying to access the shared memory from different processes
than we're used to on other platforms.

//Magnus