Shared Buffers

Siddharth Shah wrote:

Hello All,
How Postgres Maintains data in Shared Buffer

Does It maintains queried data in memory or table data and Next time
how postgres fetch data from memory
rather than disk
Which algorithm is used for storing data how data is indexed in shared
buffers

Thanks
Siddharth

Take a look at

http://www.westnet.com/~gsmith/content/postgresql/InsideBufferCache.pdf

http://postgresql.mirrors-r-us.net/files/documentation/books/aw_pgsql/hw_performance/node3.html

--Ashish

On Mon, Mar 2, 2009 at 1:31 PM, Ashish Karalkar <ashishka@synechron.com> wrote:

Take a look at

http://www.westnet.com/~gsmith/content/postgresql/InsideBufferCache.pdf

http://postgresql.mirrors-r-us.net/files/documentation/books/aw_pgsql/hw_performance/node3.html

Wouldn't it be nice, to have any presentation's like that one made by
Greg, in wiki, or part of devel docs ?

--
GJ

I believe that op system side buffering can play a role too. I our case, the DB server (machine & op sys) caches data that it pulled from disk (not necessarily from a DB) and also the disk servers do the same. If a block was removed from the DB buffer cache to accommodate more recently requested data, but the evicted block is live in memory on the DB server or the disk server, it can pull from there instead of performing an expensive disk-IO.

I humbly defer to any out there with more knowledge about this than I. Just opening up this avenue for discussion.

-dave

________________________________
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Siddharth Shah
Sent: Monday, March 02, 2009 8:12 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Shared Buffers

Hello All,
How Postgres Maintains data in Shared Buffer

Does It maintains queried data in memory or table data and Next time how postgres fetch data from memory
rather than disk
Which algorithm is used for storing data how data is indexed in shared buffers

Thanks
Siddharth

On Mon, Mar 2, 2009 at 7:40 AM, Gauthier, Dave <dave.gauthier@intel.com> wrote:

I believe that op system side buffering can play a role too.  I our case,
the DB server (machine & op sys) caches data that it pulled from disk (not
necessarily from a DB) and also the disk servers do the same.  If a block
was removed from the DB buffer cache to accommodate more recently requested
data, but the evicted block is live in memory on the DB server or the disk
server, it can pull from there instead of performing an expensive disk-IO.

This is very true for certain workloads. If your db is bigger than
memory, and you only work on a tiny bit at a time, the kernel is often
better at caching than pgsql. Run something like pgbench on a machine
with say 4 Gig of memory and a 40 Gig pgbench db dir, and you're
better off with 128M or something for shared_buffers than 1G often
times. Truly random small access, has, for me, worked better with
moderate to smaller shared_buffers. Plus if you need to run a few
memory hog queries, the kernel can flush out some free memory from
cache quickly, but shared_buffers is static. So, the kernel file
cache is self tuning.

But you'll have to test it with your application to really see.