pg_buffercache: Add per-relation summary stats - Mailing list pgsql-hackers

Hi,

See attached a patch that implements a new function,
pg_buffercache_relation_stats(), which returns per-relfilenode
statistics on the number of buffers, how many are dirtied/pinned, and
their avg usage count.

This can be used in monitoring scripts to know which relations are
kept in shared buffers, to understand performance issues better that
occur due to relations getting evicted from the cache. In our own
monitoring tool (pganalyze) we've offered a functionality like this
based on the existing pg_buffercache() function for a bit over a year
now [0], and people have found this very valuable - but it doesn't
work for larger database servers.

Specifically, performing a query that gets this information can be
prohibitively expensive when using large shared_buffers, and even on
the default 128MB shared buffers there is a measurable difference:

postgres=# WITH pg_buffercache_relation_stats AS (
SELECT relfilenode, reltablespace, reldatabase, relforknumber,
                                                COUNT(*) AS buffers,
COUNT(*) FILTER (WHERE isdirty) AS buffers_dirty,
COUNT(*) FILTER (WHERE pinning_backends > 0) AS buffers_pinned,
AVG(usagecount) AS usagecount_avg
FROM pg_buffercache
WHERE reldatabase IS NOT NULL
GROUP BY 1, 2, 3, 4

 )
SELECT * FROM pg_buffercache_relation_stats WHERE relfilenode = 2659;

 relfilenode | reltablespace | reldatabase | relforknumber | buffers |
buffers_dirty | buffers_pinned |   usagecount_avg

-------------+---------------+-------------+---------------+---------+---------------+----------------+--------------------
        2659 |          1663 |           5 |             0 |       8 |
            0 |              0 | 5.0000000000000000
        2659 |          1663 |           1 |             0 |       7 |
            0 |              0 | 5.0000000000000000
        2659 |          1663 |      229553 |             0 |       7 |
            0 |              0 | 5.0000000000000000
(3 rows)

Time: 20.991 ms

postgres=# SELECT * FROM pg_buffercache_relation_stats() WHERE
relfilenode = 2659;
 relfilenode | reltablespace | reldatabase | relforknumber | buffers |
buffers_dirty | buffers_pinned | usagecount_avg

-------------+---------------+-------------+---------------+---------+---------------+----------------+----------------
        2659 |          1663 |           1 |             0 |       7 |
            0 |              0 |              5
        2659 |          1663 |      229553 |             0 |       7 |
            0 |              0 |              5
        2659 |          1663 |           5 |             0 |       8 |
            0 |              0 |              5
(3 rows)

Time: 2.912 ms

With the new function this gets done before putting the data in the
tuplestore used for the set-returning function.

Thanks,
Lukas

[0]: https://pganalyze.com/blog/tracking-postgres-buffer-cache-statistics

-- 
Lukas Fittl