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

Hi,

On 2020-08-19 11:01:37 -0400, Tom Lane wrote:
> Hadn't been paying attention to this thread up till now, but ...
>
> Michael Paquier <michael(at)paquier(dot)xyz> writes:
> > By the way, I was looking at the code that has been committed, and I
> > think that it is awkward to have a SQL function in mcxt.c, which is a
> > rather low-level interface. I think that this new code should be
> > moved to its own file, one suggestion for a location I have being
> > src/backend/utils/adt/mcxtfuncs.c.
>
> I agree with that, but I think this patch has a bigger problem:
> why bother at all? It seems like a waste of code space and future
> maintenance effort, because there is no use-case. In the situations
> where you need to know where the memory went, you are almost never
> in a position to leisurely execute a query and send the results over
> to your client. This certainly would be useless to figure out why
> an already-running query is eating space, for instance.

I don't agree with this at all. I think there's plenty use cases. It's
e.g. very common to try to figure out why the memory usage of a process
is high. Is it memory not returned to the OS? Is it caches that have
grown too much etc.

I agree it's not perfect:

> Plus you need to be running an interactive session, or else be willing
> to hack up your application to try to get it to inspect the view (and
> log the results somewhere) at useful times.

and that we likely should address that by *also* allowing to view the
memory usage of another process. Which obviously isn't entirely
trivial. But some infrastructure likely could be reused.

> My own thoughts about improving the debugging situation would've been
> to create a way to send a signal to a session to make it dump its
> current memory map to the postmaster log (not the client, since the
> client is unlikely to be prepared to receive anything extraneous).
> But this is nothing like that.

That doesn't really work in a large number of environments, I'm
afraid. Many many users don't have access to the server log.

> If it stays, I'd like to see restrictions on who can read the view.

As long as access is grantable rather than needing a security definer
wrapper I'd be fine with that.

Greetings,

Andres Freund