From 2459cc7c49ebeec872022ae2d2099182e5200be4 Mon Sep 17 00:00:00 2001 From: Nathan Bossart Date: Thu, 6 Apr 2023 11:04:07 -0700 Subject: [PATCH v2 2/2] replace usagecount_avg with an array of usage counts --- .../pg_buffercache/expected/pg_buffercache.out | 9 +++++---- .../pg_buffercache/pg_buffercache--1.3--1.4.sql | 2 +- contrib/pg_buffercache/pg_buffercache_pages.c | 16 ++++++++++------ contrib/pg_buffercache/sql/pg_buffercache.sql | 3 ++- doc/src/sgml/pgbuffercache.sgml | 10 +++++----- 5 files changed, 23 insertions(+), 17 deletions(-) diff --git a/contrib/pg_buffercache/expected/pg_buffercache.out b/contrib/pg_buffercache/expected/pg_buffercache.out index b745dc69ea..a5921b83c5 100644 --- a/contrib/pg_buffercache/expected/pg_buffercache.out +++ b/contrib/pg_buffercache/expected/pg_buffercache.out @@ -10,11 +10,12 @@ from pg_buffercache; select buffers_used + buffers_unused > 0, buffers_dirty <= buffers_used, - buffers_pinned <= buffers_used + buffers_pinned <= buffers_used, + array_length(usagecounts, 1) > 0 from pg_buffercache_summary(); - ?column? | ?column? | ?column? -----------+----------+---------- - t | t | t + ?column? | ?column? | ?column? | ?column? +----------+----------+----------+---------- + t | t | t | t (1 row) SELECT count(*) > 0 FROM pg_buffercache_usage_counts() WHERE buffers >= 0; diff --git a/contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql b/contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql index f4702e4b4b..9120ad6ffb 100644 --- a/contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql +++ b/contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql @@ -8,7 +8,7 @@ CREATE FUNCTION pg_buffercache_summary( OUT buffers_unused int4, OUT buffers_dirty int4, OUT buffers_pinned int4, - OUT usagecount_avg float8) + OUT usagecounts int4[]) AS 'MODULE_PATHNAME', 'pg_buffercache_summary' LANGUAGE C PARALLEL SAFE; diff --git a/contrib/pg_buffercache/pg_buffercache_pages.c b/contrib/pg_buffercache/pg_buffercache_pages.c index f333967c51..da3594cc7d 100644 --- a/contrib/pg_buffercache/pg_buffercache_pages.c +++ b/contrib/pg_buffercache/pg_buffercache_pages.c @@ -13,6 +13,7 @@ #include "funcapi.h" #include "storage/buf_internals.h" #include "storage/bufmgr.h" +#include "utils/array.h" #define NUM_BUFFERCACHE_PAGES_MIN_ELEM 8 @@ -255,7 +256,9 @@ pg_buffercache_summary(PG_FUNCTION_ARGS) int32 buffers_unused = 0; int32 buffers_dirty = 0; int32 buffers_pinned = 0; - int64 usagecount_total = 0; + + int32 usagecounts[BM_MAX_USAGE_COUNT + 1] = {0}; + Datum ucdata[BM_MAX_USAGE_COUNT + 1]; if (get_call_result_type(fcinfo, NULL, &tupledesc) != TYPEFUNC_COMPOSITE) elog(ERROR, "return type must be a row type"); @@ -277,7 +280,7 @@ pg_buffercache_summary(PG_FUNCTION_ARGS) if (buf_state & BM_VALID) { buffers_used++; - usagecount_total += BUF_STATE_GET_USAGECOUNT(buf_state); + usagecounts[BUF_STATE_GET_USAGECOUNT(buf_state)]++; if (buf_state & BM_DIRTY) buffers_dirty++; @@ -295,10 +298,11 @@ pg_buffercache_summary(PG_FUNCTION_ARGS) values[2] = Int32GetDatum(buffers_dirty); values[3] = Int32GetDatum(buffers_pinned); - if (buffers_used != 0) - values[4] = Float8GetDatum((double) usagecount_total / buffers_used); - else - nulls[4] = true; + for (int i = 0; i < BM_MAX_USAGE_COUNT + 1; i++) + ucdata[i] = Int32GetDatum(usagecounts[i]); + values[4] = PointerGetDatum(construct_array(ucdata, BM_MAX_USAGE_COUNT + 1, + INT4OID, sizeof(int32), true, + TYPALIGN_INT)); /* Build and return the tuple. */ tuple = heap_form_tuple(tupledesc, values, nulls); diff --git a/contrib/pg_buffercache/sql/pg_buffercache.sql b/contrib/pg_buffercache/sql/pg_buffercache.sql index 944fbb1bea..b5288eb283 100644 --- a/contrib/pg_buffercache/sql/pg_buffercache.sql +++ b/contrib/pg_buffercache/sql/pg_buffercache.sql @@ -7,7 +7,8 @@ from pg_buffercache; select buffers_used + buffers_unused > 0, buffers_dirty <= buffers_used, - buffers_pinned <= buffers_used + buffers_pinned <= buffers_used, + array_length(usagecounts, 1) > 0 from pg_buffercache_summary(); SELECT count(*) > 0 FROM pg_buffercache_usage_counts() WHERE buffers >= 0; diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml index acd88aa813..74b1e92637 100644 --- a/doc/src/sgml/pgbuffercache.sgml +++ b/doc/src/sgml/pgbuffercache.sgml @@ -247,10 +247,10 @@ - usagecount_avg float8 + usagecounts int4[] - Average usagecount of used shared buffers + Number of used shared buffers for each possible usagecount @@ -381,9 +381,9 @@ regression=# SELECT n.nspname, c.relname, count(*) AS buffers regression=# SELECT * FROM pg_buffercache_summary(); - buffers_used | buffers_unused | buffers_dirty | buffers_pinned | usagecount_avg ---------------+----------------+---------------+----------------+---------------- - 248 | 2096904 | 39 | 0 | 3.141129 + buffers_used | buffers_unused | buffers_dirty | buffers_pinned | usagecounts +--------------+----------------+---------------+----------------+------------------ + 184 | 2096968 | 44 | 0 | {0,69,15,8,5,87} (1 row) -- 2.25.1