Re: DB page cache/query performance

[Decibel! <decibel(at)decibel(dot)org>]

On May 14, 2008, at 6:18 PM, Greg Smith wrote:
> > Also, how long should pages stay in the cache? (Assuming I have  
> > way more
> > memory than the total size of all the tables/indexes.) Is there any
> > time-based expiration (in addition to LRU-based, which in my case  
> > should
> > never be resorted to)?
>
> Forever, at least as far as the PostgreSQL one is concerned.  They  
> only get evicted when a new buffer needs to be allocated and  
> there's none available.  It's not quite a LRU mechanism either.  If  
> you visit http://www.westnet.com/~gsmith/content/postgresql/  
> there's a presentation called "Inside the PostgreSQL Buffer Cache"  
> that goes over how that part works.  You'd probably find a look  
> inside your system using pg_buffercache informative.  Some of the  
> sample queries I give only work on 8.3, but the "Buffer contents  
> summary, with percentages" one should be OK on 8.1.


Hrm... don't seqscans use a separate set of buffers in 8.3? While  
technically those won't be evicted until needed, you're unlikely to  
find stuff hanging around there for terribly long...
--
Decibel!, aka Jim C. Nasby, Database Architect  decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Attachment: smime.p7s
Description: S/MIME cryptographic signature