Re: DB page cache/query performance

On Wed, 14 May 2008, George Pavlov wrote:

> Is there a way to tell whether a query is satisfied from memory cache or
> from disk.

No.  You can look at the hit rate statistics for the tables and indexes
referenced and see how they change before and after the query, but this
just tells you about whether things were found in the PostgreSQL buffer
cache.  The database has no idea how much additional caching is going on
at the OS level below it.  Most people end up running "vmstat 1" and/or
"iostat 1" to see what is actually moving around on disk.  If you install
dstat that's a handy way to combine what would normally be split between
those two.

> Can I calculate exactly (based on PG config and OS parameters) the
> amount of memory available for DB page caches?

You can estimate it based on shared_buffers and the output from free.
Example:

$ free
              total       used       free     shared    buffers     cached
Mem:       2074940    1161764     913176          0     205416     530116
-/+ buffers/cache:     426232    1648708
Swap:      1992020          0    1992020

The upper limit for how much the OS might be caching for you is the
1648708 in this example.  Add that to the size of shared_buffers you
allocated, and you have a decent estimate for the total memory available,
which is also what to set effective_cache_size to.

> 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.

Linux doesn't do any time-based expiration that I'm available of either,
it just uses a customized second-chance LRU I think.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD