Обсуждение: Oom on temp (un-analyzed table caused by JIT) V16.1

> On 15 Jan 2024, at 07:24, Kirk Wolak <wolakk@gmail.com> wrote:

>   You have a commit [1] that MIGHT fix this.
> I have a script that recreates the problem, using random data in pg_temp.
> And a nested cursor.

Running your reproducer script in a tight loop for a fair bit of time on the
v16 HEAD I cannot see any memory growth, so it's plausible that the upcoming
16.2 will work better in your environment.

>   It took me a few days to reduce this from actual code that was experiencing this.  If I turn off JIT, the problem
goesaway.  (if I don't FETCH the first row, the memory loss does not happen.  Maybe because opening a cursor is more
decoration/prepare)
>
>   I don't have an easy way to test this script right now against the commit.

There are up to date snapshots of the upcoming v16 minor release which might
make testing easier than building postgres from source?

    https://www.postgresql.org/download/snapshots/

Admittedly I don't know whether those are built with LLVM support but I think
they might be.

> I am hopeful that your fix fixes this.

It seems likely, so it would be very valuable if you could try running the pre
-release version of v16 before 16.2 ships to verify this.

--
Daniel Gustafsson

> On 15 Jan 2024, at 16:49, Kirk Wolak <wolakk@gmail.com> wrote:
> On Mon, Jan 15, 2024 at 9:03 AM Daniel Gustafsson <daniel@yesql.se <mailto:daniel@yesql.se>> wrote:

> The script starts by creating 90 Million rows...  In my world that part of the script, plus the indexes, etc.  Takes
about8-9 minutes. 
> And has no memory loss.

That's expected, the memory leak did not affect those operations.

> I used the memory reported by HTOP to track the problem.  I Forgot to mention this.
> I am curious what you used?  (Because it doesn't display symptoms [running dog slow] until the backend has about 85%
ofthe machines memory) 

I use a combination of tools, in thise case I analyzed a build with Instruments on macOS.

> There are up to date snapshots of the upcoming v16 minor release which might
> make testing easier than building postgres from source?
>
>     https://www.postgresql.org/download/snapshots/ <https://www.postgresql.org/download/snapshots/>
>
> Thank you.  I have assigned that task to the guy who maintains our VMs/Environments.
> I will report back to you.

Great!

--
Daniel Gustafsson

> On 16 Jan 2024, at 02:53, Kirk Wolak <wolakk@gmail.com> wrote:
>
> On Mon, Jan 15, 2024 at 9:03 AM Daniel Gustafsson <daniel@yesql.se <mailto:daniel@yesql.se>> wrote:
> > On 15 Jan 2024, at 07:24, Kirk Wolak <wolakk@gmail.com <mailto:wolakk@gmail.com>> wrote:
>
> >   You have a commit [1] that MIGHT fix this.
> > I have a script that recreates the problem, using random data in pg_temp.
> > And a nested cursor.
>
> Running your reproducer script in a tight loop for a fair bit of time on the
> v16 HEAD I cannot see any memory growth, so it's plausible that the upcoming
> 16.2 will work better in your environment.
>
> Okay, I took the latest source off of git (17devel) and got it to work there in a VM.
>
> It appears this issue is fixed.  It must have been related to the issue originally tagged.

Thanks for testing and confirming!  Testing pre-release builds on real life
workloads is invaluable for the development of Postgres so thank you taking the
time.

--
Daniel Gustafsson

On Thu, 2024-01-18 at 19:50 -0500, Kirk Wolak wrote:
>   I did a little more checking and the reason I did not see the link MIGHT be because EXPLAIN did not show a JIT
attempt.
> I tried to use settings that FORCE a JIT...  But to no avail.
>
>   I am now concerned that the problem is more hidden in my use case.  Meaning I CANNOT conclude it is fixed.
> But I know of NO WAY to force a JIT (I lowered costs to 1, etc.  ).
>
>   You don't know a way to force at least the JIT analysis to happen?  (because I already knew if JIT was off, the
leakwouldn't happen).  

If you set the limits to 0, you can trigger it easily:

SET jit = on;
SET jit_above_cost = 0;
SET jit_inline_above_cost = 0;
SET jit_optimize_above_cost = 0;

EXPLAIN (ANALYZE) SELECT count(*) FROM foo;
                                                                QUERY PLAN
                  

══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════
 Finalize Aggregate  (cost=58889.84..58889.85 rows=1 width=8) (actual time=400.462..418.214 rows=1 loops=1)
   ->  Gather  (cost=58889.62..58889.83 rows=2 width=8) (actual time=400.300..418.190 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=57889.62..57889.64 rows=1 width=8) (actual time=384.876..384.878 rows=1 loops=3)
               ->  Parallel Seq Scan on foo  (cost=0.00..52681.30 rows=2083330 width=0) (actual time=0.028..168.510
rows=1666667loops=3) 
 Planning Time: 0.133 ms
 JIT:
   Functions: 8
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 1.038 ms, Inlining 279.779 ms, Optimization 38.395 ms, Emission 73.105 ms, Total 392.316 ms
 Execution Time: 478.257 ms

Yours,
Laurenz Albe

> On 19 Jan 2024, at 01:50, Kirk Wolak <wolakk@gmail.com> wrote:

>   I did a little more checking and the reason I did not see the link MIGHT be because EXPLAIN did not show a JIT
attempt.
> I tried to use settings that FORCE a JIT...  But to no avail.

Are you sure you are running a JIT enabled server?  Did you compile it yourself
or install a snapshot?

>   You don't know a way to force at least the JIT analysis to happen?  (because I already knew if JIT was off, the
leakwouldn't happen).  

If you set jit_above_cost=0 then postgres will compile a JIT enabled execution
tree.  This does bring up an interesting point, I don't think there is a way
for a user to know whether the server is jit enabled or not (apart from
explaining a query with costs adjusted but that's not all that userfriendly).
Maybe we need a way to reliably tell if JIT is active or not.

--
Daniel Gustafsson

Hi,

On Fri, Jan 19, 2024 at 10:48:12AM +0100, Daniel Gustafsson wrote:
> This does bring up an interesting point, I don't think there is a way
> for a user to know whether the server is jit enabled or not (apart
> from explaining a query with costs adjusted but that's not all that
> userfriendly).  Maybe we need a way to reliably tell if JIT is active
> or not.

I thought this is what pg_jit_available() is for?

postgres=> SHOW jit;
 jit 
-----
 on
(1 Zeile)

postgres=> SELECT pg_jit_available();
 pg_jit_available 
------------------
 f
(1 Zeile)


Michael

> On 19 Jan 2024, at 11:04, Michael Banck <mbanck@gmx.net> wrote:
> 
> Hi,
> 
> On Fri, Jan 19, 2024 at 10:48:12AM +0100, Daniel Gustafsson wrote:
>> This does bring up an interesting point, I don't think there is a way
>> for a user to know whether the server is jit enabled or not (apart
>> from explaining a query with costs adjusted but that's not all that
>> userfriendly).  Maybe we need a way to reliably tell if JIT is active
>> or not.
> 
> I thought this is what pg_jit_available() is for?

Ah, it is, I completely forgot we had that one. Thanks!

--
Daniel Gustafsson

> On 19 Jan 2024, at 23:09, Kirk Wolak <wolakk@gmail.com> wrote:

>  From a FUTURE email, I noticed pg_jit_available() and it's set to f??

Right, then this installation does not contain the necessary library to JIT
compile the query.

> Okay, so does this require a special BUILD command?

Yes, it requires that you compile with --with-llvm.  If you don't have llvm in
the PATH you might need to set LLVM_CONFIG to point to your llvm-config binary.
With autotools that would be something like:

    ./configure <other params> --with-llvm LLVM_CONFIG=/path/to/llvm-config

--
Daniel Gustafsson

On Thu, Jan 25, 2024 at 8:51 AM Kirk Wolak <wolakk@gmail.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.

Hi,

On Wed, Jan 24, 2024 at 02:50:52PM -0500, Kirk Wolak wrote:
> On Mon, Jan 22, 2024 at 1:30 AM Kirk Wolak <wolakk@gmail.com> wrote:
> > On Fri, Jan 19, 2024 at 7:03 PM Daniel Gustafsson <daniel@yesql.se> wrote:
> >> > On 19 Jan 2024, at 23:09, Kirk Wolak <wolakk@gmail.com> wrote:
> > Thank you, that made it possible to build and run...
> > UNFORTUNATELY this has a CLEAR memory leak (visible in htop)
> > I am watching it already consuming 6% of my system memory.
> >
> Daniel,
>   In the previous email, I made note that once the JIT was enabled, the
> problem exists in 17Devel.
> I re-included my script, which forced the JIT to be used...
> 
>   I attached an updated script that forced the settings.
> But this is still leaking memory (outside of the
> pg_backend_memory_context() calls).
> Probably because it's at the LLVM level?  And it does NOT happen from
> planning/opening the query.  It appears I have to fetch the rows to
> see the problem.

I had a look at this (and blogged about it here[1]) and was also
wondering what was going on with 17devel and the recent back-branch
releases, cause I could also reproduce those continuing memory leaks.

Adding some debug logging when llvm_inline_reset_caches() is called
solves the mystery: as you are calling a function, the fix that is in
17devel and the back-branch releases is not applicable and only after
the function returns llvm_inline_reset_caches() is being called (as
llvm_jit_context_in_use_count is greater than zero, presumably, so it
never reaches the call-site of llvm_inline_reset_caches()).

If you instead run your SQL in a DO-loop (as in the blog post) and not
as a PL/PgSQL function, you should see that it no longer leaks. This
might be obvious to some (and Andres mentioned it in 
https://www.postgresql.org/message-id/20210421002056.gjd6rpe6toumiqd6%40alap3.anarazel.de)
but it took me a while to figure out/find.


Michael

[1] https://www.credativ.de/en/blog/postgresql-en/quick-benchmark-postgresql-2024q1-release-performance-improvements/