Summary function for pg_buffercache
Started by Melih Mutluover 3 years ago32 messages
Hi hackers,
Added a pg_buffercache_summary() function to retrieve an aggregated summary
information with less cost.
It's often useful to know only how many buffers are used, how many of them
are dirty etc. for monitoring purposes.
This info can already be retrieved by pg_buffercache. The extension
currently creates a row with many details for each buffer, then summary
info can be aggregated from that returned table.
But it is quite expensive to run regularly for monitoring.
The attached patch adds a pg_buffercache_summary() function to get this
summary info faster.
New function only collects following info and returns them in a single row:
- used_buffers = number of buffers with a valid relfilenode (both dirty and
not)
- unused_buffers = number of buffers with invalid relfilenode
- dirty_buffers = number of dirty buffers.
- pinned_buffers = number of buffers that have at least one pinning backend
(i.e. refcount > 0)
- average usagecount of used buffers
One other difference between pg_buffercache_summary and
pg_buffercache_pages is that pg_buffercache_summary does not get locks on
buffer headers as opposed to pg_buffercache_pages.
Since the purpose of pg_buffercache_summary is just to give us an overall
idea about shared buffers and to be a cheaper function, locks are not
strictly needed.
To compare pg_buffercache_summary() and pg_buffercache_pages(), I used a
simple query to aggregate the summary information above by calling
pg_buffercache_pages().
Here is the result:
postgres=# show shared_buffers;
shared_buffers
----------------
16GB
(1 row)
Time: 0.756 ms
postgres=# SELECT relfilenode <> 0 AS is_valid, isdirty, count(*) FROM
pg_buffercache GROUP BY relfilenode <> 0, isdirty;
is_valid | isdirty | count
----------+---------+---------
t | f | 209
| | 2096904
t | t | 39
(3 rows)
Time: 1434.870 ms (00:01.435)
postgres=# select * from pg_buffercache_summary();
used_buffers | unused_buffers | dirty_buffers | pinned_buffers |
avg_usagecount
--------------+----------------+---------------+----------------+----------------
248 | 2096904 | 39 | 0 |
3.141129
(1 row)
Time: 9.712 ms
There is a significant difference between timings of those two functions,
even though they return similar results.
I would appreciate any feedback/comment on this change.
Thanks,
Melih
Attachments:
0001-Added-pg_buffercache_summary-function.patchapplication/octet-stream; name=0001-Added-pg_buffercache_summary-function.patchDownload
From 616df156cfdf97269e45d6607e52f3d925c46216 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 ++
6 files changed, 108 insertions(+), 4 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 131bd629b9..6eafa657e6 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
--
2.25.1
Re: Summary function for pg_buffercache
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
Attachments:
v2-0001-Added-pg_buffercache_summary-function.patchapplication/x-patch; name=v2-0001-Added-pg_buffercache_summary-function.patchDownload
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
Re: Summary function for pg_buffercache
Hi Melih,
I would appreciate any feedback/comment on this change.
Another benefit of pg_buffercache_summary() you didn't mention is that
it allocates much less memory than pg_buffercache_pages() does.
Here is v3 where I added this to the documentation. The patch didn't
apply to the current master branch with the following error:
```
pg_buffercache_pages.c:286:19: error: no member named 'rlocator' in
'struct buftag'
if (bufHdr->tag.rlocator.relNumber != InvalidOid)
~~~~~~~~~~~ ^
1 error generated.
```
I fixed this too. Additionally, the patch was pgindent'ed and some
typos were fixed.
However I'm afraid you can't examine BufferDesc's without taking
locks. This is explicitly stated in buf_internals.h:
"""
Buffer header lock (BM_LOCKED flag) must be held to EXAMINE or change
TAG, state or wait_backend_pgprocno fields.
"""
Let's consider this code again (this is after my fix):
```
if (RelFileNumberIsValid(BufTagGetRelNumber(bufHdr))) {
/* ... */
}
```
When somebody modifies relNumber concurrently (e.g. calls
ClearBufferTag()) this will cause an undefined behaviour.
I suggest we focus on saving the memory first and then think about the
performance, if necessary.
--
Best regards,
Aleksander Alekseev
Attachments:
v3-0001-Added-pg_buffercache_summary-function.patchapplication/octet-stream; name=v3-0001-Added-pg_buffercache_summary-function.patchDownload
From 2c93894c3f7924161ad5c0c1284f26d9a2620bed 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 v3] Added pg_buffercache_summary function
Adds pg_buffercache_summary() function into pg_buffercache extension for
retrieving summary information about overall shared_buffer usage.
Author: Melih Mutlu <m.melihmutlu@gmail.com>
Reviewed-by: Aleksander Alekseev <aleksander@timescale.com>
Discussion: https://postgr.es/m/CAGPVpCQAXYo54Q%3D8gqBsS%3Du0uk9qhnnq4%2B710BtUhUisX1XGEg%40mail.gmail.com
---
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 | 79 ++++++++++++-
contrib/pg_buffercache/sql/pg_buffercache.sql | 5 +
doc/src/sgml/pgbuffercache.sgml | 110 +++++++++++++++++-
7 files changed, 213 insertions(+), 8 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..867f148598 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,78 @@ 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 (RelFileNumberIsValid(BufTagGetRelNumber(bufHdr)))
+ {
+ 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..6cf3d56c99 100644
--- a/doc/src/sgml/pgbuffercache.sgml
+++ b/doc/src/sgml/pgbuffercache.sgml
@@ -17,10 +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
- convenient use.
+ 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.
</para>
<para>
@@ -164,6 +173,92 @@
</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>
+ Unlike <function>pg_buffercache_pages</function> function
+ <function>pg_buffercache_summary</function> doesn't take buffer manager
+ locks and buffer headers locks, thus the result is not consistent. This is
+ intentional. The purpose of this function is to provide a general idea about
+ the state of shared buffers as fast as possible. Additionally,
+ <function>pg_buffercache_summary</function> allocates much less memory.
+ </para>
+ </sect2>
+
<sect2>
<title>Sample Output</title>
@@ -191,6 +286,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.37.2
Re: Summary function for pg_buffercache
On Fri, Sep 09, 2022 at 05:36:45PM +0300, Aleksander Alekseev wrote:
However I'm afraid you can't examine BufferDesc's without taking
locks. This is explicitly stated in buf_internals.h:
Yeah, when I glanced at this patch earlier, I wondered about this.
I suggest we focus on saving the memory first and then think about the
performance, if necessary.
+1
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
Re: Summary function for pg_buffercache
Hi hackers,
I suggest we focus on saving the memory first and then think about the
performance, if necessary.+1
I made a mistake in v3 cfbot complained about. It should have been:
```
if (RelFileNumberIsValid(BufTagGetRelNumber(&bufHdr->tag)))
```
Here is the corrected patch.
--
Best regards,
Aleksander Alekseev
Attachments:
v4-0001-Added-pg_buffercache_summary-function.patchapplication/octet-stream; name=v4-0001-Added-pg_buffercache_summary-function.patchDownload
From 753f795d028d9a4ee8aff36efbfa589126261f02 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 v4] Added pg_buffercache_summary function
Adds pg_buffercache_summary() function into pg_buffercache extension for
retrieving summary information about overall shared_buffer usage.
Author: Melih Mutlu <m.melihmutlu@gmail.com>
Reviewed-by: Aleksander Alekseev <aleksander@timescale.com>
Discussion: https://postgr.es/m/CAGPVpCQAXYo54Q%3D8gqBsS%3Du0uk9qhnnq4%2B710BtUhUisX1XGEg%40mail.gmail.com
---
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 | 79 ++++++++++++-
contrib/pg_buffercache/sql/pg_buffercache.sql | 5 +
doc/src/sgml/pgbuffercache.sgml | 110 +++++++++++++++++-
7 files changed, 213 insertions(+), 8 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..c5e973bb84 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,78 @@ 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 (RelFileNumberIsValid(BufTagGetRelNumber(&bufHdr->tag)))
+ {
+ 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..6cf3d56c99 100644
--- a/doc/src/sgml/pgbuffercache.sgml
+++ b/doc/src/sgml/pgbuffercache.sgml
@@ -17,10 +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
- convenient use.
+ 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.
</para>
<para>
@@ -164,6 +173,92 @@
</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>
+ Unlike <function>pg_buffercache_pages</function> function
+ <function>pg_buffercache_summary</function> doesn't take buffer manager
+ locks and buffer headers locks, thus the result is not consistent. This is
+ intentional. The purpose of this function is to provide a general idea about
+ the state of shared buffers as fast as possible. Additionally,
+ <function>pg_buffercache_summary</function> allocates much less memory.
+ </para>
+ </sect2>
+
<sect2>
<title>Sample Output</title>
@@ -191,6 +286,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.37.2
Re: Summary function for pg_buffercache
Hi Aleksander and Nathan,
Thanks for your comments.
Aleksander Alekseev <aleksander@timescale.com>, 9 Eyl 2022 Cum, 17:36
tarihinde şunu yazdı:
However I'm afraid you can't examine BufferDesc's without taking
locks. This is explicitly stated in buf_internals.h:"""
Buffer header lock (BM_LOCKED flag) must be held to EXAMINE or change
TAG, state or wait_backend_pgprocno fields.
"""
I wasn't aware of this explanation. Thanks for pointing it out.
When somebody modifies relNumber concurrently (e.g. calls
ClearBufferTag()) this will cause an undefined behaviour.
I thought that it wouldn't really be a problem even if relNumber is
modified concurrently, since the function does not actually rely on the
actual values.
I'm not sure about what undefined behaviour could harm this badly. It
seemed to me that it would read an invalid relNumber in the worst case
scenario.
But I'm not actually familiar with buffer related parts of the code, so I
might be wrong.
And I'm okay with taking header locks if necessary.
In the attached patch, I added buffer header locks just before examining
tag as follows:
+ buf_state = LockBufHdr(bufHdr);
+ + /* Invalid RelFileNumber means the buffer is unused */ + if (RelFileNumberIsValid(BufTagGetRelNumber(&bufHdr->tag))) + { ... + } ... + UnlockBufHdr(bufHdr, buf_state);
I suggest we focus on saving the memory first and then think about the
performance, if necessary.
+1
I again did the same quick benchmarking, here are the numbers with locks.
postgres=# show shared_buffers;
shared_buffers
----------------
16GB
(1 row)
postgres=# SELECT relfilenode <> 0 AS is_valid, isdirty, count(*) FROM
pg_buffercache GROUP BY relfilenode <> 0, isdirty;
is_valid | isdirty | count
----------+---------+---------
t | f | 256
| | 2096876
t | t | 20
(3 rows)
Time: 1024.456 ms (00:01.024)
postgres=# select * from pg_buffercache_summary();
used_buffers | unused_buffers | dirty_buffers | pinned_buffers |
avg_usagecount
--------------+----------------+---------------+----------------+----------------
282 | 2096870 | 20 | 0 |
3.4574468
(1 row)
Time: 33.074 ms
Yes, locks slowed pg_buffercache_summary down. But there is still quite a
bit of performance improvement, plus memory saving as you mentioned.
Here is the corrected patch.
Also thanks for corrections.
Best,
Melih
Attachments:
v5-0001-Added-pg_buffercache_summary-function.patchapplication/octet-stream; name=v5-0001-Added-pg_buffercache_summary-function.patchDownload
From 88c943aca549d682566250f1b28dd6d9d2e974d9 Mon Sep 17 00:00:00 2001
From: Melih Mutlu <m.melihmutlu@gmail.com>
Date: Sat, 10 Sep 2022 02:08:24 +0300
Subject: [PATCH v5] Added pg_buffercache_summary function
Adds pg_buffercache_summary() function into pg_buffercache extension for
retrieving summary information about overall shared_buffer usage.
Author: Melih Mutlu <m.melihmutlu@gmail.com>
Reviewed-by: Aleksander Alekseev <aleksander@timescale.com>
Discussion: https://postgr.es/m/CAGPVpCQAXYo54Q%3D8gqBsS%3Du0uk9qhnnq4%2B710BtUhUisX1XGEg%40mail.gmail.com
---
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 | 77 +++++++++++-
contrib/pg_buffercache/sql/pg_buffercache.sql | 5 +
doc/src/sgml/pgbuffercache.sgml | 110 +++++++++++++++++-
7 files changed, 211 insertions(+), 8 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..8fbd0abced 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,76 @@ 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;
+
+ bufHdr = GetBufferDescriptor(i);
+
+ /* Lock each buffer header before inspecting. */
+ buf_state = LockBufHdr(bufHdr);
+
+ /* Invalid RelFileNumber means the buffer is unused */
+ if (RelFileNumberIsValid(BufTagGetRelNumber(&bufHdr->tag)))
+ {
+ 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++;
+
+ UnlockBufHdr(bufHdr, buf_state);
+ }
+ 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..673eee5e2c 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();
diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml
index a06fd3e26d..3d5dd95a90 100644
--- a/doc/src/sgml/pgbuffercache.sgml
+++ b/doc/src/sgml/pgbuffercache.sgml
@@ -17,10 +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
- convenient use.
+ 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.
</para>
<para>
@@ -164,6 +173,92 @@
</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>
+ Similar to <function>pg_buffercache_pages</function> function
+ <function>pg_buffercache_summary</function> doesn't take buffer manager
+ locks, thus the result is not consistent across all buffers. This is
+ intentional. The purpose of this function is to provide a general idea about
+ the state of shared buffers as fast as possible. Additionally,
+ <function>pg_buffercache_summary</function> allocates much less memory.
+ </para>
+ </sect2>
+
<sect2>
<title>Sample Output</title>
@@ -191,6 +286,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
Re: Summary function for pg_buffercache
Hi Melih,
I'm not sure about what undefined behaviour could harm this badly.
You are right that in practice nothing wrong will (probably) happen on
x86/x64 architecture with (most?) modern C compilers. This is not true in
the general case though. It's up to the compiler to decide how reading the
bufHdr->tag is going to be actually implemented. This can be one assembly
instruction or several instructions. This reading can be optimized-out if
the compiler believes the required value is already in the register, etc.
Since the result will be different depending on the assembly code used this
is an undefined behaviour and we can't use code like this.
In the attached patch, I added buffer header locks just before examining
tag as follows
Many thanks for the updated patch! It looks better now.
However I have somewhat mixed feelings about avg_usagecount. Generally
AVG() is a relatively useless methric for monitoring. What if the user
wants MIN(), MAX() or let's say a 99th percentile? I suggest splitting it
into usagecount_min, usagecount_max and usagecount_sum. AVG() can be
derived as usercount_sum / used_buffers.
Also I suggest changing the names of the columns in order to make them
consistent with the rest of the system. If you consider pg_stat_activity
and family [1]https://www.postgresql.org/docs/current/monitoring-stats.html you will notice that the columns are named
(entity)_(property), e.g. backend_xid, backend_type, client_addr, etc. So
instead of used_buffers and unused_buffers the naming should be
buffers_used and buffers_unused.
[1]: https://www.postgresql.org/docs/current/monitoring-stats.html
--
Best regards,
Aleksander Alekseev
Re: Summary function for pg_buffercache
Hello Aleksander,
I'm not sure about what undefined behaviour could harm this badly.
You are right that in practice nothing wrong will (probably) happen on
x86/x64 architecture with (most?) modern C compilers. This is not true in
the general case though. It's up to the compiler to decide how reading the
bufHdr->tag is going to be actually implemented. This can be one assembly
instruction or several instructions. This reading can be optimized-out if
the compiler believes the required value is already in the register, etc.
Since the result will be different depending on the assembly code used this
is an undefined behaviour and we can't use code like this.
Got it. Thanks for explaining.
However I have somewhat mixed feelings about avg_usagecount. Generally
AVG() is a relatively useless methric for monitoring. What if the user
wants MIN(), MAX() or let's say a 99th percentile? I suggest splitting it
into usagecount_min, usagecount_max and usagecount_sum. AVG() can be
derived as usercount_sum / used_buffers.
Won't be usagecount_max almost always 5 as "BM_MAX_USAGE_COUNT" set to 5 in
buf_internals.h? I'm not sure about how much usagecount_min would add
either.
A usagecount is always an integer between 0 and 5, it's not
something unbounded. I think the 99th percentile would be much better than
average if strong outlier values could occur. But in this case, I feel like
an average value would be sufficiently useful as well.
usagecount_sum would actually be useful since average can be derived from
it. If you think that the sum of usagecounts has a meaning just by itself,
it makes sense to include it. Otherwise, wouldn't showing directly averaged
value be more useful?
Also I suggest changing the names of the columns in order to make them
consistent with the rest of the system. If you consider pg_stat_activity
and family [1] you will notice that the columns are named
(entity)_(property), e.g. backend_xid, backend_type, client_addr, etc. So
instead of used_buffers and unused_buffers the naming should be
buffers_used and buffers_unused.[1]: https://www.postgresql.org/docs/current/monitoring-stats.html
You're right. I will change the names accordingly. Thanks.
Regards,
Melih
Re: Summary function for pg_buffercache
Hi,
On 2022-09-09 17:36:45 +0300, Aleksander Alekseev wrote:
I suggest we focus on saving the memory first and then think about the
performance, if necessary.
Personally I think the locks part is at least as important - it's what makes
the production impact higher.
Greetings,
Andres Freund
Re: Summary function for pg_buffercache
Hi,
Also I suggest changing the names of the columns in order to make them
consistent with the rest of the system. If you consider pg_stat_activity
and family [1] you will notice that the columns are named
(entity)_(property), e.g. backend_xid, backend_type, client_addr, etc. So
instead of used_buffers and unused_buffers the naming should be
buffers_used and buffers_unused.[1]: https://www.postgresql.org/docs/current/monitoring-stats.html
I changed these names and updated the patch.
However I have somewhat mixed feelings about avg_usagecount. Generally
AVG() is a relatively useless methric for monitoring. What if the user
wants MIN(), MAX() or let's say a 99th percentile? I suggest splitting it
into usagecount_min, usagecount_max and usagecount_sum. AVG() can be
derived as usercount_sum / used_buffers.Won't be usagecount_max almost always 5 as "BM_MAX_USAGE_COUNT" set to 5
in buf_internals.h? I'm not sure about how much usagecount_min would add
either.
A usagecount is always an integer between 0 and 5, it's not
something unbounded. I think the 99th percentile would be much better than
average if strong outlier values could occur. But in this case, I feel like
an average value would be sufficiently useful as well.
usagecount_sum would actually be useful since average can be derived from
it. If you think that the sum of usagecounts has a meaning just by itself,
it makes sense to include it. Otherwise, wouldn't showing directly averaged
value be more useful?
Aleksander, do you still think the average usagecount is a bit useless? Or
does it make sense to you to keep it like this?
I suggest we focus on saving the memory first and then think about the
performance, if necessary.
Personally I think the locks part is at least as important - it's what
makes
the production impact higher.
I agree that it's important due to its high impact. I'm not sure how to
avoid any undefined behaviour without locks though.
Even with locks, performance is much better. But is it good enough for
production?
Thanks,
Melih
Attachments:
v6-0001-Added-pg_buffercache_summary-function.patchapplication/octet-stream; name=v6-0001-Added-pg_buffercache_summary-function.patchDownload
From 3968f832ffff21b00f9368ee5362fa5d8a7057ad Mon Sep 17 00:00:00 2001
From: Melih Mutlu <m.melihmutlu@gmail.com>
Date: Sat, 10 Sep 2022 02:08:24 +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 | 8 ++
.../pg_buffercache--1.3--1.4.sql | 13 +++
contrib/pg_buffercache/pg_buffercache.control | 2 +-
contrib/pg_buffercache/pg_buffercache_pages.c | 77 +++++++++++-
contrib/pg_buffercache/sql/pg_buffercache.sql | 5 +
doc/src/sgml/pgbuffercache.sgml | 110 +++++++++++++++++-
7 files changed, 210 insertions(+), 8 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..c5a9bc52a2 100644
--- a/contrib/pg_buffercache/expected/pg_buffercache.out
+++ b/contrib/pg_buffercache/expected/pg_buffercache.out
@@ -8,3 +8,11 @@ from pg_buffercache;
t
(1 row)
+select buffers_used + buffers_unused > 0,
+ buffers_dirty < buffers_used,
+ buffers_pinned < buffers_used
+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..77e250b430
--- /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 (buffers_used int4, buffers_unused int4, buffers_dirty int4,
+ buffers_pinned int4, usagecount_avg 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..d3c5ce527c 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,76 @@ 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 buffers_used = 0;
+ int32 buffers_unused = 0;
+ int32 buffers_dirty = 0;
+ int32 buffers_pinned = 0;
+ float usagecount_avg = 0;
+
+ /* Construct a tuple descriptor for the result rows. */
+ tupledesc = CreateTemplateTupleDesc(NUM_BUFFERCACHE_SUMMARY_ELEM);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 1, "buffers_used",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 2, "buffers_unused",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 3, "buffers_dirty",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 4, "buffers_pinned",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 5, "usagecount_avg",
+ FLOAT4OID, -1, 0);
+
+ BlessTupleDesc(tupledesc);
+
+ for (int i = 0; i < NBuffers; i++)
+ {
+ BufferDesc *bufHdr;
+ uint32 buf_state;
+
+ bufHdr = GetBufferDescriptor(i);
+
+ /* Lock each buffer header before inspecting. */
+ buf_state = LockBufHdr(bufHdr);
+
+ /* Invalid RelFileNumber means the buffer is unused */
+ if (RelFileNumberIsValid(BufTagGetRelNumber(&bufHdr->tag)))
+ {
+ buffers_used++;
+ usagecount_avg += BUF_STATE_GET_USAGECOUNT(buf_state);
+
+ if (buf_state & BM_DIRTY)
+ buffers_dirty++;
+ }
+ else
+ buffers_unused++;
+
+ if (BUF_STATE_GET_REFCOUNT(buf_state) > 0)
+ buffers_pinned++;
+
+ UnlockBufHdr(bufHdr, buf_state);
+ }
+ usagecount_avg /= buffers_used;
+
+ memset(nulls, 0, sizeof(nulls));
+ values[0] = Int32GetDatum(buffers_used);
+ values[1] = Int32GetDatum(buffers_unused);
+ values[2] = Int32GetDatum(buffers_dirty);
+ values[3] = Int32GetDatum(buffers_pinned);
+ values[4] = Float4GetDatum(usagecount_avg);
+
+ /* 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..2c06055b7b 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 buffers_used + buffers_unused > 0,
+ buffers_dirty < buffers_used,
+ buffers_pinned < buffers_used
+from pg_buffercache_summary();
diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml
index a06fd3e26d..7feabcd6bf 100644
--- a/doc/src/sgml/pgbuffercache.sgml
+++ b/doc/src/sgml/pgbuffercache.sgml
@@ -17,10 +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
- convenient use.
+ 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.
</para>
<para>
@@ -164,6 +173,92 @@
</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>buffers_used</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>buffers_unused</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>buffers_dirty</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>buffers_pinned</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>usagecount_avg</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>
+ Similar to <function>pg_buffercache_pages</function> function
+ <function>pg_buffercache_summary</function> doesn't take buffer manager
+ locks, thus the result is not consistent across all buffers. This is
+ intentional. The purpose of this function is to provide a general idea about
+ the state of shared buffers as fast as possible. Additionally,
+ <function>pg_buffercache_summary</function> allocates much less memory.
+ </para>
+ </sect2>
+
<sect2>
<title>Sample Output</title>
@@ -191,6 +286,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();
+ buffers_used | buffers_unused | buffers_dirty | buffers_pinned | usagecount_avg
+--------------+----------------+---------------+----------------+----------------
+ 248 | 2096904 | 39 | 0 | 3.141129
+(1 row)
</screen>
</sect2>
--
2.25.1
Re: Summary function for pg_buffercache
Hi Melih,
I changed these names and updated the patch.
Thanks for the updated patch!
Aleksander, do you still think the average usagecount is a bit useless? Or does it make sense to you to keep it like this?
I don't mind keeping the average.
I'm not sure how to avoid any undefined behaviour without locks though.
Even with locks, performance is much better. But is it good enough for production?
Potentially you could avoid taking locks by utilizing atomic
operations and lock-free algorithms. But these algorithms are
typically error-prone and not always produce a faster code than the
lock-based ones. I'm pretty confident this is out of scope of this
particular patch.
The patch v6 had several defacts:
* Trailing whitespaces (can be checked by applying the patch with `git am`)
* Wrong code formatting (can be fixed with pgindent)
* Several empty lines were removed which is not related to the
proposed change (can be seen with `git diff`)
* An unlikely division by zero if buffers_used = 0
* Missing part of the commit message added in v4
Here is a corrected patch v7. To me it seems to be in pretty good
shape, unless cfbot and/or other hackers will report any issues.
--
Best regards,
Aleksander Alekseev
Attachments:
v7-0001-Added-pg_buffercache_summary-function.patchapplication/octet-stream; name=v7-0001-Added-pg_buffercache_summary-function.patchDownload
From fc0c73533df08fda995907101aa474c551702bae Mon Sep 17 00:00:00 2001
From: Melih Mutlu <m.melihmutlu@gmail.com>
Date: Sat, 10 Sep 2022 02:08:24 +0300
Subject: [PATCH v7] Added pg_buffercache_summary function
Adds pg_buffercache_summary() function into pg_buffercache extension for
retrieving summary information about overall shared_buffer usage.
Author: Melih Mutlu <m.melihmutlu@gmail.com>
Reviewed-by: Aleksander Alekseev <aleksander@timescale.com>
Discussion: https://postgr.es/m/CAGPVpCQAXYo54Q%3D8gqBsS%3Du0uk9qhnnq4%2B710BtUhUisX1XGEg%40mail.gmail.com
---
contrib/pg_buffercache/Makefile | 3 +-
.../expected/pg_buffercache.out | 8 ++
.../pg_buffercache--1.3--1.4.sql | 13 +++
contrib/pg_buffercache/pg_buffercache.control | 2 +-
contrib/pg_buffercache/pg_buffercache_pages.c | 77 ++++++++++++
contrib/pg_buffercache/sql/pg_buffercache.sql | 5 +
doc/src/sgml/pgbuffercache.sgml | 110 +++++++++++++++++-
7 files changed, 212 insertions(+), 6 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..c5a9bc52a2 100644
--- a/contrib/pg_buffercache/expected/pg_buffercache.out
+++ b/contrib/pg_buffercache/expected/pg_buffercache.out
@@ -8,3 +8,11 @@ from pg_buffercache;
t
(1 row)
+select buffers_used + buffers_unused > 0,
+ buffers_dirty < buffers_used,
+ buffers_pinned < buffers_used
+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..77e250b430
--- /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 (buffers_used int4, buffers_unused int4, buffers_dirty int4,
+ buffers_pinned int4, usagecount_avg 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..3063e7df1a 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;
@@ -59,6 +60,7 @@ typedef struct
* 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 +239,78 @@ 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 buffers_used = 0;
+ int32 buffers_unused = 0;
+ int32 buffers_dirty = 0;
+ int32 buffers_pinned = 0;
+ float usagecount_avg = 0;
+
+ /* Construct a tuple descriptor for the result rows. */
+ tupledesc = CreateTemplateTupleDesc(NUM_BUFFERCACHE_SUMMARY_ELEM);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 1, "buffers_used",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 2, "buffers_unused",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 3, "buffers_dirty",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 4, "buffers_pinned",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 5, "usagecount_avg",
+ FLOAT4OID, -1, 0);
+
+ BlessTupleDesc(tupledesc);
+
+ for (int i = 0; i < NBuffers; i++)
+ {
+ BufferDesc *bufHdr;
+ uint32 buf_state;
+
+ bufHdr = GetBufferDescriptor(i);
+
+ /* Lock each buffer header before inspecting. */
+ buf_state = LockBufHdr(bufHdr);
+
+ /* Invalid RelFileNumber means the buffer is unused */
+ if (RelFileNumberIsValid(BufTagGetRelNumber(&bufHdr->tag)))
+ {
+ buffers_used++;
+ usagecount_avg += BUF_STATE_GET_USAGECOUNT(buf_state);
+
+ if (buf_state & BM_DIRTY)
+ buffers_dirty++;
+ }
+ else
+ buffers_unused++;
+
+ if (BUF_STATE_GET_REFCOUNT(buf_state) > 0)
+ buffers_pinned++;
+
+ UnlockBufHdr(bufHdr, buf_state);
+ }
+
+ if(buffers_used != 0)
+ usagecount_avg = usagecount_avg / buffers_used;
+
+ memset(nulls, 0, sizeof(nulls));
+ values[0] = Int32GetDatum(buffers_used);
+ values[1] = Int32GetDatum(buffers_unused);
+ values[2] = Int32GetDatum(buffers_dirty);
+ values[3] = Int32GetDatum(buffers_pinned);
+ values[4] = Float4GetDatum(usagecount_avg);
+
+ /* 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..2c06055b7b 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 buffers_used + buffers_unused > 0,
+ buffers_dirty < buffers_used,
+ buffers_pinned < buffers_used
+from pg_buffercache_summary();
diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml
index a06fd3e26d..5496a07e57 100644
--- a/doc/src/sgml/pgbuffercache.sgml
+++ b/doc/src/sgml/pgbuffercache.sgml
@@ -17,10 +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
- convenient use.
+ 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.
</para>
<para>
@@ -164,6 +173,92 @@
</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>buffers_used</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>buffers_unused</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>buffers_dirty</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>buffers_pinned</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>usagecount_avg</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>
+ Similar to <function>pg_buffercache_pages</function> function
+ <function>pg_buffercache_summary</function> doesn't take buffer manager
+ locks, thus the result is not consistent across all buffers. This is
+ intentional. The purpose of this function is to provide a general idea about
+ the state of shared buffers as fast as possible. Additionally,
+ <function>pg_buffercache_summary</function> allocates much less memory.
+ </para>
+ </sect2>
+
<sect2>
<title>Sample Output</title>
@@ -191,6 +286,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();
+ buffers_used | buffers_unused | buffers_dirty | buffers_pinned | usagecount_avg
+--------------+----------------+---------------+----------------+----------------
+ 248 | 2096904 | 39 | 0 | 3.141129
+(1 row)
</screen>
</sect2>
--
2.37.2
Re: Summary function for pg_buffercache
Hi hackers,
Here is a corrected patch v7. To me it seems to be in pretty good
shape, unless cfbot and/or other hackers will report any issues.
There was a missing empty line in pg_buffercache.out which made the
tests fail. Here is a corrected v8 patch.
--
Best regards,
Aleksander Alekseev
Attachments:
v8-0001-Added-pg_buffercache_summary-function.patchapplication/octet-stream; name=v8-0001-Added-pg_buffercache_summary-function.patchDownload
From c05cc758cdbe37f7d299930f9fd4ee3f8b8bd0cc Mon Sep 17 00:00:00 2001
From: Melih Mutlu <m.melihmutlu@gmail.com>
Date: Sat, 10 Sep 2022 02:08:24 +0300
Subject: [PATCH v8] Added pg_buffercache_summary function
Adds pg_buffercache_summary() function into pg_buffercache extension for
retrieving summary information about overall shared_buffer usage.
Author: Melih Mutlu <m.melihmutlu@gmail.com>
Reviewed-by: Aleksander Alekseev <aleksander@timescale.com>
Discussion: https://postgr.es/m/CAGPVpCQAXYo54Q%3D8gqBsS%3Du0uk9qhnnq4%2B710BtUhUisX1XGEg%40mail.gmail.com
---
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 | 77 ++++++++++++
contrib/pg_buffercache/sql/pg_buffercache.sql | 5 +
doc/src/sgml/pgbuffercache.sgml | 110 +++++++++++++++++-
7 files changed, 213 insertions(+), 6 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..295e71e2e2 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 buffers_used + buffers_unused > 0,
+ buffers_dirty < buffers_used,
+ buffers_pinned < buffers_used
+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..77e250b430
--- /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 (buffers_used int4, buffers_unused int4, buffers_dirty int4,
+ buffers_pinned int4, usagecount_avg 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..3063e7df1a 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;
@@ -59,6 +60,7 @@ typedef struct
* 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 +239,78 @@ 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 buffers_used = 0;
+ int32 buffers_unused = 0;
+ int32 buffers_dirty = 0;
+ int32 buffers_pinned = 0;
+ float usagecount_avg = 0;
+
+ /* Construct a tuple descriptor for the result rows. */
+ tupledesc = CreateTemplateTupleDesc(NUM_BUFFERCACHE_SUMMARY_ELEM);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 1, "buffers_used",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 2, "buffers_unused",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 3, "buffers_dirty",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 4, "buffers_pinned",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 5, "usagecount_avg",
+ FLOAT4OID, -1, 0);
+
+ BlessTupleDesc(tupledesc);
+
+ for (int i = 0; i < NBuffers; i++)
+ {
+ BufferDesc *bufHdr;
+ uint32 buf_state;
+
+ bufHdr = GetBufferDescriptor(i);
+
+ /* Lock each buffer header before inspecting. */
+ buf_state = LockBufHdr(bufHdr);
+
+ /* Invalid RelFileNumber means the buffer is unused */
+ if (RelFileNumberIsValid(BufTagGetRelNumber(&bufHdr->tag)))
+ {
+ buffers_used++;
+ usagecount_avg += BUF_STATE_GET_USAGECOUNT(buf_state);
+
+ if (buf_state & BM_DIRTY)
+ buffers_dirty++;
+ }
+ else
+ buffers_unused++;
+
+ if (BUF_STATE_GET_REFCOUNT(buf_state) > 0)
+ buffers_pinned++;
+
+ UnlockBufHdr(bufHdr, buf_state);
+ }
+
+ if(buffers_used != 0)
+ usagecount_avg = usagecount_avg / buffers_used;
+
+ memset(nulls, 0, sizeof(nulls));
+ values[0] = Int32GetDatum(buffers_used);
+ values[1] = Int32GetDatum(buffers_unused);
+ values[2] = Int32GetDatum(buffers_dirty);
+ values[3] = Int32GetDatum(buffers_pinned);
+ values[4] = Float4GetDatum(usagecount_avg);
+
+ /* 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..2c06055b7b 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 buffers_used + buffers_unused > 0,
+ buffers_dirty < buffers_used,
+ buffers_pinned < buffers_used
+from pg_buffercache_summary();
diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml
index a06fd3e26d..5496a07e57 100644
--- a/doc/src/sgml/pgbuffercache.sgml
+++ b/doc/src/sgml/pgbuffercache.sgml
@@ -17,10 +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
- convenient use.
+ 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.
</para>
<para>
@@ -164,6 +173,92 @@
</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>buffers_used</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>buffers_unused</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>buffers_dirty</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>buffers_pinned</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>usagecount_avg</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>
+ Similar to <function>pg_buffercache_pages</function> function
+ <function>pg_buffercache_summary</function> doesn't take buffer manager
+ locks, thus the result is not consistent across all buffers. This is
+ intentional. The purpose of this function is to provide a general idea about
+ the state of shared buffers as fast as possible. Additionally,
+ <function>pg_buffercache_summary</function> allocates much less memory.
+ </para>
+ </sect2>
+
<sect2>
<title>Sample Output</title>
@@ -191,6 +286,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();
+ buffers_used | buffers_unused | buffers_dirty | buffers_pinned | usagecount_avg
+--------------+----------------+---------------+----------------+----------------
+ 248 | 2096904 | 39 | 0 | 3.141129
+(1 row)
</screen>
</sect2>
--
2.37.2
Re: Summary function for pg_buffercache
Aleksander Alekseev <aleksander@timescale.com>, 20 Eyl 2022 Sal, 13:57
tarihinde şunu yazdı:
There was a missing empty line in pg_buffercache.out which made the
tests fail. Here is a corrected v8 patch.
I was just sending a corrected patch without the missing line.
Thanks a lot for all these reviews and the corrected patch.
Best,
Melih
Re: Summary function for pg_buffercache
Hi,
Seems like cfbot tests are passing now:
https://cirrus-ci.com/build/4727923671302144
Best,
Melih
Melih Mutlu <m.melihmutlu@gmail.com>, 20 Eyl 2022 Sal, 14:00 tarihinde şunu
yazdı:
Show quoted text
Aleksander Alekseev <aleksander@timescale.com>, 20 Eyl 2022 Sal, 13:57
tarihinde şunu yazdı:There was a missing empty line in pg_buffercache.out which made the
tests fail. Here is a corrected v8 patch.I was just sending a corrected patch without the missing line.
Thanks a lot for all these reviews and the corrected patch.
Best,
Melih
Re: Summary function for pg_buffercache
Hi,
Correct me if I’m wrong.
The doc says we don’t take lock during pg_buffercache_summary, but I see locks in the v8 patch, Isn’t it?
```
Similar to <function>pg_buffercache_pages</function> function
<function>pg_buffercache_summary</function> doesn't take buffer manager
locks, thus the result is not consistent across all buffers. This is
intentional. The purpose of this function is to provide a general idea about
the state of shared buffers as fast as possible. Additionally,
<function>pg_buffercache_summary</function> allocates much less memory.
```
Regards,
Zhang Mingli
Show quoted text
On Sep 20, 2022, 20:10 +0800, Melih Mutlu <m.melihmutlu@gmail.com>, wrote:
Hi,
Seems like cfbot tests are passing now:
https://cirrus-ci.com/build/4727923671302144Best,
MelihMelih Mutlu <m.melihmutlu@gmail.com>, 20 Eyl 2022 Sal, 14:00 tarihinde şunu yazdı:
Aleksander Alekseev <aleksander@timescale.com>, 20 Eyl 2022 Sal, 13:57 tarihinde şunu yazdı:
There was a missing empty line in pg_buffercache.out which made the
tests fail. Here is a corrected v8 patch.I was just sending a corrected patch without the missing line.
Thanks a lot for all these reviews and the corrected patch.
Best,
Melih
Re: Summary function for pg_buffercache
Hi Zhang,
The doc says we don’t take lock during pg_buffercache_summary, but I see locks in the v8 patch, Isn’t it?
```
Similar to <function>pg_buffercache_pages</function> function
<function>pg_buffercache_summary</function> doesn't take buffer manager
locks [...]
```
Correct, the procedure doesn't take the locks of the buffer manager.
It does take the locks of every individual buffer.
I agree that the text is somewhat confusing, but it is consistent with
the current description of pg_buffercache [1]https://www.postgresql.org/docs/current/pgbuffercache.html. I think this is a
problem worth addressing but it also seems to be out of scope of the
proposed patch.
[1]: https://www.postgresql.org/docs/current/pgbuffercache.html
--
Best regards,
Aleksander Alekseev
Re: Summary function for pg_buffercache
Hi,
Regards,
Zhang Mingli
On Sep 20, 2022, 20:43 +0800, Aleksander Alekseev <aleksander@timescale.com>, wrote:
Correct, the procedure doesn't take the locks of the buffer manager.
It does take the locks of every individual buffer.
Ah, now I get it, thanks.
Re: Summary function for pg_buffercache
Hi Zhang,
Those are two different locks.
The locks that are taken in the patch are for buffer headers. This locks
only the current buffer and makes that particular buffer's info consistent
within itself.
However, the lock mentioned in the doc is for buffer manager which would
prevent changes on any buffer if it's held.
pg_buffercache_summary (and pg_buffercache_pages) does not hold buffer
manager lock. Therefore, consistency across all buffers is not guaranteed.
For pg_buffercache_pages, self-consistent buffer information is useful
since it shows each buffer separately.
For pg_buffercache_summary, even self-consistency may not matter much since
results are aggregated and we can't see individual buffer information.
Consistency across all buffers is also not a concern since its purpose is
to give an overall idea about the state of buffers.
I see that these two different locks in the same context can be confusing.
I hope it is a bit more clear now.
Best,
Melih
Show quoted text
Re: Summary function for pg_buffercache
Hi,
On Sep 20, 2022, 20:49 +0800, Melih Mutlu <m.melihmutlu@gmail.com>, wrote:
Hi Zhang,
Those are two different locks.
The locks that are taken in the patch are for buffer headers. This locks only the current buffer and makes that particular buffer's info consistent within itself.However, the lock mentioned in the doc is for buffer manager which would prevent changes on any buffer if it's held.
pg_buffercache_summary (and pg_buffercache_pages) does not hold buffer manager lock. Therefore, consistency across all buffers is not guaranteed.For pg_buffercache_pages, self-consistent buffer information is useful since it shows each buffer separately.
For pg_buffercache_summary, even self-consistency may not matter much since results are aggregated and we can't see individual buffer information.
Consistency across all buffers is also not a concern since its purpose is to give an overall idea about the state of buffers.I see that these two different locks in the same context can be confusing. I hope it is a bit more clear now.
Best,
Melih
Thanks for your explanation, LGTM.
Re: Summary function for pg_buffercache
Hi,
On 2022-09-20 12:45:24 +0300, Aleksander Alekseev wrote:
I'm not sure how to avoid any undefined behaviour without locks though.
Even with locks, performance is much better. But is it good enough for production?Potentially you could avoid taking locks by utilizing atomic
operations and lock-free algorithms. But these algorithms are
typically error-prone and not always produce a faster code than the
lock-based ones. I'm pretty confident this is out of scope of this
particular patch.
Why would you need lockfree operations? All you need to do is to read
BufferDesc->state into a local variable and then make decisions based on that?
+ for (int i = 0; i < NBuffers; i++) + { + BufferDesc *bufHdr; + uint32 buf_state; + + bufHdr = GetBufferDescriptor(i); + + /* Lock each buffer header before inspecting. */ + buf_state = LockBufHdr(bufHdr); + + /* Invalid RelFileNumber means the buffer is unused */ + if (RelFileNumberIsValid(BufTagGetRelNumber(&bufHdr->tag))) + { + buffers_used++; + usagecount_avg += BUF_STATE_GET_USAGECOUNT(buf_state); + + if (buf_state & BM_DIRTY) + buffers_dirty++; + } + else + buffers_unused++; + + if (BUF_STATE_GET_REFCOUNT(buf_state) > 0) + buffers_pinned++; + + UnlockBufHdr(bufHdr, buf_state); + }
I.e. instead of locking the buffer header as done above, this could just do
something along these lines:
BufferDesc *bufHdr;
uint32 buf_state;
bufHdr = GetBufferDescriptor(i);
buf_state = pg_atomic_read_u32(&bufHdr->state);
if (buf_state & BM_VALID)
{
buffers_used++;
usagecount_avg += BUF_STATE_GET_USAGECOUNT(buf_state);
if (buf_state & BM_DIRTY)
buffers_dirty++;
}
else
buffers_unused++;
if (BUF_STATE_GET_REFCOUNT(buf_state) > 0)
buffers_pinned++;
Without a memory barrier you can get very slightly "out-of-date" values of the
state, but that's fine in this case.
Greetings,
Andres Freund
Re: Summary function for pg_buffercache
Hi Andres,
All you need to do is to read BufferDesc->state into a local variable and then make decisions based on that
You are right, thanks.
Here is the corrected patch.
--
Best regards,
Aleksander Alekseev
Attachments:
v9-0001-Added-pg_buffercache_summary-function.patchapplication/octet-stream; name=v9-0001-Added-pg_buffercache_summary-function.patchDownload
From 775e2034cbff0ecded51862dad365ed068ded50c Mon Sep 17 00:00:00 2001
From: Melih Mutlu <m.melihmutlu@gmail.com>
Date: Sat, 10 Sep 2022 02:08:24 +0300
Subject: [PATCH v9] Added pg_buffercache_summary function
Adds pg_buffercache_summary() function into pg_buffercache extension for
retrieving summary information about overall shared_buffer usage.
Melih Mutlu, reviewed by Andres Freund, Aleksander Alekseev
Discussion: https://postgr.es/m/CAGPVpCQAXYo54Q%3D8gqBsS%3Du0uk9qhnnq4%2B710BtUhUisX1XGEg%40mail.gmail.com
---
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 | 72 ++++++++++++
contrib/pg_buffercache/sql/pg_buffercache.sql | 5 +
doc/src/sgml/pgbuffercache.sgml | 110 +++++++++++++++++-
7 files changed, 208 insertions(+), 6 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..295e71e2e2 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 buffers_used + buffers_unused > 0,
+ buffers_dirty < buffers_used,
+ buffers_pinned < buffers_used
+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..77e250b430
--- /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 (buffers_used int4, buffers_unused int4, buffers_dirty int4,
+ buffers_pinned int4, usagecount_avg 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..7c07c2893f 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;
@@ -59,6 +60,7 @@ typedef struct
* 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 +239,73 @@ 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 buffers_used = 0;
+ int32 buffers_unused = 0;
+ int32 buffers_dirty = 0;
+ int32 buffers_pinned = 0;
+ float usagecount_avg = 0;
+
+ /* Construct a tuple descriptor for the result rows. */
+ tupledesc = CreateTemplateTupleDesc(NUM_BUFFERCACHE_SUMMARY_ELEM);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 1, "buffers_used",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 2, "buffers_unused",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 3, "buffers_dirty",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 4, "buffers_pinned",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 5, "usagecount_avg",
+ FLOAT4OID, -1, 0);
+
+ BlessTupleDesc(tupledesc);
+
+ for (int i = 0; i < NBuffers; i++)
+ {
+ BufferDesc *bufHdr;
+ uint32 buf_state;
+
+ bufHdr = GetBufferDescriptor(i);
+ buf_state = pg_atomic_read_u32(&bufHdr->state);
+
+ if (buf_state & BM_VALID)
+ {
+ buffers_used++;
+ usagecount_avg += BUF_STATE_GET_USAGECOUNT(buf_state);
+
+ if (buf_state & BM_DIRTY)
+ buffers_dirty++;
+ }
+ else
+ buffers_unused++;
+
+ if (BUF_STATE_GET_REFCOUNT(buf_state) > 0)
+ buffers_pinned++;
+ }
+
+ if (buffers_used != 0)
+ usagecount_avg = usagecount_avg / buffers_used;
+
+ memset(nulls, 0, sizeof(nulls));
+ values[0] = Int32GetDatum(buffers_used);
+ values[1] = Int32GetDatum(buffers_unused);
+ values[2] = Int32GetDatum(buffers_dirty);
+ values[3] = Int32GetDatum(buffers_pinned);
+ values[4] = Float4GetDatum(usagecount_avg);
+
+ /* 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..2c06055b7b 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 buffers_used + buffers_unused > 0,
+ buffers_dirty < buffers_used,
+ buffers_pinned < buffers_used
+from pg_buffercache_summary();
diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml
index a06fd3e26d..5496a07e57 100644
--- a/doc/src/sgml/pgbuffercache.sgml
+++ b/doc/src/sgml/pgbuffercache.sgml
@@ -17,10 +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
- convenient use.
+ 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.
</para>
<para>
@@ -164,6 +173,92 @@
</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>buffers_used</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>buffers_unused</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>buffers_dirty</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>buffers_pinned</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>usagecount_avg</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>
+ Similar to <function>pg_buffercache_pages</function> function
+ <function>pg_buffercache_summary</function> doesn't take buffer manager
+ locks, thus the result is not consistent across all buffers. This is
+ intentional. The purpose of this function is to provide a general idea about
+ the state of shared buffers as fast as possible. Additionally,
+ <function>pg_buffercache_summary</function> allocates much less memory.
+ </para>
+ </sect2>
+
<sect2>
<title>Sample Output</title>
@@ -191,6 +286,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();
+ buffers_used | buffers_unused | buffers_dirty | buffers_pinned | usagecount_avg
+--------------+----------------+---------------+----------------+----------------
+ 248 | 2096904 | 39 | 0 | 3.141129
+(1 row)
</screen>
</sect2>
--
2.37.2
Re: Summary function for pg_buffercache
Hi,
Since header locks are removed again, I put some doc changes and comments
back.
Thanks,
Melih
Attachments:
v10-0001-Added-pg_buffercache_summary-function.patchapplication/octet-stream; name=v10-0001-Added-pg_buffercache_summary-function.patchDownload
From fc9de3092acd869681af43e6235e19d2e00a2242 Mon Sep 17 00:00:00 2001
From: Melih Mutlu <m.melihmutlu@gmail.com>
Date: Sat, 10 Sep 2022 02:08:24 +0300
Subject: [PATCH v10] Added pg_buffercache_summary function
Adds pg_buffercache_summary() function into pg_buffercache extension for
retrieving summary information about overall shared_buffer usage.
Author: Melih Mutlu <m.melihmutlu@gmail.com>
Reviewed-by: Aleksander Alekseev <aleksander@timescale.com>, Andres Freund <andres@anarazel.de>
Discussion: https://postgr.es/m/CAGPVpCQAXYo54Q%3D8gqBsS%3Du0uk9qhnnq4%2B710BtUhUisX1XGEg%40mail.gmail.com
---
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 | 78 ++++++++++++
contrib/pg_buffercache/sql/pg_buffercache.sql | 5 +
doc/src/sgml/pgbuffercache.sgml | 111 +++++++++++++++++-
7 files changed, 215 insertions(+), 6 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..295e71e2e2 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 buffers_used + buffers_unused > 0,
+ buffers_dirty < buffers_used,
+ buffers_pinned < buffers_used
+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..77e250b430
--- /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 (buffers_used int4, buffers_unused int4, buffers_dirty int4,
+ buffers_pinned int4, usagecount_avg 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..b1c0fa96ad 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;
@@ -59,6 +60,7 @@ typedef struct
* 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 +239,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 buffers_used = 0;
+ int32 buffers_unused = 0;
+ int32 buffers_dirty = 0;
+ int32 buffers_pinned = 0;
+ float usagecount_avg = 0;
+
+ /* Construct a tuple descriptor for the result rows. */
+ tupledesc = CreateTemplateTupleDesc(NUM_BUFFERCACHE_SUMMARY_ELEM);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 1, "buffers_used",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 2, "buffers_unused",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 3, "buffers_dirty",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 4, "buffers_pinned",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 5, "usagecount_avg",
+ 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);
+
+ if (buf_state & BM_VALID)
+ {
+ buffers_used++;
+ usagecount_avg += BUF_STATE_GET_USAGECOUNT(buf_state);
+
+ if (buf_state & BM_DIRTY)
+ buffers_dirty++;
+ }
+ else
+ buffers_unused++;
+
+ if (BUF_STATE_GET_REFCOUNT(buf_state) > 0)
+ buffers_pinned++;
+ }
+
+ if (buffers_used != 0)
+ usagecount_avg = usagecount_avg / buffers_used;
+
+ memset(nulls, 0, sizeof(nulls));
+ values[0] = Int32GetDatum(buffers_used);
+ values[1] = Int32GetDatum(buffers_unused);
+ values[2] = Int32GetDatum(buffers_dirty);
+ values[3] = Int32GetDatum(buffers_pinned);
+ values[4] = Float4GetDatum(usagecount_avg);
+
+ /* 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..2c06055b7b 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 buffers_used + buffers_unused > 0,
+ buffers_dirty < buffers_used,
+ buffers_pinned < buffers_used
+from pg_buffercache_summary();
diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml
index a06fd3e26d..bf56346808 100644
--- a/doc/src/sgml/pgbuffercache.sgml
+++ b/doc/src/sgml/pgbuffercache.sgml
@@ -17,10 +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
- convenient use.
+ 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.
</para>
<para>
@@ -164,6 +173,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>buffers_used</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>buffers_unused</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>buffers_dirty</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>buffers_pinned</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>usagecount_avg</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>
+ <function>pg_buffercache_summary</function> doesn't take buffer manager
+ locks. Unlike <function>pg_buffercache_pages</function> function
+ <function>pg_buffercache_summary</function> doesn't take buffer headers locks
+ either, thus the result is not consistent. This is intentional. The purpose
+ of this function is to provide a general idea about the state of shared
+ buffers as fast as possible. Additionally, <function>pg_buffercache_summary</function>
+ allocates much less memory.
+ </para>
+ </sect2>
+
<sect2>
<title>Sample Output</title>
@@ -191,6 +287,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();
+ buffers_used | buffers_unused | buffers_dirty | buffers_pinned | usagecount_avg
+--------------+----------------+---------------+----------------+----------------
+ 248 | 2096904 | 39 | 0 | 3.141129
+(1 row)
</screen>
</sect2>
--
2.25.1
Re: Summary function for pg_buffercache
Hi,
On 2022-09-22 18:22:44 +0300, Melih Mutlu wrote:
Since header locks are removed again, I put some doc changes and comments
back.
Due to the merge of the meson build system, this needs to adjust meson.build
as well.
--- 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 buffers_used + buffers_unused > 0, + buffers_dirty < buffers_used, + buffers_pinned < buffers_used
Doesn't these have to be "<=" instead of "<"?
+ 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. + */
I think the "consistent snapshot" bit is misleading - even taking buffer
header locks wouldn't give you that.
+ if (buffers_used != 0) + usagecount_avg = usagecount_avg / buffers_used;
Perhaps the average should be NULL in the buffers_used == 0 case?
+ <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. </para>
I think these sentences are missing a "The " at the start?
"shared buffer caches" isn't right - I think I'd just drop the "caches".
+ <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> + <function>pg_buffercache_summary</function> doesn't take buffer manager + locks. Unlike <function>pg_buffercache_pages</function> function + <function>pg_buffercache_summary</function> doesn't take buffer headers locks + either, thus the result is not consistent. This is intentional. The purpose + of this function is to provide a general idea about the state of shared + buffers as fast as possible. Additionally, <function>pg_buffercache_summary</function> + allocates much less memory. + </para> + </sect2>
I don't think this mentioning of buffer header locks is useful for users - nor
is it I think correct. Acquiring the buffer header locks wouldn't add *any*
additional consistency.
Greetings,
Andres Freund
Re: Summary function for pg_buffercache
Hi Andres,
Adjusted the patch so that it will work with meson now.
Also addressed your other reviews as well.
I hope explanations in comments/docs are better now.
Best,
Melih
Attachments:
v11-0001-Added-pg_buffercache_summary-function.patchapplication/octet-stream; name=v11-0001-Added-pg_buffercache_summary-function.patchDownload
From 3511dc3f73d4c43c58309d62a07777e5eb1f9b26 Mon Sep 17 00:00:00 2001
From: Melih Mutlu <m.melihmutlu@gmail.com>
Date: Sat, 10 Sep 2022 02:08:24 +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.
Melih Mutlu, reviewed by Andres Freund, Aleksander Alekseev
Discussion: https://postgr.es/m/CAGPVpCQAXYo54Q%3D8gqBsS%3Du0uk9qhnnq4%2B710BtUhUisX1XGEg%40mail.gmail.com
---
contrib/pg_buffercache/Makefile | 3 +-
.../expected/pg_buffercache.out | 9 ++
contrib/pg_buffercache/meson.build | 1 +
.../pg_buffercache--1.3--1.4.sql | 13 ++
contrib/pg_buffercache/pg_buffercache.control | 2 +-
contrib/pg_buffercache/pg_buffercache_pages.c | 87 ++++++++++++++
contrib/pg_buffercache/sql/pg_buffercache.sql | 5 +
doc/src/sgml/pgbuffercache.sgml | 111 +++++++++++++++++-
8 files changed, 224 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..6798eb7432 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 buffers_used + buffers_unused > 0,
+ buffers_dirty <= buffers_used,
+ buffers_pinned <= buffers_used
+from pg_buffercache_summary();
+ ?column? | ?column? | ?column?
+----------+----------+----------
+ t | t | t
+(1 row)
+
diff --git a/contrib/pg_buffercache/meson.build b/contrib/pg_buffercache/meson.build
index 2c69eae3ea..9f34754d6c 100644
--- a/contrib/pg_buffercache/meson.build
+++ b/contrib/pg_buffercache/meson.build
@@ -11,6 +11,7 @@ install_data(
'pg_buffercache--1.1--1.2.sql',
'pg_buffercache--1.2--1.3.sql',
'pg_buffercache--1.2.sql',
+ 'pg_buffercache--1.3--1.4.sql',
'pg_buffercache.control',
kwargs: contrib_data_args,
)
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..77e250b430
--- /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 (buffers_used int4, buffers_unused int4, buffers_dirty int4,
+ buffers_pinned int4, usagecount_avg 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..3541ea778f 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;
@@ -59,6 +60,7 @@ typedef struct
* 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 +239,88 @@ 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 buffers_used = 0;
+ int32 buffers_unused = 0;
+ int32 buffers_dirty = 0;
+ int32 buffers_pinned = 0;
+ float usagecount_avg = 0;
+
+ /* Construct a tuple descriptor for the result rows. */
+ tupledesc = CreateTemplateTupleDesc(NUM_BUFFERCACHE_SUMMARY_ELEM);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 1, "buffers_used",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 2, "buffers_unused",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 3, "buffers_dirty",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 4, "buffers_pinned",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 5, "usagecount_avg",
+ 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, 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);
+
+ if (buf_state & BM_VALID)
+ {
+ buffers_used++;
+ usagecount_avg += BUF_STATE_GET_USAGECOUNT(buf_state);
+
+ if (buf_state & BM_DIRTY)
+ buffers_dirty++;
+ }
+ else
+ buffers_unused++;
+
+ if (BUF_STATE_GET_REFCOUNT(buf_state) > 0)
+ buffers_pinned++;
+ }
+
+ if (buffers_used != 0)
+ usagecount_avg = usagecount_avg / buffers_used;
+
+ memset(nulls, 0, sizeof(nulls));
+ values[0] = Int32GetDatum(buffers_used);
+ values[1] = Int32GetDatum(buffers_unused);
+ values[2] = Int32GetDatum(buffers_dirty);
+ values[3] = Int32GetDatum(buffers_pinned);
+
+ if (buffers_used != 0)
+ {
+ usagecount_avg = usagecount_avg / buffers_used;
+ values[4] = Float4GetDatum(usagecount_avg);
+ }
+ else
+ {
+ nulls[4] = true;
+ }
+
+ /* 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..897dfbc924 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 buffers_used + buffers_unused > 0,
+ buffers_dirty <= buffers_used,
+ buffers_pinned <= buffers_used
+from pg_buffercache_summary();
diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml
index a06fd3e26d..6c38ee5d5e 100644
--- a/doc/src/sgml/pgbuffercache.sgml
+++ b/doc/src/sgml/pgbuffercache.sgml
@@ -9,7 +9,7 @@
<para>
The <filename>pg_buffercache</filename> module provides a means for
- examining what's happening in the shared buffer cache in real time.
+ examining what's happening in the shared buffer in real time.
</para>
<indexterm>
@@ -17,10 +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
- convenient use.
+ The module provides C functions <function>pg_buffercache_pages</function>
+ and <function>pg_buffercache_summary</function>.
+ </para>
+
+ <para>
+ The <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>
+ The <function>pg_buffercache_summary</function> function returns a table with a single row
+ that contains summarized and aggregated information about shared buffer.
</para>
<para>
@@ -164,6 +173,91 @@
</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>buffers_used</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>buffers_unused</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>buffers_dirty</structfield> <type>int4</type>
+ </para>
+ <para>
+ Number of dirty shared buffers
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>buffers_pinned</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>usagecount_avg</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>
+ The <function>pg_buffercache_summary</function> doesn't provide a result
+ that is consistent across all buffers. This is intentional. The purpose
+ of this function is to provide a general idea about the state of shared
+ buffers as fast as possible. Additionally, <function>pg_buffercache_summary</function>
+ allocates much less memory.
+ </para>
+ </sect2>
+
<sect2>
<title>Sample Output</title>
@@ -191,6 +285,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();
+ buffers_used | buffers_unused | buffers_dirty | buffers_pinned | usagecount_avg
+--------------+----------------+---------------+----------------+----------------
+ 248 | 2096904 | 39 | 0 | 3.141129
+(1 row)
</screen>
</sect2>
--
2.25.1
Re: Summary function for pg_buffercache
Hi all,
The patch needed a rebase due to recent changes on pg_buffercache.
You can find the updated version attached.
Best,
Melih
Attachments:
v12-0001-Added-pg_buffercache_summary-function.patchapplication/octet-stream; name=v12-0001-Added-pg_buffercache_summary-function.patchDownload
From babcb59ce4f8a10c1e34692a9c04d42636c509c6 Mon Sep 17 00:00:00 2001
From: Melih Mutlu <m.melihmutlu@gmail.com>
Date: Sat, 10 Sep 2022 02:08:24 +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.
Melih Mutlu, reviewed by Andres Freund, Aleksander Alekseev
Discussion: https://postgr.es/m/CAGPVpCQAXYo54Q%3D8gqBsS%3Du0uk9qhnnq4%2B710BtUhUisX1XGEg%40mail.gmail.com
---
contrib/pg_buffercache/Makefile | 2 +-
.../expected/pg_buffercache.out | 9 ++
contrib/pg_buffercache/meson.build | 1 +
.../pg_buffercache--1.4--1.5.sql | 13 ++
contrib/pg_buffercache/pg_buffercache.control | 2 +-
contrib/pg_buffercache/pg_buffercache_pages.c | 87 ++++++++++++++
contrib/pg_buffercache/sql/pg_buffercache.sql | 5 +
doc/src/sgml/pgbuffercache.sgml | 111 +++++++++++++++++-
8 files changed, 223 insertions(+), 7 deletions(-)
create mode 100644 contrib/pg_buffercache/pg_buffercache--1.4--1.5.sql
diff --git a/contrib/pg_buffercache/Makefile b/contrib/pg_buffercache/Makefile
index 4d88eba5e3..022c0acbe7 100644
--- a/contrib/pg_buffercache/Makefile
+++ b/contrib/pg_buffercache/Makefile
@@ -7,7 +7,7 @@ OBJS = \
EXTENSION = pg_buffercache
DATA = pg_buffercache--1.0--1.1.sql pg_buffercache--1.1--1.2.sql pg_buffercache--1.2.sql \
- pg_buffercache--1.2--1.3.sql pg_buffercache--1.3--1.4.sql
+ pg_buffercache--1.2--1.3.sql pg_buffercache--1.3--1.4.sql pg_buffercache--1.4--1.5.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..6798eb7432 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 buffers_used + buffers_unused > 0,
+ buffers_dirty <= buffers_used,
+ buffers_pinned <= buffers_used
+from pg_buffercache_summary();
+ ?column? | ?column? | ?column?
+----------+----------+----------
+ t | t | t
+(1 row)
+
diff --git a/contrib/pg_buffercache/meson.build b/contrib/pg_buffercache/meson.build
index 9f34754d6c..fd6b6a36d3 100644
--- a/contrib/pg_buffercache/meson.build
+++ b/contrib/pg_buffercache/meson.build
@@ -12,6 +12,7 @@ install_data(
'pg_buffercache--1.2--1.3.sql',
'pg_buffercache--1.2.sql',
'pg_buffercache--1.3--1.4.sql',
+ 'pg_buffercache--1.4--1.5.sql',
'pg_buffercache.control',
kwargs: contrib_data_args,
)
diff --git a/contrib/pg_buffercache/pg_buffercache--1.4--1.5.sql b/contrib/pg_buffercache/pg_buffercache--1.4--1.5.sql
new file mode 100644
index 0000000000..054012d3a8
--- /dev/null
+++ b/contrib/pg_buffercache/pg_buffercache--1.4--1.5.sql
@@ -0,0 +1,13 @@
+/* contrib/pg_buffercache/pg_buffercache--1.4--1.5.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 (buffers_used int4, buffers_unused int4, buffers_dirty int4,
+ buffers_pinned int4, usagecount_avg 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 a82ae5f9bb..5ee875f77d 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.4'
+default_version = '1.5'
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 a45f240499..6122d49108 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;
@@ -60,6 +61,7 @@ typedef struct
*/
PG_FUNCTION_INFO_V1(pg_buffercache_pages);
PG_FUNCTION_INFO_V1(pg_buffercache_pages_v1_4);
+PG_FUNCTION_INFO_V1(pg_buffercache_summary);
static Datum
pg_buffercache_pages_internal(PG_FUNCTION_ARGS, Oid rfn_typid)
@@ -268,3 +270,88 @@ pg_buffercache_pages_v1_4(PG_FUNCTION_ARGS)
{
return pg_buffercache_pages_internal(fcinfo, INT8OID);
}
+
+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 buffers_used = 0;
+ int32 buffers_unused = 0;
+ int32 buffers_dirty = 0;
+ int32 buffers_pinned = 0;
+ float usagecount_avg = 0;
+
+ /* Construct a tuple descriptor for the result rows. */
+ tupledesc = CreateTemplateTupleDesc(NUM_BUFFERCACHE_SUMMARY_ELEM);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 1, "buffers_used",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 2, "buffers_unused",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 3, "buffers_dirty",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 4, "buffers_pinned",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 5, "usagecount_avg",
+ 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, 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);
+
+ if (buf_state & BM_VALID)
+ {
+ buffers_used++;
+ usagecount_avg += BUF_STATE_GET_USAGECOUNT(buf_state);
+
+ if (buf_state & BM_DIRTY)
+ buffers_dirty++;
+ }
+ else
+ buffers_unused++;
+
+ if (BUF_STATE_GET_REFCOUNT(buf_state) > 0)
+ buffers_pinned++;
+ }
+
+ if (buffers_used != 0)
+ usagecount_avg = usagecount_avg / buffers_used;
+
+ memset(nulls, 0, sizeof(nulls));
+ values[0] = Int32GetDatum(buffers_used);
+ values[1] = Int32GetDatum(buffers_unused);
+ values[2] = Int32GetDatum(buffers_dirty);
+ values[3] = Int32GetDatum(buffers_pinned);
+
+ if (buffers_used != 0)
+ {
+ usagecount_avg = usagecount_avg / buffers_used;
+ values[4] = Float4GetDatum(usagecount_avg);
+ }
+ else
+ {
+ nulls[4] = true;
+ }
+
+ /* 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..897dfbc924 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 buffers_used + buffers_unused > 0,
+ buffers_dirty <= buffers_used,
+ buffers_pinned <= buffers_used
+from pg_buffercache_summary();
diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml
index e222265580..2fef21c344 100644
--- a/doc/src/sgml/pgbuffercache.sgml
+++ b/doc/src/sgml/pgbuffercache.sgml
@@ -9,7 +9,7 @@
<para>
The <filename>pg_buffercache</filename> module provides a means for
- examining what's happening in the shared buffer cache in real time.
+ examining what's happening in the shared buffer in real time.
</para>
<indexterm>
@@ -17,10 +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
- convenient use.
+ The module provides C functions <function>pg_buffercache_pages</function>
+ and <function>pg_buffercache_summary</function>.
+ </para>
+
+ <para>
+ The <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>
+ The <function>pg_buffercache_summary</function> function returns a table with a single row
+ that contains summarized and aggregated information about shared buffer.
</para>
<para>
@@ -164,6 +173,91 @@
</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>buffers_used</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>buffers_unused</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>buffers_dirty</structfield> <type>int4</type>
+ </para>
+ <para>
+ Number of dirty shared buffers
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>buffers_pinned</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>usagecount_avg</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>
+ The <function>pg_buffercache_summary</function> doesn't provide a result
+ that is consistent across all buffers. This is intentional. The purpose
+ of this function is to provide a general idea about the state of shared
+ buffers as fast as possible. Additionally, <function>pg_buffercache_summary</function>
+ allocates much less memory.
+ </para>
+ </sect2>
+
<sect2>
<title>Sample Output</title>
@@ -191,6 +285,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();
+ buffers_used | buffers_unused | buffers_dirty | buffers_pinned | usagecount_avg
+--------------+----------------+---------------+----------------+----------------
+ 248 | 2096904 | 39 | 0 | 3.141129
+(1 row)
</screen>
</sect2>
--
2.25.1
Re: Summary function for pg_buffercache
Regards,
Zhang Mingli
On Sep 28, 2022, 21:50 +0800, Melih Mutlu <m.melihmutlu@gmail.com>, wrote:
Hi all,
The patch needed a rebase due to recent changes on pg_buffercache.
You can find the updated version attached.Best,
Melih
```
+
+ if (buffers_used != 0)
+ usagecount_avg = usagecount_avg / buffers_used;
+
+ memset(nulls, 0, sizeof(nulls));
+ values[0] = Int32GetDatum(buffers_used);
+ values[1] = Int32GetDatum(buffers_unused);
+ values[2] = Int32GetDatum(buffers_dirty);
+ values[3] = Int32GetDatum(buffers_pinned);
+
+ if (buffers_used != 0)
+ {
+ usagecount_avg = usagecount_avg / buffers_used;
+ values[4] = Float4GetDatum(usagecount_avg);
+ }
+ else
+ {
+ nulls[4] = true;
+ }
```Why compute usagecount_avg twice?
Re: Summary function for pg_buffercache
Zhang Mingli <zmlpostgres@gmail.com>, 28 Eyl 2022 Çar, 17:31 tarihinde şunu
yazdı:
Why compute usagecount_avg twice?
I should have removed the first one, but I think I missed it.
Nice catch.
Attached an updated version.
Thanks,
Melih
Attachments:
v13-0001-Added-pg_buffercache_summary-function.patchapplication/octet-stream; name=v13-0001-Added-pg_buffercache_summary-function.patchDownload
From 849aed397dde289cfe168e90afb62f02b7f64bcc Mon Sep 17 00:00:00 2001
From: Melih Mutlu <m.melihmutlu@gmail.com>
Date: Sat, 10 Sep 2022 02:08:24 +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.
Melih Mutlu, reviewed by Andres Freund, Aleksander Alekseev
Discussion: https://postgr.es/m/CAGPVpCQAXYo54Q%3D8gqBsS%3Du0uk9qhnnq4%2B710BtUhUisX1XGEg%40mail.gmail.com
---
contrib/pg_buffercache/Makefile | 2 +-
.../expected/pg_buffercache.out | 9 ++
contrib/pg_buffercache/meson.build | 1 +
.../pg_buffercache--1.4--1.5.sql | 13 ++
contrib/pg_buffercache/pg_buffercache.control | 2 +-
contrib/pg_buffercache/pg_buffercache_pages.c | 84 +++++++++++++
contrib/pg_buffercache/sql/pg_buffercache.sql | 5 +
doc/src/sgml/pgbuffercache.sgml | 111 +++++++++++++++++-
8 files changed, 220 insertions(+), 7 deletions(-)
create mode 100644 contrib/pg_buffercache/pg_buffercache--1.4--1.5.sql
diff --git a/contrib/pg_buffercache/Makefile b/contrib/pg_buffercache/Makefile
index 4d88eba5e3..022c0acbe7 100644
--- a/contrib/pg_buffercache/Makefile
+++ b/contrib/pg_buffercache/Makefile
@@ -7,7 +7,7 @@ OBJS = \
EXTENSION = pg_buffercache
DATA = pg_buffercache--1.0--1.1.sql pg_buffercache--1.1--1.2.sql pg_buffercache--1.2.sql \
- pg_buffercache--1.2--1.3.sql pg_buffercache--1.3--1.4.sql
+ pg_buffercache--1.2--1.3.sql pg_buffercache--1.3--1.4.sql pg_buffercache--1.4--1.5.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..6798eb7432 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 buffers_used + buffers_unused > 0,
+ buffers_dirty <= buffers_used,
+ buffers_pinned <= buffers_used
+from pg_buffercache_summary();
+ ?column? | ?column? | ?column?
+----------+----------+----------
+ t | t | t
+(1 row)
+
diff --git a/contrib/pg_buffercache/meson.build b/contrib/pg_buffercache/meson.build
index 9f34754d6c..fd6b6a36d3 100644
--- a/contrib/pg_buffercache/meson.build
+++ b/contrib/pg_buffercache/meson.build
@@ -12,6 +12,7 @@ install_data(
'pg_buffercache--1.2--1.3.sql',
'pg_buffercache--1.2.sql',
'pg_buffercache--1.3--1.4.sql',
+ 'pg_buffercache--1.4--1.5.sql',
'pg_buffercache.control',
kwargs: contrib_data_args,
)
diff --git a/contrib/pg_buffercache/pg_buffercache--1.4--1.5.sql b/contrib/pg_buffercache/pg_buffercache--1.4--1.5.sql
new file mode 100644
index 0000000000..054012d3a8
--- /dev/null
+++ b/contrib/pg_buffercache/pg_buffercache--1.4--1.5.sql
@@ -0,0 +1,13 @@
+/* contrib/pg_buffercache/pg_buffercache--1.4--1.5.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 (buffers_used int4, buffers_unused int4, buffers_dirty int4,
+ buffers_pinned int4, usagecount_avg 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 a82ae5f9bb..5ee875f77d 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.4'
+default_version = '1.5'
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 a45f240499..72a6498eb2 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;
@@ -60,6 +61,7 @@ typedef struct
*/
PG_FUNCTION_INFO_V1(pg_buffercache_pages);
PG_FUNCTION_INFO_V1(pg_buffercache_pages_v1_4);
+PG_FUNCTION_INFO_V1(pg_buffercache_summary);
static Datum
pg_buffercache_pages_internal(PG_FUNCTION_ARGS, Oid rfn_typid)
@@ -268,3 +270,85 @@ pg_buffercache_pages_v1_4(PG_FUNCTION_ARGS)
{
return pg_buffercache_pages_internal(fcinfo, INT8OID);
}
+
+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 buffers_used = 0;
+ int32 buffers_unused = 0;
+ int32 buffers_dirty = 0;
+ int32 buffers_pinned = 0;
+ float usagecount_avg = 0;
+
+ /* Construct a tuple descriptor for the result rows. */
+ tupledesc = CreateTemplateTupleDesc(NUM_BUFFERCACHE_SUMMARY_ELEM);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 1, "buffers_used",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 2, "buffers_unused",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 3, "buffers_dirty",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 4, "buffers_pinned",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 5, "usagecount_avg",
+ 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, 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);
+
+ if (buf_state & BM_VALID)
+ {
+ buffers_used++;
+ usagecount_avg += BUF_STATE_GET_USAGECOUNT(buf_state);
+
+ if (buf_state & BM_DIRTY)
+ buffers_dirty++;
+ }
+ else
+ buffers_unused++;
+
+ if (BUF_STATE_GET_REFCOUNT(buf_state) > 0)
+ buffers_pinned++;
+ }
+
+ memset(nulls, 0, sizeof(nulls));
+ values[0] = Int32GetDatum(buffers_used);
+ values[1] = Int32GetDatum(buffers_unused);
+ values[2] = Int32GetDatum(buffers_dirty);
+ values[3] = Int32GetDatum(buffers_pinned);
+
+ if (buffers_used != 0)
+ {
+ usagecount_avg = usagecount_avg / buffers_used;
+ values[4] = Float4GetDatum(usagecount_avg);
+ }
+ else
+ {
+ nulls[4] = true;
+ }
+
+ /* 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..897dfbc924 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 buffers_used + buffers_unused > 0,
+ buffers_dirty <= buffers_used,
+ buffers_pinned <= buffers_used
+from pg_buffercache_summary();
diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml
index e222265580..2fef21c344 100644
--- a/doc/src/sgml/pgbuffercache.sgml
+++ b/doc/src/sgml/pgbuffercache.sgml
@@ -9,7 +9,7 @@
<para>
The <filename>pg_buffercache</filename> module provides a means for
- examining what's happening in the shared buffer cache in real time.
+ examining what's happening in the shared buffer in real time.
</para>
<indexterm>
@@ -17,10 +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
- convenient use.
+ The module provides C functions <function>pg_buffercache_pages</function>
+ and <function>pg_buffercache_summary</function>.
+ </para>
+
+ <para>
+ The <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>
+ The <function>pg_buffercache_summary</function> function returns a table with a single row
+ that contains summarized and aggregated information about shared buffer.
</para>
<para>
@@ -164,6 +173,91 @@
</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>buffers_used</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>buffers_unused</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>buffers_dirty</structfield> <type>int4</type>
+ </para>
+ <para>
+ Number of dirty shared buffers
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>buffers_pinned</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>usagecount_avg</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>
+ The <function>pg_buffercache_summary</function> doesn't provide a result
+ that is consistent across all buffers. This is intentional. The purpose
+ of this function is to provide a general idea about the state of shared
+ buffers as fast as possible. Additionally, <function>pg_buffercache_summary</function>
+ allocates much less memory.
+ </para>
+ </sect2>
+
<sect2>
<title>Sample Output</title>
@@ -191,6 +285,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();
+ buffers_used | buffers_unused | buffers_dirty | buffers_pinned | usagecount_avg
+--------------+----------------+---------------+----------------+----------------
+ 248 | 2096904 | 39 | 0 | 3.141129
+(1 row)
</screen>
</sect2>
--
2.25.1
Re: Summary function for pg_buffercache
Hi,
On Sep 28, 2022, 22:41 +0800, Melih Mutlu <m.melihmutlu@gmail.com>, wrote:
Zhang Mingli <zmlpostgres@gmail.com>, 28 Eyl 2022 Çar, 17:31 tarihinde şunu yazdı:
Why compute usagecount_avg twice?
I should have removed the first one, but I think I missed it.
Nice catch.Attached an updated version.
Thanks,
Melih
Hmm, I just apply v13 patch but failed.
Part of errors:
```
error: patch failed: contrib/pg_buffercache/pg_buffercache.control:1 error: contrib/pg_buffercache/pg_buffercache.control: patch does not apply
Checking patch contrib/pg_buffercache/pg_buffercache_pages.c...
error: while searching for:
*/
PG_FUNCTION_INFO_V1(pg_buffercache_pages);
PG_FUNCTION_INFO_V1(pg_buffercache_pages_v1_4);
```
Rebase on master and then apply our changes again?
Regards,
Zhang Mingli
Show quoted text
Re: Summary function for pg_buffercache
Hi,
Seems like the commit a448e49bcbe40fb72e1ed85af910dd216d45bad8 reverts the
changes on pg_buffercache.
Why compute usagecount_avg twice?
Then, I'm going back to v11 + the fix for this.
Thanks,
Melih
Attachments:
v14-0001-Added-pg_buffercache_summary-function.patchapplication/octet-stream; name=v14-0001-Added-pg_buffercache_summary-function.patchDownload
From 350149a89b02a2c85b9f6a5a5aad7cf15daa1bb0 Mon Sep 17 00:00:00 2001
From: Melih Mutlu <m.melihmutlu@gmail.com>
Date: Sat, 10 Sep 2022 02:08:24 +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.
Melih Mutlu, reviewed by Andres Freund, Aleksander Alekseev
Discussion: https://postgr.es/m/CAGPVpCQAXYo54Q%3D8gqBsS%3Du0uk9qhnnq4%2B710BtUhUisX1XGEg%40mail.gmail.com
---
contrib/pg_buffercache/Makefile | 3 +-
.../expected/pg_buffercache.out | 9 ++
contrib/pg_buffercache/meson.build | 1 +
.../pg_buffercache--1.3--1.4.sql | 13 ++
contrib/pg_buffercache/pg_buffercache.control | 2 +-
contrib/pg_buffercache/pg_buffercache_pages.c | 84 +++++++++++++
contrib/pg_buffercache/sql/pg_buffercache.sql | 5 +
doc/src/sgml/pgbuffercache.sgml | 111 +++++++++++++++++-
8 files changed, 221 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..6798eb7432 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 buffers_used + buffers_unused > 0,
+ buffers_dirty <= buffers_used,
+ buffers_pinned <= buffers_used
+from pg_buffercache_summary();
+ ?column? | ?column? | ?column?
+----------+----------+----------
+ t | t | t
+(1 row)
+
diff --git a/contrib/pg_buffercache/meson.build b/contrib/pg_buffercache/meson.build
index 2c69eae3ea..9f34754d6c 100644
--- a/contrib/pg_buffercache/meson.build
+++ b/contrib/pg_buffercache/meson.build
@@ -11,6 +11,7 @@ install_data(
'pg_buffercache--1.1--1.2.sql',
'pg_buffercache--1.2--1.3.sql',
'pg_buffercache--1.2.sql',
+ 'pg_buffercache--1.3--1.4.sql',
'pg_buffercache.control',
kwargs: contrib_data_args,
)
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..77e250b430
--- /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 (buffers_used int4, buffers_unused int4, buffers_dirty int4,
+ buffers_pinned int4, usagecount_avg 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..0ec023133a 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;
@@ -59,6 +60,7 @@ typedef struct
* 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 +239,85 @@ 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 buffers_used = 0;
+ int32 buffers_unused = 0;
+ int32 buffers_dirty = 0;
+ int32 buffers_pinned = 0;
+ float usagecount_avg = 0;
+
+ /* Construct a tuple descriptor for the result rows. */
+ tupledesc = CreateTemplateTupleDesc(NUM_BUFFERCACHE_SUMMARY_ELEM);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 1, "buffers_used",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 2, "buffers_unused",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 3, "buffers_dirty",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 4, "buffers_pinned",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 5, "usagecount_avg",
+ 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, 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);
+
+ if (buf_state & BM_VALID)
+ {
+ buffers_used++;
+ usagecount_avg += BUF_STATE_GET_USAGECOUNT(buf_state);
+
+ if (buf_state & BM_DIRTY)
+ buffers_dirty++;
+ }
+ else
+ buffers_unused++;
+
+ if (BUF_STATE_GET_REFCOUNT(buf_state) > 0)
+ buffers_pinned++;
+ }
+
+ memset(nulls, 0, sizeof(nulls));
+ values[0] = Int32GetDatum(buffers_used);
+ values[1] = Int32GetDatum(buffers_unused);
+ values[2] = Int32GetDatum(buffers_dirty);
+ values[3] = Int32GetDatum(buffers_pinned);
+
+ if (buffers_used != 0)
+ {
+ usagecount_avg = usagecount_avg / buffers_used;
+ values[4] = Float4GetDatum(usagecount_avg);
+ }
+ else
+ {
+ nulls[4] = true;
+ }
+
+ /* 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..897dfbc924 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 buffers_used + buffers_unused > 0,
+ buffers_dirty <= buffers_used,
+ buffers_pinned <= buffers_used
+from pg_buffercache_summary();
diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml
index a06fd3e26d..6c38ee5d5e 100644
--- a/doc/src/sgml/pgbuffercache.sgml
+++ b/doc/src/sgml/pgbuffercache.sgml
@@ -9,7 +9,7 @@
<para>
The <filename>pg_buffercache</filename> module provides a means for
- examining what's happening in the shared buffer cache in real time.
+ examining what's happening in the shared buffer in real time.
</para>
<indexterm>
@@ -17,10 +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
- convenient use.
+ The module provides C functions <function>pg_buffercache_pages</function>
+ and <function>pg_buffercache_summary</function>.
+ </para>
+
+ <para>
+ The <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>
+ The <function>pg_buffercache_summary</function> function returns a table with a single row
+ that contains summarized and aggregated information about shared buffer.
</para>
<para>
@@ -164,6 +173,91 @@
</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>buffers_used</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>buffers_unused</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>buffers_dirty</structfield> <type>int4</type>
+ </para>
+ <para>
+ Number of dirty shared buffers
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>buffers_pinned</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>usagecount_avg</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>
+ The <function>pg_buffercache_summary</function> doesn't provide a result
+ that is consistent across all buffers. This is intentional. The purpose
+ of this function is to provide a general idea about the state of shared
+ buffers as fast as possible. Additionally, <function>pg_buffercache_summary</function>
+ allocates much less memory.
+ </para>
+ </sect2>
+
<sect2>
<title>Sample Output</title>
@@ -191,6 +285,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();
+ buffers_used | buffers_unused | buffers_dirty | buffers_pinned | usagecount_avg
+--------------+----------------+---------------+----------------+----------------
+ 248 | 2096904 | 39 | 0 | 3.141129
+(1 row)
</screen>
</sect2>
--
2.25.1
Re: Summary function for pg_buffercache
Hi,
On Sep 28, 2022, 23:20 +0800, Melih Mutlu <m.melihmutlu@gmail.com>, wrote:
Hi,
Seems like the commit a448e49bcbe40fb72e1ed85af910dd216d45bad8 reverts the changes on pg_buffercache.
Why compute usagecount_avg twice?
Then, I'm going back to v11 + the fix for this.
Thanks,
Melih
Looks good to me.
Regards,
Zhang Mingli
Re: Summary function for pg_buffercache
Hi,
On 2022-09-28 18:19:57 +0300, Melih Mutlu wrote:
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..77e250b430 --- /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 (buffers_used int4, buffers_unused int4, buffers_dirty int4, + buffers_pinned int4, usagecount_avg real) +AS 'MODULE_PATHNAME', 'pg_buffercache_summary' +LANGUAGE C PARALLEL SAFE;
I think using RETURNS TABLE isn't quite right here, as it implies 'SETOF'. But
the function doesn't return a set of rows. I changed this to use OUT
parameters.
+-- Don't want these to be available to public. +REVOKE ALL ON FUNCTION pg_buffercache_summary() FROM PUBLIC;
I think this needs to grant to pg_monitor too. See
pg_buffercache--1.2--1.3.sql
I added a test verifying the permissions are right, with the hope that it'll
make future contributors try to add a parallel test and notice the permissions
aren't right.
+ /* Construct a tuple descriptor for the result rows. */ + tupledesc = CreateTemplateTupleDesc(NUM_BUFFERCACHE_SUMMARY_ELEM);
Given that we define the return type on the SQL level, it imo is nicer to use
get_call_result_type() here.
+ TupleDescInitEntry(tupledesc, (AttrNumber) 5, "usagecount_avg", + FLOAT4OID, -1, 0);
I changed this to FLOAT8. Not that the precision will commonly be useful, but
it doesn't seem worth having to even think about whether there are cases where
it'd matter.
I also changed it so that the accumulation happens in an int64 variable named
usagecount_total, which gets converted to a double only when actually
computing the result.
<para> The <filename>pg_buffercache</filename> module provides a means for - examining what's happening in the shared buffer cache in real time. + examining what's happening in the shared buffer in real time. </para>
This seems to be an unnecessary / unrelated change. I suspect you made it in
response to
/messages/by-id/20220922161014.copbzwdl3ja4nt6z@awork3.anarazel.de
but that was about a different sentence, where you said 'shared buffer caches'
(even though there is only a single shared buffer cache).
<indexterm>
@@ -17,10 +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 - convenient use. + The module provides C functions <function>pg_buffercache_pages</function> + and <function>pg_buffercache_summary</function>. + </para> + + <para> + The <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>
I rephrased this, because it sounds like the function returns a set of records
and a view.
+ <para> + The <function>pg_buffercache_summary</function> function returns a table with a single row + that contains summarized and aggregated information about shared buffer. </para>
"summarized and aggregated" is quite redundant.
+ <table id="pgbuffercachesummary-columns"> + <title><structname>pg_buffercachesummary</structname> Columns</title>
Missing underscore.
+ <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>buffers_unused</structfield> <type>int4</type> + </para> + <para> + Number of shared buffers that not currently being used + </para></entry> + </row>
There's a missing 'are' in here, I think. I rephrased all of these to
"Number of (used|unused|dirty|pinned) shared buffers"
+ <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>buffers_dirty</structfield> <type>int4</type> + </para> + <para> + Number of dirty shared buffers + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>buffers_pinned</structfield> <type>int4</type> + </para> + <para> + Number of shared buffers that has a pinned backend + </para></entry> + </row>
Backends pin buffers, not the other way round...
+ <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> + The <function>pg_buffercache_summary</function> doesn't provide a result + that is consistent across all buffers. This is intentional. The purpose + of this function is to provide a general idea about the state of shared + buffers as fast as possible. Additionally, <function>pg_buffercache_summary</function> + allocates much less memory. + </para>
I still didn't like this comment. Please see the attached.
I intentionally put my changes into a fixup commit, in case you want to look
at the differences.
Greetings,
Andres Freund
Attachments:
v15-0001-pg_buffercache-Add-pg_buffercache_summary.patchtext/x-diff; charset=us-asciiDownload
From 9caf0911bca9be5b630f8086befc861331e91c5a Mon Sep 17 00:00:00 2001
From: Melih Mutlu <m.melihmutlu@gmail.com>
Date: Sat, 10 Sep 2022 02:08:24 +0300
Subject: [PATCH v15 1/2] pg_buffercache: Add pg_buffercache_summary()
Using pg_buffercache_summary() is significantly cheaper than querying
pg_buffercache and summarizing in SQL.
Author: Melih Mutlu <m.melihmutlu@gmail.com>
Reviewed-by: Andres Freund <andres@anarazel.de>
Reviewed-by: Aleksander Alekseev <aleksander@timescale.com>
Reviewed-by: Zhang Mingli <zmlpostgres@gmail.com>
Discussion: https://postgr.es/m/CAGPVpCQAXYo54Q%3D8gqBsS%3Du0uk9qhnnq4%2B710BtUhUisX1XGEg%40mail.gmail.com
---
contrib/pg_buffercache/Makefile | 3 +-
.../expected/pg_buffercache.out | 9 ++
contrib/pg_buffercache/meson.build | 1 +
.../pg_buffercache--1.3--1.4.sql | 13 ++
contrib/pg_buffercache/pg_buffercache.control | 2 +-
contrib/pg_buffercache/pg_buffercache_pages.c | 84 +++++++++++++
contrib/pg_buffercache/sql/pg_buffercache.sql | 5 +
doc/src/sgml/pgbuffercache.sgml | 111 +++++++++++++++++-
8 files changed, 221 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 d74b3e853c6..d6b58d4da94 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 138556efc9f..6798eb74322 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 buffers_used + buffers_unused > 0,
+ buffers_dirty <= buffers_used,
+ buffers_pinned <= buffers_used
+from pg_buffercache_summary();
+ ?column? | ?column? | ?column?
+----------+----------+----------
+ t | t | t
+(1 row)
+
diff --git a/contrib/pg_buffercache/meson.build b/contrib/pg_buffercache/meson.build
index dd9948e5f0b..ff7f9162cee 100644
--- a/contrib/pg_buffercache/meson.build
+++ b/contrib/pg_buffercache/meson.build
@@ -19,6 +19,7 @@ install_data(
'pg_buffercache--1.1--1.2.sql',
'pg_buffercache--1.2--1.3.sql',
'pg_buffercache--1.2.sql',
+ 'pg_buffercache--1.3--1.4.sql',
'pg_buffercache.control',
kwargs: contrib_data_args,
)
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 00000000000..77e250b430f
--- /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 (buffers_used int4, buffers_unused int4, buffers_dirty int4,
+ buffers_pinned int4, usagecount_avg 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 8c060ae9abf..a82ae5f9bb5 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 c5754ea9fa5..a8f9750ac42 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;
@@ -59,6 +60,7 @@ typedef struct
* 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 +239,85 @@ 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 buffers_used = 0;
+ int32 buffers_unused = 0;
+ int32 buffers_dirty = 0;
+ int32 buffers_pinned = 0;
+ float usagecount_avg = 0;
+
+ /* Construct a tuple descriptor for the result rows. */
+ tupledesc = CreateTemplateTupleDesc(NUM_BUFFERCACHE_SUMMARY_ELEM);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 1, "buffers_used",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 2, "buffers_unused",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 3, "buffers_dirty",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 4, "buffers_pinned",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 5, "usagecount_avg",
+ FLOAT4OID, -1, 0);
+
+ BlessTupleDesc(tupledesc);
+
+ for (int i = 0; i < NBuffers; i++)
+ {
+ BufferDesc *bufHdr;
+ uint32 buf_state;
+
+ /*
+ * This function summarizes the state of all headers. Locking the
+ * buffer headers wouldn't provide an improved result as the state of
+ * the buffer can still change after we release the lock and it'd
+ * noticeably increase the cost of the function.
+ */
+ bufHdr = GetBufferDescriptor(i);
+ buf_state = pg_atomic_read_u32(&bufHdr->state);
+
+ if (buf_state & BM_VALID)
+ {
+ buffers_used++;
+ usagecount_avg += BUF_STATE_GET_USAGECOUNT(buf_state);
+
+ if (buf_state & BM_DIRTY)
+ buffers_dirty++;
+ }
+ else
+ buffers_unused++;
+
+ if (BUF_STATE_GET_REFCOUNT(buf_state) > 0)
+ buffers_pinned++;
+ }
+
+ memset(nulls, 0, sizeof(nulls));
+ values[0] = Int32GetDatum(buffers_used);
+ values[1] = Int32GetDatum(buffers_unused);
+ values[2] = Int32GetDatum(buffers_dirty);
+ values[3] = Int32GetDatum(buffers_pinned);
+
+ if (buffers_used != 0)
+ {
+ usagecount_avg = usagecount_avg / buffers_used;
+ values[4] = Float4GetDatum(usagecount_avg);
+ }
+ else
+ {
+ nulls[4] = true;
+ }
+
+ /* 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 e1ba6f7e8d4..897dfbc9245 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 buffers_used + buffers_unused > 0,
+ buffers_dirty <= buffers_used,
+ buffers_pinned <= buffers_used
+from pg_buffercache_summary();
diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml
index a06fd3e26de..6c38ee5d5eb 100644
--- a/doc/src/sgml/pgbuffercache.sgml
+++ b/doc/src/sgml/pgbuffercache.sgml
@@ -9,7 +9,7 @@
<para>
The <filename>pg_buffercache</filename> module provides a means for
- examining what's happening in the shared buffer cache in real time.
+ examining what's happening in the shared buffer in real time.
</para>
<indexterm>
@@ -17,10 +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
- convenient use.
+ The module provides C functions <function>pg_buffercache_pages</function>
+ and <function>pg_buffercache_summary</function>.
+ </para>
+
+ <para>
+ The <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>
+ The <function>pg_buffercache_summary</function> function returns a table with a single row
+ that contains summarized and aggregated information about shared buffer.
</para>
<para>
@@ -164,6 +173,91 @@
</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>buffers_used</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>buffers_unused</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>buffers_dirty</structfield> <type>int4</type>
+ </para>
+ <para>
+ Number of dirty shared buffers
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>buffers_pinned</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>usagecount_avg</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>
+ The <function>pg_buffercache_summary</function> doesn't provide a result
+ that is consistent across all buffers. This is intentional. The purpose
+ of this function is to provide a general idea about the state of shared
+ buffers as fast as possible. Additionally, <function>pg_buffercache_summary</function>
+ allocates much less memory.
+ </para>
+ </sect2>
+
<sect2>
<title>Sample Output</title>
@@ -191,6 +285,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();
+ buffers_used | buffers_unused | buffers_dirty | buffers_pinned | usagecount_avg
+--------------+----------------+---------------+----------------+----------------
+ 248 | 2096904 | 39 | 0 | 3.141129
+(1 row)
</screen>
</sect2>
--
2.38.0
v15-0002-fixup-pg_buffercache-Add-pg_buffercache_summary.patchtext/x-diff; charset=us-asciiDownload
From 40597bd30c6c9c4ef9a87864d4f6e2b2b0d7548f Mon Sep 17 00:00:00 2001
From: Andres Freund <andres@anarazel.de>
Date: Wed, 12 Oct 2022 12:20:43 -0700
Subject: [PATCH v15 2/2] fixup! pg_buffercache: Add pg_buffercache_summary()
---
.../expected/pg_buffercache.out | 24 ++++++++
.../pg_buffercache--1.3--1.4.sql | 10 +++-
contrib/pg_buffercache/pg_buffercache_pages.c | 27 ++-------
contrib/pg_buffercache/sql/pg_buffercache.sql | 13 +++++
doc/src/sgml/pgbuffercache.sgml | 55 +++++++++++--------
5 files changed, 80 insertions(+), 49 deletions(-)
diff --git a/contrib/pg_buffercache/expected/pg_buffercache.out b/contrib/pg_buffercache/expected/pg_buffercache.out
index 6798eb74322..635f01e3b21 100644
--- a/contrib/pg_buffercache/expected/pg_buffercache.out
+++ b/contrib/pg_buffercache/expected/pg_buffercache.out
@@ -17,3 +17,27 @@ from pg_buffercache_summary();
t | t | t
(1 row)
+-- Check that the functions / views can't be accessed by default. To avoid
+-- having to create a dedicated user, use the pg_database_owner pseudo-role.
+SET ROLE pg_database_owner;
+SELECT * FROM pg_buffercache;
+ERROR: permission denied for view pg_buffercache
+SELECT * FROM pg_buffercache_pages() AS p (wrong int);
+ERROR: permission denied for function pg_buffercache_pages
+SELECT * FROM pg_buffercache_summary();
+ERROR: permission denied for function pg_buffercache_summary
+RESET role;
+-- Check that pg_monitor is allowed to query view / function
+SET ROLE pg_monitor;
+SELECT count(*) > 0 FROM pg_buffercache;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT buffers_used + buffers_unused > 0 FROM pg_buffercache_summary();
+ ?column?
+----------
+ 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
index 77e250b430f..8f212dc5e93 100644
--- a/contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql
+++ b/contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql
@@ -3,11 +3,15 @@
-- 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 (buffers_used int4, buffers_unused int4, buffers_dirty int4,
- buffers_pinned int4, usagecount_avg real)
+CREATE FUNCTION pg_buffercache_summary(
+ OUT buffers_used int4,
+ OUT buffers_unused int4,
+ OUT buffers_dirty int4,
+ OUT buffers_pinned int4,
+ OUT usagecount_avg float8)
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;
+GRANT EXECUTE ON FUNCTION pg_buffercache_summary() TO pg_monitor;
diff --git a/contrib/pg_buffercache/pg_buffercache_pages.c b/contrib/pg_buffercache/pg_buffercache_pages.c
index a8f9750ac42..1c6a2f22caa 100644
--- a/contrib/pg_buffercache/pg_buffercache_pages.c
+++ b/contrib/pg_buffercache/pg_buffercache_pages.c
@@ -253,22 +253,10 @@ pg_buffercache_summary(PG_FUNCTION_ARGS)
int32 buffers_unused = 0;
int32 buffers_dirty = 0;
int32 buffers_pinned = 0;
- float usagecount_avg = 0;
+ int64 usagecount_total = 0;
- /* Construct a tuple descriptor for the result rows. */
- tupledesc = CreateTemplateTupleDesc(NUM_BUFFERCACHE_SUMMARY_ELEM);
- TupleDescInitEntry(tupledesc, (AttrNumber) 1, "buffers_used",
- INT4OID, -1, 0);
- TupleDescInitEntry(tupledesc, (AttrNumber) 2, "buffers_unused",
- INT4OID, -1, 0);
- TupleDescInitEntry(tupledesc, (AttrNumber) 3, "buffers_dirty",
- INT4OID, -1, 0);
- TupleDescInitEntry(tupledesc, (AttrNumber) 4, "buffers_pinned",
- INT4OID, -1, 0);
- TupleDescInitEntry(tupledesc, (AttrNumber) 5, "usagecount_avg",
- FLOAT4OID, -1, 0);
-
- BlessTupleDesc(tupledesc);
+ if (get_call_result_type(fcinfo, NULL, &tupledesc) != TYPEFUNC_COMPOSITE)
+ elog(ERROR, "return type must be a row type");
for (int i = 0; i < NBuffers; i++)
{
@@ -287,7 +275,7 @@ pg_buffercache_summary(PG_FUNCTION_ARGS)
if (buf_state & BM_VALID)
{
buffers_used++;
- usagecount_avg += BUF_STATE_GET_USAGECOUNT(buf_state);
+ usagecount_total += BUF_STATE_GET_USAGECOUNT(buf_state);
if (buf_state & BM_DIRTY)
buffers_dirty++;
@@ -306,14 +294,9 @@ pg_buffercache_summary(PG_FUNCTION_ARGS)
values[3] = Int32GetDatum(buffers_pinned);
if (buffers_used != 0)
- {
- usagecount_avg = usagecount_avg / buffers_used;
- values[4] = Float4GetDatum(usagecount_avg);
- }
+ values[4] = Float8GetDatum((double) usagecount_total / buffers_used);
else
- {
nulls[4] = true;
- }
/* 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 897dfbc9245..2e2e0a74517 100644
--- a/contrib/pg_buffercache/sql/pg_buffercache.sql
+++ b/contrib/pg_buffercache/sql/pg_buffercache.sql
@@ -9,3 +9,16 @@ select buffers_used + buffers_unused > 0,
buffers_dirty <= buffers_used,
buffers_pinned <= buffers_used
from pg_buffercache_summary();
+
+-- Check that the functions / views can't be accessed by default. To avoid
+-- having to create a dedicated user, use the pg_database_owner pseudo-role.
+SET ROLE pg_database_owner;
+SELECT * FROM pg_buffercache;
+SELECT * FROM pg_buffercache_pages() AS p (wrong int);
+SELECT * FROM pg_buffercache_summary();
+RESET role;
+
+-- Check that pg_monitor is allowed to query view / function
+SET ROLE pg_monitor;
+SELECT count(*) > 0 FROM pg_buffercache;
+SELECT buffers_used + buffers_unused > 0 FROM pg_buffercache_summary();
diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml
index 6c38ee5d5eb..8f314ee8ff4 100644
--- a/doc/src/sgml/pgbuffercache.sgml
+++ b/doc/src/sgml/pgbuffercache.sgml
@@ -9,27 +9,33 @@
<para>
The <filename>pg_buffercache</filename> module provides a means for
- examining what's happening in the shared buffer in real time.
+ examining what's happening in the shared buffer cache in real time.
</para>
<indexterm>
<primary>pg_buffercache_pages</primary>
</indexterm>
+ <indexterm>
+ <primary>pg_buffercache_summary</primary>
+ </indexterm>
+
<para>
- The module provides C functions <function>pg_buffercache_pages</function>
- and <function>pg_buffercache_summary</function>.
+ The module provides the <function>pg_buffercache_pages()</function>
+ function, wrapped in the <structname>pg_buffercache</structname> view, and
+ the <function>pg_buffercache_summary()</function> function.
</para>
<para>
- The <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.
+ The <function>pg_buffercache_pages()</function> function returns a set of
+ records, each row describing the state of one shared buffer entry. The
+ <structname>pg_buffercache</structname> view wraps the function for
+ convenient use.
</para>
<para>
- The <function>pg_buffercache_summary</function> function returns a table with a single row
- that contains summarized and aggregated information about shared buffer.
+ The <function>pg_buffercache_summary()</function> function returns a single
+ row summarizing the state of the shared buffer cache.
</para>
<para>
@@ -174,14 +180,14 @@
</sect2>
<sect2>
- <title>The <structname>pg_buffercache_summary</structname> Function</title>
+ <title>The <function>pg_buffercache_summary()</function> Function</title>
<para>
- The definitions of the columns exposed by the function are shown in <xref linkend="pgbuffercachesummary-columns"/>.
+ The definitions of the columns exposed by the function are shown in <xref linkend="pgbuffercache_summary-columns"/>.
</para>
- <table id="pgbuffercachesummary-columns">
- <title><structname>pg_buffercachesummary</structname> Columns</title>
+ <table id="pgbuffercache_summary-columns">
+ <title><function>pg_buffercache_summary()</function> Output Columns</title>
<tgroup cols="1">
<thead>
<row>
@@ -200,7 +206,7 @@
<structfield>buffers_used</structfield> <type>int4</type>
</para>
<para>
- Number of shared buffers currently being used
+ Number of unused shared buffers
</para></entry>
</row>
@@ -209,7 +215,7 @@
<structfield>buffers_unused</structfield> <type>int4</type>
</para>
<para>
- Number of shared buffers that not currently being used
+ Number of unused shared buffers
</para></entry>
</row>
@@ -227,13 +233,13 @@
<structfield>buffers_pinned</structfield> <type>int4</type>
</para>
<para>
- Number of shared buffers that has a pinned backend
+ Number of pinned shared buffers
</para></entry>
</row>
<row>
<entry role="catalog_table_entry"><para role="column_definition">
- <structfield>usagecount_avg</structfield> <type>float</type>
+ <structfield>usagecount_avg</structfield> <type>float8</type>
</para>
<para>
Average usagecount of used shared buffers
@@ -244,17 +250,18 @@
</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.
+ The <function>pg_buffercache_summary()</function> function returns a
+ single row summarizing the state of all shared buffers. Similar and more
+ detailed information is provided by the
+ <structname>pg_buffercache</structname> view, but
+ <function>pg_buffercache_summary()</function> is significantly cheaper.
</para>
<para>
- The <function>pg_buffercache_summary</function> doesn't provide a result
- that is consistent across all buffers. This is intentional. The purpose
- of this function is to provide a general idea about the state of shared
- buffers as fast as possible. Additionally, <function>pg_buffercache_summary</function>
- allocates much less memory.
+ Like the <structname>pg_buffercache</structname> view,
+ <function>pg_buffercache_summary()</function> does not acquire buffer
+ manager locks. Therefore concurrent activity can lead to minor inaccuracies
+ in the result.
</para>
</sect2>
--
2.38.0