Re: Alerting on memory use and instance crash
| От | Rahila Syed |
|---|---|
| Тема | Re: Alerting on memory use and instance crash |
| Дата | |
| Msg-id | CAH2L28vP1qPxrDLOMRduvm0oR6Yj6GOuEySFYgpu3gjZ=WAjkA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Alerting on memory use and instance crash (sud <suds1434@gmail.com>) |
| Список | pgsql-general |
On Fri, Oct 10, 2025 at 8:58 PM sud <suds1434@gmail.com> 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
В списке pgsql-general по дате отправления: