pg.ddx.io pgsql-hackers@postgresql.org mailing list archive help / color / mirror / Atom feed From: torikoshia <torikoshia@oss.nttdata.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: Wed, 01 Jul 2020 14:48:02 +0900
Message-ID: <6c828f3a1d44dba81999fc7990b8f1c5@oss.nttdata.com> (raw )
In-Reply-To: <78268b5f-53ed-4645-1abb-23c1030cce0a@oss.nttdata.com >
References: <72a656e0f71d0860161e0b3f67e4d771@oss.nttdata.com >
<398eb7cd-5f75-be1c-3897-36865b7bfabf@oss.nttdata.com >
<CA+TgmobkpFV0UB67kzXuD36--OFHwz1bs=L_6PZbD4nxKqUQMw@mail.gmail.com >
<3193c7d590f3212e543b63df977da758@oss.nttdata.com >
<78268b5f-53ed-4645-1abb-23c1030cce0a@oss.nttdata.com >
On Mon, Jun 29, 2020 at 3:13 PM Fujii Masao
<masao.fujii@oss.nttdata.com> wrote:
> Could you add this patch to Commitfest 2020-07?
Thanks for notifying, I've added it.
BTW, I registered you as an author because this patch used
lots of pg_cheat_funcs' codes.
https://commitfest.postgresql.org/28/2622/
> This patch provides only the function, but isn't it convenient to
> provide the view like pg_shmem_allocations?
> Sounds good. But isn't it better to document each column?
> Otherwise, users cannot undersntad what "ident" column indicates.
Agreed.
Attached a patch for creating a view for local memory context
and its explanation on the document.
Regards,
--
Atsushi Torikoshi
NTT DATA CORPORATION
Attachments:
0002-Adding-a-function-exposing-memory-usage-of-local-backend.patch (text/x-diff, 15.4K) download
From 055af903a3dbf146d97dd3fb01a6a7d3d3bd2ae0 Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <torikoshia@oss.nttdata.com>
Date: Wed, 29 Jul 2020 13:02:29 +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.
It also adds a new view pg_stat_local_memory_contexts for exposing
local backend memory contexts.
---
doc/src/sgml/monitoring.sgml | 137 +++++++++++++++++++++++++++
src/backend/catalog/system_views.sql | 13 +++
src/backend/postmaster/pgstat.c | 80 ++++++++++++++++
src/backend/utils/adt/pgstatfuncs.c | 45 +++++++++
src/include/catalog/catversion.h | 2 +-
src/include/catalog/pg_proc.dat | 9 ++
src/include/pgstat.h | 6 +-
src/test/regress/expected/rules.out | 10 ++
8 files changed, 300 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index dfa9d0d641..9b82af54d0 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -350,6 +350,15 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
</entry>
</row>
+ <row>
+ <entry><structname>pg_stat_local_memory_contexts</structname><indexterm><primary>pg_stat_local_memory_contexts</primary></indexterm></entry>
+ <entry>One row per memory context, showing information about
+ the current memory context on the local backend.
+ See <link linkend="monitoring-pg-stat-local-memory-contexts-view">
+ <structname>pg_stat_local_memory_contexts</structname></link> for details.
+ </entry>
+ </row>
+
<row>
<entry><structname>pg_stat_progress_analyze</structname><indexterm><primary>pg_stat_progress_analyze</primary></indexterm></entry>
<entry>One row for each backend (including autovacuum worker processes) running
@@ -3053,6 +3062,120 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
</sect2>
+ <sect2 id="monitoring-pg-stat-local-memory-contexts">
+ <title><structname>pg_stat_local_memory_contexts</structname></title>
+
+ <indexterm>
+ <primary>pg_stat_local_memory_contexts</primary>
+ </indexterm>
+
+ <para>
+ The <structname>pg_stat_local_memory_contexts</structname> view will
+ contain one row per memory context, showing information about the
+ current memory context on the local backend.
+ </para>
+
+ <table id="monitoring-pg-stat-local-memory-contexts-view" xreflabel="pg_stat_local_memory_contexts">
+ <title><structname>pg_stat_local_memory_contexts</structname> View</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>name</structfield> <type>text</type>
+ </para>
+ <para>
+ Name of the context
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>ident</structfield> <type>text</type>
+ </para>
+ <para>
+ Identification information of the context. This field is truncated if the identification field is longer than <symbol>MEMORY_CONTEXT_IDENT_SIZE</symbol> (1024 characters in a standard build)
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>parent</structfield> <type>text</type>
+ </para>
+ <para>
+ Name of the parent of this context
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>level</structfield> <type>integer</type>
+ </para>
+ <para>
+ Distance from TopMemoryContext in context tree
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>total_bytes</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Total bytes requested from malloc
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>total_nblocks</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Total number of malloc blocks
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>free_bytes</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Free space in bytes
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>free_chunks</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Total number of free chunks
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>used_bytes</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Used space in bytes
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ </sect2>
+
<sect2 id="monitoring-pg-stat-archiver-view">
<title><structname>pg_stat_archiver</structname></title>
@@ -4617,6 +4740,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/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 5314e9348f..134b20f13f 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -793,6 +793,19 @@ CREATE VIEW pg_stat_replication AS
JOIN pg_stat_get_wal_senders() AS W ON (S.pid = W.pid)
LEFT JOIN pg_authid AS U ON (S.usesysid = U.oid);
+CREATE VIEW pg_stat_local_memory_contexts AS
+ SELECT
+ s.name,
+ s.ident,
+ s.parent,
+ s.level,
+ s.total_bytes,
+ s.total_nblocks,
+ s.free_bytes,
+ s.free_chunks,
+ s.used_bytes
+ FROM pg_stat_get_memory_contexts() s;
+
CREATE VIEW pg_stat_slru AS
SELECT
s.name,
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/catversion.h b/src/include/catalog/catversion.h
index 7644147cf5..50aa3e01c0 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 202006151
+#define CATALOG_VERSION_NO 202007011
#endif
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);
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index b813e32215..d1d989d1df 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1857,6 +1857,16 @@ pg_stat_gssapi| SELECT s.pid,
s.gss_enc AS encrypted
FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, sslcompression, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, gss_enc, leader_pid)
WHERE (s.client_port IS NOT NULL);
+pg_stat_local_memory_contexts| SELECT s.name,
+ s.ident,
+ s.parent,
+ s.level,
+ s.total_bytes,
+ s.total_nblocks,
+ s.free_bytes,
+ s.free_chunks,
+ s.used_bytes
+ FROM pg_stat_get_memory_contexts() s(name, ident, parent, level, total_bytes, total_nblocks, free_bytes, free_chunks, used_bytes);
pg_stat_progress_analyze| SELECT s.pid,
s.datid,
d.datname,
--
2.18.1
view thread (57+ messages) latest in thread
Message-ID: <6c828f3a1d44dba81999fc7990b8f1c5@oss.nttdata.com>
Permalink: ../6c828f3a1d44dba81999fc7990b8f1c5@oss.nttdata.com/
Also on: postgresql.org/message-id/6c828f3a1d44dba81999fc7990b8f1c5@oss.nttdata.com
copy link · copy postgr.es · copy git am
reply 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, masao.fujii@oss.nttdata.com
Subject: Re: Creating a function for exposing memory usage of backend process
In-Reply-To: <6c828f3a1d44dba81999fc7990b8f1c5@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