public inbox for [email protected]
help / color / mirror / Atom feedFrom: Rahila Syed <[email protected]>
To: sud <[email protected]>
Cc: Ron Johnson <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Alerting on memory use and instance crash
Date: Tue, 4 Nov 2025 10:06:33 +0530
Message-ID: <CAH2L28vP1qPxrDLOMRduvm0oR6Yj6GOuEySFYgpu3gjZ=WAjkA@mail.gmail.com> (raw)
In-Reply-To: <CAD=mzVWY5b-atBYZsiHj3beVpo30OFFULhKLeH_S10TEJHwn5A@mail.gmail.com>
References: <CAD=mzVXjx-ULCM-ZdSz2RDSoL8EA1Bsd+0gBxgOCTWV_tC4GDw@mail.gmail.com>
<[email protected]>
<CAD=mzVVBHZ6480qrbPYhvpkkKqGeBpahV-+=r0xXU7O0XXQ5zw@mail.gmail.com>
<CANzqJaCpM18oHAYpZHk9cW=0zjxMGKLKZOPCi1eHGm4ssb4ZsA@mail.gmail.com>
<CAD=mzVV5upya19WHE0do-ievkCccgZaPydKKM_Z5p+n+moa69A@mail.gmail.com>
<CAH2L28uRJcPy36jFd7TjGxm9YjAqdOLKZLwc7NxPoC4S=_DuJw@mail.gmail.com>
<CAD=mzVWY5b-atBYZsiHj3beVpo30OFFULhKLeH_S10TEJHwn5A@mail.gmail.com>
On Fri, Oct 10, 2025 at 8:58 PM sud <[email protected]> wrote:>
> Thank you so much. That helps.
>
> I am planning to use pg_stat_get_backend_memory_contexts function
something as below by joining this to the pg_stat_activity. Hope this is
the right usage. Somehow i am getting an error stating the function doesn't
exist but it might be because of the version. I will try with a higher
version.
>
> SELECT pa.pid,
> pa.usename,
> pa.application_name,
> pa.state,
> mc.name AS memory_context,
> pg_size_pretty(mc.used_bytes) AS used_memory
> FROM pg_stat_activity pa
> JOIN LATERAL pg_stat_get_backend_memory_contexts(pa.pid) mc ON TRUE
> WHERE pa.pid <> pg_backend_pid()
> ORDER BY mc.used_bytes DESC;
>
The function pg_stat_get_backend_memory_contexts(pid) is not available in
the master branch yet;
this feature is still under development. That is the reason you are getting
the error stating
function doesn't exist.
When I apply the latest patch on this proposed here, [1]
that contains the said function, and run your query , I get something like
follows:
postgres=# SELECT pa.pid,
pa.usename,
pa.application_name,
pa.state,
mc.name AS memory_context,
pg_size_pretty(mc.used_bytes) AS used_memory
FROM pg_stat_activity pa
JOIN LATERAL pg_get_process_memory_contexts(pa.pid, false) mc ON TRUE
WHERE pa.pid <> pg_backend_pid()
ORDER BY mc.used_bytes DESC;
pid | usename | application_name | state | memory_context |
used_memory
-------+---------+------------------+-------+--------------------------+-------------
36876 | rahila | | | TopMemoryContext |
202 kB
36875 | | | | TopMemoryContext |
200 kB
36868 | | | | TopMemoryContext |
176 kB
36869 | | | | TopMemoryContext |
164 kB
36866 | | | | TopMemoryContext |
164 kB
36867 | | | | TopMemoryContext |
164 kB
36874 | | | | TopMemoryContext |
164 kB
36865 | | | | TopMemoryContext |
164 kB
36876 | rahila | | | CacheMemoryContext |
141 kB
36875 | | | | CacheMemoryContext |
141 kB
36874 | | | | Timezones | 99
kB
36868 | | | | Timezones | 99
kB
36876 | rahila | | | Timezones | 99
kB
36866 | | | | Timezones | 99
kB
36869 | | | | Timezones | 99
kB
36865 | | | | Timezones | 99
kB
36875 | | | | Timezones | 99
kB
36867 | | | | Timezones | 99
kB
36876 | rahila | | | WAL record construction | 42
kB
36865 | | | | WAL record construction | 42
kB
36869 | | | | WAL record construction | 42
kB
36875 | | | | WAL record construction | 42
kB
36868 | | | | WAL record construction | 42
kB
36874 | | | | WAL record construction | 42
kB
36866 | | | | WAL record construction | 42
kB
36867 | | | | WAL record construction | 42
kB
36876 | rahila | | | GUC hash table | 21
kB
36869 | | | | GUC hash table | 21
kB
36868 | | | | GUC hash table | 21
kB
36867 | | | | GUC hash table | 21
kB
36875 | | | | GUC hash table | 21
kB
36865 | | | | GUC hash table | 21
kB
36866 | | | | GUC hash table | 21
kB
36874 | | | | GUC hash table | 21
kB
36875 | | | | smgr relation table | 11
kB
36876 | rahila | | | smgr relation table | 11
kB
36866 | | | | smgr relation table | 11
kB
36868 | | | | smgr relation table | 11
kB
36865 | | | | smgr relation table | 11
kB
36867 | | | | smgr relation table | 11
kB
36868 | | | | GUCMemoryContext | 11
kB
36876 | rahila | | | GUCMemoryContext | 11
kB
36866 | | | | GUCMemoryContext | 11
kB
36865 | | | | GUCMemoryContext | 11
kB
36867 | | | | GUCMemoryContext | 11
kB
36869 | | | | GUCMemoryContext | 11
kB
36874 | | | | GUCMemoryContext | 11
kB
36875 | | | | GUCMemoryContext | 11
kB
36876 | rahila | | | Relcache by OID |
8648 bytes
36875 | | | | Relcache by OID |
8648 bytes
36875 | | | | PgStat Shared Ref Hash |
8552 bytes
36876 | rahila | | | PgStat Shared Ref Hash |
8552 bytes
36874 | | | | PgStat Shared Ref Hash |
8552 bytes
36875 | | | | Portal hash |
7576 bytes
36867 | | | | LOCALLOCK hash |
7576 bytes
36874 | | | | LOCALLOCK hash |
7576 bytes
36866 | | | | LOCALLOCK hash |
7576 bytes
36865 | | | | LOCALLOCK hash |
7576 bytes
36868 | | | | LOCALLOCK hash |
7576 bytes
36876 | rahila | | | Portal hash |
7576 bytes
36875 | | | | LOCALLOCK hash |
7576 bytes
36876 | rahila | | | LOCALLOCK hash |
7576 bytes
36868 | | | | Pending Ops Table |
7576 bytes
36869 | | | | LOCALLOCK hash |
7576 bytes
36874 | | | | PrivateRefCount |
5520 bytes
36876 | rahila | | | PrivateRefCount |
5520 bytes
36866 | | | | PrivateRefCount |
5520 bytes
36865 | | | | PrivateRefCount |
5520 bytes
36869 | | | | PrivateRefCount |
5520 bytes
36875 | | | | PrivateRefCount |
5520 bytes
36867 | | | | PrivateRefCount |
5520 bytes
36868 | | | | PrivateRefCount |
5520 bytes
36876 | rahila | | | PgStat Pending |
504 bytes
36875 | | | | PgStat Pending |
504 bytes
36875 | | | | PgStat Shared Ref |
456 bytes
36866 | | | | MdSmgr |
400 bytes
36876 | rahila | | | PgStat Shared Ref |
384 bytes
36874 | | | | PgStat Shared Ref |
312 bytes
36875 | | | | Autovacuum database list |
312 bytes
36865 | | | | MdSmgr |
272 bytes
36876 | rahila | | | MdSmgr |
256 bytes
36875 | | | | MdSmgr |
256 bytes
36867 | | | | MdSmgr |
256 bytes
36874 | | | | MdSmgr |
240 bytes
36866 | | | | ErrorContext |
240 bytes
36876 | rahila | | | TopPortalContext |
240 bytes
36865 | | | | ErrorContext |
240 bytes
36876 | rahila | | | TransactionAbortContext |
240 bytes
36876 | rahila | | | TopTransactionContext |
240 bytes
36867 | | | | ErrorContext |
240 bytes
36875 | | | | ErrorContext |
240 bytes
36868 | | | | Checkpointer |
240 bytes
36868 | | | | MdSmgr |
240 bytes
36868 | | | | Pending ops context |
240 bytes
36868 | | | | ErrorContext |
240 bytes
36875 | | | | TopPortalContext |
240 bytes
36869 | | | | Background Writer |
240 bytes
36869 | | | | MdSmgr |
240 bytes
36874 | | | | ErrorContext |
240 bytes
36875 | | | | Autovacuum Launcher |
240 bytes
36869 | | | | ErrorContext |
240 bytes
36875 | | | | TransactionAbortContext |
240 bytes
36875 | | | | TopTransactionContext |
240 bytes
36874 | | | | Wal Writer |
240 bytes
36876 | rahila | | | ErrorContext |
240 bytes
(105 rows)
> However, is the below query, which was shared by Veem in above email
thread is also going to give similar memory consumption information i.e.
Avg memory consumption per query from pg_stat_statements?
This gives the memory consumed by reading in temporary files for a
particular statement or query
It does not give the complete picture of memory usage by a PostgreSQL
process. Apart
from temp_blks_read, a PostgreSQL process allocates more memory which can
be
viewed by a utility like pg_backend_memory_contexts for the backend process
attached to
the current session.
Thank you,
Rahila Syed
[1] PostgreSQL: Enhancing Memory Context Statistics Reporting
<https://www.postgresql.org/message-id/flat/CAH2L28v8mc9HDt8QoSJ8TRmKau_8FM_HKS41NeO9-6ZAkuZKXw@mail....;
view thread (11+ messages)
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: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Alerting on memory use and instance crash
In-Reply-To: <CAH2L28vP1qPxrDLOMRduvm0oR6Yj6GOuEySFYgpu3gjZ=WAjkA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox