From: Nathan Bossart <nathandbossart@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: pgsql-hackers@postgresql.org
Cc: schneider@ardentperf.com
Subject: Re: monitoring usage count distribution
Date: Thu, 6 Apr 2023 11:06:08 -0700
Message-ID: <20230406180608.GA275542@nathanxps13> (raw)
In-Reply-To: <2478091.1680802355@sss.pgh.pa.us>
References: <CA+Tgmoa-c3avHFBdko9d7bGQuYXrJhHHjhG1yAs8h-k_sLbyPQ@mail.gmail.com>
<20230404232919.uibzbhjdylk3mlvp@awork3.anarazel.de>
<CA+TgmobrQ90KGN4dwxL8q-LV529gCMmzXiy8CG5eQwjYTPzZLA@mail.gmail.com>
<20230405175124.GA31516@nathanxps13>
<CA+TgmoYRHDDJTgPcFkomz4KzzsQHXRJtqKeJyOk4AZNTjf=TnQ@mail.gmail.com>
<20230405190921.ck7v4y2h3ogoanvg@awork3.anarazel.de>
<20230405194101.GB105962@nathanxps13>
<2061855.1680725784@sss.pgh.pa.us>
<CA+TgmobRvYRxubzHY7PgnuYhJHPBGSF8a-6Ax+cz0Fm0EbxGgA@mail.gmail.com>
<2478091.1680802355@sss.pgh.pa.us>
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.comAttachments:download all (2 files, .tar.gz)v2-0001-introduce-pg_buffercache_usage_counts.patch (text/x-diff, 9.9K) download
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.outindex 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.sqlindex 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.cindex 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.sqlindex 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.sgmlindex 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.patch (text/x-diff, 5.5K) download
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.outindex 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.sqlindex 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.cindex 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.sqlindex 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.sgmlindex 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: nathandbossart@gmail.com, tgl@sss.pgh.pa.us, robertmhaas@gmail.com, andres@anarazel.de, schneider@ardentperf.com
Subject: Re: monitoring usage count distribution
In-Reply-To: <20230406180608.GA275542@nathanxps13>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox