On 5/12/23 23:36, Melanie Plageman wrote:
> Thanks for continuing to work on this.
>
> Are you planning to modify what is displayed for memory usage in
> EXPLAIN ANALYZE?
>
We could do that, but we can do that separately - it's a separate and
independent improvement, I think.
Also, do you have a proposal how to change the explain output? In
principle we already have the number of batches, so people can calculate
the "peak" amount of memory (assuming they realize what it means).
I think the main problem with adding this info to EXPLAIN is that I'm
not sure it's very useful in practice. I've only really heard about this
memory explosion issue when the query dies with OOM or takes forever,
but EXPLAIN ANALYZE requires the query to complete.
A separate memory context (which pg_log_backend_memory_contexts can
dump to server log) is more valuable, I think.
> Also, since that won't help a user who OOMs, I wondered if the spillCxt
> is helpful on its own or if we need some kind of logging message for
> users to discover that this is what led them to running out of memory.
>
I think the separate memory context is definitely an improvement,
helpful on it's own it makes it clear *what* allocated the memory. It
requires having the memory context stats, but we may already dump them
into the server log if malloc returns NULL. Granted, it depends on how
the system is configured and it won't help when OOM killer hits :-(
I wouldn't object to having some sort of log message, but when exactly
would we emit it? Presumably after exceeding some amount of memory, but
what would it be? It can't be too low (not to trigger it too often) or
too high (failing to report the issue). Also, do you think it should go
to the user or just to the server log?
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company