MIME-Version: 1.0
References: <CA+T=_GV-igvf8gLRK4w22aN1qB6F1zgHD9dxnEHtK=XEoTTPEA@mail.gmail.com>
In-Reply-To: <CA+T=_GV-igvf8gLRK4w22aN1qB6F1zgHD9dxnEHtK=XEoTTPEA@mail.gmail.com>
From: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Date: Fri, 23 Aug 2024 17:32:10 +0530
Message-ID: <CAExHW5tKyRhZ7FsMsNu_ED-_0czr9jfUez4J7e+5vzv4sGg0qQ@mail.gmail.com>
Subject: Re: Trim the heap free memory
To: shawn wang <shawn.wang.pg@gmail.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAExHW5tKyRhZ7FsMsNu_ED-_0czr9jfUez4J7e%2B5vzv4sGg0qQ%40mail.gmail.com>
Precedence: bulk

Hi Shawn,


On Fri, Aug 23, 2024 at 2:24=E2=80=AFPM shawn wang <shawn.wang.pg@gmail.com=
> wrote:
>
> Hi hackers,
>
> Currently, all processes in PostgreSQL actually use malloc to allocate an=
d free memory. In the case of long connections where business queries are e=
xecuted over extended periods, the distribution of memory can become extrem=
ely complex.
>
> Under certain circumstances, a common issue in memory usage due to the ca=
ching strategy of malloc may arise: even if memory is released through the =
free function, it may not be returned to the OS in a timely manner. This ca=
n lead to high system memory usage, affecting performance and the operation=
 of other applications, and may even result in Out-Of-Memory (OOM) errors.
>
> To address this issue, I have developed a new function called pg_trim_bac=
kend_heap_free_memory, based on the existing pg_log_backend_memory_contexts=
 function. This function triggers the specified process to execute the mall=
oc_trim operation by sending signals, thereby releasing as much unreturned =
memory to the operating system as possible. This not only helps to optimize=
 memory usage but can also significantly enhance system performance under m=
emory pressure.
>
> Here is an example of using the pg_trim_backend_heap_free_memory function=
 to demonstrate its effect:
>>
>> CREATE OR REPLACE FUNCTION public.partition_create(schemaname character =
varying, numberofpartition integer)
>> RETURNS integer
>> LANGUAGE plpgsql
>> AS $function$
>> declare
>> currentTableId integer;
>> currentSchemaName varchar(100);
>> currentTableName varchar(100);
>> begin
>> execute 'create schema ' || schemaname;
>> execute 'create table ' || schemaname || '.' || schemaname || 'hashtable=
 (p1 text, p2 text, p3 text, p4 int, p5 int, p6 int, p7 int, p8 text, p9 na=
me, p10 varchar, p11 text, p12 text, p13 text) PARTITION BY HASH(p1);';
>> currentTableId :=3D 1;
>> loop
>> currentTableName :=3D schemaname || '.' || schemaname || 'hashtable' || =
ltrim(currentTableId::varchar(10));
>> execute 'create table ' || currentTableName || ' PARTITION OF ' || schem=
aname || '.' || schemaname || 'hashtable' || ' FOR VALUES WITH(MODULUS ' ||=
 numberofpartition || ', REMAINDER ' || currentTableId - 1 || ')';
>> currentTableId :=3D currentTableId + 1;
>> if (currentTableId > numberofpartition) then exit; end if;
>> end loop;
>> return currentTableId - 1;
>> END $function$;
>>
>> select public.partition_create('test3', 5000);
>> select public.partition_create('test4', 5000);
>> select count(*) from test4.test4hashtable a, test3.test3hashtable b wher=
e a.p1=3Db.p1;
>
> You are now about to see the memory size of the process executing the que=
ry.
>>
>> postgres   68673  1.2  0.0 610456 124768 ?        Ss   08:25   0:01 post=
gres: postgres postgres [local] idle
>> Size:              89600 kB
>> KernelPageSize:        4 kB
>> MMUPageSize:           4 kB
>> Rss:               51332 kB
>> Pss:               51332 kB
>>
>> 02b65000-082e5000 rw-p 00000000 00:00 0                                 =
 [heap]
>
>
>
> After use pg_trim_backend_heap_free_memory, you will see:
>>
>> postgres=3D# select pg_trim_backend_heap_free_memory(pg_backend_pid());
>> 2024-08-23 08:27:53.958 UTC [68673] LOG:  trimming heap free memory of P=
ID 68673
>>  pg_trim_backend_heap_free_memory
>> ----------------------------------
>>  t
>> (1 row)
>> 02b65000-082e5000 rw-p 00000000 00:00 0                                 =
 [heap]
>> Size:              89600 kB
>> KernelPageSize:        4 kB
>> MMUPageSize:           4 kB
>> Rss:                4888 kB
>> Pss:                4888 kB
>>
>> postgres   68673  1.2  0.0 610456 75244 ?        Ss   08:26   0:01 postg=
res: postgres postgres [local] idle
>
>
> Looking forward to your feedback,
Looks useful.

How much time does malloc_trim() take to finish? Does it affect the
current database activity in that backend? It may be good to see
effect of this function by firing the function on random backends
while the query is running through pgbench.

In the patch I don't see definitions of
ProcessTrimHeapFreeMemoryInterrupt() and
HandleTrimHeapFreeMemoryInterrupt(). Am I missing something?

--
Best Wishes,
Ashutosh Bapat