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

From Melih Mutlu
Subject Re: Parent/child context relation in pg_get_backend_memory_contexts()
Date
Msg-id CAGPVpCRNGd4V6MsxOttDZG4Hbf-4c1rrQKL4CsLyhaSY4sNkjA@mail.gmail.com
Whole thread Raw
In response to Parent/child context relation in pg_get_backend_memory_contexts()  (Melih Mutlu <m.melihmutlu@gmail.com>)
Responses Re: Parent/child context relation in pg_get_backend_memory_contexts()
Re: Parent/child context relation in pg_get_backend_memory_contexts()
List pgsql-hackers
Hi hackers,


Melih Mutlu <m.melihmutlu@gmail.com>, 16 Haz 2023 Cum, 17:03 tarihinde şunu yazdı:
With this change, here's a query to find how much space used by each
context including its children:

> WITH RECURSIVE cte AS (
>     SELECT id, total_bytes, id as root, name as root_name
>     FROM memory_contexts
> UNION ALL
>     SELECT r.id, r.total_bytes, cte.root, cte.root_name
>     FROM memory_contexts r
>     INNER JOIN cte ON r.parent_id = cte.id
> ),
> memory_contexts AS (
>     SELECT * FROM pg_backend_memory_contexts
> )
> SELECT root as id, root_name as name, sum(total_bytes)
> FROM cte
> GROUP BY root, root_name
> ORDER BY sum DESC;

Given that the above query to get total bytes including all children is still a complex one, I decided to add an additional info in pg_backend_memory_contexts.
The new "path" field displays an integer array that consists of ids of all parents for the current context. This way it's easier to tell whether a context is a child of another context, and we don't need to use recursive queries to get this info.

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;


We still need to use cte since ids are not persisted and might change in each run of pg_backend_memory_contexts. Materializing the result can prevent any inconsistencies due to id change. Also it can be even good for performance reasons as well.

Any thoughts?

Thanks,
--
Melih Mutlu
Microsoft
Attachment

pgsql-hackers by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: cataloguing NOT NULL constraints
Next
From: Chapman Flack
Date:
Subject: Re: Extract numeric filed in JSONB more effectively