How to View Shared Buffer Statistics Using pg_buffercache

By digoal

Background

You can view the statistics of a shared buffer on PostgreSQL through pg_buffercache.

The respective fields are as follows:

Name	Type	References	Description
bufferid	INTEGER	-	ID, in the range 1..shared_buffers
relfilenode	OID	pg_class.relfilenode	Filenode number of the relation
reltablespace	OID	pg_tablespace.oid	Tablespace OID of the relation
reldatabase	OID	pg_database.oid	Database OID of the relation
relforknumber	SMALLINT	-	Fork number within the relation; see `include/common/relpath.h`
relblocknumber	BIGINT	-	Page number within the relation
isdirty	boolean	-	Is the page dirty?
usagecount	SMALLINT	-	Clock-sweep access count
pinning_backends	INTEGER	-	Number of backends pinning this buffer

The fields include those that indicate which database, object, tablespace, and file the buffer ID and buffer belong to and the block id.

They also inform whether the buffer is a dirty page, the number of backends by which it was pinned, and the number of times it was pinned.

Explanation:

relforknumber (data, vm, fsm): Fork number within the relation; see include/common/relpath.h
usagecount: The number of times this buffer ID gets pinned. A maximum of 16 (4 bits) clock-sweep access counts are recorded.
pinning_backends: The number of backends that pinned this buffer ID. The maximum value is 18 in bits.

Example

regression=# SELECT c.relname, count(*) AS buffers  
             FROM pg_buffercache b INNER JOIN pg_class c  
             ON b.relfilenode = pg_relation_filenode(c.oid) AND  
                b.reldatabase IN (0, (SELECT oid FROM pg_database  
                                      WHERE datname = current_database()))  
             GROUP BY c.relname  
             ORDER BY 2 DESC  
             LIMIT 10;  
  
             relname             | buffers  
---------------------------------+---------  
 tenk2                           |     345  
 tenk1                           |     141  
 pg_proc                         |      46  
 pg_class                        |      45  
 pg_attribute                    |      43  
 pg_class_relname_nsp_index      |      30  
 pg_proc_proname_args_nsp_index  |      28  
 pg_attribute_relid_attnam_index |      26  
 pg_depend                       |      22  
 pg_depend_reference_index       |      20  
(10 rows)

References

1) https://www.postgresql.org/docs/12/pgbuffercache.html

2) contrib/pg_buffercache/pg_buffercache_pages.c

                        fctx->record[i].usagecount = BUF_STATE_GET_USAGECOUNT(buf_state);  
                        fctx->record[i].pinning_backends = BUF_STATE_GET_REFCOUNT(buf_state);

3) src/include/common/relpath.h

/*  
 * Stuff for fork names.  
 *  
 * The physical storage of a relation consists of one or more forks.  
 * The main fork is always created, but in addition to that there can be  
 * additional forks for storing various metadata. ForkNumber is used when  
 * we need to refer to a specific fork in a relation.
 */  
typedef enum ForkNumber  
{  
        InvalidForkNumber = -1,  
        MAIN_FORKNUM = 0,  
        FSM_FORKNUM,  
        VISIBILITYMAP_FORKNUM,  
        INIT_FORKNUM  
  
        /*  
         * NOTE: if you add a new fork, change MAX_FORKNUM and possibly  
         * FORKNAMECHARS below, and update the forkNames array in  
         * src/common/relpath.c  
         */  
} ForkNumber;

4) src/include/storage/buf_internals.h

/*  
 * Buffer state is a single 32-bit variable where the following data is combined.  
 *  
 * - 18 bits refcount  
 * - 4 bits usage count  
 * - 10 bits of flags  
 *  
 * Combining these values allows performing some operations without locking  
 * the buffer header, by modifying them together with a CAS loop.  
 *  
 * The definition of buffer state components is below.  
 */  
#define BUF_REFCOUNT_ONE 1  
#define BUF_REFCOUNT_MASK ((1U << 18) - 1)  
#define BUF_USAGECOUNT_MASK 0x003C0000U  
#define BUF_USAGECOUNT_ONE (1U << 18)  
#define BUF_USAGECOUNT_SHIFT 18  
#define BUF_FLAG_MASK 0xFFC00000U  
  
/* Get refcount and usagecount from buffer state */  
#define BUF_STATE_GET_REFCOUNT(state) ((state) & BUF_REFCOUNT_MASK)  
#define BUF_STATE_GET_USAGECOUNT(state) (((state) & BUF_USAGECOUNT_MASK) >> BUF_USAGECOUNT_SHIFT)

5) src/backend/storage/buffer/bufmgr.c

/*  
 * PinBuffer -- make buffer unavailable for replacement.  
 *  
 * For the default access strategy, the buffer's usage_count is incremented  
 * when we first pin it; for other strategies, we just make sure the usage_count  
 * isn't zero. (The idea of the latter is that we don't want synchronized  
 * heap scans to inflate the count, but we need it not to be zero to discourage  
 * other backends from stealing buffers from our ring. As long as we cycle  
 * through the ring faster than the global clock-sweep cycles, buffers in  
 * our ring won't be chosen as victims for replacement by other backends.)  
 *  
 * This should be applied only to shared buffers, never local ones.  
 *  
 * Since buffers are pinned/unpinned very frequently, pin buffers without  
 * taking the buffer header lock; instead update the state variable in the loop of  
 * CAS operations. Hopefully, it's just a single CAS.  
 *  
 * Note that ResourceOwnerEnlargeBuffers must have been done already.  
 *  
 * Returns true if the buffer is BM_VALID, else false. This provision allows  
 * some callers to avoid an extra spinlock cycle.
 */  
static bool  
PinBuffer(BufferDesc *buf, BufferAccessStrategy strategy)  
{  
  
....  
  
                        if (strategy == NULL)  
                        {  
                                /* Default case: increase usagecount unless already max. */  
                                if (BUF_STATE_GET_USAGECOUNT(buf_state) < BM_MAX_USAGE_COUNT)  
                                        buf_state += BUF_USAGECOUNT_ONE;  
                        }  
                        else  
                        {  
                                /*  
                                 * Ring buffers shouldn't evict others from the pool. Thus we  
                                 * don't make usagecount more than 1.
                                 */  
                                if (BUF_STATE_GET_USAGECOUNT(buf_state) == 0)  
                                        buf_state += BUF_USAGECOUNT_ONE;  
                        }  
......

Community

How to View Shared Buffer Statistics Using pg_buffercache

Background

Example

References

Read previous post:

Read next post:

digoal

You may also like

Comments

digoal

Related Products

PolarDB for PostgreSQL

AnalyticDB for PostgreSQL

Managed Service for Prometheus

Application Real-Time Monitoring Service