On 2024-10-22 03:24, Rahila Syed wrote:
Hi,
PostgreSQL provides following capabilities for reporting memory
contexts statistics.
1. pg_get_backend_memory_contexts(); [1]
2. pg_log_backend_memory_contexts(pid); [2]
[1] provides a view of memory context statistics for a local backend,
while [2] prints the memory context statistics of any backend or
auxiliary
process to the PostgreSQL logs. Although [1] offers detailed
statistics,
it is limited to the local backend, restricting its use to PostgreSQL
client backends only.
On the other hand, [2] provides the statistics for all backends but
logs them in a file,
which may not be convenient for quick access.
I propose enhancing memory context statistics reporting by combining
these
capabilities and offering a view of memory statistics for all
PostgreSQL backends
and auxiliary processes.
Thanks for working on this!
I originally tried to develop something like your proposal in [2], but
there were some difficulties and settled down to implement
pg_log_backend_memory_contexts().
Attached is a patch that implements this functionality. It introduces
a SQL function
that takes the PID of a backend as an argument, returning a set of
records,
each containing statistics for a single memory context. The underlying
C function
sends a signal to the backend and waits for it to publish its memory
context statistics
before returning them to the user. The publishing backend copies
these statistics
during the next CHECK_FOR_INTERRUPTS call.
I remember waiting for dumping memory contexts stats could cause trouble
considering some erroneous cases.
For example, just after the target process finished dumping stats,
pg_get_remote_backend_memory_contexts() caller is terminated before
reading the stats, calling pg_get_remote_backend_memory_contexts() has
no response any more:
[session1]$ psql
(40699)=#
$ kill -s SIGSTOP 40699
[session2] psql
(40866)=# select * FROM
pg_get_remote_backend_memory_contexts('40699', false); -- waiting
$ kill -s SIGSTOP 40866
$ kill -s SIGCONT 40699
[session3] psql
(47656) $ select pg_terminate_backend(40866);
$ kill -s SIGCONT 40866 -- session2 terminated
[session3] (47656)=# select * FROM
pg_get_remote_backend_memory_contexts('47656', false); -- no response
It seems the reason is memCtxState->in_use is now and
memCtxState->proc_id is 40699.
We can continue to use pg_get_remote_backend_memory_contexts() after
specifying 40699, but it'd be hard to understand for users.
This approach facilitates on-demand publication of memory statistics
for a specific backend, rather than collecting them at regular
intervals.
Since past memory context statistics may no longer be relevant,
there is little value in retaining historical data. Any collected
statistics
can be discarded once read by the client backend.
A fixed-size shared memory block, currently accommodating 30 records,
is used to store the statistics. This number was chosen arbitrarily,
as it covers all parent contexts at level 1 (i.e., direct children of
the top memory context)
based on my tests.
Further experiments are needed to determine the optimal number
for summarizing memory statistics.
Any additional statistics that exceed the shared memory capacity
are written to a file per backend in the PG_TEMP_FILES_DIR. The client
backend
first reads from the shared memory, and if necessary, retrieves the
remaining data from the file,
combining everything into a unified view. The files are cleaned up
automatically
if a backend crashes or during server restarts.
The statistics are reported in a breadth-first search order of the
memory context tree,
with parent contexts reported before their children. This provides a
cumulative summary
before diving into the details of each child context's consumption.
The rationale behind the shared memory chunk is to ensure that the
majority of contexts which are the direct children of
TopMemoryContext,
fit into memory
This allows a client to request a summary of memory statistics,
which can be served from memory without the overhead of file access,
unless necessary.
A publishing backend signals waiting client backends using a condition
variable when it has finished writing its statistics to memory.
The client backend checks whether the statistics belong to the
requested backend.
If not, it continues waiting on the condition variable, timing out
after 2 minutes.
This timeout is an arbitrary choice, and further work is required to
determine
a more practical value.
All backends use the same memory space to publish their statistics.
Before publishing, a backend checks whether the previous statistics
have been
successfully read by a client using a shared flag, "in_use."
This flag is set by the publishing backend and cleared by the client
backend once the data is read. If a backend cannot publish due to
shared
memory being occupied, it exits the interrupt processing code,
and the client backend times out with a warning.
Please find below an example query to fetch memory contexts from the
backend
with id '106114'. Second argument -'get_summary' is 'false',
indicating a request for statistics of all the contexts.
postgres=#
select * FROM pg_get_remote_backend_memory_contexts('116292', false)
LIMIT 2;
-[ RECORD 1 ]-+----------------------
name | TopMemoryContext
ident |
type | AllocSet
path | {0}
total_bytes | 97696
total_nblocks | 5
free_bytes | 15376
free_chunks | 11
used_bytes | 82320
pid | 116292
-[ RECORD 2 ]-+----------------------
name | RowDescriptionContext
ident |
type | AllocSet
path | {0,1}
total_bytes | 8192
total_nblocks | 1
free_bytes | 6912
free_chunks | 0
used_bytes | 1280
pid | 116292
32d3ed8165f821f introduced 1-based path to pg_backend_memory_contexts,
but pg_get_remote_backend_memory_contexts() seems to have 0-base path.
pg_backend_memory_contexts has "level" column, but
pg_get_remote_backend_memory_contexts doesn't.
Are there any reasons for these?
TODO:
1. Determine the behaviour when the statistics don't fit in one file.
[1] PostgreSQL: Re: Creating a function for exposing memory usage of
backend process [1]
[2] PostgreSQL: Re: Get memory contexts of an arbitrary backend
process [2]
Thank you,
Rahila Syed
Links:
------
[1]
https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fmessage-id%2F0a768ae1-1703-59c7-86cc-7068ff5e318c%2540oss.nttdata.com&data=05%7C02%7Csyedrahila%40microsoft.com%7C3b35e97c29cf4796042408dcee8a4dbb%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C638647525436604911%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=cbO2DBP6IsgMPTEVFNh%2FKeq4IoK3MZvTpzKkCQzNPMo%3D&reserved=0
[2]
https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fmessage-id%2Fbea016ad-d1a7-f01d-a7e8-01106a1de77f%2540oss.nttdata.com&data=05%7C02%7Csyedrahila%40microsoft.com%7C3b35e97c29cf4796042408dcee8a4dbb%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C638647525436629740%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=UCwkwg6kikVEf0oHf3%2BlliA%2FTUdMG%2F0cOiMta7fjPPk%3D&reserved=0
--
Regards,
--
Atsushi Torikoshi
Seconded from NTT DATA GROUP CORPORATION to SRA OSS K.K.
