Re: Creating a function for exposing memory usage of backend process

On 2020-06-20 03:11, Robert Haas wrote:
> On Wed, Jun 17, 2020 at 11:56 PM Fujii Masao
> <masao(dot)fujii(at)oss(dot)nttdata(dot)com> wrote:
>> > As a first step, to deal with (3) I'd like to add
>> > pg_stat_get_backend_memory_context() which target is limited to the
>> > local backend process.
>>
>> +1
>
> +1 from me, too.

Attached a patch that adds a function exposing memory usage of local
backend.

It's almost same as pg_cheat_funcs's pg_stat_get_memory_context().
I've also added MemoryContexts identifier because it seems useful to
distinguish the same kind of memory contexts.

For example, when there are many prepared statements we can
distinguish them using identifiers, which shows the cached query.

=# SELECT name, ident FROM pg_stat_get_memory_contexts() WHERE name =
'CachedPlanSource';
name | ident
------------------+--------------------------------
CachedPlanSource | PREPARE q1(text) AS SELECT ..;
CachedPlanSource | PREPARE q2(text) AS SELECT ..;
(2 rows)

> Something that exposed this via shared memory would
> be quite useful, and there are other things we'd like to expose
> similarly, such as the plan(s) from the running queries, or even just
> the untruncated query string(s). I'd like to have a good
> infrastructure for that sort of thing, but I think it's quite tricky
> to do properly. You need a variable-size chunk of shared memory, so
> probably it has to use DSM somehow, and you need to update it as
> things change, but if you update it too frequently performance will
> stink. If you ping processes to do the updates, how do you know when
> they've completed them, and what happens if they don't respond in a
> timely fashion? These are probably all solvable problems, but I don't
> think they are very easy ones.

Thanks for your comments!

It seems hard as you pointed out.
I'm going to consider it along with the advice of Fujii and Kasahara.

Regards,

--
Atsushi Torikoshi
NTT DATA CORPORATION