Re: shared_buffers advice

2010/5/27 Konrad Garus <konrad(dot)garus(at)gmail(dot)com>:
> 2010/5/26 Cédric Villemain <cedric(dot)villemain(dot)debian(at)gmail(dot)com>:
>
>> 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.
>
> Since pg_buffercache is 4-8 times smaller, it would seem to be
> extremely rare to me. And when PG requests a block, it also needs to
> evict something from shared_buffers.

3 very important things :
* postgresql shared buffers are database oriented
* OS shared buffers are *more* complex and will not evict the same
buffers as postgres.
* OS page cache can handle tens of GB where postgres usually have no
gain in performance over 10GB.

>
>> You can try pgfincore extension to grab stats from OS cache and/or
>> patch postgresql if you want real stats ;)
>
> Thank you! It seems to be the tool I was looking for. Could help me
> locate and troubleshoot the hogs in page cache. I also find the
> snapshot/restore function promising. Every morning our cache is cold
> or filled with irrelevant data left by nightly batch jobs, thus
> severely impacting the performance. Seems to be exactly what this tool
> is for.
>
> How does it work? How stable is it? Can we use it in production on a
> daily basis?

It works thanks to mincore/posix_fadvise stuff : you need linux.
It is stable enough in my own experiment. I did use it for debugging
purpose in production servers with succes.
BUT :
* snapshot/restore is done via a flat_file (one per segment or
table/index) and *it is not removed* when you drop a table.
* it might exist corner case not yet handled (like snapshot a
database, change things like drop table, truncate table, then restore)

It needs some polish to be totally production ready but the job can be done.

>
>> pgbuffercache is provided with postgresql and deliver very usefull information :
>> http://www.postgresql.org/docs/8.4/interactive/pgbuffercache.html
>
> Thank you. I already am using it. I've already found a few hogs with it.
>
> --
> Konrad Garus
>

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