Re: monitoring usage count distribution

On Mon, Jan 30, 2023 at 6:30 PM Nathan Bossart <nathandbossart(at)gmail(dot)com> wrote:
> My colleague Jeremy Schneider (CC'd) was recently looking into usage count
> distributions for various workloads, and he mentioned that it would be nice
> to have an easy way to do $SUBJECT. I've attached a patch that adds a
> pg_buffercache_usage_counts() function. This function returns a row per
> possible usage count with some basic information about the corresponding
> buffers.
>
> postgres=# SELECT * FROM pg_buffercache_usage_counts();
> usage_count | buffers | dirty | pinned
> -------------+---------+-------+--------
> 0 | 0 | 0 | 0
> 1 | 1436 | 671 | 0
> 2 | 102 | 88 | 0
> 3 | 23 | 21 | 0
> 4 | 9 | 7 | 0
> 5 | 164 | 106 | 0
> (6 rows)
>
> This new function provides essentially the same information as
> pg_buffercache_summary(), but pg_buffercache_summary() only shows the
> average usage count for the buffers in use. If there is interest in this
> idea, another approach to consider could be to alter
> pg_buffercache_summary() instead.

I'm skeptical that pg_buffercache_summary() is a good idea at all, but
having it display the average usage count seems like a particularly
poor idea. That information is almost meaningless. Replacing that with
a six-element integer array would be a clear improvement and, IMHO,
better than adding yet another function to the extension.

--
Robert Haas
EDB: http://www.enterprisedb.com