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 1pMdbt-0002E1-JR for pgsql-hackers@arkaria.postgresql.org; Mon, 30 Jan 2023 23:30:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pMdbs-0002tG-C0 for pgsql-hackers@arkaria.postgresql.org; Mon, 30 Jan 2023 23:30:48 +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 1pMdbr-0002sl-T8 for pgsql-hackers@lists.postgresql.org; Mon, 30 Jan 2023 23:30:48 +0000 Received: from mail-pj1-x1035.google.com ([2607:f8b0:4864:20::1035]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pMdbp-0007B1-2F for pgsql-hackers@postgresql.org; Mon, 30 Jan 2023 23:30:46 +0000 Received: by mail-pj1-x1035.google.com with SMTP id cl23-20020a17090af69700b0022c745bfdc3so6105958pjb.3 for ; Mon, 30 Jan 2023 15:30:44 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=content-disposition:mime-version:message-id:subject:cc:to:from:date :from:to:cc:subject:date:message-id:reply-to; bh=ascRa8uXRriPuPizOD/XfKbTdWRudSXBxdUYoysfvD8=; b=bz0MHTsZgkuaQ7/uri+orSf7QoJzZV8jYilUJznZp2skKVozrXwT2t5J8E9p7NWgnt nnVTNtmka60Y8dJvkvhzur9Kdk+RTsfG4Denm6swztnJbObMmZRwGrzqnBYGl7rQcmKH pJeMoVq9vdvzCTsB4Xv5w53CkbyNHSCBRXkycdUb+jCDGnNNY+Ztyfe67nzHAuQmAYBT 4N0AXIVEC0kEf5OEbbPyfsdjCD+CoXgaynzWTmUDN44VfIR2xnBU+r/VLlehpzUYPnbD p7MIxukEJDQ0CU5xpVXkNsdhPIKZHROB68jBxAUOAdmKRVS57gnQBlUL2SbdXvxIpyi+ h8sg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=content-disposition:mime-version:message-id:subject:cc:to:from:date :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=ascRa8uXRriPuPizOD/XfKbTdWRudSXBxdUYoysfvD8=; b=RbfqNdwdCzDydM/Q8RyiTAyi0M4o1RBbPGxcn7ibKrQtOK5b+Zi7lDG/4ffX3OzXCM cMe8p7KiLJzoABB6KcFbhXkECuykSQLknudID3fk/qdyqKSfu7dZQU2N/Haob6DZaVH2 BiPZbgS2eiqqnw8Y6644s/Wyf6DF9z+ZswsNOqv5l55jDkSmFFsU6RHz0aZZNEvp8CkX Lc7Fs/FBAT5iPS1fauP5BVhK4WwKJziUOElmKr7MhdANhOALpnTfGk9IUeLymaOAJUQ2 yMXGQnoYA0GvEI762kP7rpLyoH3aeMtI4yz55diQ5kRVG1FWEmUiXc1n+RER10H9Dmx2 nwwA== X-Gm-Message-State: AFqh2kp59LfU7O50P+Rwfpg4T29f3q+GnHzVLgl9KGU49VFB7C8ElsA+ AXv4QwlzQvYbIoYjD0j6kk/7g3goPjA= X-Google-Smtp-Source: AMrXdXs5f+CsQz32AAfVvO9kuZt5g2rjcibbL/RUrwH9+Yi3mvxfvRpNCbcyOz27xbf4fSovcTqgug== X-Received: by 2002:a17:90a:4e4c:b0:228:cf57:32c1 with SMTP id t12-20020a17090a4e4c00b00228cf5732c1mr54702911pjl.0.1675121443459; Mon, 30 Jan 2023 15:30:43 -0800 (PST) Received: from nathanxps13 ([50.47.162.83]) by smtp.gmail.com with ESMTPSA id q95-20020a17090a1b6800b0022698aa22d9sm7900994pjq.31.2023.01.30.15.30.41 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Mon, 30 Jan 2023 15:30:42 -0800 (PST) Date: Mon, 30 Jan 2023 15:30:40 -0800 From: Nathan Bossart To: pgsql-hackers@postgresql.org Cc: schneider@ardentperf.com Subject: monitoring usage count distribution Message-ID: <20230130233040.GA2800702@nathanxps13> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="IS0zKkzwUGydFO0o" Content-Disposition: inline List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --IS0zKkzwUGydFO0o Content-Type: text/plain; charset=us-ascii Content-Disposition: inline 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 --IS0zKkzwUGydFO0o Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v1-0001-introduce-pg_buffercache_usage_counts.patch" From b2fd87696185537d2cbb611cf70e743d7e197406 Mon Sep 17 00:00:00 2001 From: Nathan Bossart 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 @@ 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. @@ -38,6 +39,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 @@ -265,6 +272,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 @@ -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) -- 2.25.1 --IS0zKkzwUGydFO0o--