Re: postgresql.conf recommendations

On Sat, Feb 9, 2013 at 1:16 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> On Sat, Feb 9, 2013 at 6:51 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
>> On Thu, Feb 7, 2013 at 7:41 AM, Charles Gomes <charlesrg(at)outlook(dot)com> wrote:
>>> I've benchmarked shared_buffers with high and low settings, in a server
>>> dedicated to postgres with 48GB my settings are:
>>> shared_buffers = 37GB
>>> effective_cache_size = 38GB
>>>
>>> Having a small number and depending on OS caching is unpredictable, if the
>>> server is dedicated to postgres you want make sure postgres has the memory.
>>> A random unrelated process doing a cat /dev/sda1 should not destroy postgres
>>> buffers.
>>> I agree your problem is most related to dirty background ration, where
>>> buffers are READ only and have nothing to do with disk writes.
>>
>> You make an assertion here but do not tell us of your benchmarking
>> methods.
>
> Well, he is not the only one committing that sin.

I'm not asking for a complete low level view. but it would be nice to
know if he's benchmarking heavy read or write loads, lots of users, a
few users, something. All we get is "I've benchmarked a lot" followed
by "don't let the OS do the caching." At least with my testing I was
using a large transactional system (heavy write) and there I KNOW from
testing that large shared_buffers do nothing but get in the way.

all the rest of the stuff you mention is why we have effective cache
size which tells postgresql about how much of the data CAN be cached.
In short, postgresql is designed to use and / or rely on OS cache.