Re: Unexplained rapid growth in memory usage of idle backends

On Fri, May 12, 2023 at 7:57 PM Alex Balashov <abalashov(at)evaristesys(dot)com>
wrote:

>
>
> >> 782839 postgres 20 0 2999896 2.7g 551832 S 8.7 4.3 220:54.55
> postgres
>

If you press 'c' within top, it will change the display to show the full
process title, which will give you more info. Although presumably that
info would mostly be redundant to what you can already see in
pg_stat_actiivty, but it is very nice to see the correlation between the
CPU and the Memory and this other info. I usually configure my 'top' so
that this is the default mode for it to start in.

> `pg_stat_activity` just reveals an active WalSender (for streaming
replication) and the `pg_stat_activity` query (WHERE state <> 'idle')
itself. Once in a while, I'll catch a shortlived query in _one_ of these,
if I run it often enough.

It is hard to square this with your "top" output. I can see how they could
be idle while holding memory, but how could they be idle while exercising
that much CPU currently, and having accumulated so much CPU usage? Are you
checking pg_stat_activity as an unprivileged user, in which case 'state'
will show up as NULL for other users' processes? Maybe you have more than
one db server running, and the one accumulating the memory is not in the
one you are checking pg_stat_activiy against? Or maybe you have malware
running in the postgres account, and it is just masquerading as postgres
but is not actually postgres.

Is the number of 'idle' processes you see in pg_stat_activity matching up
with the number of postgres processes you see in 'top'?

[about memory context...]

> 2023-05-12 23:53:23.293 UTC [3934125] eti_adm(at)eti LOG: Grand total:
> 6417232 bytes in 2461 blocks; 2409920 free (468 chunks); 4007312 used
>
> But what can I learn from this that might be of applied value?
>

I would say there is nothing of value there. It looks like that is for the
wrong backend. That is, one which was not using much memory at the time.
Try again making sure to catch a process which is actually using a lot.
Although it could be that the process is truly using a lot of memory but
just outside of the PostgreSQL memory accounting system. That could be due
to a custom extension, a postgres bug (or perhaps a JIT bug), or malware.

Cheers,

Jeff