Re: PostgreSQL 14.4 ERROR: out of memory issues
| От | Aleš Zelený |
|---|---|
| Тема | Re: PostgreSQL 14.4 ERROR: out of memory issues |
| Дата | |
| Msg-id | CAODqTUZ1KJEEnhw6_+CVBtLkjPuo40hDxy6E5O6ShP4MfhMQTQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: PostgreSQL 14.4 ERROR: out of memory issues (Aleš Zelený <zeleny.ales@gmail.com>) |
| Ответы |
Re: PostgreSQL 14.4 ERROR: out of memory issues
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| Список | pgsql-general |
Hello,
I did some testing and the result is that on pg12 there are no such problems. Pg13 and Pg 14.3 tests will follow but based on monitoring processed RssAnon memory, I've found a correlation that only processes by a single user are suffering from the memory allocation (it looks to be a memory leak to me).
For the given DB user, I've configured log_min_duration_stattement to 0 and afterward analyzed the Postgres server log with all the statements in the time window when I spot a significant memory growth for a single process in a short time (a few minutes).
This correlation points my attention to a SQL function called frequently and I've tried to do some testing with it.
CREATE OR REPLACE FUNCTION tescase (_id_data_provider integer, _external_id bigint DEFAULT NULL::bigint, _external_complete_id character varying DEFAULT NULL::character varying)
RETURNS TABLE(id_gm bigint, id_opp bigint, id_opp_state integer)
LANGUAGE sql
AS $function$
RETURNS TABLE(id_gm bigint, id_opp bigint, id_opp_state integer)
LANGUAGE sql
AS $function$
SELECT ... simple join of two tables...
WHERE opd.id_data_provider = _id_data_provider
AND CASE WHEN _external_id IS NULL
THEN external_id IS NULL
ELSE external_id = _external_id
END
AND CASE WHEN _external_complete_id IS NULL
THEN _external_complete_id IS NULL
ELSE external_complete_id = _external_complete_id
END;
$function$
AND CASE WHEN _external_id IS NULL
THEN external_id IS NULL
ELSE external_id = _external_id
END
AND CASE WHEN _external_complete_id IS NULL
THEN _external_complete_id IS NULL
ELSE external_complete_id = _external_complete_id
END;
$function$
;
It is a kind of creative construct for me, but it works. The key here is that if I replace at least one of the "CASEd" where conditions, it seems not to suffer from the memory leak issue.
Finally, I've found, that even having the function as is and before the test disabling JIT (SET jit = off;) and calling the function 100k times, RssAnon memory for the given process is stable and only 3612 kB, while when JIT is enabled (the default setting on the server suffering from the memory leak, RssAnon memory for the given process growth in a linear manner over time (canceled when it reached 5GB).
Tested on real application data I could not share, but if I got a chance to give try to the preparation of a synthetic test case, then I thought it'll be time to submit it as a bug.
In the smaps for the given process, it is possible to identify the address of a heap allocated memory wich constantly grows every execution (while other mapped heap segment's size is static ), but I have no clue whether it might help, it looks like:
while :; do date; sed -n '/^02d93000.*/,/^VmFlags/p' /proc/31600/smaps; sleep 2; done
Út srp 2 17:10:18 CEST 2022
02d93000-20fe1000 rw-p 00000000 00:00 0 [heap]
Size: 493880 kB
Rss: 492784 kB
Pss: 492784 kB
Shared_Clean: 0 kB
Shared_Dirty: 0 kB
Private_Clean: 0 kB
Private_Dirty: 492784 kB
Referenced: 492560 kB
Anonymous: 492784 kB
AnonHugePages: 0 kB
Swap: 0 kB
KernelPageSize: 4 kB
MMUPageSize: 4 kB
Locked: 0 kB
VmFlags: rd wr mr mp me ac sd
Út srp 2 17:10:20 CEST 2022
02d93000-21003000 rw-p 00000000 00:00 0 [heap]
Size: 494016 kB
Rss: 493660 kB
Pss: 493660 kB
Shared_Clean: 0 kB
Shared_Dirty: 0 kB
Private_Clean: 0 kB
Private_Dirty: 493660 kB
Referenced: 493436 kB
Anonymous: 493660 kB
AnonHugePages: 0 kB
Swap: 0 kB
KernelPageSize: 4 kB
MMUPageSize: 4 kB
Locked: 0 kB
VmFlags: rd wr mr mp me ac sd
Út srp 2 17:10:18 CEST 2022
02d93000-20fe1000 rw-p 00000000 00:00 0 [heap]
Size: 493880 kB
Rss: 492784 kB
Pss: 492784 kB
Shared_Clean: 0 kB
Shared_Dirty: 0 kB
Private_Clean: 0 kB
Private_Dirty: 492784 kB
Referenced: 492560 kB
Anonymous: 492784 kB
AnonHugePages: 0 kB
Swap: 0 kB
KernelPageSize: 4 kB
MMUPageSize: 4 kB
Locked: 0 kB
VmFlags: rd wr mr mp me ac sd
Út srp 2 17:10:20 CEST 2022
02d93000-21003000 rw-p 00000000 00:00 0 [heap]
Size: 494016 kB
Rss: 493660 kB
Pss: 493660 kB
Shared_Clean: 0 kB
Shared_Dirty: 0 kB
Private_Clean: 0 kB
Private_Dirty: 493660 kB
Referenced: 493436 kB
Anonymous: 493660 kB
AnonHugePages: 0 kB
Swap: 0 kB
KernelPageSize: 4 kB
MMUPageSize: 4 kB
Locked: 0 kB
VmFlags: rd wr mr mp me ac sd
Thanks for any hints or comments.
Ales
В списке pgsql-general по дате отправления: