Re: shared_buffers advice

2010/5/24 Konrad Garus <konrad(dot)garus(at)gmail(dot)com>:
> 2010/3/11 Paul McGarry <paul(at)paulmcgarry(dot)com>:
>
>> I'm basically wondering how the postgresql cache (ie shared_buffers)
>> and the OS page_cache interact. The general advice seems to be to
>> assign 1/4 of RAM to shared buffers.
>>
>> I don't have a good knowledge of the internals but I'm wondering if
>> this will effectively mean that roughly the same amount of RAM being
>> used for the OS page cache will be used for redundantly caching
>> something the Postgres is caching as well?
>
> I have a similar problem but I can't see an answer in this thread.
>
> Our dedicated server has 16 GB RAM. Among other settings
> shared_buffers is 2 GB, effective_cache_size is 12 GB.
>
> Do shared_buffers duplicate contents of OS page cache? If so, how do I
> know if 25% RAM is the right value for me? Actually it would not seem
> to be true - the less redundancy the better.

At the moment where a block is requested for the first time (usualy
8kb from postgres, so in fact 2 blocks in OS), you have 'duplicate'
buffers.
But, depending of your workload, it is not so bad because those 2
blocks should not be requested untill some time (because in postgresql
shared buffers) and should be evicted by OS in favor of new blocks
requests.
Again it depends on your workload, if you have a case where you
refresh a lot the shared buffers then you will have more blocks in the
2 caches at the same time.

You can try pgfincore extension to grab stats from OS cache and/or
patch postgresql if you want real stats ;)
pgbuffercache is provided with postgresql and deliver very usefull information :
http://www.postgresql.org/docs/8.4/interactive/pgbuffercache.html

>
> Another question - is there a tool or built-in statistic that tells
> when/how often/how much a table is read from disk? I mean physical
> read, not poll from OS cache to shared_buffers.
>
> --
> Konrad Garus
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

--
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support