Thread: plpgsql memory leaks
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$
;
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$
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.
Regards
Pavel
pá 12. 1. 2024 v 10:27 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:
HiI 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 100MBbut rewrittenCREATE 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
RegardsPavel
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
Michael
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$
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
Michael
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
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
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$
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
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
regards, tom lane