I am on PG 8.1.10 on RedHat Linux. Some page cache/query performance
questions:
Is there a way to tell whether a query is satisfied from memory cache or
from disk. The only way I know of is based on the time the query takes
(reported by EXPLAIN ANALYZE) -- comparing the first-time performance
(after dropping the Linux page caches "echo 3 >
/proc/sys/vm/drop_caches" and restarting the server) vs. subsequent
invocations.
Can I calculate exactly (based on PG config and OS parameters) the
amount of memory available for DB page caches? Can someone walk me
through the calculation or point me to a "for dummies" version.
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)?
All of this is prompted by watching the performance of some queries that
according to my understanding should be served from page cache always
(except for the first time after the server starts) -- instead they
periodically exhibit execution times that look like they are not coming
from cache.
TIA,
George