Re: Add tracking of backend memory allocated to pg_stat_activity

Greetings,

* Drouvot, Bertrand (bdrouvot(at)amazon(dot)com) wrote:
> On 9/9/22 7:08 PM, Justin Pryzby wrote:
> >On Fri, Sep 09, 2022 at 12:34:15PM -0400, Stephen Frost wrote:
> >>>While we are at it, what do you think about also recording the max memory
> >>>allocated by a backend? (could be useful and would avoid sampling for which
> >>>there is no guarantee to sample the max anyway).
> >>What would you do with that information..? By itself, it doesn't strike
> >>me as useful. Perhaps it'd be interesting to grab the max required for
> >>a particular query in pg_stat_statements or such but again, that's a
> >>very different thing.
>
> >Storing the maxrss per backend somewhere would be useful (and avoid the
> >issue of "sampling" with top), after I agree that it ought to be exposed
> >to a view. For example, it might help to determine whether (and which!)
> >backends are using large multiple of work_mem, and then whether that can
> >be increased. If/when we had a "memory budget allocator", this would
> >help to determine how to set its GUCs, maybe to see "which backends are
> >using the work_mem that are precluding this other backend from using
> >efficient query plan".
>
> +1.

I still have a hard time seeing the value in tracking which backends are
using the most memory over the course of a backend's entire lifetime,
which would involve lots of different queries, some of which might use
many multiples of work_mem and others not. Much more interesting would
be to track this as part of pg_stat_statements and associated with
queries.

Either way, this looks like an independent feature which someone who has
interest in could work on but generally doesn't impact what the feature
of this thread is about; a feature which has already shown merit in
finding a recently introduced memory leak and is the basis of another
feature being contemplated to help avoid OOM-killer introduced crashes.

Thanks,

Stephen