Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pkU04-00054W-Uy for pgsql-hackers@arkaria.postgresql.org; Thu, 06 Apr 2023 18:06:21 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pkU03-0006MC-OP for pgsql-hackers@arkaria.postgresql.org; Thu, 06 Apr 2023 18:06:19 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pkU03-0006M3-54 for pgsql-hackers@lists.postgresql.org; Thu, 06 Apr 2023 18:06:19 +0000 Received: from mail-ot1-x331.google.com ([2607:f8b0:4864:20::331]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pkTzw-0003Hg-1V for pgsql-hackers@postgresql.org; Thu, 06 Apr 2023 18:06:18 +0000 Received: by mail-ot1-x331.google.com with SMTP id r40-20020a05683044a800b006a14270bc7eso17767870otv.6 for ; Thu, 06 Apr 2023 11:06:11 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; t=1680804371; h=in-reply-to:content-disposition:mime-version:references:message-id :subject:cc:to:from:date:from:to:cc:subject:date:message-id:reply-to; bh=Lsj7UmkHjnanTipcd974HGzt2mgcWMjUvFpzyym17OI=; b=WPuxkp//oDvpWJlzluQLtQZ25LmtX+AYqK2HqJfzeX0IVgQUgdQ/yyr2pml5Ah7GGb bW6ZDwvzfYiRPcPpsOCMrKz2jLZDHHNGwmaYsEEBtyqKljsBMbqFnnoCuzYVVWKd1bl2 NTkOkHd8GImK38bIGl0sj9EPQP6vP+JhWW8F0lPOb2eeSMhgtfjReGlgg62KGyrTaiX2 iqcMZHz74MPleM/StxoKHqVA2lANe+dZyepXXmdOwMfUrxDWiw8vGftG1tddlH9FjuiB Pnb5AGZ8+Wd+WisK0askZTmYXCkrWArgWD0nJyetlTN98/y6oe2gaz8wTe+EMTYlLdwI vMgg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1680804371; h=in-reply-to:content-disposition:mime-version:references:message-id :subject:cc:to:from:date:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=Lsj7UmkHjnanTipcd974HGzt2mgcWMjUvFpzyym17OI=; b=lg2yi6YaJWa48LVIid0DYT5hebKsA2jkal4jiwiGdHgcdjfRCjHN0lcMJAXgXr8IIA VATuRs53c+a8/TESzKXG/2QxTqD4eZV99DxOnNLcvqwwwVR1rjzcnVorcvg5IcfqEEsw z4st4r9vYT8N+myfvr1UYIskKbgc/z2iF92dYcpAsiUcz5cyoAoIc8j8fXri96sdTJ+t 0eitCaVk93BKfrhkFcw4KH4GBe0sdPKCg+LYnAjNPbdEodgSM0h6IgaTDmS35WfbnTkh 2iBoaZA4PH5ukqBuDhEkptwtngEkLIz/pi7icGDZIZI1bOEJuKdYYxbNnYHBAJOWOX6D yjOQ== X-Gm-Message-State: AAQBX9fza6wsMnqk+XfRZ5KrFIRIbkPwux71C3VgtBlyNQJ/TIEPMD7q YYTAlKgOzHxhjCCz4fIkw8M= X-Google-Smtp-Source: AKy350Z88VrRmAM6PCeUIJnDwXDMAf1yLrQwKg4l7Ok0Ri06lvdTWrugthtBZ4yvSikAzU35pzvbHg== X-Received: by 2002:a9d:6c13:0:b0:69d:e8b5:6faa with SMTP id f19-20020a9d6c13000000b0069de8b56faamr4442035otq.34.1680804371351; Thu, 06 Apr 2023 11:06:11 -0700 (PDT) Received: from nathanxps13 ([2600:1700:48f8:340f:ea61:dcca:fc91:a55a]) by smtp.gmail.com with ESMTPSA id g11-20020a9d618b000000b006a11dd6d2c1sm946292otk.29.2023.04.06.11.06.09 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Thu, 06 Apr 2023 11:06:10 -0700 (PDT) Date: Thu, 6 Apr 2023 11:06:08 -0700 From: Nathan Bossart To: Tom Lane Cc: Robert Haas , Andres Freund , pgsql-hackers@postgresql.org, schneider@ardentperf.com Subject: Re: monitoring usage count distribution Message-ID: <20230406180608.GA275542@nathanxps13> References: <20230404232919.uibzbhjdylk3mlvp@awork3.anarazel.de> <20230405175124.GA31516@nathanxps13> <20230405190921.ck7v4y2h3ogoanvg@awork3.anarazel.de> <20230405194101.GB105962@nathanxps13> <2061855.1680725784@sss.pgh.pa.us> <2478091.1680802355@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="mYCpIKhGyMATD0i+" Content-Disposition: inline In-Reply-To: <2478091.1680802355@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --mYCpIKhGyMATD0i+ Content-Type: text/plain; charset=us-ascii Content-Disposition: inline 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 --mYCpIKhGyMATD0i+ Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v2-0001-introduce-pg_buffercache_usage_counts.patch" From 6ad6a8e3a9ed0d0265e1869c0eaa793881c2fa77 Mon Sep 17 00:00:00 2001 From: Nathan Bossart 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 @@ The module provides the pg_buffercache_pages() - function, wrapped in the pg_buffercache view, and - the pg_buffercache_summary() function. + function, wrapped in the pg_buffercache view, + the pg_buffercache_summary() function, and the + pg_buffercache_usage_counts() function. @@ -39,6 +40,12 @@ row summarizing the state of the shared buffer cache. + + The pg_buffercache_usage_counts() function returns a set + of records, each row describing the number of buffers with a given usage + count. + + By default, use is restricted to superusers and roles with privileges of the pg_monitor role. Access may be granted to others @@ -266,6 +273,84 @@ + + The <function>pg_buffercache_usage_counts()</function> Function + + + The definitions of the columns exposed by the function are shown in + . + + + + <function>pg_buffercache_usage_counts()</function> Output Columns + + + + + Column Type + + + Description + + + + + + + + usage_count int4 + + + A usage count + + + + + + buffers int4 + + + Number of buffers with the usage count + + + + + + dirty int4 + + + Number of dirty buffers with the usage count + + + + + + pinned int4 + + + Number of pinned buffers with the usage count + + + + +
+ + + The pg_buffercache_usage_counts() function returns a + set of rows summarizing the usage counts of all shared buffers. Similar and + more detailed information is provided by the + pg_buffercache view, but + pg_buffercache_usage_counts() is significantly cheaper. + + + + Like the pg_buffercache view, + pg_buffercache_usage_counts() does not acquire buffer + manager locks. Therefore concurrent activity can lead to minor inaccuracies + in the result. + + + Sample Output @@ -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) -- 2.25.1 --mYCpIKhGyMATD0i+ Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v2-0002-replace-usagecount_avg-with-an-array-of-usage-cou.patch" From 2459cc7c49ebeec872022ae2d2099182e5200be4 Mon Sep 17 00:00:00 2001 From: Nathan Bossart Date: Thu, 6 Apr 2023 11:04:07 -0700 Subject: [PATCH v2 2/2] replace usagecount_avg with an array of usage counts --- .../pg_buffercache/expected/pg_buffercache.out | 9 +++++---- .../pg_buffercache/pg_buffercache--1.3--1.4.sql | 2 +- contrib/pg_buffercache/pg_buffercache_pages.c | 16 ++++++++++------ contrib/pg_buffercache/sql/pg_buffercache.sql | 3 ++- doc/src/sgml/pgbuffercache.sgml | 10 +++++----- 5 files changed, 23 insertions(+), 17 deletions(-) diff --git a/contrib/pg_buffercache/expected/pg_buffercache.out b/contrib/pg_buffercache/expected/pg_buffercache.out index b745dc69ea..a5921b83c5 100644 --- a/contrib/pg_buffercache/expected/pg_buffercache.out +++ b/contrib/pg_buffercache/expected/pg_buffercache.out @@ -10,11 +10,12 @@ from pg_buffercache; select buffers_used + buffers_unused > 0, buffers_dirty <= buffers_used, - buffers_pinned <= buffers_used + buffers_pinned <= buffers_used, + array_length(usagecounts, 1) > 0 from pg_buffercache_summary(); - ?column? | ?column? | ?column? -----------+----------+---------- - t | t | t + ?column? | ?column? | ?column? | ?column? +----------+----------+----------+---------- + t | t | t | t (1 row) SELECT count(*) > 0 FROM pg_buffercache_usage_counts() WHERE buffers >= 0; diff --git a/contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql b/contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql index f4702e4b4b..9120ad6ffb 100644 --- a/contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql +++ b/contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql @@ -8,7 +8,7 @@ CREATE FUNCTION pg_buffercache_summary( OUT buffers_unused int4, OUT buffers_dirty int4, OUT buffers_pinned int4, - OUT usagecount_avg float8) + OUT usagecounts int4[]) AS 'MODULE_PATHNAME', 'pg_buffercache_summary' LANGUAGE C PARALLEL SAFE; diff --git a/contrib/pg_buffercache/pg_buffercache_pages.c b/contrib/pg_buffercache/pg_buffercache_pages.c index f333967c51..da3594cc7d 100644 --- a/contrib/pg_buffercache/pg_buffercache_pages.c +++ b/contrib/pg_buffercache/pg_buffercache_pages.c @@ -13,6 +13,7 @@ #include "funcapi.h" #include "storage/buf_internals.h" #include "storage/bufmgr.h" +#include "utils/array.h" #define NUM_BUFFERCACHE_PAGES_MIN_ELEM 8 @@ -255,7 +256,9 @@ pg_buffercache_summary(PG_FUNCTION_ARGS) int32 buffers_unused = 0; int32 buffers_dirty = 0; int32 buffers_pinned = 0; - int64 usagecount_total = 0; + + int32 usagecounts[BM_MAX_USAGE_COUNT + 1] = {0}; + Datum ucdata[BM_MAX_USAGE_COUNT + 1]; if (get_call_result_type(fcinfo, NULL, &tupledesc) != TYPEFUNC_COMPOSITE) elog(ERROR, "return type must be a row type"); @@ -277,7 +280,7 @@ pg_buffercache_summary(PG_FUNCTION_ARGS) if (buf_state & BM_VALID) { buffers_used++; - usagecount_total += BUF_STATE_GET_USAGECOUNT(buf_state); + usagecounts[BUF_STATE_GET_USAGECOUNT(buf_state)]++; if (buf_state & BM_DIRTY) buffers_dirty++; @@ -295,10 +298,11 @@ pg_buffercache_summary(PG_FUNCTION_ARGS) values[2] = Int32GetDatum(buffers_dirty); values[3] = Int32GetDatum(buffers_pinned); - if (buffers_used != 0) - values[4] = Float8GetDatum((double) usagecount_total / buffers_used); - else - nulls[4] = true; + for (int i = 0; i < BM_MAX_USAGE_COUNT + 1; i++) + ucdata[i] = Int32GetDatum(usagecounts[i]); + values[4] = PointerGetDatum(construct_array(ucdata, BM_MAX_USAGE_COUNT + 1, + INT4OID, sizeof(int32), true, + TYPALIGN_INT)); /* Build and return the tuple. */ tuple = heap_form_tuple(tupledesc, values, nulls); diff --git a/contrib/pg_buffercache/sql/pg_buffercache.sql b/contrib/pg_buffercache/sql/pg_buffercache.sql index 944fbb1bea..b5288eb283 100644 --- a/contrib/pg_buffercache/sql/pg_buffercache.sql +++ b/contrib/pg_buffercache/sql/pg_buffercache.sql @@ -7,7 +7,8 @@ from pg_buffercache; select buffers_used + buffers_unused > 0, buffers_dirty <= buffers_used, - buffers_pinned <= buffers_used + buffers_pinned <= buffers_used, + array_length(usagecounts, 1) > 0 from pg_buffercache_summary(); SELECT count(*) > 0 FROM pg_buffercache_usage_counts() WHERE buffers >= 0; diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml index acd88aa813..74b1e92637 100644 --- a/doc/src/sgml/pgbuffercache.sgml +++ b/doc/src/sgml/pgbuffercache.sgml @@ -247,10 +247,10 @@ - usagecount_avg float8 + usagecounts int4[] - Average usagecount of used shared buffers + Number of used shared buffers for each possible usagecount @@ -381,9 +381,9 @@ regression=# SELECT n.nspname, c.relname, count(*) AS buffers regression=# SELECT * FROM pg_buffercache_summary(); - buffers_used | buffers_unused | buffers_dirty | buffers_pinned | usagecount_avg ---------------+----------------+---------------+----------------+---------------- - 248 | 2096904 | 39 | 0 | 3.141129 + buffers_used | buffers_unused | buffers_dirty | buffers_pinned | usagecounts +--------------+----------------+---------------+----------------+------------------ + 184 | 2096968 | 44 | 0 | {0,69,15,8,5,87} (1 row) -- 2.25.1 --mYCpIKhGyMATD0i+--