Hi hackers,
I also added documentation changes into the patch.
You can find it attached.
I would appreciate any feedback about this pg_buffercache_summary function.
Best,
Melih
From 82e92d217dd240a9b6c1184cf29d4718343558b8 Mon Sep 17 00:00:00 2001
From: Melih Mutlu <m.melihmutlu@gmail.com>
Date: Tue, 9 Aug 2022 16:42:23 +0300
Subject: [PATCH] Added pg_buffercache_summary function
Adds pg_buffercache_summary() function into pg_buffercache extension for
retrieving summary information about overall shared_buffer usage.
---
contrib/pg_buffercache/Makefile | 3 +-
.../expected/pg_buffercache.out | 9 ++
.../pg_buffercache--1.3--1.4.sql | 13 +++
contrib/pg_buffercache/pg_buffercache.control | 2 +-
contrib/pg_buffercache/pg_buffercache_pages.c | 80 ++++++++++++-
contrib/pg_buffercache/sql/pg_buffercache.sql | 5 +
doc/src/sgml/pgbuffercache.sgml | 110 +++++++++++++++++-
7 files changed, 215 insertions(+), 7 deletions(-)
create mode 100644 contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql
diff --git a/contrib/pg_buffercache/Makefile b/contrib/pg_buffercache/Makefile
index d74b3e853c..d6b58d4da9 100644
--- a/contrib/pg_buffercache/Makefile
+++ b/contrib/pg_buffercache/Makefile
@@ -7,7 +7,8 @@ OBJS = \
EXTENSION = pg_buffercache
DATA = pg_buffercache--1.2.sql pg_buffercache--1.2--1.3.sql \
- pg_buffercache--1.1--1.2.sql pg_buffercache--1.0--1.1.sql
+ pg_buffercache--1.1--1.2.sql pg_buffercache--1.0--1.1.sql \
+ pg_buffercache--1.3--1.4.sql
PGFILEDESC = "pg_buffercache - monitoring of shared buffer cache in real-time"
REGRESS = pg_buffercache
diff --git a/contrib/pg_buffercache/expected/pg_buffercache.out b/contrib/pg_buffercache/expected/pg_buffercache.out
index 138556efc9..6994761d0a 100644
--- a/contrib/pg_buffercache/expected/pg_buffercache.out
+++ b/contrib/pg_buffercache/expected/pg_buffercache.out
@@ -8,3 +8,12 @@ from pg_buffercache;
t
(1 row)
+select used_buffers + unused_buffers > 0,
+ dirty_buffers < used_buffers,
+ pinned_buffers < used_buffers
+from pg_buffercache_summary();
+ ?column? | ?column? | ?column?
+----------+----------+----------
+ t | t | t
+(1 row)
+
diff --git a/contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql b/contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql
new file mode 100644
index 0000000000..02800dbf18
--- /dev/null
+++ b/contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql
@@ -0,0 +1,13 @@
+/* contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pg_buffercache UPDATE TO '1.4'" to load this file. \quit
+
+CREATE FUNCTION pg_buffercache_summary()
+RETURNS TABLE (used_buffers int4, unused_buffers int4, dirty_buffers int4,
+ pinned_buffers int4, avg_usagecount real)
+AS 'MODULE_PATHNAME', 'pg_buffercache_summary'
+LANGUAGE C PARALLEL SAFE;
+
+-- Don't want these to be available to public.
+REVOKE ALL ON FUNCTION pg_buffercache_summary() FROM PUBLIC;
diff --git a/contrib/pg_buffercache/pg_buffercache.control b/contrib/pg_buffercache/pg_buffercache.control
index 8c060ae9ab..a82ae5f9bb 100644
--- a/contrib/pg_buffercache/pg_buffercache.control
+++ b/contrib/pg_buffercache/pg_buffercache.control
@@ -1,5 +1,5 @@
# pg_buffercache extension
comment = 'examine the shared buffer cache'
-default_version = '1.3'
+default_version = '1.4'
module_pathname = '$libdir/pg_buffercache'
relocatable = true
diff --git a/contrib/pg_buffercache/pg_buffercache_pages.c b/contrib/pg_buffercache/pg_buffercache_pages.c
index c5754ea9fa..89f8a2e834 100644
--- a/contrib/pg_buffercache/pg_buffercache_pages.c
+++ b/contrib/pg_buffercache/pg_buffercache_pages.c
@@ -17,6 +17,7 @@
#define NUM_BUFFERCACHE_PAGES_MIN_ELEM 8
#define NUM_BUFFERCACHE_PAGES_ELEM 9
+#define NUM_BUFFERCACHE_SUMMARY_ELEM 5
PG_MODULE_MAGIC;
@@ -43,7 +44,6 @@ typedef struct
int32 pinning_backends;
} BufferCachePagesRec;
-
/*
* Function context for data persisting over repeated calls.
*/
@@ -53,12 +53,12 @@ typedef struct
BufferCachePagesRec *record;
} BufferCachePagesContext;
-
/*
* Function returning data from the shared buffer cache - buffer number,
* relation node/tablespace/database/blocknum and dirty indicator.
*/
PG_FUNCTION_INFO_V1(pg_buffercache_pages);
+PG_FUNCTION_INFO_V1(pg_buffercache_summary);
Datum
pg_buffercache_pages(PG_FUNCTION_ARGS)
@@ -237,3 +237,79 @@ pg_buffercache_pages(PG_FUNCTION_ARGS)
else
SRF_RETURN_DONE(funcctx);
}
+
+Datum
+pg_buffercache_summary(PG_FUNCTION_ARGS)
+{
+ Datum result;
+ TupleDesc tupledesc;
+ HeapTuple tuple;
+ Datum values[NUM_BUFFERCACHE_SUMMARY_ELEM];
+ bool nulls[NUM_BUFFERCACHE_SUMMARY_ELEM];
+
+ int32 used_buffers = 0;
+ int32 unused_buffers = 0;
+ int32 dirty_buffers = 0;
+ int32 pinned_buffers = 0;
+ float avg_usagecount = 0;
+
+ /* Construct a tuple descriptor for the result rows. */
+ tupledesc = CreateTemplateTupleDesc(NUM_BUFFERCACHE_SUMMARY_ELEM);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 1, "used_buffers",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 2, "unused_buffers",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 3, "dirty_buffers",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 4, "pinned_buffers",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 5, "avg_usagecount",
+ FLOAT4OID, -1, 0);
+
+ BlessTupleDesc(tupledesc);
+
+ for (int i = 0; i < NBuffers; i++)
+ {
+ BufferDesc *bufHdr;
+ uint32 buf_state;
+
+ /*
+ * No need to get locks on buffer headers as we don't rely on
+ * the results in detail. Therefore, we don't get a consistent
+ * snapshot across all buffers and it is not guaranteed that
+ * the information of each buffer is self-consistent as opposed
+ * to pg_buffercache_pages.
+ */
+ bufHdr = GetBufferDescriptor(i);
+ buf_state = pg_atomic_read_u32(&bufHdr->state);
+
+ /* Invalid RelFileNumber means the buffer is unused */
+ if(bufHdr->tag.rlocator.relNumber != InvalidOid)
+ {
+ used_buffers++;
+ avg_usagecount += BUF_STATE_GET_USAGECOUNT(buf_state);
+
+ if (buf_state & BM_DIRTY)
+ dirty_buffers++;
+ }
+ else
+ unused_buffers++;
+
+ if (BUF_STATE_GET_REFCOUNT(buf_state) > 0)
+ pinned_buffers++;
+ }
+ avg_usagecount /= used_buffers;
+
+ memset(nulls, 0, sizeof(nulls));
+ values[0] = Int32GetDatum(used_buffers);
+ values[1] = Int32GetDatum(unused_buffers);
+ values[2] = Int32GetDatum(dirty_buffers);
+ values[3] = Int32GetDatum(pinned_buffers);
+ values[4] = Float4GetDatum(avg_usagecount);
+
+ /* Build and return the tuple. */
+ tuple = heap_form_tuple(tupledesc, values, nulls);
+ result = HeapTupleGetDatum(tuple);
+
+ PG_RETURN_DATUM(result);
+}
diff --git a/contrib/pg_buffercache/sql/pg_buffercache.sql b/contrib/pg_buffercache/sql/pg_buffercache.sql
index e1ba6f7e8d..04e78b1faf 100644
--- a/contrib/pg_buffercache/sql/pg_buffercache.sql
+++ b/contrib/pg_buffercache/sql/pg_buffercache.sql
@@ -4,3 +4,8 @@ select count(*) = (select setting::bigint
from pg_settings
where name = 'shared_buffers')
from pg_buffercache;
+
+select used_buffers + unused_buffers > 0,
+ dirty_buffers < used_buffers,
+ pinned_buffers < used_buffers
+from pg_buffercache_summary();
\ No newline at end of file
diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml
index a06fd3e26d..e3ef76caeb 100644
--- a/doc/src/sgml/pgbuffercache.sgml
+++ b/doc/src/sgml/pgbuffercache.sgml
@@ -17,9 +17,19 @@
</indexterm>
<para>
- The module provides a C function <function>pg_buffercache_pages</function>
- that returns a set of records, plus a view
- <structname>pg_buffercache</structname> that wraps the function for
+ The module provides C functions <function>pg_buffercache_pages</function>
+ and <function>pg_buffercache_summary</function>.
+ </para>
+
+ <para>
+ <function>pg_buffercache_pages</function> function
+ returns a set of records, plus a view <structname>pg_buffercache</structname> that wraps the function for
+ convenient use is provided.
+ </para>
+
+ <para>
+ <function>pg_buffercache_summary</function> function returns a table with a single row
+ that contains summarized and aggregated information about shared buffer caches.
convenient use.
</para>
@@ -164,6 +174,93 @@
</para>
</sect2>
+ <sect2>
+ <title>The <structname>pg_buffercache_summary</structname> Function</title>
+
+ <para>
+ The definitions of the columns exposed by the function are shown in <xref linkend="pgbuffercachesummary-columns"/>.
+ </para>
+
+ <table id="pgbuffercachesummary-columns">
+ <title><structname>pg_buffercachesummary</structname> Columns</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>used_buffers</structfield> <type>int4</type>
+ </para>
+ <para>
+ Number of shared buffers currently being used
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>unused_buffers</structfield> <type>int4</type>
+ </para>
+ <para>
+ Number of shared buffers that not currently being used
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>dirty_buffers</structfield> <type>int4</type>
+ </para>
+ <para>
+ Number of dirty shared buffer caches
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>pinned_buffers</structfield> <type>int4</type>
+ </para>
+ <para>
+ Number of shared buffers that has a pinned backend
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>avg_usagecount</structfield> <type>float</type>
+ </para>
+ <para>
+ Average usagecount of used shared buffers
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ There is a single row to show summarized information of all shared buffers.
+ <function>pg_buffercache_summary</function> is not interested
+ in the state of each shared buffer, only shows aggregated information.
+ </para>
+
+ <para>
+ Buffer manager locks are not taken to copy as in <function>pg_buffercache_pages</function>,
+ so it doesn't provide a consistent set of results across all buffers either.
+ Additonally, <function>pg_buffercache_summary</function> does not get locks on
+ buffer headers too. Therefore, self-consistency of the information for each buffer
+ is not guaranteed. Since the purpose of this function is just to give an overall
+ idea about the state of shared buffers, locks are not
+ strictly needed.
+ </para>
+ </sect2>
+
<sect2>
<title>Sample Output</title>
@@ -191,6 +288,13 @@ regression=# SELECT n.nspname, c.relname, count(*) AS buffers
public | gin_test_tbl | 188
public | spgist_text_tbl | 182
(10 rows)
+
+
+regression=# SELECT * FROM pg_buffercache_summary();
+ used_buffers | unused_buffers | dirty_buffers | pinned_buffers | avg_usagecount
+--------------+----------------+---------------+----------------+----------------
+ 248 | 2096904 | 39 | 0 | 3.141129
+(1 row)
</screen>
</sect2>
--
2.25.1
