On 2020-06-20 03:11, Robert Haas wrote:
On Wed, Jun 17, 2020 at 11:56 PM Fujii Masao
<masao.fu...@oss.nttdata.com> wrote:
> As a first step, to deal with (3) I'd like to add
> pg_stat_get_backend_memory_context() which target is limited to the
> local backend process.
+1
+1 from me, too.
Attached a patch that adds a function exposing memory usage of local
backend.
It's almost same as pg_cheat_funcs's pg_stat_get_memory_context().
I've also added MemoryContexts identifier because it seems useful to
distinguish the same kind of memory contexts.
For example, when there are many prepared statements we can
distinguish them using identifiers, which shows the cached query.
=# SELECT name, ident FROM pg_stat_get_memory_contexts() WHERE name =
'CachedPlanSource';
name | ident
------------------+--------------------------------
CachedPlanSource | PREPARE q1(text) AS SELECT ..;
CachedPlanSource | PREPARE q2(text) AS SELECT ..;
(2 rows)
Something that exposed this via shared memory would
be quite useful, and there are other things we'd like to expose
similarly, such as the plan(s) from the running queries, or even just
the untruncated query string(s). I'd like to have a good
infrastructure for that sort of thing, but I think it's quite tricky
to do properly. You need a variable-size chunk of shared memory, so
probably it has to use DSM somehow, and you need to update it as
things change, but if you update it too frequently performance will
stink. If you ping processes to do the updates, how do you know when
they've completed them, and what happens if they don't respond in a
timely fashion? These are probably all solvable problems, but I don't
think they are very easy ones.
Thanks for your comments!
It seems hard as you pointed out.
I'm going to consider it along with the advice of Fujii and Kasahara.
Regards,
--
Atsushi Torikoshi
NTT DATA CORPORATION
From 055af903a3dbf146d97dd3fb01a6a7d3d3bd2ae0 Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <torikos...@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
