Re: Adding column "mem_usage" to view pg_prepared_statements

Am 05.08.2019 um 19:16 schrieb Andres Freund:
> On 2019-07-28 06:20:40 +0000, Daniel Migowski wrote:
>> how do you want to generalize it? Are you thinking about a view solely
>> for the display of the memory usage of different objects?
> I'm not quite sure. I'm just not sure that adding separate
> infrastructure for various objects is a sutainable approach. We'd likely
> want to have this for prepared statements, for cursors, for the current
> statement, for various caches, ...
>
> I think an approach would be to add an 'owning_object' field to memory
> contexts, which has to point to a Node type if set. A table returning reporting
> function could recursively walk through the memory contexts, starting at
> TopMemoryContext. Whenever it encounters a context with owning_object
> set, it prints a string version of nodeTag(owning_object). For some node
> types it knows about (e.g. PreparedStatement, Portal, perhaps some of
> the caches), it prints additional metadata specific to the type (so for
> prepared statement it'd be something like 'prepared statement', '[name
> of prepared statement]'), and prints information about the associated
> context and all its children.
I understand. So it would be something like the output of
MemoryContextStatsInternal, but in table form with some extra columns. I
would have loved this extra information already in
MemoryContextStatsInternal btw., so it might be a good idea to upgrade
it first to find the information and wrap a table function over it
afterwards.
> The general context information probably should be something like:
> context_name, context_ident,
> context_total_bytes, context_total_blocks, context_total_freespace, context_total_freechunks, context_total_used, context_total_children
> context_self_bytes, context_self_blocks, context_self_freespace, context_self_freechunks, context_self_used, context_self_children,
>
> It might make sense to have said function return a row for the contexts
> it encounters that do not have an owner set too (that way we'd e.g. get
> CacheMemoryContext handled), but then still recurse.
A nice way to learn about the internals of the server and to analyze the
effects of memory reducing enhancements.
> Arguably the proposed owning_object field would be a bit redundant with
> the already existing ident/MemoryContextSetIdentifier field, which
> e.g. already associates the query string with the contexts used for a
> prepared statement. But I'm not convinced that's going to be enough
> context in a lot of cases, because e.g. for prepared statements it could
> be interesting to have access to both the prepared statement name, and
> the statement.
The identifier seems to be more like a category at the moment, because
it does not seem to hold any relevant information about the object in
question. So a more specific name would be nice.
> The reason I like something like this is that we wouldn't add new
> columns to a number of views, and lack views to associate such
> information to for some objects. And it'd be disproportional to add all
> the information to numerous places anyway.
I understand your argumentation, but things like Cursors and Portals are
rather short living while prepared statements seem to be the place where
memory really builds up.
> One counter-argument is that it'd be more expensive to get information
> specific to prepared statements (or other object types) that way. I'm
> not sure I buy that that's a problem - this isn't something that's
> likely going to be used at a high frequency. But if it becomes a
> problem, we can add a function that starts that process at a distinct
> memory context (e.g. a function that does this just for a single
> prepared statement, identified by name) - but I'd not start there.
I also see no problem here, and with Konstantin Knizhnik's autoprepare I
wouldn't use this very often anyway, more just for monitoring purposes,
where I don't care if my query is a bit more complex.
>> While being interesting I still believe monitoring the mem usage of
>> prepared statements is a bit more important than that of other objects
>> because of how they change memory consumption of the server without
>> using any DDL or configuration options and I am not aware of other
>> objects with the same properties, or are there some? And for the other
>> volatile objects like tables and indexes and their contents PostgreSQL
>> already has it's information functions.
> Plenty other objects have that property. E.g. cursors. And for the
> catalog/relation/... caches it's even more pernicious - the client might
> have closed all its "handles", but we still use memory (and it's
> absolutely crucial for performance).

Maybe we can do both? Add a single column to pg_prepared_statements, and
add another table for the output of MemoryContextStatsDetail? This has
the advantage that the single real memory indicator useful for end users
(to the question: How much mem takes my sh*t up?) is in
pg_prepared_statements and some more intrinsic information in a detail
view.

Thinking about the latter I am against such a table, at least in the
form where it gives information like context_total_freechunks, because
it would just be useful for us developers. Why should any end user care
for how many chunks are still open in a MemoryContext, except when he is
working on C-style extensions. Could just be a source of confusion for
them.

Let's think about the goal this should have: The end user should be able
to monitor the memory consumption of things he's in control of or could
affect the system performance. Should such a table automatically
aggregate some information? I think so. I would not add more than two
memory columns to the view, just mem_used and mem_reserved. And even
mem_used is questionable, because in his eyes only the memory he cannot
use for other stuff because of object x is important for him (that was
the reason I just added one column). He would even ask: WHY is there 50%
more memory reserved than used, and how I can optimize it? (Would lead
to more curious PostgreSQL developers maybe, so that's maybe a plus).

Something that also clearly speaks FOR such a table and against my
proposal is, that if someone cares for memory, he would most likely care
for ALL his memory, and in that case monitoring prepared statements
would just be a small subset of stuff to monitor. Ok, I am defeated and
will rewrite my patch if the next proposal finds approval:

I would propose a table pg_mem_usage containing the columns
object_class, name, detail, mem_usage (rename them if it fits the style
of the other tables more). The name would be empty for some objects like
the unnamed prepared statement, the query strings would be in the detail
column. One could add a final "Other" row containing the mem no specific
output line has been accounted for. Also it could contain lines for
Cursors and other stuff I am to novice to think of here.

And last: A reason why still we need a child-parent-relationship in this
table (and distinct this_ and total_ mem functions), is that prepared
statements start up to use much more memory when the Generic Plan is
stored in it after a few uses. As a user I always had the assumption
that prepared a statement would already do all the required work to be
fast, but a statement just becomes blazingly fast when the Generic Plan
is available (and used), and it would be nice to see for which
statements that plan has already been generated to consume his memory. I
believe the reason for this would be the fear of excessive memory usage.

On the other hand: The Generic Plan had been created for the first
invocation of the prepared statement, why not store it immediatly. It is
a named statement for a reason that it is intended to be reused, even
when it is just twice, and since memory seems not to be seen as a scarce
resource in this context why not store that immediately. Would drop the
need for a hierarchy here also.

Any comments?

Regards,
Daniel Migowski