Thank you Rafia. Here is a v2 patch. Rafia Sabih <rafia.pghack...@gmail.com> 于2024年8月23日周五 18:30写道：
>
>
> On Fri, 23 Aug 2024 at 10:54, shawn wang <shawn.wang...@gmail.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
>
v2-0001-Trim-Heap-Free-Memory.patch
Description: Binary data
