0001-Adding-a-function-exposing-memory-usage-of-local-backend.patch

From 055af903a3dbf146d97dd3fb01a6a7d3d3bd2ae0 Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <torikoshia@oss.nttdata.com>
Date: Mon, 29 Jun 2020 07:48:49 +0900
Subject: [PATCH] Add a function exposing memory usage of local backend.

Backend processes sometimes use a lot of memory because of various
reasons like caches, prepared statements and cursors.

Previously, the only way to examine the usage of backend processes
was attaching a debugger and call MemoryContextStats() and it was
not so convenient in some cases.

This patch implements a new SQL-callable function
pg_stat_get_memory_contexts() which exposes memory usage of the
local backend.
---
 doc/src/sgml/monitoring.sgml        | 14 +++++
 src/backend/postmaster/pgstat.c     | 80 +++++++++++++++++++++++++++++
 src/backend/utils/adt/pgstatfuncs.c | 45 ++++++++++++++++
 src/include/catalog/pg_proc.dat     |  9 ++++
 src/include/pgstat.h                |  6 ++-
 5 files changed, 153 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index dfa9d0d641..74c8663981 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4617,6 +4617,20 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_stat_get_memory_contexts</primary>
+        </indexterm>
+        <function>pg_stat_get_memory_contexts</function> ()
+        <returnvalue>setof record</returnvalue>
+       </para>
+       <para>
+        Returns records of information about all memory contexts of the
+        local backend.
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index c022597bc0..140f111654 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -62,6 +62,7 @@
 #include "storage/procsignal.h"
 #include "storage/sinvaladt.h"
 #include "utils/ascii.h"
+#include "utils/builtins.h"
 #include "utils/guc.h"
 #include "utils/memutils.h"
 #include "utils/ps_status.h"
@@ -167,6 +168,13 @@ static const char *const slru_names[] = {
  */
 static PgStat_MsgSLRU SLRUStats[SLRU_NUM_ELEMENTS];
 
+/* ----------
+ * The max bytes for showing identifiers of MemoryContext.
+ * ----------
+ */
+#define MEMORY_CONTEXT_IDENT_SIZE	1024
+
+
 /* ----------
  * Local data
  * ----------
@@ -2691,6 +2699,78 @@ pgstat_fetch_slru(void)
 	return slruStats;
 }
 
+void
+pgstat_put_memory_contexts_tuplestore(Tuplestorestate *tupstore,
+								  TupleDesc tupdesc, MemoryContext context,
+								  MemoryContext parent, int level)
+{
+#define PG_STAT_GET_MEMORY_CONTEXT_COLS	9
+	Datum		values[PG_STAT_GET_MEMORY_CONTEXT_COLS];
+	bool		nulls[PG_STAT_GET_MEMORY_CONTEXT_COLS];
+	MemoryContextCounters stat;
+	MemoryContext child;
+	const char *name = context->name;
+	const char *ident = context->ident;
+
+	if (context == NULL)
+		return;
+
+	/*
+	 * It seems preferable to label dynahash contexts with just the hash table
+	 * name.  Those are already unique enough, so the "dynahash" part isn't
+	 * very helpful, and this way is more consistent with pre-v11 practice.
+	 */
+	if (ident && strcmp(name, "dynahash") == 0)
+	{
+		name = ident;
+		ident = NULL;
+	}
+
+	/* Examine the context itself */
+	memset(&stat, 0, sizeof(stat));
+	(*context->methods->stats) (context, NULL, (void *) &level, &stat);
+
+	memset(nulls, 0, sizeof(nulls));
+	values[0] = CStringGetTextDatum(name);
+
+	if (ident)
+	{
+		int		idlen = strlen(ident);
+		char		clipped_ident[MEMORY_CONTEXT_IDENT_SIZE];
+
+		/*
+		 * Some identifiers such as SQL query string can be very long,
+		 * truncate oversize identifiers.
+		 */
+		if (idlen >= MEMORY_CONTEXT_IDENT_SIZE)
+			idlen = pg_mbcliplen(ident, idlen, MEMORY_CONTEXT_IDENT_SIZE - 1);
+
+		memcpy(clipped_ident, ident, idlen);
+		clipped_ident[idlen] = '\0';
+		values[1] = CStringGetTextDatum(clipped_ident);
+	}
+	else
+		nulls[1] = true;
+
+	if (parent == NULL)
+		nulls[2] = true;
+	else
+		values[2] = CStringGetTextDatum(parent->name);
+	values[3] = Int32GetDatum(level);
+	values[4] = Int64GetDatum(stat.totalspace);
+	values[5] = Int64GetDatum(stat.nblocks);
+	values[6] = Int64GetDatum(stat.freespace);
+	values[7] = Int64GetDatum(stat.freechunks);
+	values[8] = Int64GetDatum(stat.totalspace - stat.freespace);
+	tuplestore_putvalues(tupstore, tupdesc, values, nulls);
+
+	for (child = context->firstchild; child != NULL; child = child->nextchild)
+	{
+		pgstat_put_memory_contexts_tuplestore(tupstore, tupdesc,
+									  child, context, level + 1);
+	}
+}
+
 
 /* ------------------------------------------------------------
  * Functions for management of the shared-memory PgBackendStatus array
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 2aff739466..3e21b47b63 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1182,6 +1182,51 @@ pg_stat_get_backend_client_addr(PG_FUNCTION_ARGS)
 										 CStringGetDatum(remote_host)));
 }
 
+Datum
+pg_stat_get_memory_contexts(PG_FUNCTION_ARGS)
+{
+	ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+	TupleDesc	tupdesc;
+	Tuplestorestate *tupstore;
+	MemoryContext per_query_ctx;
+	MemoryContext oldcontext;
+
+	/* check to see if caller supports us returning a tuplestore */
+	if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("set-valued function called in context that cannot accept a set")));
+	if (!(rsinfo->allowedModes & SFRM_Materialize))
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("materialize mode required, but it is not allowed in this context")));
+
+	/* Build a tuple descriptor for our result type */
+	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+		elog(ERROR, "return type must be a row type");
+
+	per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
+	oldcontext = MemoryContextSwitchTo(per_query_ctx);
+
+	tupstore = tuplestore_begin_heap(true, false, work_mem);
+	rsinfo->returnMode = SFRM_Materialize;
+	rsinfo->setResult = tupstore;
+	rsinfo->setDesc = tupdesc;
+
+	MemoryContextSwitchTo(oldcontext);
+
+	tupdesc = rsinfo->setDesc;
+	tupstore = rsinfo->setResult;
+
+	pgstat_put_memory_contexts_tuplestore(tupstore, tupdesc,
+										  TopMemoryContext, NULL, 0);
+
+	/* clean up and return the tuplestore */
+	tuplestore_donestoring(tupstore);
+
+	return (Datum) 0;
+}
+
 Datum
 pg_stat_get_backend_client_port(PG_FUNCTION_ARGS)
 {
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 61f2c2f5b4..8d1e63fbca 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5329,6 +5329,15 @@
   proname => 'pg_stat_get_db_blocks_hit', provolatile => 's',
   proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
   prosrc => 'pg_stat_get_db_blocks_hit' },
+{ oid => '2282', prorows => '100', proretset => 't',
+  descr => 'statistics: information about all memory contexts of local backend',
+  proname => 'pg_stat_get_memory_contexts', provolatile => 'v',
+  proparallel => 'r', prorettype => 'record', proargtypes => '',
+  proallargtypes => '{text,text,text,int4,int8,int8,int8,int8,int8}',
+  proargmodes => '{o,o,o,o,o,o,o,o,o}',
+  proargnames => '{name, ident, parent, level, total_bytes, total_nblocks, free_bytes, free_chunks, used_bytes}',
+  prosrc => 'pg_stat_get_memory_contexts' },
+  prosrc => 'pg_stat_get_backend_memory_contexts' },
 { oid => '2758', descr => 'statistics: tuples returned for database',
   proname => 'pg_stat_get_db_tuples_returned', provolatile => 's',
   proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 1387201382..6a43bd8e60 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -20,6 +20,7 @@
 #include "storage/proc.h"
 #include "utils/hsearch.h"
 #include "utils/relcache.h"
+#include "utils/tuplestore.h"
 
 
 /* ----------
@@ -1474,7 +1475,10 @@ extern int	pgstat_fetch_stat_numbackends(void);
 extern PgStat_ArchiverStats *pgstat_fetch_stat_archiver(void);
 extern PgStat_GlobalStats *pgstat_fetch_global(void);
 extern PgStat_SLRUStats *pgstat_fetch_slru(void);
-
+extern void pgstat_put_memory_contexts_tuplestore(Tuplestorestate *tupstore,
+									TupleDesc tupdesc,
+									MemoryContext context,
+									MemoryContext parent, int level);
 extern void pgstat_count_slru_page_zeroed(int slru_idx);
 extern void pgstat_count_slru_page_hit(int slru_idx);
 extern void pgstat_count_slru_page_read(int slru_idx);
-- 
2.18.1