Figuring out shared buffer pressure

As part of a blog, I started looking at how a user could measure the
pressure on shared buffers, e.g. how much are they being used, recycled,
etc.

They way you normally do it on older operating systems is to see how
many buffers on the free list (about to be reused) are reclaimed as
needed --- that usually indicates kernel cache pressure. Unfortunately,
we don't have a freelist, except for initial assignment of shared
buffers on startup.

I then started looking at pg_buffercache, and thought perhaps the
'usagecount' column could give me a way of measuring this. For example,
excessive scanning of the shared buffers for replaceable buffers would
indicate pressure, which might show as a low usagecount.

I ran the attached SQL script, and got the attached results. You can
see that the first few attempts to use many shared buffers was thwarted
by our GetAccessStrategy() function that prevent sequential access from
blowing away other shared buffers, limiting such scans to 256k:

http://doxygen.postgresql.org/freelist_8c_source.html#l00410

Our storage/buffers/README file has the reason for the size (to fit in
CPU cache), line 204:

http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/storage/buffer/README

I realize we can't size this based on shared buffers because it is based
on the CPU cache size, but it seems we are purposely storing buffers in
the kernel rather than shared buffers in this case. I suppose if we
allowed a table to use more, there would be no way to keep all the
memory in a single 256k area, but are we sure this is the right
approach?

Based on what I found, I can see no way users can see how heavily their
shared buffers are being used. Is that correct?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +