Re: Oom on temp (un-analyzed table caused by JIT) V16.1 [ NOT Fixed ]

On Wed, Jan 24, 2024 at 4:16 PM Thomas Munro <thomas(dot)munro(at)gmail(dot)com> wrote:

> On Thu, Jan 25, 2024 at 8:51 AM Kirk Wolak <wolakk(at)gmail(dot)com> wrote:
> > getrusage(RUSAGE_SELF, &usage);
> > memory_usage_bytes = usage.ru_maxrss * 1024;
>
> FWIW log_statement_stats = on shows that in the logs. See ShowUsage()
> in postgres.c.
>

Thank you for this, here is the *TERMINAL *(Below is the tail of the log).
Notice that the pg_backend_memory_contexts does NOT show the memory
consumed.
But your logging sure did! (I wonder if I enable logging during planning,
but there is like 82,000 cursors being opened... (This removed the FETCH
and still leaks)

7:01:08 kwolak(at)postgres= # *select pg_temp.fx(497);*
NOTICE: ("9848 kB","10 MB","638 kB")
NOTICE: -----------after close, Count a: 82636, count b: 82636
NOTICE: ("9997 kB","10 MB","648 kB")
fx
----

(1 row)

Time: 525870.117 ms (08:45.870)

*Tail*:

024-01-24 17:01:08.752 EST [28804] DETAIL: ! system usage stats:
! 0.001792 s user, 0.000000 s system, 0.005349 s elapsed
! [560.535969 s user, 31.441656 s system total]
! 185300 kB max resident size
! 232/0 [29219648/54937864] filesystem blocks in/out
! 0/25 [0/1016519] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 10/1 [62671/9660] voluntary/involuntary context switches
2024-01-24 17:01:08.752 EST [28804] STATEMENT: explain SELECT DISTINCT
seid, fr_field_name, st_field_name
FROM pg_temp.parts
WHERE seid <> 497 AND partnum >= '1'
ORDER BY seid;
2024-01-24 17:01:08.759 EST [28804] LOG: QUERY STATISTICS
2024-01-24 17:01:08.759 EST [28804] DETAIL: ! system usage stats:
! 0.006207 s user, 0.000092 s system, 0.006306 s elapsed
! [560.542262 s user, 31.441748 s system total]
!* 185300 kB max resident size*
! 0/0 [29219648/54937864] filesystem blocks in/out
! 0/4 [0/1016523] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/1 [62672/9661] voluntary/involuntary context switches
2024-01-24 17:01:08.759 EST [28804] STATEMENT: SELECT 'pg_temp.fx(497); --
Not run, do \dt+ parts';
2024-01-24 17:04:30.844 EST [28746] LOG: checkpoint starting: time
2024-01-24 17:04:32.931 EST [28746] LOG: checkpoint complete: wrote 21
buffers (0.1%); 0 WAL file(s) added, 0 removed, 0 recycled; write=2.008 s,
sync=0.006 s, total=2.087 s; sync files=15, longest=0.001 s, average=0.001
s; distance=98 kB, estimate=134 kB; lsn=0/16304D8, redo lsn=0/1630480
2024-01-24 17:11:06.350 EST [28804] LOG: QUERY STATISTICS
2024-01-24 17:11:06.350 EST [28804] DETAIL: ! system usage stats:
! 515.952870 s user, 6.688389 s system, 525.869933 s elapsed
! [1076.495280 s user, 38.130145 s system total]
!* 708104 kB max resident size*
! 370000/3840 [29589648/54941712] filesystem blocks in/out
! 0/327338 [0/1343861] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 22001/5216 [84675/14878] voluntary/involuntary context
switches
2024-01-24 17:11:06.350 EST [28804] STATEMENT: * select pg_temp.fx(497);*
2024-01-24 17:12:16.162 EST [28804] LOG: QUERY STATISTICS
2024-01-24 17:12:16.162 EST [28804] DETAIL: ! system usage stats:
! 1.130029 s user, 0.007727 s system, 1.157486 s elapsed
! [1077.625396 s user, 38.137921 s system total]
! *708104 kB max resident size*
! 992/0 [29590640/54941720] filesystem blocks in/out
! 3/41 [3/1343902] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 9/68 [84685/14946] voluntary/involuntary context switches
2024-01-24 17:12:16.162 EST [28804] STATEMENT: select now();
2024-01-24 17:12:30.944 EST [28804] LOG: QUERY STATISTICS
2024-01-24 17:12:30.944 EST [28804] DETAIL: ! system usage stats:
! 0.004561 s user, 0.000019 s system, 0.004580 s elapsed
! [1077.630064 s user, 38.137944 s system total]
! *708104 kB max resident size*
! 0/0 [29590640/54941728] filesystem blocks in/out
! 0/4 [3/1343906] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [84686/14947] voluntary/involuntary context switches
2024-01-24 17:12:30.944 EST [28804] STATEMENT: select now();