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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, 19 May 2008, Decibel! wrote:

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

Not quite a separate set, and I have been remiss that I only skim over
that in the presentation right now. Keep meaning to write down those
details, and now that you ask that time is now. A quick read of the code
suggests that in 8.3, the following rules apply:

1) If you are doing a scan where the table is larger than
(shared_buffers/4) or you are doing a VACUUM, your backend gets allocated
a ring list it keeps track of the buffers it has requested in.

2) The ring size is 8K * min(shared_buffers/8,32) which for any
non-trivial buffer size cases is 256K. As you request pages they get
added to the list of ones in the ring. Here shared_buffers is specified
as in older versions, as a count of 8K buffers.

3) Once the ring is full and you circle around to a page that's already
been used, if its usage count is <=1 (which means that nobody else has
used it since it was put in there) that page will get evicted and then
re-used rather than allocating a new one in the normal fashion.

4) If someone else is using the buffer, instead a new one is allocated the
normal way and it replaces the original entry in the ring.

So, yes, in 8.3 it's possible that you can have sequential scans of large
tables or the VACUUM data pass through the buffer cache, but not remain in
it afterwards. I didn't think George would ever run into this in the
specific example he asked about because of (1). This behavior only kicks
in if you're scanning a table large relative to the total shared buffer
cache and that didn't seem like an issue in his case.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

> From: Greg Smith [mailto:gsmith@gregsmith.com]
> Sent: Monday, May 19, 2008 9:03 PM
>
> So, yes, in 8.3 it's possible that you can have sequential
> scans of large
> tables or the VACUUM data pass through the buffer cache, but
> not remain in
> it afterwards. I didn't think George would ever run into this in the
> specific example he asked about because of (1). This
> behavior only kicks
> in if you're scanning a table large relative to the total
> shared buffer
> cache and that didn't seem like an issue in his case.

Correct -- the tables in this example were tiny, shared buffers are
large, and, in any case, I am still on 8.1...

George

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general