Re: Parent/child context relation in pg_get_backend_memory_contexts() - Mailing list pgsql-hackers

From Stephen Frost
Subject Re: Parent/child context relation in pg_get_backend_memory_contexts()
Date
Msg-id ZTGnM1qZq9ItyqK0@tamriel.snowman.net
Whole thread Raw
In response to Re: Parent/child context relation in pg_get_backend_memory_contexts()  (Andres Freund <andres@anarazel.de>)
List pgsql-hackers
Greetings,

* Andres Freund (andres@anarazel.de) wrote:
> On 2023-10-18 15:53:30 -0400, Stephen Frost wrote:
> > > Here how pg_backend_memory_contexts would look like with this patch:
> > >
> > > postgres=# SELECT name, id, parent, parent_id, path
> > > FROM pg_backend_memory_contexts
> > > ORDER BY total_bytes DESC LIMIT 10;
> > >           name           | id  |      parent      | parent_id |     path
> > > -------------------------+-----+------------------+-----------+--------------
> > >  CacheMemoryContext      |  27 | TopMemoryContext |         0 | {0}
> > >  Timezones               | 124 | TopMemoryContext |         0 | {0}
> > >  TopMemoryContext        |   0 |                  |           |
> > >  MessageContext          |   8 | TopMemoryContext |         0 | {0}
> > >  WAL record construction | 118 | TopMemoryContext |         0 | {0}
> > >  ExecutorState           |  18 | PortalContext    |        17 | {0,16,17}
> > >  TupleSort main          |  19 | ExecutorState    |        18 | {0,16,17,18}
> > >  TransactionAbortContext |  14 | TopMemoryContext |         0 | {0}
> > >  smgr relation table     |  10 | TopMemoryContext |         0 | {0}
> > >  GUC hash table          | 123 | GUCMemoryContext |       122 | {0,122}
> > > (10 rows)
> > >
> > > An example query to calculate the total_bytes including its children for a
> > > context (say CacheMemoryContext) would look like this:
> > >
> > > WITH contexts AS (
> > > SELECT * FROM pg_backend_memory_contexts
> > > )
> > > SELECT sum(total_bytes)
> > > FROM contexts
> > > WHERE ARRAY[(SELECT id FROM contexts WHERE name = 'CacheMemoryContext')] <@
> > > path;
> >
> > I wonder if we should perhaps just include
> > "total_bytes_including_children" as another column?  Certainly seems
> > like a very useful thing that folks would like to see.
>
> The "issue" is where to stop - should we also add that for some of the other
> columns? They are a bit less important, but not that much.

I'm not sure the others really make sense to aggregate in this way as
free space isn't able to be moved between contexts.  That said, if
someone wants it then I'm not against that.  I'm actively in support of
adding an aggregated total though as that, at least to me, seems to be
very useful to have.

Thanks,

Stephen

Attachment

pgsql-hackers by date:

Previous
From: Jelte Fennema
Date:
Subject: Re: prevent non-superuser terminate bgworker running as superuser
Next
From: Stephen Frost
Date:
Subject: Re: Add the ability to limit the amount of memory that can be allocated to backends.