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

[Kirk Wolak]

On Mon, Jan 15, 2024 at 9:03 AM Daniel Gustafsson  wrote:

> > On 15 Jan 2024, at 07:24, Kirk Wolak  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!

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

[Daniel Gustafsson]

> On 16 Jan 2024, at 02:53, Kirk Wolak  wrote:
> 
> On Mon, Jan 15, 2024 at 9:03 AM Daniel Gustafsson  > wrote:
> > On 15 Jan 2024, at 07:24, Kirk Wolak  > > 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

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

[Kirk Wolak]

On Tue, Jan 16, 2024 at 3:43 AM Daniel Gustafsson  wrote:

> > On 16 Jan 2024, at 02:53, Kirk Wolak  wrote:
> >
> > On Mon, Jan 15, 2024 at 9:03 AM Daniel Gustafsson  > wrote:
> > > On 15 Jan 2024, at 07:24, Kirk Wolak  wol...@gmail.com>> wrote:
> >...
> > 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,
  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 leak wouldn't happen).

Thanks,

Kirk Out!
PS: I assume there is no pg_jit(1) function I can call. LOL

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

[Laurenz Albe]

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 leak wouldn'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=167 loops=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

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

[Daniel Gustafsson]

> On 19 Jan 2024, at 01:50, 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.

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 leak wouldn'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

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

[Michael Banck]

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

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

[Daniel Gustafsson]

> On 19 Jan 2024, at 11:04, Michael Banck  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

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

[Kirk Wolak]

On Fri, Jan 19, 2024 at 4:20 AM Laurenz Albe 
wrote:

> 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 leak wouldn'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;
>

Okay,
  I Did exactly this (just now).  But the EXPLAIN does not contain the JIT?

---
 Sort  (cost=5458075.88..5477861.00 rows=7914047 width=12)
   Sort Key: seid
   ->  HashAggregate  (cost=3910139.62..4280784.00 rows=7914047 width=12)
 Group Key: seid, fr_field_name, st_field_name
 Planned Partitions: 128
 ->  Seq Scan on parts  (cost=0.00..1923249.00 rows=2985
width=12)
   Filter: ((seid <> 497) AND ((partnum)::text >= '1'::text))
(7 rows)

>From a FUTURE email, I noticed pg_jit_available()

and it's set to f??

Okay, so does this require a special BUILD command?

postgres=# select pg_jit_available();
 pg_jit_available
--
 f
(1 row)

postgres=# \dconfig *jit*
 List of configuration parameters
Parameter|  Value
-+-
 jit | on
 jit_above_cost  | 10
 jit_debugging_support   | off
 jit_dump_bitcode| off
 jit_expressions | on
 jit_inline_above_cost   | 50
 jit_optimize_above_cost | 50
 jit_profiling_support   | off
 jit_provider| llvmjit
 jit_tuple_deforming | on
(10 rows)

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

[Daniel Gustafsson]

> On 19 Jan 2024, at 23:09, Kirk Wolak  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  --with-llvm LLVM_CONFIG=/path/to/llvm-config

--
Daniel Gustafsson

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

[Kirk Wolak]

On Fri, Jan 19, 2024 at 7:03 PM Daniel Gustafsson  wrote:

> > On 19 Jan 2024, at 23:09, Kirk Wolak  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  --with-llvm LLVM_CONFIG=/path/to/llvm-config
>
> --
> Daniel Gustafsson
>

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.

I am re-attaching my script.  WHICH includes the settings to FORCE JIT.
It also does an EXPLAIN so you can verify that JIT is on (this is what I
added/noticed!)
And it takes over 20 minutes to get this far.  It's still running.
I am re-attaching the script. (as I tweaked it).

This creates 90 million rows of data, so it takes a while.
I BELIEVE that it consumes far less memory if you do not fetch any rows (I
had problems reproducing it if no rows were fetched).
So, this may be beyond the planning stages.

Thanks,

Kirk Out!
CREATE TABLE pg_temp.parts
(
seid bigint,
r_field_name_1   smallint,
fr_field_namesmallint   NOT NULL,
p1_field_namevarchar(4),
qty_field_name   integer,
p5_field_namevarchar(30),
partnum  varchar(30),
st_field_namesmallint DEFAULT 0 NOT NULL
); -- drop table pg_temp.parts;

INSERT INTO pg_temp.parts (seid, partnum, qty_field_name, fr_field_name, 
st_field_name)
SELECT (RANDOM() * 500 + 1)::bigint   AS seid,
   trunc(RANDOM() * 1)::text AS 
partnum,
   CASE
   WHEN q.rnd BETWEEN 0 AND 0.45 THEN FLOOR(RANDOM() * 900) + 100 -- 
Random number in the range [100, 999]
   WHEN q.rnd BETWEEN 0.46 AND 0.96 THEN LEAST(TRUNC(FLOOR(RANDOM() * 
99) + 1000)::int, 99::int) -- Random number in the range [1000, ]
   ELSE FLOOR(RANDOM() * 900) + 100 -- Random number in the 
range [10, 99]
   END AS 
qty_field_name,
   CASE WHEN RANDOM() < 0.72 THEN 0::smallint ELSE 1::smallint END AS 
fr_field_name,
   CASE WHEN RANDOM() < 0.46 THEN 1::smallint ELSE 2::smallint END AS 
st_field_name
  FROM (SELECT RANDOM() AS rnd, x FROM GENERATE_SERIES(1, 90_000_000) x) q;

CREATE INDEX idx_parts_supid ON pg_temp.parts USING btree (seid, p1_field_name, 
partnum, st_field_name, r_field_name_1, qty_field_name);
CREATE INDEX idx_parts_p5 ON pg_temp.parts USING btree (p5_field_name, seid, 
st_field_name, r_field_name_1, p1_field_name);
CREATE INDEX idx_parts_partnum ON pg_temp.parts USING btree (partnum, seid, 
st_field_name, r_field_name_1, p1_field_name);

CREATE OR REPLACE FUNCTION pg_temp.fx(asupplier bigint = 497 )
RETURNS void
LANGUAGE plpgsql
AS
$function$
DECLARE
supplier_parts   CURSOR (sid bigint) FOR  -- Again, selecting with 
COUNT() would reduce 1 query per row!
SELECT
partnum, qty_field_name, st_field_name, sum(qty_field_name) as qty
FROM pg_temp.parts
WHERE seid = sid AND (st_field_name = 1)
GROUP BY partnum, qty_field_name, st_field_name
ORDER BY partnum, qty_field_name, st_field_name;
supplier_part_qty_matches CURSOR (sid bigint, pnum varchar(30), pqty 
bigint) FOR
SELECT DISTINCT
seid, fr_field_name, partnum, st_field_name
FROM pg_temp.parts
WHERE seid <> sid AND partnum = pnum AND qty_field_name = pqty
ORDER BY seid, partnum;

a_partnum varchar(30);
a_qty integer;
a_st  smallint;
a_cnt integer = 0;
b_partnum varchar(30);
b_fr  smallint;
b_seidbigint;
b_st  smallint;
b_cnt bigint = 0;
BEGIN
RAISE NOTICE '%', (SELECT (PG_SIZE_PRETTY(SUM(used_bytes)), 
PG_SIZE_PRETTY(SUM(total_bytes)), PG_SIZE_PRETTY(SUM(free_bytes))) FROM 
pg_get_backend_memory_contexts());
OPEN supplier_parts (asupplier);
LOOP
FETCH supplier_parts INTO a_partnum, a_qty, a_st, a_qty;
EXIT WHEN NOT FOUND;
a_cnt := a_cnt + 1;
OPEN supplier_part_qty_matches (sid := asupplier, pnum := a_partnum, 
pqty := a_qty);
LOOP
FETCH supplier_part_qty_matches INTO b_seid, b_fr, b_partnum, b_st;
b_cnt := b_cnt + 1;
EXIT WHEN TRUE;  -- no Need to loop here  One FETCH per query 
triggers the losses.
END LOOP;
CLOSE supplier_part_qty_matches;
END LOOP;
CLOSE supplier_parts;
RAISE NOTICE '---after close, C