On Fri, May 12, 2023 at 7:57 PM Alex Balashov <abalashov@xxxxxxxxxxxxxxx> 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@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
