From: torikoshia <torikoshia@oss.nttdata.com>
To: "Robert Haas <robertmhaas@gmail.com>
To: Fujii Masao" <masao.fujii@oss.nttdata.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Creating a function for exposing memory usage of backend process
Date: Mon, 29 Jun 2020 12:01:55 +0900
Message-ID: <3193c7d590f3212e543b63df977da758@oss.nttdata.com> (raw)
In-Reply-To: <CA+TgmobkpFV0UB67kzXuD36--OFHwz1bs=L_6PZbD4nxKqUQMw@mail.gmail.com>
References: <72a656e0f71d0860161e0b3f67e4d771@oss.nttdata.com>
<398eb7cd-5f75-be1c-3897-36865b7bfabf@oss.nttdata.com>
<CA+TgmobkpFV0UB67kzXuD36--OFHwz1bs=L_6PZbD4nxKqUQMw@mail.gmail.com>
On 2020-06-20 03:11, Robert Haas wrote:
> On Wed, Jun 17, 2020 at 11:56 PM Fujii Masao
> <masao.fujii@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
Attachments:0001-Adding-a-function-exposing-memory-usage-of-local-backend.patch (text/x-diff, 8.7K) download
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.sgmlindex 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.cindex 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.cindex 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.datindex 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.hindex 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
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: torikoshia@oss.nttdata.com, robertmhaas@gmail.com, masao.fujii@oss.nttdata.com
Subject: Re: Creating a function for exposing memory usage of backend process
In-Reply-To: <3193c7d590f3212e543b63df977da758@oss.nttdata.com>
* 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