Thread: plpgsql memory leaks

plpgsql memory leaks

From
Pavel Stehule
Date:
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.

Regards

Pavel

Re: plpgsql memory leaks

From
Pavel Stehule
Date:


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


Regards

Pavel

Re: plpgsql memory leaks

From
Michael Banck
Date:
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



Re: plpgsql memory leaks

From
Pavel Stehule
Date:


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

Re: plpgsql memory leaks

From
Michael Banck
Date:
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



Re: plpgsql memory leaks

From
Pavel Stehule
Date:


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

 


Michael

Re: plpgsql memory leaks

From
Tom Lane
Date:
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



Re: plpgsql memory leaks

From
Pavel Stehule
Date:
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


 

                        regards, tom lane