monitoring usage count distribution

Nathan Bossart <nathandbossart@gmail.com>

From: Nathan Bossart <nathandbossart@gmail.com>
To: pgsql-hackers@postgresql.org
Cc: schneider@ardentperf.com
Date: 2023-01-30T23:30:40Z
Lists: pgsql-hackers

Attachments

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.

Thoughts?

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

Commits

  1. Add pg_buffercache_usage_counts() to contrib/pg_buffercache.