plpgsql memory leaks

pá 12. 1. 2024 v 10:27 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

Hi

I have reported very memory expensive pattern:

CREATE OR REPLACE FUNCTION public.fx(iter integer)
RETURNS void
LANGUAGE plpgsql
AS $function$
declare
c cursor(m bigint) for select distinct i from generate_series(1, m) g(i);
t bigint;
s bigint;
begin
for i in 1..iter
loop
open c(m := i * 10000);
s := 0;
loop
fetch c into t;
exit when not found;
s := s + t;
end loop;
close c; raise notice '%=%', i, s;
end loop;
end;
$function$
;

This script takes for 100 iterations 100MB

but rewritten

CREATE OR REPLACE FUNCTION public.fx(iter integer)
RETURNS void
LANGUAGE plpgsql
AS $function$
declare
t bigint;
s bigint;
begin
for i in 1..iter
loop
s := 0;
for t in select ic from generate_series(1, i * 10000) g(ic)
loop
s := s + t;
end loop;
raise notice '%=%', i, s;
end loop;
end;
$function$

takes lot of megabytes of memory too.

The megabytes leaks are related to JIT. With JIT off the memory consumption
is significantly less although there are some others probably.

regards

Pavel

Hi,

On Fri, Jan 12, 2024 at 11:02:14AM +0100, Pavel Stehule wrote:

p� 12. 1. 2024 v 10:27 odes�latel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

Hi

I have reported very memory expensive pattern:

[...]

takes lot of megabytes of memory too.

The megabytes leaks are related to JIT. With JIT off the memory consumption
is significantly less although there are some others probably.

I cannot readily reproduce this.

Which version of Postgres is this and on which platform/distribution?

Did you try keep jit on but set jit_inline_above_cost to 0?

The back-branches have a fix for the above case, i.e. llvmjit memleaks
that can be worked-around by setting jit_inline_above_cost=0.

Michael

pá 12. 1. 2024 v 11:54 odesílatel Michael Banck <mbanck@gmx.net> napsal:

Hi,

On Fri, Jan 12, 2024 at 11:02:14AM +0100, Pavel Stehule wrote:

pá 12. 1. 2024 v 10:27 odesílatel Pavel Stehule <pavel.stehule@gmail.com

napsal:

Hi

I have reported very memory expensive pattern:

[...]

takes lot of megabytes of memory too.

The megabytes leaks are related to JIT. With JIT off the memory

consumption

is significantly less although there are some others probably.

I cannot readily reproduce this.

Which version of Postgres is this and on which platform/distribution?

It was tested on master branch (pg 17) on Fedora 39

Did you try keep jit on but set jit_inline_above_cost to 0?

The back-branches have a fix for the above case, i.e. llvmjit memleaks
that can be worked-around by setting jit_inline_above_cost=0.

I'll do recheck

Pavel

Hi,

On Fri, Jan 12, 2024 at 01:35:24PM +0100, Pavel Stehule wrote:

p� 12. 1. 2024 v 11:54 odes�latel Michael Banck <mbanck@gmx.net> napsal:

Which version of Postgres is this and on which platform/distribution?

It was tested on master branch (pg 17) on Fedora 39

Did you try keep jit on but set jit_inline_above_cost to 0?

The back-branches have a fix for the above case, i.e. llvmjit memleaks
that can be worked-around by setting jit_inline_above_cost=0.

I got that wrong, it needs to be -1 to disable it.

But if you are already running the master branch, it is probably a
separate issue.

Michael

pá 12. 1. 2024 v 14:53 odesílatel Michael Banck <mbanck@gmx.net> napsal:

Hi,

On Fri, Jan 12, 2024 at 01:35:24PM +0100, Pavel Stehule wrote:

pá 12. 1. 2024 v 11:54 odesílatel Michael Banck <mbanck@gmx.net> napsal:

Which version of Postgres is this and on which platform/distribution?

It was tested on master branch (pg 17) on Fedora 39

Did you try keep jit on but set jit_inline_above_cost to 0?

The back-branches have a fix for the above case, i.e. llvmjit memleaks
that can be worked-around by setting jit_inline_above_cost=0.

I got that wrong, it needs to be -1 to disable it.

But if you are already running the master branch, it is probably a
separate issue.

I tested code

CREATE OR REPLACE FUNCTION public.fx(iter integer)
RETURNS void
LANGUAGE plpgsql
AS $function$
declare
c cursor(m bigint) for select distinct i from generate_series(1, m) g(i);
t bigint;
s bigint;
begin
for i in 1..iter
loop
s := 0;
for r in c(i*10000)
loop
s := s + r.i;
end loop;
raise notice '%=%', i, s;
end loop;
end;
$function$

default master branch - res 190MB ram
jit_inline_above_cost = -1 doesn't helps
disabling JIT doesn't helps too,

so it looks like the wrong hypothesis , and the problem is maybe somewhere
else :-/

Regards

Pavel

Pavel Stehule <pavel.stehule@gmail.com> writes:

default master branch - res 190MB ram
jit_inline_above_cost = -1 doesn't helps
disabling JIT doesn't helps too,

so it looks like the wrong hypothesis , and the problem is maybe somewhere
else :-/

I see no leak with these examples on HEAD, either with or without
--enable-llvm --- the process size stays quite stable according
to "top". I wonder if you are using some extension that's
contributing to the problem.

regards, tom lane

Hi

pá 12. 1. 2024 v 22:25 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

Pavel Stehule <pavel.stehule@gmail.com> writes:

default master branch - res 190MB ram
jit_inline_above_cost = -1 doesn't helps
disabling JIT doesn't helps too,

so it looks like the wrong hypothesis , and the problem is maybe

somewhere

else :-/

I see no leak with these examples on HEAD, either with or without
--enable-llvm --- the process size stays quite stable according
to "top". I wonder if you are using some extension that's
contributing to the problem.

memory info after DO $$ BEGIN END $$;

(2024-01-13 05:36:46) postgres=# do $$ begin end $$;
DO
(2024-01-13 05:37:16) postgres=# select meminfo();
NOTICE: Total non-mmapped bytes (arena): 1114112
NOTICE: # of free chunks (ordblks): 11
NOTICE: # of free fastbin blocks (smblks): 0
NOTICE: # of mapped regions (hblks): 2
NOTICE: Bytes in mapped regions (hblkhd): 401408
NOTICE: Max. total allocated space (usmblks): 0
NOTICE: Free bytes held in fastbins (fsmblks): 0
NOTICE: Total allocated space (uordblks): 1039216
NOTICE: Total free space (fordblks): 74896
NOTICE: Topmost releasable block (keepcost): 67360

after script execution

NOTICE: ("1165 kB","1603 kB","438 kB")
NOTICE: Total non-mmapped bytes (arena): 22548480
NOTICE: # of free chunks (ordblks): 25
NOTICE: # of free fastbin blocks (smblks): 0
NOTICE: # of mapped regions (hblks): 2
NOTICE: Bytes in mapped regions (hblkhd): 401408
NOTICE: Max. total allocated space (usmblks): 0
NOTICE: Free bytes held in fastbins (fsmblks): 0
NOTICE: Total allocated space (uordblks): 1400224
NOTICE: Total free space (fordblks): 21148256
NOTICE: Topmost releasable block (keepcost): 20908384

so attached memory is 20MB - but is almost free. The sum of memory context
is very stable without leaks (used 1165kB).

but when I modify the script to

CREATE OR REPLACE FUNCTION public.fx(iter integer)
RETURNS void
LANGUAGE plpgsql
AS $function$
declare
c cursor(m bigint) for select distinct i from generate_series(1, m) g(i);
t bigint;
s bigint;
begin
for i in 1..iter
loop
open c(m := i * 10000);
s := 0;
loop
fetch c into t;
exit when not found;
s := s + t;
end loop;
raise notice '===========before close';
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());
--perform meminfo();
raise notice '-----------after close';
close c;
raise notice '%=%', i, s;
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());
--perform meminfo();
end loop;
end;
$function$

meminfo is simple extension - see the attachment, I got interesting things

NOTICE: ===========before close
NOTICE: ("149 MB","154 MB","5586 kB")
NOTICE: Total non-mmapped bytes (arena): 132960256
NOTICE: # of free chunks (ordblks): 49
NOTICE: # of free fastbin blocks (smblks): 0
NOTICE: # of mapped regions (hblks): 4
NOTICE: Bytes in mapped regions (hblkhd): 51265536
NOTICE: Max. total allocated space (usmblks): 0
NOTICE: Free bytes held in fastbins (fsmblks): 0
NOTICE: Total allocated space (uordblks): 110730576
NOTICE: Total free space (fordblks): 22229680
NOTICE: Topmost releasable block (keepcost): 133008

so this script really used mbytes memory, but it is related to query
`select distinct i from generate_series(1, m) g(i);`

This maybe is in correlation to my default work mem 64MB - when I set work
mem to 10MB, then it consumes only 15MB

So I was confused because it uses only about 3x work_mem, which is not too
bad.

Regards

Pavel