monitoring usage count distribution
My colleague Jeremy Schneider (CC'd) was recently looking into usage count
distributions for various workloads, and he mentioned that it would be nice
to have an easy way to do $SUBJECT. I've attached a patch that adds a
pg_buffercache_usage_counts() function. This function returns a row per
possible usage count with some basic information about the corresponding
buffers.
postgres=# SELECT * FROM pg_buffercache_usage_counts();
usage_count | buffers | dirty | pinned
-------------+---------+-------+--------
0 | 0 | 0 | 0
1 | 1436 | 671 | 0
2 | 102 | 88 | 0
3 | 23 | 21 | 0
4 | 9 | 7 | 0
5 | 164 | 106 | 0
(6 rows)
This new function provides essentially the same information as
pg_buffercache_summary(), but pg_buffercache_summary() only shows the
average usage count for the buffers in use. If there is interest in this
idea, another approach to consider could be to alter
pg_buffercache_summary() instead.
Thoughts?
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
Attachments:
v1-0001-introduce-pg_buffercache_usage_counts.patchtext/x-diff; charset=us-asciiDownload
From b2fd87696185537d2cbb611cf70e743d7e197406 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathandbossart@gmail.com>
Date: Fri, 27 Jan 2023 16:39:43 -0800
Subject: [PATCH v1 1/1] introduce pg_buffercache_usage_counts()
---
.../expected/pg_buffercache.out | 14 +++
.../pg_buffercache--1.3--1.4.sql | 13 +++
contrib/pg_buffercache/pg_buffercache_pages.c | 46 ++++++++
contrib/pg_buffercache/sql/pg_buffercache.sql | 4 +
doc/src/sgml/pgbuffercache.sgml | 101 +++++++++++++++++-
5 files changed, 176 insertions(+), 2 deletions(-)
diff --git a/contrib/pg_buffercache/expected/pg_buffercache.out b/contrib/pg_buffercache/expected/pg_buffercache.out
index 635f01e3b2..b745dc69ea 100644
--- a/contrib/pg_buffercache/expected/pg_buffercache.out
+++ b/contrib/pg_buffercache/expected/pg_buffercache.out
@@ -17,6 +17,12 @@ from pg_buffercache_summary();
t | t | t
(1 row)
+SELECT count(*) > 0 FROM pg_buffercache_usage_counts() WHERE buffers >= 0;
+ ?column?
+----------
+ 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;
@@ -26,6 +32,8 @@ 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
+SELECT * FROM pg_buffercache_usage_counts();
+ERROR: permission denied for function pg_buffercache_usage_counts
RESET role;
-- Check that pg_monitor is allowed to query view / function
SET ROLE pg_monitor;
@@ -41,3 +49,9 @@ SELECT buffers_used + buffers_unused > 0 FROM pg_buffercache_summary();
t
(1 row)
+SELECT count(*) > 0 FROM pg_buffercache_usage_counts();
+ ?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 8f212dc5e9..f4702e4b4b 100644
--- a/contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql
+++ b/contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql
@@ -15,3 +15,16 @@ 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;
+
+CREATE FUNCTION pg_buffercache_usage_counts(
+ OUT usage_count int4,
+ OUT buffers int4,
+ OUT dirty int4,
+ OUT pinned int4)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'pg_buffercache_usage_counts'
+LANGUAGE C PARALLEL SAFE;
+
+-- Don't want these to be available to public.
+REVOKE ALL ON FUNCTION pg_buffercache_usage_counts() FROM PUBLIC;
+GRANT EXECUTE ON FUNCTION pg_buffercache_usage_counts() TO pg_monitor;
diff --git a/contrib/pg_buffercache/pg_buffercache_pages.c b/contrib/pg_buffercache/pg_buffercache_pages.c
index 1c6a2f22ca..f333967c51 100644
--- a/contrib/pg_buffercache/pg_buffercache_pages.c
+++ b/contrib/pg_buffercache/pg_buffercache_pages.c
@@ -18,6 +18,7 @@
#define NUM_BUFFERCACHE_PAGES_MIN_ELEM 8
#define NUM_BUFFERCACHE_PAGES_ELEM 9
#define NUM_BUFFERCACHE_SUMMARY_ELEM 5
+#define NUM_BUFFERCACHE_USAGE_COUNTS_ELEM 4
PG_MODULE_MAGIC;
@@ -61,6 +62,7 @@ typedef struct
*/
PG_FUNCTION_INFO_V1(pg_buffercache_pages);
PG_FUNCTION_INFO_V1(pg_buffercache_summary);
+PG_FUNCTION_INFO_V1(pg_buffercache_usage_counts);
Datum
pg_buffercache_pages(PG_FUNCTION_ARGS)
@@ -304,3 +306,47 @@ pg_buffercache_summary(PG_FUNCTION_ARGS)
PG_RETURN_DATUM(result);
}
+
+Datum
+pg_buffercache_usage_counts(PG_FUNCTION_ARGS)
+{
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ int usage_counts[BM_MAX_USAGE_COUNT + 1] = {0};
+ int dirty[BM_MAX_USAGE_COUNT + 1] = {0};
+ int pinned[BM_MAX_USAGE_COUNT + 1] = {0};
+ Datum values[NUM_BUFFERCACHE_USAGE_COUNTS_ELEM];
+ bool nulls[NUM_BUFFERCACHE_USAGE_COUNTS_ELEM] = {0};
+
+ InitMaterializedSRF(fcinfo, 0);
+
+ for (int i = 0; i < NBuffers; i++)
+ {
+ BufferDesc *bufHdr = GetBufferDescriptor(i);
+ uint32 buf_state = pg_atomic_read_u32(&bufHdr->state);
+ int usage_count;
+
+ if ((buf_state & BM_VALID) == 0)
+ continue;
+
+ usage_count = BUF_STATE_GET_USAGECOUNT(buf_state);
+ usage_counts[usage_count]++;
+
+ if (buf_state & BM_DIRTY)
+ dirty[usage_count]++;
+
+ if (BUF_STATE_GET_REFCOUNT(buf_state) > 0)
+ pinned[usage_count]++;
+ }
+
+ for (int i = 0; i < BM_MAX_USAGE_COUNT + 1; i++)
+ {
+ values[0] = Int32GetDatum(i);
+ values[1] = Int32GetDatum(usage_counts[i]);
+ values[2] = Int32GetDatum(dirty[i]);
+ values[3] = Int32GetDatum(pinned[i]);
+
+ tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
+ }
+
+ return (Datum) 0;
+}
diff --git a/contrib/pg_buffercache/sql/pg_buffercache.sql b/contrib/pg_buffercache/sql/pg_buffercache.sql
index 2e2e0a7451..944fbb1bea 100644
--- a/contrib/pg_buffercache/sql/pg_buffercache.sql
+++ b/contrib/pg_buffercache/sql/pg_buffercache.sql
@@ -10,15 +10,19 @@ select buffers_used + buffers_unused > 0,
buffers_pinned <= buffers_used
from pg_buffercache_summary();
+SELECT count(*) > 0 FROM pg_buffercache_usage_counts() WHERE buffers >= 0;
+
-- 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();
+SELECT * FROM pg_buffercache_usage_counts();
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();
+SELECT count(*) > 0 FROM pg_buffercache_usage_counts();
diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml
index f5d1901af2..29db3f613b 100644
--- a/doc/src/sgml/pgbuffercache.sgml
+++ b/doc/src/sgml/pgbuffercache.sgml
@@ -22,8 +22,9 @@
<para>
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.
+ function, wrapped in the <structname>pg_buffercache</structname> view,
+ the <function>pg_buffercache_summary()</function> function, and the
+ <function>pg_buffercache_usage_counts()</function> function.
</para>
<para>
@@ -38,6 +39,12 @@
row summarizing the state of the shared buffer cache.
</para>
+ <para>
+ The <function>pg_buffercache_usage_counts()</function> function returns a set
+ of records, each row describing the number of buffers with a given usage
+ count.
+ </para>
+
<para>
By default, use is restricted to superusers and roles with privileges of the
<literal>pg_monitor</literal> role. Access may be granted to others
@@ -265,6 +272,84 @@
</para>
</sect2>
+ <sect2>
+ <title>The <function>pg_buffercache_usage_counts()</function> Function</title>
+
+ <para>
+ The definitions of the columns exposed by the function are shown in
+ <xref linkend="pgbuffercache_usage_counts-columns"/>.
+ </para>
+
+ <table id="pgbuffercache_usage_counts-columns">
+ <title><function>pg_buffercache_usage_counts()</function> Output 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>usage_count</structfield> <type>int4</type>
+ </para>
+ <para>
+ A usage count
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>buffers</structfield> <type>int4</type>
+ </para>
+ <para>
+ Number of buffers with the usage count
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>dirty</structfield> <type>int4</type>
+ </para>
+ <para>
+ Number of dirty buffers with the usage count
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>pinned</structfield> <type>int4</type>
+ </para>
+ <para>
+ Number of pinned buffers with the usage count
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ The <function>pg_buffercache_usage_counts()</function> function returns a
+ set of rows summarizing the usage counts of all shared buffers. Similar and
+ more detailed information is provided by the
+ <structname>pg_buffercache</structname> view, but
+ <function>pg_buffercache_usage_counts()</function> is significantly cheaper.
+ </para>
+
+ <para>
+ Like the <structname>pg_buffercache</structname> view,
+ <function>pg_buffercache_usage_counts()</function> does not acquire buffer
+ manager locks. Therefore concurrent activity can lead to minor inaccuracies
+ in the result.
+ </para>
+ </sect2>
+
<sect2 id="pgbuffercache-sample-output">
<title>Sample Output</title>
@@ -299,6 +384,18 @@ regression=# SELECT * FROM pg_buffercache_summary();
--------------+----------------+---------------+----------------+----------------
248 | 2096904 | 39 | 0 | 3.141129
(1 row)
+
+
+regression=# SELECT * FROM pg_buffercache_usage_counts();
+ usage_count | buffers | dirty | pinned
+-------------+---------+-------+--------
+ 0 | 0 | 0 | 0
+ 1 | 1436 | 671 | 0
+ 2 | 102 | 88 | 0
+ 3 | 23 | 21 | 0
+ 4 | 9 | 7 | 0
+ 5 | 164 | 106 | 0
+(6 rows)
</screen>
</sect2>
--
2.25.1
On Mon, 30 Jan 2023 at 18:31, Nathan Bossart <nathandbossart@gmail.com> wrote:
My colleague Jeremy Schneider (CC'd) was recently looking into usage count
distributions for various workloads, and he mentioned that it would be nice
to have an easy way to do $SUBJECT. I've attached a patch that adds a
pg_buffercache_usage_counts() function. This function returns a row per
possible usage count with some basic information about the corresponding
buffers.postgres=# SELECT * FROM pg_buffercache_usage_counts();
usage_count | buffers | dirty | pinned
-------------+---------+-------+--------
0 | 0 | 0 | 0
1 | 1436 | 671 | 0
2 | 102 | 88 | 0
3 | 23 | 21 | 0
4 | 9 | 7 | 0
5 | 164 | 106 | 0
(6 rows)This new function provides essentially the same information as
pg_buffercache_summary(), but pg_buffercache_summary() only shows the
average usage count for the buffers in use. If there is interest in this
idea, another approach to consider could be to alter
pg_buffercache_summary() instead.
Tom expressed skepticism that there's wide interest here. It seems as
much from the lack of response. But perhaps that's just because people
don't understand what the importance of this info is -- I certainly
don't :)
I feel like the original sin here is having the function return an
aggregate data. If it returned the raw data then people could slice,
dice, and aggregate the data in any ways they want using SQL. And
perhaps people would come up with queries that have more readily
interpretable important information?
Obviously there are performance questions in that but I suspect they
might be solvable given how small the data for each buffer are.
Just as a warning though -- if nobody was interested in this patch
please don't take my comments as a recommendation that you spend a lot
of time developing a more complex version in the same direction
without seeing if anyone agrees with my suggestion :)
--
greg
On Mon, Jan 30, 2023 at 6:30 PM Nathan Bossart <nathandbossart@gmail.com> wrote:
My colleague Jeremy Schneider (CC'd) was recently looking into usage count
distributions for various workloads, and he mentioned that it would be nice
to have an easy way to do $SUBJECT. I've attached a patch that adds a
pg_buffercache_usage_counts() function. This function returns a row per
possible usage count with some basic information about the corresponding
buffers.postgres=# SELECT * FROM pg_buffercache_usage_counts();
usage_count | buffers | dirty | pinned
-------------+---------+-------+--------
0 | 0 | 0 | 0
1 | 1436 | 671 | 0
2 | 102 | 88 | 0
3 | 23 | 21 | 0
4 | 9 | 7 | 0
5 | 164 | 106 | 0
(6 rows)This new function provides essentially the same information as
pg_buffercache_summary(), but pg_buffercache_summary() only shows the
average usage count for the buffers in use. If there is interest in this
idea, another approach to consider could be to alter
pg_buffercache_summary() instead.
I'm skeptical that pg_buffercache_summary() is a good idea at all, but
having it display the average usage count seems like a particularly
poor idea. That information is almost meaningless. Replacing that with
a six-element integer array would be a clear improvement and, IMHO,
better than adding yet another function to the extension.
--
Robert Haas
EDB: http://www.enterprisedb.com
Robert Haas <robertmhaas@gmail.com> writes:
On Mon, Jan 30, 2023 at 6:30 PM Nathan Bossart <nathandbossart@gmail.com> wrote:
My colleague Jeremy Schneider (CC'd) was recently looking into usage count
distributions for various workloads, and he mentioned that it would be nice
to have an easy way to do $SUBJECT.
I'm skeptical that pg_buffercache_summary() is a good idea at all, but
having it display the average usage count seems like a particularly
poor idea. That information is almost meaningless. Replacing that with
a six-element integer array would be a clear improvement and, IMHO,
better than adding yet another function to the extension.
I had not realized that pg_buffercache_summary() is new in v16,
but since it is, we still have time to rethink its definition.
+1 for de-aggregating --- I agree that the overall average is
unlikely to have much value.
regards, tom lane
On Tue, Apr 4, 2023 at 2:40 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Mon, Jan 30, 2023 at 6:30 PM Nathan Bossart <nathandbossart@gmail.com> wrote:
My colleague Jeremy Schneider (CC'd) was recently looking into usage count
distributions for various workloads, and he mentioned that it would be nice
to have an easy way to do $SUBJECT.I'm skeptical that pg_buffercache_summary() is a good idea at all, but
having it display the average usage count seems like a particularly
poor idea. That information is almost meaningless. Replacing that with
a six-element integer array would be a clear improvement and, IMHO,
better than adding yet another function to the extension.I had not realized that pg_buffercache_summary() is new in v16,
but since it is, we still have time to rethink its definition.
+1 for de-aggregating --- I agree that the overall average is
unlikely to have much value.
So, I have used pg_buffercache_summary() to give me a high-level idea of
the usage count when I am benchmarking a particular workload -- and I
would have found it harder to look at 6 rows instead of 1. That being
said, having six rows is more versatile as you could aggregate it
yourself easily.
- Melanie
Hi,
On 2023-04-04 14:14:36 -0400, Greg Stark wrote:
Tom expressed skepticism that there's wide interest here. It seems as
much from the lack of response. But perhaps that's just because people
don't understand what the importance of this info is -- I certainly
don't :)
pg_buffercache has exposed the raw data for a long time. The problem is that
it's way too slow to look at that way.
Greetings,
Andres Freund
Hi,
On 2023-04-04 14:31:36 -0400, Robert Haas wrote:
On Mon, Jan 30, 2023 at 6:30 PM Nathan Bossart <nathandbossart@gmail.com> wrote:
My colleague Jeremy Schneider (CC'd) was recently looking into usage count
distributions for various workloads, and he mentioned that it would be nice
to have an easy way to do $SUBJECT. I've attached a patch that adds a
pg_buffercache_usage_counts() function. This function returns a row per
possible usage count with some basic information about the corresponding
buffers.postgres=# SELECT * FROM pg_buffercache_usage_counts();
usage_count | buffers | dirty | pinned
-------------+---------+-------+--------
0 | 0 | 0 | 0
1 | 1436 | 671 | 0
2 | 102 | 88 | 0
3 | 23 | 21 | 0
4 | 9 | 7 | 0
5 | 164 | 106 | 0
(6 rows)This new function provides essentially the same information as
pg_buffercache_summary(), but pg_buffercache_summary() only shows the
average usage count for the buffers in use. If there is interest in this
idea, another approach to consider could be to alter
pg_buffercache_summary() instead.I'm skeptical that pg_buffercache_summary() is a good idea at all
Why? It's about two orders of magnitude faster than querying the equivalent
data by aggregating in SQL. And knowing how many free and dirty buffers are
over time is something quite useful to monitor / correlate with performance
issues.
but having it display the average usage count seems like a particularly poor
idea. That information is almost meaningless.
I agree there are more meaningful ways to represent the data, but I don't
agree that it's almost meaningless. It can give you a rough estimate of
whether data in s_b is referenced or not.
Replacing that with a six-element integer array would be a clear improvement
and, IMHO, better than adding yet another function to the extension.
I'd have no issue with that.
Greetings,
Andres Freund
On Tue, Apr 4, 2023 at 7:29 PM Andres Freund <andres@anarazel.de> wrote:
I'm skeptical that pg_buffercache_summary() is a good idea at all
Why? It's about two orders of magnitude faster than querying the equivalent
data by aggregating in SQL. And knowing how many free and dirty buffers are
over time is something quite useful to monitor / correlate with performance
issues.
Well, OK, fair point.
but having it display the average usage count seems like a particularly poor
idea. That information is almost meaningless.I agree there are more meaningful ways to represent the data, but I don't
agree that it's almost meaningless. It can give you a rough estimate of
whether data in s_b is referenced or not.
I might have overstated my case.
Replacing that with a six-element integer array would be a clear improvement
and, IMHO, better than adding yet another function to the extension.I'd have no issue with that.
Cool.
--
Robert Haas
EDB: http://www.enterprisedb.com
On Wed, Apr 05, 2023 at 09:44:58AM -0400, Robert Haas wrote:
On Tue, Apr 4, 2023 at 7:29 PM Andres Freund <andres@anarazel.de> wrote:
Replacing that with a six-element integer array would be a clear improvement
and, IMHO, better than adding yet another function to the extension.I'd have no issue with that.
Cool.
The six-element array approach won't show the number of dirty and pinned
buffers for each usage count, but I'm not sure that's a deal-breaker.
Barring objections, I'll post an updated patch shortly with that approach.
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
On Wed, Apr 5, 2023 at 1:51 PM Nathan Bossart <nathandbossart@gmail.com> wrote:
On Wed, Apr 05, 2023 at 09:44:58AM -0400, Robert Haas wrote:
On Tue, Apr 4, 2023 at 7:29 PM Andres Freund <andres@anarazel.de> wrote:
Replacing that with a six-element integer array would be a clear improvement
and, IMHO, better than adding yet another function to the extension.I'd have no issue with that.
Cool.
The six-element array approach won't show the number of dirty and pinned
buffers for each usage count, but I'm not sure that's a deal-breaker.
Barring objections, I'll post an updated patch shortly with that approach.
Right, well, I would personally be OK with 6 rows too, but I don't
know what other people want. I think either this or that is better
than average.
--
Robert Haas
EDB: http://www.enterprisedb.com
Robert Haas <robertmhaas@gmail.com> writes:
On Wed, Apr 5, 2023 at 1:51 PM Nathan Bossart <nathandbossart@gmail.com> wrote:
The six-element array approach won't show the number of dirty and pinned
buffers for each usage count, but I'm not sure that's a deal-breaker.
Barring objections, I'll post an updated patch shortly with that approach.
Right, well, I would personally be OK with 6 rows too, but I don't
know what other people want. I think either this or that is better
than average.
Seems to me that six rows would be easier to aggregate manually.
An array column seems less SQL-ish and harder to manipulate.
regards, tom lane
Hi,
On 2023-04-05 15:00:20 -0400, Robert Haas wrote:
On Wed, Apr 5, 2023 at 1:51 PM Nathan Bossart <nathandbossart@gmail.com> wrote:
On Wed, Apr 05, 2023 at 09:44:58AM -0400, Robert Haas wrote:
On Tue, Apr 4, 2023 at 7:29 PM Andres Freund <andres@anarazel.de> wrote:
Replacing that with a six-element integer array would be a clear improvement
and, IMHO, better than adding yet another function to the extension.I'd have no issue with that.
Cool.
The six-element array approach won't show the number of dirty and pinned
buffers for each usage count, but I'm not sure that's a deal-breaker.
Barring objections, I'll post an updated patch shortly with that approach.Right, well, I would personally be OK with 6 rows too, but I don't
know what other people want. I think either this or that is better
than average.
I would not mind having a separate function returning 6 rows, if we really
want that, but making pg_buffercache_summary() return 6 rows would imo make it
less useful for getting a quick overview. At least I am not that quick summing
up multple rows, just to get a quick overview over the number of dirty rows.
Greetings,
Andres Freund
On Wed, Apr 05, 2023 at 03:07:10PM -0400, Tom Lane wrote:
Seems to me that six rows would be easier to aggregate manually.
An array column seems less SQL-ish and harder to manipulate.
+1
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
On Wed, Apr 05, 2023 at 12:09:21PM -0700, Andres Freund wrote:
I would not mind having a separate function returning 6 rows, if we really
want that, but making pg_buffercache_summary() return 6 rows would imo make it
less useful for getting a quick overview. At least I am not that quick summing
up multple rows, just to get a quick overview over the number of dirty rows.
This is what v1-0001 does. We could probably make pg_buffercache_summary a
view on pg_buffercache_usage_counts, too, but that doesn't strike me as
tremendously important.
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
Nathan Bossart <nathandbossart@gmail.com> writes:
On Wed, Apr 05, 2023 at 12:09:21PM -0700, Andres Freund wrote:
I would not mind having a separate function returning 6 rows, if we really
want that, but making pg_buffercache_summary() return 6 rows would imo make it
less useful for getting a quick overview. At least I am not that quick summing
up multple rows, just to get a quick overview over the number of dirty rows.
This is what v1-0001 does. We could probably make pg_buffercache_summary a
view on pg_buffercache_usage_counts, too, but that doesn't strike me as
tremendously important.
Having two functions doesn't seem unreasonable to me either.
Robert spoke against it to start with, does he still want to
advocate for that?
regards, tom lane
On Wed, Apr 5, 2023 at 4:16 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Nathan Bossart <nathandbossart@gmail.com> writes:
On Wed, Apr 05, 2023 at 12:09:21PM -0700, Andres Freund wrote:
I would not mind having a separate function returning 6 rows, if we really
want that, but making pg_buffercache_summary() return 6 rows would imo make it
less useful for getting a quick overview. At least I am not that quick summing
up multple rows, just to get a quick overview over the number of dirty rows.This is what v1-0001 does. We could probably make pg_buffercache_summary a
view on pg_buffercache_usage_counts, too, but that doesn't strike me as
tremendously important.Having two functions doesn't seem unreasonable to me either.
Robert spoke against it to start with, does he still want to
advocate for that?
My position is that if we replace the average usage count with
something that gives a count for each usage count, that's a win. I
don't have a strong opinion on an array vs. a result set vs. some
other way of doing that. If we leave the average usage count in there
and add yet another function to give the detail, I tend to think
that's not a great plan, but I'll desist if everyone else thinks
otherwise.
--
Robert Haas
EDB: http://www.enterprisedb.com
Robert Haas <robertmhaas@gmail.com> writes:
On Wed, Apr 5, 2023 at 4:16 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Having two functions doesn't seem unreasonable to me either.
Robert spoke against it to start with, does he still want to
advocate for that?
My position is that if we replace the average usage count with
something that gives a count for each usage count, that's a win. I
don't have a strong opinion on an array vs. a result set vs. some
other way of doing that. If we leave the average usage count in there
and add yet another function to give the detail, I tend to think
that's not a great plan, but I'll desist if everyone else thinks
otherwise.
There seems to be enough support for the existing summary function
definition to leave it as-is; Andres likes it for one, and I'm not
excited about trying to persuade him he's wrong. But a second
slightly-less-aggregated summary function is clearly useful as well.
So I'm now thinking that we do want the patch as-submitted.
(Caveat: I've not read the patch, just the description.)
regards, tom lane
On Thu, Apr 06, 2023 at 01:32:35PM -0400, Tom Lane wrote:
There seems to be enough support for the existing summary function
definition to leave it as-is; Andres likes it for one, and I'm not
excited about trying to persuade him he's wrong. But a second
slightly-less-aggregated summary function is clearly useful as well.
So I'm now thinking that we do want the patch as-submitted.
(Caveat: I've not read the patch, just the description.)
In case we want to do both, here's a 0002 that changes usagecount_avg to an
array of usage counts.
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
Attachments:
v2-0001-introduce-pg_buffercache_usage_counts.patchtext/x-diff; charset=us-asciiDownload
From 6ad6a8e3a9ed0d0265e1869c0eaa793881c2fa77 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathandbossart@gmail.com>
Date: Fri, 27 Jan 2023 16:39:43 -0800
Subject: [PATCH v2 1/2] introduce pg_buffercache_usage_counts()
---
.../expected/pg_buffercache.out | 14 +++
.../pg_buffercache--1.3--1.4.sql | 13 +++
contrib/pg_buffercache/pg_buffercache_pages.c | 46 ++++++++
contrib/pg_buffercache/sql/pg_buffercache.sql | 4 +
doc/src/sgml/pgbuffercache.sgml | 101 +++++++++++++++++-
5 files changed, 176 insertions(+), 2 deletions(-)
diff --git a/contrib/pg_buffercache/expected/pg_buffercache.out b/contrib/pg_buffercache/expected/pg_buffercache.out
index 635f01e3b2..b745dc69ea 100644
--- a/contrib/pg_buffercache/expected/pg_buffercache.out
+++ b/contrib/pg_buffercache/expected/pg_buffercache.out
@@ -17,6 +17,12 @@ from pg_buffercache_summary();
t | t | t
(1 row)
+SELECT count(*) > 0 FROM pg_buffercache_usage_counts() WHERE buffers >= 0;
+ ?column?
+----------
+ 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;
@@ -26,6 +32,8 @@ 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
+SELECT * FROM pg_buffercache_usage_counts();
+ERROR: permission denied for function pg_buffercache_usage_counts
RESET role;
-- Check that pg_monitor is allowed to query view / function
SET ROLE pg_monitor;
@@ -41,3 +49,9 @@ SELECT buffers_used + buffers_unused > 0 FROM pg_buffercache_summary();
t
(1 row)
+SELECT count(*) > 0 FROM pg_buffercache_usage_counts();
+ ?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 8f212dc5e9..f4702e4b4b 100644
--- a/contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql
+++ b/contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql
@@ -15,3 +15,16 @@ 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;
+
+CREATE FUNCTION pg_buffercache_usage_counts(
+ OUT usage_count int4,
+ OUT buffers int4,
+ OUT dirty int4,
+ OUT pinned int4)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'pg_buffercache_usage_counts'
+LANGUAGE C PARALLEL SAFE;
+
+-- Don't want these to be available to public.
+REVOKE ALL ON FUNCTION pg_buffercache_usage_counts() FROM PUBLIC;
+GRANT EXECUTE ON FUNCTION pg_buffercache_usage_counts() TO pg_monitor;
diff --git a/contrib/pg_buffercache/pg_buffercache_pages.c b/contrib/pg_buffercache/pg_buffercache_pages.c
index 1c6a2f22ca..f333967c51 100644
--- a/contrib/pg_buffercache/pg_buffercache_pages.c
+++ b/contrib/pg_buffercache/pg_buffercache_pages.c
@@ -18,6 +18,7 @@
#define NUM_BUFFERCACHE_PAGES_MIN_ELEM 8
#define NUM_BUFFERCACHE_PAGES_ELEM 9
#define NUM_BUFFERCACHE_SUMMARY_ELEM 5
+#define NUM_BUFFERCACHE_USAGE_COUNTS_ELEM 4
PG_MODULE_MAGIC;
@@ -61,6 +62,7 @@ typedef struct
*/
PG_FUNCTION_INFO_V1(pg_buffercache_pages);
PG_FUNCTION_INFO_V1(pg_buffercache_summary);
+PG_FUNCTION_INFO_V1(pg_buffercache_usage_counts);
Datum
pg_buffercache_pages(PG_FUNCTION_ARGS)
@@ -304,3 +306,47 @@ pg_buffercache_summary(PG_FUNCTION_ARGS)
PG_RETURN_DATUM(result);
}
+
+Datum
+pg_buffercache_usage_counts(PG_FUNCTION_ARGS)
+{
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ int usage_counts[BM_MAX_USAGE_COUNT + 1] = {0};
+ int dirty[BM_MAX_USAGE_COUNT + 1] = {0};
+ int pinned[BM_MAX_USAGE_COUNT + 1] = {0};
+ Datum values[NUM_BUFFERCACHE_USAGE_COUNTS_ELEM];
+ bool nulls[NUM_BUFFERCACHE_USAGE_COUNTS_ELEM] = {0};
+
+ InitMaterializedSRF(fcinfo, 0);
+
+ for (int i = 0; i < NBuffers; i++)
+ {
+ BufferDesc *bufHdr = GetBufferDescriptor(i);
+ uint32 buf_state = pg_atomic_read_u32(&bufHdr->state);
+ int usage_count;
+
+ if ((buf_state & BM_VALID) == 0)
+ continue;
+
+ usage_count = BUF_STATE_GET_USAGECOUNT(buf_state);
+ usage_counts[usage_count]++;
+
+ if (buf_state & BM_DIRTY)
+ dirty[usage_count]++;
+
+ if (BUF_STATE_GET_REFCOUNT(buf_state) > 0)
+ pinned[usage_count]++;
+ }
+
+ for (int i = 0; i < BM_MAX_USAGE_COUNT + 1; i++)
+ {
+ values[0] = Int32GetDatum(i);
+ values[1] = Int32GetDatum(usage_counts[i]);
+ values[2] = Int32GetDatum(dirty[i]);
+ values[3] = Int32GetDatum(pinned[i]);
+
+ tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
+ }
+
+ return (Datum) 0;
+}
diff --git a/contrib/pg_buffercache/sql/pg_buffercache.sql b/contrib/pg_buffercache/sql/pg_buffercache.sql
index 2e2e0a7451..944fbb1bea 100644
--- a/contrib/pg_buffercache/sql/pg_buffercache.sql
+++ b/contrib/pg_buffercache/sql/pg_buffercache.sql
@@ -10,15 +10,19 @@ select buffers_used + buffers_unused > 0,
buffers_pinned <= buffers_used
from pg_buffercache_summary();
+SELECT count(*) > 0 FROM pg_buffercache_usage_counts() WHERE buffers >= 0;
+
-- 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();
+SELECT * FROM pg_buffercache_usage_counts();
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();
+SELECT count(*) > 0 FROM pg_buffercache_usage_counts();
diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml
index f49d197c5f..acd88aa813 100644
--- a/doc/src/sgml/pgbuffercache.sgml
+++ b/doc/src/sgml/pgbuffercache.sgml
@@ -23,8 +23,9 @@
<para>
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.
+ function, wrapped in the <structname>pg_buffercache</structname> view,
+ the <function>pg_buffercache_summary()</function> function, and the
+ <function>pg_buffercache_usage_counts()</function> function.
</para>
<para>
@@ -39,6 +40,12 @@
row summarizing the state of the shared buffer cache.
</para>
+ <para>
+ The <function>pg_buffercache_usage_counts()</function> function returns a set
+ of records, each row describing the number of buffers with a given usage
+ count.
+ </para>
+
<para>
By default, use is restricted to superusers and roles with privileges of the
<literal>pg_monitor</literal> role. Access may be granted to others
@@ -266,6 +273,84 @@
</para>
</sect2>
+ <sect2>
+ <title>The <function>pg_buffercache_usage_counts()</function> Function</title>
+
+ <para>
+ The definitions of the columns exposed by the function are shown in
+ <xref linkend="pgbuffercache_usage_counts-columns"/>.
+ </para>
+
+ <table id="pgbuffercache_usage_counts-columns">
+ <title><function>pg_buffercache_usage_counts()</function> Output 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>usage_count</structfield> <type>int4</type>
+ </para>
+ <para>
+ A usage count
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>buffers</structfield> <type>int4</type>
+ </para>
+ <para>
+ Number of buffers with the usage count
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>dirty</structfield> <type>int4</type>
+ </para>
+ <para>
+ Number of dirty buffers with the usage count
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>pinned</structfield> <type>int4</type>
+ </para>
+ <para>
+ Number of pinned buffers with the usage count
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ The <function>pg_buffercache_usage_counts()</function> function returns a
+ set of rows summarizing the usage counts of all shared buffers. Similar and
+ more detailed information is provided by the
+ <structname>pg_buffercache</structname> view, but
+ <function>pg_buffercache_usage_counts()</function> is significantly cheaper.
+ </para>
+
+ <para>
+ Like the <structname>pg_buffercache</structname> view,
+ <function>pg_buffercache_usage_counts()</function> does not acquire buffer
+ manager locks. Therefore concurrent activity can lead to minor inaccuracies
+ in the result.
+ </para>
+ </sect2>
+
<sect2 id="pgbuffercache-sample-output">
<title>Sample Output</title>
@@ -300,6 +385,18 @@ regression=# SELECT * FROM pg_buffercache_summary();
--------------+----------------+---------------+----------------+----------------
248 | 2096904 | 39 | 0 | 3.141129
(1 row)
+
+
+regression=# SELECT * FROM pg_buffercache_usage_counts();
+ usage_count | buffers | dirty | pinned
+-------------+---------+-------+--------
+ 0 | 0 | 0 | 0
+ 1 | 1436 | 671 | 0
+ 2 | 102 | 88 | 0
+ 3 | 23 | 21 | 0
+ 4 | 9 | 7 | 0
+ 5 | 164 | 106 | 0
+(6 rows)
</screen>
</sect2>
--
2.25.1
v2-0002-replace-usagecount_avg-with-an-array-of-usage-cou.patchtext/x-diff; charset=us-asciiDownload
From 2459cc7c49ebeec872022ae2d2099182e5200be4 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathandbossart@gmail.com>
Date: Thu, 6 Apr 2023 11:04:07 -0700
Subject: [PATCH v2 2/2] replace usagecount_avg with an array of usage counts
---
.../pg_buffercache/expected/pg_buffercache.out | 9 +++++----
.../pg_buffercache/pg_buffercache--1.3--1.4.sql | 2 +-
contrib/pg_buffercache/pg_buffercache_pages.c | 16 ++++++++++------
contrib/pg_buffercache/sql/pg_buffercache.sql | 3 ++-
doc/src/sgml/pgbuffercache.sgml | 10 +++++-----
5 files changed, 23 insertions(+), 17 deletions(-)
diff --git a/contrib/pg_buffercache/expected/pg_buffercache.out b/contrib/pg_buffercache/expected/pg_buffercache.out
index b745dc69ea..a5921b83c5 100644
--- a/contrib/pg_buffercache/expected/pg_buffercache.out
+++ b/contrib/pg_buffercache/expected/pg_buffercache.out
@@ -10,11 +10,12 @@ from pg_buffercache;
select buffers_used + buffers_unused > 0,
buffers_dirty <= buffers_used,
- buffers_pinned <= buffers_used
+ buffers_pinned <= buffers_used,
+ array_length(usagecounts, 1) > 0
from pg_buffercache_summary();
- ?column? | ?column? | ?column?
-----------+----------+----------
- t | t | t
+ ?column? | ?column? | ?column? | ?column?
+----------+----------+----------+----------
+ t | t | t | t
(1 row)
SELECT count(*) > 0 FROM pg_buffercache_usage_counts() WHERE buffers >= 0;
diff --git a/contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql b/contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql
index f4702e4b4b..9120ad6ffb 100644
--- a/contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql
+++ b/contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql
@@ -8,7 +8,7 @@ CREATE FUNCTION pg_buffercache_summary(
OUT buffers_unused int4,
OUT buffers_dirty int4,
OUT buffers_pinned int4,
- OUT usagecount_avg float8)
+ OUT usagecounts int4[])
AS 'MODULE_PATHNAME', 'pg_buffercache_summary'
LANGUAGE C PARALLEL SAFE;
diff --git a/contrib/pg_buffercache/pg_buffercache_pages.c b/contrib/pg_buffercache/pg_buffercache_pages.c
index f333967c51..da3594cc7d 100644
--- a/contrib/pg_buffercache/pg_buffercache_pages.c
+++ b/contrib/pg_buffercache/pg_buffercache_pages.c
@@ -13,6 +13,7 @@
#include "funcapi.h"
#include "storage/buf_internals.h"
#include "storage/bufmgr.h"
+#include "utils/array.h"
#define NUM_BUFFERCACHE_PAGES_MIN_ELEM 8
@@ -255,7 +256,9 @@ pg_buffercache_summary(PG_FUNCTION_ARGS)
int32 buffers_unused = 0;
int32 buffers_dirty = 0;
int32 buffers_pinned = 0;
- int64 usagecount_total = 0;
+
+ int32 usagecounts[BM_MAX_USAGE_COUNT + 1] = {0};
+ Datum ucdata[BM_MAX_USAGE_COUNT + 1];
if (get_call_result_type(fcinfo, NULL, &tupledesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
@@ -277,7 +280,7 @@ pg_buffercache_summary(PG_FUNCTION_ARGS)
if (buf_state & BM_VALID)
{
buffers_used++;
- usagecount_total += BUF_STATE_GET_USAGECOUNT(buf_state);
+ usagecounts[BUF_STATE_GET_USAGECOUNT(buf_state)]++;
if (buf_state & BM_DIRTY)
buffers_dirty++;
@@ -295,10 +298,11 @@ pg_buffercache_summary(PG_FUNCTION_ARGS)
values[2] = Int32GetDatum(buffers_dirty);
values[3] = Int32GetDatum(buffers_pinned);
- if (buffers_used != 0)
- values[4] = Float8GetDatum((double) usagecount_total / buffers_used);
- else
- nulls[4] = true;
+ for (int i = 0; i < BM_MAX_USAGE_COUNT + 1; i++)
+ ucdata[i] = Int32GetDatum(usagecounts[i]);
+ values[4] = PointerGetDatum(construct_array(ucdata, BM_MAX_USAGE_COUNT + 1,
+ INT4OID, sizeof(int32), true,
+ TYPALIGN_INT));
/* Build and return the tuple. */
tuple = heap_form_tuple(tupledesc, values, nulls);
diff --git a/contrib/pg_buffercache/sql/pg_buffercache.sql b/contrib/pg_buffercache/sql/pg_buffercache.sql
index 944fbb1bea..b5288eb283 100644
--- a/contrib/pg_buffercache/sql/pg_buffercache.sql
+++ b/contrib/pg_buffercache/sql/pg_buffercache.sql
@@ -7,7 +7,8 @@ from pg_buffercache;
select buffers_used + buffers_unused > 0,
buffers_dirty <= buffers_used,
- buffers_pinned <= buffers_used
+ buffers_pinned <= buffers_used,
+ array_length(usagecounts, 1) > 0
from pg_buffercache_summary();
SELECT count(*) > 0 FROM pg_buffercache_usage_counts() WHERE buffers >= 0;
diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml
index acd88aa813..74b1e92637 100644
--- a/doc/src/sgml/pgbuffercache.sgml
+++ b/doc/src/sgml/pgbuffercache.sgml
@@ -247,10 +247,10 @@
<row>
<entry role="catalog_table_entry"><para role="column_definition">
- <structfield>usagecount_avg</structfield> <type>float8</type>
+ <structfield>usagecounts</structfield> <type>int4[]</type>
</para>
<para>
- Average usagecount of used shared buffers
+ Number of used shared buffers for each possible usagecount
</para></entry>
</row>
</tbody>
@@ -381,9 +381,9 @@ regression=# SELECT n.nspname, c.relname, count(*) AS buffers
regression=# SELECT * FROM pg_buffercache_summary();
- buffers_used | buffers_unused | buffers_dirty | buffers_pinned | usagecount_avg
---------------+----------------+---------------+----------------+----------------
- 248 | 2096904 | 39 | 0 | 3.141129
+ buffers_used | buffers_unused | buffers_dirty | buffers_pinned | usagecounts
+--------------+----------------+---------------+----------------+------------------
+ 184 | 2096968 | 44 | 0 | {0,69,15,8,5,87}
(1 row)
--
2.25.1
Nathan Bossart <nathandbossart@gmail.com> writes:
On Thu, Apr 06, 2023 at 01:32:35PM -0400, Tom Lane wrote:
There seems to be enough support for the existing summary function
definition to leave it as-is; Andres likes it for one, and I'm not
excited about trying to persuade him he's wrong. But a second
slightly-less-aggregated summary function is clearly useful as well.
So I'm now thinking that we do want the patch as-submitted.
(Caveat: I've not read the patch, just the description.)
In case we want to do both, here's a 0002 that changes usagecount_avg to an
array of usage counts.
I'm not sure if there is consensus for 0002, but I reviewed and pushed
0001. I made one non-cosmetic change: it no longer skips invalid
buffers. Otherwise, the row for usage count 0 would be pretty useless.
Also it seemed to me that sum(buffers) ought to agree with the
shared_buffers setting.
regards, tom lane
On Fri, Apr 07, 2023 at 02:29:31PM -0400, Tom Lane wrote:
I'm not sure if there is consensus for 0002, but I reviewed and pushed
0001. I made one non-cosmetic change: it no longer skips invalid
buffers. Otherwise, the row for usage count 0 would be pretty useless.
Also it seemed to me that sum(buffers) ought to agree with the
shared_buffers setting.
Makes sense. Thanks!
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com