Re: plpgsql memory leaks
От | Pavel Stehule |
---|---|
Тема | Re: plpgsql memory leaks |
Дата | |
Msg-id | CAFj8pRD0CWiuvc8tU-6MWgFQRnMqa7WxVN76QA0NzKKhHkEHMg@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: plpgsql memory leaks (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Xiaoran WangДата:
Сообщение: Re: Recovering from detoast-related catcache invalidations