Re: Trim the heap free memory

On Fri, 23 Aug 2024 at 10:54, shawn wang <shawn(dot)wang(dot)pg(at)gmail(dot)com> wrote:

> Hi hackers,
>
> Currently, all processes in PostgreSQL actually use malloc to allocate
> and free memory. In the case of long connections where business queries are
> executed over extended periods, the distribution of memory can become
> extremely complex.
>
> Under certain circumstances, a common issue in memory usage due to the
> caching 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 can 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_backend_heap_free_memory, based on the existing
> pg_log_backend_memory_contexts function. This function triggers the
> specified process to execute the malloc_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 memory 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
>> name, p10 varchar, p11 text, p12 text, p13 text) PARTITION BY HASH(p1);';
>> currentTableId := 1;
>> loop
>> currentTableName := schemaname || '.' || schemaname || 'hashtable' ||
>> ltrim(currentTableId::varchar(10));
>> execute 'create table ' || currentTableName || ' PARTITION OF ' ||
>> schemaname || '.' || schemaname || 'hashtable' || ' FOR VALUES WITH(MODULUS
>> ' || numberofpartition || ', REMAINDER ' || currentTableId - 1 || ')';
>> currentTableId := 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 where
>> a.p1=b.p1;
>
> You are now about to see the memory size of the process executing the
> query.
>
>> postgres 68673 1.2 0.0 610456 124768 ? Ss 08:25 0:01
>> postgres: 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=# 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
>> PID 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
>> postgres: postgres postgres [local] idle
>>
>
> Looking forward to your feedback,
>
> Regards,
>
> --
> Shawn Wang
>
>
> Now
>
Liked the idea. Unfortunately, at the moment it is giving compilation error
--

make[4]: *** No rule to make target `memtrim.o', needed by `objfiles.txt'.
Stop.
--
Regards,
Rafia Sabih