Horton

Thread

  1. Trim the heap free memory

    shawn wang <shawn.wang.pg@gmail.com> — 2024-08-23T08:53:58Z

    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

  2. Re: Trim the heap free memory

    Rafia Sabih <rafia.pghackers@gmail.com> — 2024-08-23T10:30:12Z

    On Fri, 23 Aug 2024 at 10:54, shawn wang <shawn.wang.pg@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

  3. Re: Trim the heap free memory

    Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> — 2024-08-23T12:02:10Z

    Hi Shawn,


On Fri, Aug 23, 2024 at 2:24 PM shawn wang <shawn.wang.pg@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,
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

  4. Re: Trim the heap free memory

    shawn wang <shawn.wang.pg@gmail.com> — 2024-08-24T02:26:41Z

    Thank you Rafia. Here is a v2 patch.

Rafia Sabih <rafia.pghackers@gmail.com> 于2024年8月23日周五 18:30写道：

>
>
> On Fri, 23 Aug 2024 at 10:54, shawn wang <shawn.wang.pg@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
>

  5. Re: Trim the heap free memory

    shawn wang <shawn.wang.pg@gmail.com> — 2024-08-24T02:42:04Z

    Hi Ashutosh, thank you for your response.
Firstly, the purpose of caching memory in malloc is for performance, so
when we execute malloc_trim(), it will affect the efficiency of memory
usage in the subsequent operation. Secondly, the function of malloc_trim()
is to lock and traverse the bins, then execute madvise on the memory that
can be released. When there is a lot of memory in the bins, the traversal
time will also increase. I once placed malloc_trim() to execute at the end
of each query, which resulted in a 20% performance drop. Therefore, I use
it as such a function. The new v2 patch has included the omitted code.

Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> 于2024年8月23日周五 20:02写道：

> Hi Shawn,
>
>
> On Fri, Aug 23, 2024 at 2:24 PM shawn wang <shawn.wang.pg@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,
> 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
>

  6. Re: Trim the heap free memory

    Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> — 2024-08-26T11:04:49Z

    Hi Shawn,
It will be good to document usage of this function. Please add
document changes in your patch. We need to document the impact of this
function so that users can judiciously decide whether or not to use
this function and under what conditions. Also they would know what to
expect when they use this function.

Running it after a query finishes is one thing but that can't be
guaranteed because of the asynchronous nature of signal handlers.
malloc_trim() may be called while a query is being executed. We need
to assess that impact as well.

Can you please share some numbers - TPS, latency etc. with and without
this function invoked during a benchmark run?

--
Best Wishes,
Ashutosh Bapat

On Sat, Aug 24, 2024 at 8:12 AM shawn wang <shawn.wang.pg@gmail.com> wrote:
>
> Hi Ashutosh, thank you for your response.
> Firstly, the purpose of caching memory in malloc is for performance, so when we execute malloc_trim(), it will affect the efficiency of memory usage in the subsequent operation. Secondly, the function of malloc_trim() is to lock and traverse the bins, then execute madvise on the memory that can be released. When there is a lot of memory in the bins, the traversal time will also increase. I once placed malloc_trim() to execute at the end of each query, which resulted in a 20% performance drop. Therefore, I use it as such a function. The new v2 patch has included the omitted code.
>
> Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> 于2024年8月23日周五 20:02写道：
>>
>> Hi Shawn,
>>
>>
>> On Fri, Aug 23, 2024 at 2:24 PM shawn wang <shawn.wang.pg@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,
>> 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

  7. Re: Trim the heap free memory

    shawn wang <shawn.wang.pg@gmail.com> — 2024-08-28T10:54:36Z

    Hi Ashutosh,

Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> 于2024年8月26日周一 19:05写道：

> Hi Shawn,
> It will be good to document usage of this function. Please add
> document changes in your patch. We need to document the impact of this
> function so that users can judiciously decide whether or not to use
> this function and under what conditions. Also they would know what to
> expect when they use this function.


I have already incorporated the usage of this function into the new patch.

Currently, there is no memory information that can be extremely accurate to
reflect whether a trim operation should be performed. Here are two
conditions
that can be used as references:
1. Check the difference between the process's memory usage (for example,
the top command, due to the relationship with shared memory, it is necessary
to subtract SHR from RES) and the statistics of the memory context. If the
difference is very large, this function should be used to release memory;
2. Execute malloc_stats(). If the system bytes are greater than the
in-use bytes, this indicates that this function can be used to release
memory.

>
>
Running it after a query finishes is one thing but that can't be
> guaranteed because of the asynchronous nature of signal handlers.
> malloc_trim() may be called while a query is being executed. We need
> to assess that impact as well.
>
> Can you please share some numbers - TPS, latency etc. with and without
> this function invoked during a benchmark run?
>

I have placed malloc_trim() at the end of the exec_simple_query function,
so that malloc_trim() is executed once for each SQL statement executed. I
used pgbench to reproduce the performance impact,
and the results are as follows.
*Database preparation：*

> create database testc;
> create user t1;
> alter database testc owner to t1;
> ./pgbench testc -U t1 -i -s 100
> ./pgbench testc -U t1 -S -c 100 -j 100 -T 600

*Without Trim*:

> $./pgbench testc -U t1 -S -c 100 -j 100 -T 600
> pgbench (18devel)
> starting vacuum...end.
> transaction type: <builtin: select only>
> scaling factor: 100
> query mode: simple
> number of clients: 100
> number of threads: 100
> maximum number of tries: 1
> duration: 600 s
> number of transactions actually processed: 551984376
> number of failed transactions: 0 (0.000%)
> latency average = 0.109 ms
> initial connection time = 23.569 ms
> tps = 920001.842189 (without initial connection time)

*With Trim :*

> $./pgbench testc -U t1 -S -c 100 -j 100 -T 600
> pgbench (18devel)
> starting vacuum...end.
> transaction type: <builtin: select only>
> scaling factor: 100
> query mode: simple
> number of clients: 100
> number of threads: 100
> maximum number of tries: 1
> duration: 600 s
> number of transactions actually processed: 470690787
> number of failed transactions: 0 (0.000%)
> latency average = 0.127 ms
> initial connection time = 23.632 ms
> tps = 784511.901558 (without initial connection time)

  8. Re: Trim the heap free memory

    Rafia Sabih <rafia.pghackers@gmail.com> — 2024-09-11T10:24:49Z

    Unfortunately, I still see a compiling issue with this patch,

memtrim.c:15:10: fatal error: 'malloc.h' file not found
#include <malloc.h>
         ^~~~~~~~~~
1 error generated.

On Wed, 28 Aug 2024 at 12:54, shawn wang <shawn.wang.pg@gmail.com> wrote:

> Hi Ashutosh,
>
> Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> 于2024年8月26日周一 19:05写道：
>
>> Hi Shawn,
>> It will be good to document usage of this function. Please add
>> document changes in your patch. We need to document the impact of this
>> function so that users can judiciously decide whether or not to use
>> this function and under what conditions. Also they would know what to
>> expect when they use this function.
>
>
> I have already incorporated the usage of this function into the new patch.
>
>
> Currently, there is no memory information that can be extremely accurate to
> reflect whether a trim operation should be performed. Here are two
> conditions
> that can be used as references:
> 1. Check the difference between the process's memory usage (for example,
> the top command, due to the relationship with shared memory, it is
> necessary
> to subtract SHR from RES) and the statistics of the memory context. If the
> difference is very large, this function should be used to release memory;
> 2. Execute malloc_stats(). If the system bytes are greater than the
> in-use bytes, this indicates that this function can be used to release
> memory.
>
>>
>>
> Running it after a query finishes is one thing but that can't be
>> guaranteed because of the asynchronous nature of signal handlers.
>> malloc_trim() may be called while a query is being executed. We need
>> to assess that impact as well.
>>
>> Can you please share some numbers - TPS, latency etc. with and without
>> this function invoked during a benchmark run?
>>
>
> I have placed malloc_trim() at the end of the exec_simple_query function,
> so that malloc_trim() is executed once for each SQL statement executed. I
> used pgbench to reproduce the performance impact,
> and the results are as follows.
> *Database preparation：*
>
>> create database testc;
>> create user t1;
>> alter database testc owner to t1;
>> ./pgbench testc -U t1 -i -s 100
>> ./pgbench testc -U t1 -S -c 100 -j 100 -T 600
>
> *Without Trim*:
>
>> $./pgbench testc -U t1 -S -c 100 -j 100 -T 600
>> pgbench (18devel)
>> starting vacuum...end.
>> transaction type: <builtin: select only>
>> scaling factor: 100
>> query mode: simple
>> number of clients: 100
>> number of threads: 100
>> maximum number of tries: 1
>> duration: 600 s
>> number of transactions actually processed: 551984376
>> number of failed transactions: 0 (0.000%)
>> latency average = 0.109 ms
>> initial connection time = 23.569 ms
>> tps = 920001.842189 (without initial connection time)
>
> *With Trim :*
>
>> $./pgbench testc -U t1 -S -c 100 -j 100 -T 600
>> pgbench (18devel)
>> starting vacuum...end.
>> transaction type: <builtin: select only>
>> scaling factor: 100
>> query mode: simple
>> number of clients: 100
>> number of threads: 100
>> maximum number of tries: 1
>> duration: 600 s
>> number of transactions actually processed: 470690787
>> number of failed transactions: 0 (0.000%)
>> latency average = 0.127 ms
>> initial connection time = 23.632 ms
>> tps = 784511.901558 (without initial connection time)
>
>

-- 
Regards,
Rafia Sabih

  9. Re: Trim the heap free memory

    shawn wang <shawn.wang.pg@gmail.com> — 2024-09-12T02:40:24Z

    Hi Rafia,

I have made the necessary adjustment by replacing the inclusion of malloc.h
with stdlib.h in the relevant codebase. This change should address the
previous concerns regarding memory allocation functions.

Could you please perform another round of testing to ensure that everything
is functioning as expected with this modification?

Thank you for your assistance.

Best regards, Shawn


Rafia Sabih <rafia.pghackers@gmail.com> 于2024年9月11日周三 18:25写道：

> Unfortunately, I still see a compiling issue with this patch,
>
> memtrim.c:15:10: fatal error: 'malloc.h' file not found
> #include <malloc.h>
>          ^~~~~~~~~~
> 1 error generated.
>
> On Wed, 28 Aug 2024 at 12:54, shawn wang <shawn.wang.pg@gmail.com> wrote:
>
>> Hi Ashutosh,
>>
>> Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> 于2024年8月26日周一 19:05写道：
>>
>>> Hi Shawn,
>>> It will be good to document usage of this function. Please add
>>> document changes in your patch. We need to document the impact of this
>>> function so that users can judiciously decide whether or not to use
>>> this function and under what conditions. Also they would know what to
>>> expect when they use this function.
>>
>>
>> I have already incorporated the usage of this function into the new patch.
>>
>>
>> Currently, there is no memory information that can be extremely accurate
>> to
>> reflect whether a trim operation should be performed. Here are two
>> conditions
>> that can be used as references:
>> 1. Check the difference between the process's memory usage (for example,
>> the top command, due to the relationship with shared memory, it is
>> necessary
>> to subtract SHR from RES) and the statistics of the memory context. If the
>> difference is very large, this function should be used to release memory;
>> 2. Execute malloc_stats(). If the system bytes are greater than the
>> in-use bytes, this indicates that this function can be used to release
>> memory.
>>
>>>
>>>
>> Running it after a query finishes is one thing but that can't be
>>> guaranteed because of the asynchronous nature of signal handlers.
>>> malloc_trim() may be called while a query is being executed. We need
>>> to assess that impact as well.
>>>
>>> Can you please share some numbers - TPS, latency etc. with and without
>>> this function invoked during a benchmark run?
>>>
>>
>> I have placed malloc_trim() at the end of the exec_simple_query function,
>> so that malloc_trim() is executed once for each SQL statement executed. I
>> used pgbench to reproduce the performance impact,
>> and the results are as follows.
>> *Database preparation：*
>>
>>> create database testc;
>>> create user t1;
>>> alter database testc owner to t1;
>>> ./pgbench testc -U t1 -i -s 100
>>> ./pgbench testc -U t1 -S -c 100 -j 100 -T 600
>>
>> *Without Trim*:
>>
>>> $./pgbench testc -U t1 -S -c 100 -j 100 -T 600
>>> pgbench (18devel)
>>> starting vacuum...end.
>>> transaction type: <builtin: select only>
>>> scaling factor: 100
>>> query mode: simple
>>> number of clients: 100
>>> number of threads: 100
>>> maximum number of tries: 1
>>> duration: 600 s
>>> number of transactions actually processed: 551984376
>>> number of failed transactions: 0 (0.000%)
>>> latency average = 0.109 ms
>>> initial connection time = 23.569 ms
>>> tps = 920001.842189 (without initial connection time)
>>
>> *With Trim :*
>>
>>> $./pgbench testc -U t1 -S -c 100 -j 100 -T 600
>>> pgbench (18devel)
>>> starting vacuum...end.
>>> transaction type: <builtin: select only>
>>> scaling factor: 100
>>> query mode: simple
>>> number of clients: 100
>>> number of threads: 100
>>> maximum number of tries: 1
>>> duration: 600 s
>>> number of transactions actually processed: 470690787
>>> number of failed transactions: 0 (0.000%)
>>> latency average = 0.127 ms
>>> initial connection time = 23.632 ms
>>> tps = 784511.901558 (without initial connection time)
>>
>>
>
> --
> Regards,
> Rafia Sabih
>

  10. Re: Trim the heap free memory

    David Rowley <dgrowleyml@gmail.com> — 2024-09-12T08:42:24Z

    On Thu, 12 Sept 2024 at 14:40, shawn wang <shawn.wang.pg@gmail.com> wrote:
> Could you please perform another round of testing to ensure that everything is functioning as expected with this modification?

One way to get a few machines with various build systems testing this
is to register the patch on the commitfest app in [1]. You can then
see if the patch is passing the continuous integration tests in [2].
One day soon the features of [2] should be combined with [1].

David

[1] https://commitfest.postgresql.org/50/
[2] http://cfbot.cputube.org/

  11. Re: Trim the heap free memory

    shawn wang <shawn.wang.pg@gmail.com> — 2024-09-15T17:48:44Z

    Thank you for your valuable suggestion.

I have successfully registered my patch for the commitfest. However, upon
integration, I encountered several errors during the testing phase. I am
currently investigating the root causes of these issues and will work on
providing the necessary fixes. If you have any further insights or
recommendations, I would greatly appreciate your guidance.

Thank you once again for your support.

Best regards, Shawn

David Rowley <dgrowleyml@gmail.com> 于2024年9月12日周四 16:42写道：

> On Thu, 12 Sept 2024 at 14:40, shawn wang <shawn.wang.pg@gmail.com> wrote:
> > Could you please perform another round of testing to ensure that
> everything is functioning as expected with this modification?
>
> One way to get a few machines with various build systems testing this
> is to register the patch on the commitfest app in [1]. You can then
> see if the patch is passing the continuous integration tests in [2].
> One day soon the features of [2] should be combined with [1].
>
> David
>
> [1] https://commitfest.postgresql.org/50/
> [2] http://cfbot.cputube.org/
>

  12. Re: Trim the heap free memory

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-09-15T18:22:14Z

    shawn wang <shawn.wang.pg@gmail.com> writes:
> I have successfully registered my patch for the commitfest. However, upon
> integration, I encountered several errors during the testing phase. I am
> currently investigating the root causes of these issues and will work on
> providing the necessary fixes.

I should think the root cause is pretty obvious: malloc_trim() is
a glibc-ism.

I'm fairly doubtful that this is something we should spend time on.
It can never work on any non-glibc platform.  Even granting that
a Linux-only feature could be worth having, I'm really doubtful
that our memory allocation patterns are such that malloc_trim()
could be expected to free a useful amount of memory mid-query.
The single test case you showed suggested that maybe we could
usefully prod glibc to free memory at query completion, but we
don't need all this interrupt infrastructure to do that.  I think
we could likely get 95% of the benefit with about a five-line
patch.

			regards, tom lane

  13. Re: Trim the heap free memory

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-09-15T19:16:27Z

    I wrote:
> The single test case you showed suggested that maybe we could
> usefully prod glibc to free memory at query completion, but we
> don't need all this interrupt infrastructure to do that.  I think
> we could likely get 95% of the benefit with about a five-line
> patch.

To try to quantify that a little, I wrote a very quick-n-dirty
patch to apply malloc_trim during finish_xact_command and log
the effects.  (I am not asserting this is the best place to
call malloc_trim; it's just one plausible possibility.)  Patch
attached, as well as statistics collected from a run of the
core regression tests followed by

grep malloc_trim postmaster.log | sed 's/.*LOG:/LOG:/' | sort -k4n | uniq -c >trim_savings.txt

We can see that out of about 43K test queries, 32K saved nothing
whatever, and in only four was more than a couple of meg saved.
That's pretty discouraging IMO.  It might be useful to look closer
at the behavior of those top four though.  I see them as

2024-09-15 14:58:06.146 EDT [960138] LOG:  malloc_trim saved 7228 kB
2024-09-15 14:58:06.146 EDT [960138] STATEMENT:  ALTER TABLE delete_test_table ADD PRIMARY KEY (a,b,c,d);

2024-09-15 14:58:09.861 EDT [960949] LOG:  malloc_trim saved 12488 kB
2024-09-15 14:58:09.861 EDT [960949] STATEMENT:  with recursive search_graph(f, t, label, is_cycle, path) as (
		select *, false, array[row(g.f, g.t)] from graph g
		union distinct
		select g.*, row(g.f, g.t) = any(path), path || row(g.f, g.t)
		from graph g, search_graph sg
		where g.f = sg.t and not is_cycle
	)
	select * from search_graph;

2024-09-15 14:58:09.866 EDT [960949] LOG:  malloc_trim saved 12488 kB
2024-09-15 14:58:09.866 EDT [960949] STATEMENT:  with recursive search_graph(f, t, label) as (
		select * from graph g
		union distinct
		select g.*
		from graph g, search_graph sg
		where g.f = sg.t
	) cycle f, t set is_cycle to 'Y' default 'N' using path
	select * from search_graph;

2024-09-15 14:58:09.853 EDT [960949] LOG:  malloc_trim saved 12616 kB
2024-09-15 14:58:09.853 EDT [960949] STATEMENT:  with recursive search_graph(f, t, label) as (
		select * from graph0 g
		union distinct
		select g.*
		from graph0 g, search_graph sg
		where g.f = sg.t
	) search breadth first by f, t set seq
	select * from search_graph order by seq;

I don't understand why WITH RECURSIVE queries might be more prone
to leave non-garbage-collected memory behind than other queries,
but maybe that is worth looking into.

			regards, tom lane

  14. Re: Trim the heap free memory

    shawn wang <shawn.wang.pg@gmail.com> — 2024-09-18T02:56:08Z

    Thank you very much for your response and suggestions.

As you mentioned, the patch here is actually designed for glibc's ptmalloc2
andis not applicable to other platforms. I will consider supporting it only
on the Linux platform in the future. In the memory management strategy of
ptmalloc2, there is a certain amount of non-garbage-collected memory, which
is closely related to the order and method of memory allocation and
release. To reduce the performance overhead caused by frequent allocation
and release of small blocks of memory, ptmalloc2 intentionally retains this
part of the memory. The malloc_trim function locks, traverses memory
blocks, and uses madvise to release this part of the memory, but this
process may also have a negative impact on performance. In the process of
exploring solutions, I also considered a variety of strategies, including
scheduling malloc_trim to be executed at regular intervals or triggering
malloc_trim after a specific number of free operations. However, we found
that these methods are not optimal solutions.

> We can see that out of about 43K test queries, 32K saved nothing
> whatever, and in only four was more than a couple of meg saved.
> That's pretty discouraging IMO.  It might be useful to look closer
> at the behavior of those top four though.  I see them as


I have previously encountered situations where the non-garbage-collected
memory of wal_sender was approximately hundreds of megabytes or even
exceeded 1GB, but I was unable to reproduce this situation using simple
SQL. Therefore, I introduced an asynchronous processing function, hoping to
manage memory more efficiently without affecting performance.


In addition, I have considered the following optimization strategies:

   1.

   Adjust the configuration of ptmalloc2 through the mallopt function to
   use mmap rather than sbrk for memory allocation. This can immediately
   return the memory to the operating system when it is released, but it may
   affect performance due to the higher overhead of mmap.
   2.

   Use other memory allocators such as jemalloc or tcmalloc, and adjust
   relevant parameters to reduce the generation of non-garbage-collected
   memory. However, these allocators are designed for multi-threaded and may
   lead to increased memory usage per process.
   3.

   Build a set of memory context (memory context) allocation functions
   based on mmap, delegating the responsibility of memory management entirely
   to the database level. Although this solution can effectively control
   memory allocation, it requires a large-scale engineering implementation.

I look forward to further discussing these solutions with you and exploring
the best memory management practices together.

Best regards, Shawn

Tom Lane <tgl@sss.pgh.pa.us> 于2024年9月16日周一 03:16写道：

> I wrote:
> > The single test case you showed suggested that maybe we could
> > usefully prod glibc to free memory at query completion, but we
> > don't need all this interrupt infrastructure to do that.  I think
> > we could likely get 95% of the benefit with about a five-line
> > patch.
>
> To try to quantify that a little, I wrote a very quick-n-dirty
> patch to apply malloc_trim during finish_xact_command and log
> the effects.  (I am not asserting this is the best place to
> call malloc_trim; it's just one plausible possibility.)  Patch
> attached, as well as statistics collected from a run of the
> core regression tests followed by
>
> grep malloc_trim postmaster.log | sed 's/.*LOG:/LOG:/' | sort -k4n | uniq
> -c >trim_savings.txt
>
> We can see that out of about 43K test queries, 32K saved nothing
> whatever, and in only four was more than a couple of meg saved.
> That's pretty discouraging IMO.  It might be useful to look closer
> at the behavior of those top four though.  I see them as
>
> 2024-09-15 14:58:06.146 EDT [960138] LOG:  malloc_trim saved 7228 kB
> 2024-09-15 14:58:06.146 EDT [960138] STATEMENT:  ALTER TABLE
> delete_test_table ADD PRIMARY KEY (a,b,c,d);
>
> 2024-09-15 14:58:09.861 EDT [960949] LOG:  malloc_trim saved 12488 kB
> 2024-09-15 14:58:09.861 EDT [960949] STATEMENT:  with recursive
> search_graph(f, t, label, is_cycle, path) as (
>                 select *, false, array[row(g.f, g.t)] from graph g
>                 union distinct
>                 select g.*, row(g.f, g.t) = any(path), path || row(g.f,
> g.t)
>                 from graph g, search_graph sg
>                 where g.f = sg.t and not is_cycle
>         )
>         select * from search_graph;
>
> 2024-09-15 14:58:09.866 EDT [960949] LOG:  malloc_trim saved 12488 kB
> 2024-09-15 14:58:09.866 EDT [960949] STATEMENT:  with recursive
> search_graph(f, t, label) as (
>                 select * from graph g
>                 union distinct
>                 select g.*
>                 from graph g, search_graph sg
>                 where g.f = sg.t
>         ) cycle f, t set is_cycle to 'Y' default 'N' using path
>         select * from search_graph;
>
> 2024-09-15 14:58:09.853 EDT [960949] LOG:  malloc_trim saved 12616 kB
> 2024-09-15 14:58:09.853 EDT [960949] STATEMENT:  with recursive
> search_graph(f, t, label) as (
>                 select * from graph0 g
>                 union distinct
>                 select g.*
>                 from graph0 g, search_graph sg
>                 where g.f = sg.t
>         ) search breadth first by f, t set seq
>         select * from search_graph order by seq;
>
> I don't understand why WITH RECURSIVE queries might be more prone
> to leave non-garbage-collected memory behind than other queries,
> but maybe that is worth looking into.
>
>                         regards, tom lane
>
>

  15. Re: Trim the heap free memory

    Tomas Vondra <tomas@vondra.me> — 2024-12-08T04:23:26Z

    On 9/18/24 04:56, shawn wang wrote:
> Thank you very much for your response and suggestions.
> 
> As you mentioned, the patch here is actually designed for glibc's
> ptmalloc2 andis not applicable to other platforms. I will consider
> supporting it only on the Linux platform in the future. In the memory
> management strategy of ptmalloc2, there is a certain amount of non-
> garbage-collected memory, which is closely related to the order and
> method of memory allocation and release. To reduce the performance
> overhead caused by frequent allocation and release of small blocks of
> memory, ptmalloc2 intentionally retains this part of the memory. The
> malloc_trim function locks, traverses memory blocks, and uses madvise to
> release this part of the memory, but this process may also have a
> negative impact on performance. In the process of exploring solutions, I
> also considered a variety of strategies, including scheduling
> malloc_trim to be executed at regular intervals or triggering
> malloc_trim after a specific number of free operations. However, we
> found that these methods are not optimal solutions.
> 
>     We can see that out of about 43K test queries, 32K saved nothing
>     whatever, and in only four was more than a couple of meg saved.
>     That's pretty discouraging IMO.  It might be useful to look closer
>     at the behavior of those top four though.  I see them as
> 
> 
> I have previously encountered situations where the non-garbage-collected
> memory of wal_sender was approximately hundreds of megabytes or even
> exceeded 1GB, but I was unable to reproduce this situation using simple
> SQL. Therefore, I introduced an asynchronous processing function, hoping
> to manage memory more efficiently without affecting performance.
>  

I doubt a system function is the right approach to deal with these
memory allocation issues. The function has to be called by the user,
which means the user is expected to monitor the system and decide when
to invoke the function. That seems far from trivial - it would require
collecting OS-level information about memory usage, and I suppose it'd
need to happen fairly often to actually help with OOM reliably.

> 
> In addition, I have considered the following optimization strategies:
> 
>  1.
> 
>     Adjust the configuration of ptmalloc2 through the mallopt function
>     to use mmap rather than sbrk for memory allocation. This can
>     immediately return the memory to the operating system when it is
>     released, but it may affect performance due to the higher overhead
>     of mmap.
> 

Sure, forcing the system to release memory more aggressively may affect
performance - that's the tradeoff done by glibc. But calling the new
pg_trim_backend_heap_free_memory() function is not free either.

But why would it force returning the memory to be returned immediately?
The decision whether to trim memory is driven by M_TRIM_THRESHOLD, and
that does not need to be 0. In fact, it's 128kB by default, i.e. glibc
trims memory automatically, if it can trim at least 128kB.

Yes, by default the thresholds are adjusted dynamically, which I guess
is one way to get excessive memory usage that could have been solved by
calling malloc_trim(). But setting the option to any value disabled the
dynamic behavior, it doesn't need to be set to 0.


>  2.
> 
>     Use other memory allocators such as jemalloc or tcmalloc, and adjust
>     relevant parameters to reduce the generation of non-garbage-
>     collected memory. However, these allocators are designed for multi-
>     threaded and may lead to increased memory usage per process.
> 

Right, that's kinda the opposite of trying to not waste memory.

But it also suggests syscalls (done by malloc) may be a problem under
high concurrency - not just with multi-threading, but even with regular
processes. And for glibc that matters too, of course - in fact, it may
be pretty important to allow glibc to cache more memory (by increasing
M_TOP_PAD) to get good throughput in certain workloads ...

>  3.
> 
>     Build a set of memory context (memory context) allocation functions
>     based on mmap, delegating the responsibility of memory management
>     entirely to the database level. Although this solution can
>     effectively control memory allocation, it requires a large-scale
>     engineering implementation.
> 

Why would it be complex? You could just as well set M_MMAP_THRESHOLD to
some low value, so that all malloc() calls are handled by mmap()
internally. Not sure it's a good idea, though.

> I look forward to further discussing these solutions with you and
> exploring the best memory management practices together.
> 

Adjusting the glibc malloc() behavior may be important, but I don't
think a system function is a good approach. It's possible to change the
behavior by setting environment variables, which is pretty easy, but
maybe we could have some thing that does the same thing using mallopt().

That's what Ronan Dunklau proposed in thread [1] a year ago ... I like
that approach much more, it's much simpler for the user.


[1]
https://www.postgresql.org/message-id/flat/3424675.QJadu78ljV%40aivenlaptop

regards

-- 
Tomas Vondra

  16. Re: Trim the heap free memory

    Tomas Vondra <tomas@vondra.me> — 2024-12-08T18:48:38Z

    
On 12/8/24 05:23, Tomas Vondra wrote:
> On 9/18/24 04:56, shawn wang wrote:
>> Thank you very much for your response and suggestions.
>>
>> As you mentioned, the patch here is actually designed for glibc's
>> ptmalloc2 andis not applicable to other platforms. I will consider
>> supporting it only on the Linux platform in the future. In the memory
>> management strategy of ptmalloc2, there is a certain amount of non-
>> garbage-collected memory, which is closely related to the order and
>> method of memory allocation and release. To reduce the performance
>> overhead caused by frequent allocation and release of small blocks of
>> memory, ptmalloc2 intentionally retains this part of the memory. The
>> malloc_trim function locks, traverses memory blocks, and uses madvise to
>> release this part of the memory, but this process may also have a
>> negative impact on performance. In the process of exploring solutions, I
>> also considered a variety of strategies, including scheduling
>> malloc_trim to be executed at regular intervals or triggering
>> malloc_trim after a specific number of free operations. However, we
>> found that these methods are not optimal solutions.
>>
>>     We can see that out of about 43K test queries, 32K saved nothing
>>     whatever, and in only four was more than a couple of meg saved.
>>     That's pretty discouraging IMO.  It might be useful to look closer
>>     at the behavior of those top four though.  I see them as
>>
>>
>> I have previously encountered situations where the non-garbage-collected
>> memory of wal_sender was approximately hundreds of megabytes or even
>> exceeded 1GB, but I was unable to reproduce this situation using simple
>> SQL. Therefore, I introduced an asynchronous processing function, hoping
>> to manage memory more efficiently without affecting performance.
>>  
> 
> I doubt a system function is the right approach to deal with these
> memory allocation issues. The function has to be called by the user,
> which means the user is expected to monitor the system and decide when
> to invoke the function. That seems far from trivial - it would require
> collecting OS-level information about memory usage, and I suppose it'd
> need to happen fairly often to actually help with OOM reliably.
> 
>>
>> In addition, I have considered the following optimization strategies:
>>
>>  1.
>>
>>     Adjust the configuration of ptmalloc2 through the mallopt function
>>     to use mmap rather than sbrk for memory allocation. This can
>>     immediately return the memory to the operating system when it is
>>     released, but it may affect performance due to the higher overhead
>>     of mmap.
>>
> 
> Sure, forcing the system to release memory more aggressively may affect
> performance - that's the tradeoff done by glibc. But calling the new
> pg_trim_backend_heap_free_memory() function is not free either.
> 
> But why would it force returning the memory to be returned immediately?
> The decision whether to trim memory is driven by M_TRIM_THRESHOLD, and
> that does not need to be 0. In fact, it's 128kB by default, i.e. glibc
> trims memory automatically, if it can trim at least 128kB.
> 
> Yes, by default the thresholds are adjusted dynamically, which I guess
> is one way to get excessive memory usage that could have been solved by
> calling malloc_trim(). But setting the option to any value disabled the
> dynamic behavior, it doesn't need to be set to 0.
> 
> 
>>  2.
>>
>>     Use other memory allocators such as jemalloc or tcmalloc, and adjust
>>     relevant parameters to reduce the generation of non-garbage-
>>     collected memory. However, these allocators are designed for multi-
>>     threaded and may lead to increased memory usage per process.
>>
> 
> Right, that's kinda the opposite of trying to not waste memory.
> 
> But it also suggests syscalls (done by malloc) may be a problem under
> high concurrency - not just with multi-threading, but even with regular
> processes. And for glibc that matters too, of course - in fact, it may
> be pretty important to allow glibc to cache more memory (by increasing
> M_TOP_PAD) to get good throughput in certain workloads ...
> 
>>  3.
>>
>>     Build a set of memory context (memory context) allocation functions
>>     based on mmap, delegating the responsibility of memory management
>>     entirely to the database level. Although this solution can
>>     effectively control memory allocation, it requires a large-scale
>>     engineering implementation.
>>
> 
> Why would it be complex? You could just as well set M_MMAP_THRESHOLD to
> some low value, so that all malloc() calls are handled by mmap()
> internally. Not sure it's a good idea, though.
> 
>> I look forward to further discussing these solutions with you and
>> exploring the best memory management practices together.
>>
> 
> Adjusting the glibc malloc() behavior may be important, but I don't
> think a system function is a good approach. It's possible to change the
> behavior by setting environment variables, which is pretty easy, but
> maybe we could have some thing that does the same thing using mallopt().
> 
> That's what Ronan Dunklau proposed in thread [1] a year ago ... I like
> that approach much more, it's much simpler for the user.
> 

To propose something less abstract / more tangible, I think we should do
something like this:

1) add a bit of code for glibc-based systems, that adjusts selected
malloc parameters using mallopt() during startup

2) add a GUC that enables this, with the default being the regular glibc
behavior (with dynamic adjustment of various thresholds)


Which exact parameters would this set is an open question, but based on
my earlier experiments, Ronan's earlier patches, etc. I think it should
adjust at least

  M_TRIM_THRESHOLD - to make sure we trim heap regularly
  M_TOP_PAD - to make sure we cache some allocated memory

I wonder if maybe we should tune M_MMAP_THRESHOLD, which on 64-bit
systems defaults to 32MB, so we don't really mmap() very often for
regular memory contexts. But I don't know if that's a good idea, that
would need some experiments.

I believe that's essentially what Ronan Dunklau proposed, but it
stalled. Not because of some inherent complexity, but because of
concerns about introducing glibc-specific code.

Based on my recent experiments I think it's clearly worth it (esp. with
high concurrency workloads). If glibc was a niche, it'd be a different
situation, but I'd guess vast majority of databases runs on glibc. Yes,
it's possible to do these changes without new code (e.g. by setting the
environment variables), but that's rather inconvenient.

Perhaps it'd be possible to make it a bit smarter by looking at malloc
stats, and adjust the trim/pad thresholds, but I'd leave that for the
future. It might even lead to similar issues with excessive memory usage
just like the logic built into glibc.

But maybe we could at least print / provide some debugging information?
That would help with adjusting the GUC ...


regards

-- 
Tomas Vondra

  17. Re: Trim the heap free memory

    Jakub Wartak <jakub.wartak@enterprisedb.com> — 2025-01-21T09:16:03Z

    On Sun, Dec 8, 2024 at 7:48 PM Tomas Vondra <tomas@vondra.me> wrote:
[..]
> >> I have previously encountered situations where the non-garbage-collected
> >> memory of wal_sender was approximately hundreds of megabytes or even
> >> exceeded 1GB, but I was unable to reproduce this situation using simple
> >> SQL. Therefore, I introduced an asynchronous processing function, hoping
> >> to manage memory more efficiently without affecting performance.
> >>
> >
> > I doubt a system function is the right approach to deal with these
> > memory allocation issues. The function has to be called by the user,
> > which means the user is expected to monitor the system and decide when
> > to invoke the function. That seems far from trivial - it would require
> > collecting OS-level information about memory usage, and I suppose it'd
> > need to happen fairly often to actually help with OOM reliably.
[..]

> > Sure, forcing the system to release memory more aggressively may affect
> > performance - that's the tradeoff done by glibc. But calling the new
> > pg_trim_backend_heap_free_memory() function is not free either.
> >
> > But why would it force returning the memory to be returned immediately?
> > The decision whether to trim memory is driven by M_TRIM_THRESHOLD, and
> > that does not need to be 0. In fact, it's 128kB by default, i.e. glibc
> > trims memory automatically, if it can trim at least 128kB.
[..]

> To propose something less abstract / more tangible, I think we should do
> something like this:
>
> 1) add a bit of code for glibc-based systems, that adjusts selected
> malloc parameters using mallopt() during startup
>
> 2) add a GUC that enables this, with the default being the regular glibc
> behavior (with dynamic adjustment of various thresholds)
>
>
> Which exact parameters would this set is an open question, but based on
> my earlier experiments, Ronan's earlier patches, etc. I think it should
> adjust at least
>
>   M_TRIM_THRESHOLD - to make sure we trim heap regularly
>   M_TOP_PAD - to make sure we cache some allocated memory
>
> I wonder if maybe we should tune M_MMAP_THRESHOLD, which on 64-bit
> systems defaults to 32MB, so we don't really mmap() very often for
> regular memory contexts. But I don't know if that's a good idea, that
> would need some experiments.
>
> I believe that's essentially what Ronan Dunklau proposed, but it
> stalled. Not because of some inherent complexity, but because of
> concerns about introducing glibc-specific code.
>
> Based on my recent experiments I think it's clearly worth it (esp. with
> high concurrency workloads). If glibc was a niche, it'd be a different
> situation, but I'd guess vast majority of databases runs on glibc. Yes,
> it's possible to do these changes without new code (e.g. by setting the
> environment variables), but that's rather inconvenient.
>
> Perhaps it'd be possible to make it a bit smarter by looking at malloc
> stats, and adjust the trim/pad thresholds, but I'd leave that for the
> future. It might even lead to similar issues with excessive memory usage
> just like the logic built into glibc.
>
> But maybe we could at least print / provide some debugging information?
> That would help with adjusting the GUC ...

Hi all,

Thread bump. Just to add one single data point to this discussion, we
have been chasing some ghost memory leaks that apparently were not
memory leaks after all (they stop at certain threshold like 1.2GB)
but there were still OOMs present, and after some experimentation it
seemed that memory ended up being used in MemoryContexts, but
afterwards it was released (so outside of TopMemoryContext) when
session went idle/idle in transaction, but the processes was *still*
having it allocated. Injecting a call to `malloc_trim()` released
backend memory for sessions that were idle for some time.

E.g. with PG 13.x I've got more or less sample reproducer (thanks to
my colleague Matthew Gwillam-Kelly who was working on initial
identification of the problem):

DROP TABLE p;
CREATE TABLE p (
    id                 int not null,
    sensor_id       bigint not null,
    val             bigint
) PARTITION BY HASH (sensor_id);
CREATE INDEX p_idx ON P (val);
SELECT 'CREATE TABLE p_'||g||' PARTITION OF p FOR VALUES WITH (MODULUS
1000, REMAINDER ' || g || ');' FROM generate_series(0, 999) g;
\gexec
INSERT INTO p SELECT g, g, g FROM generate_series(1, 1000000) g;
ANALYZE p;

Run `UPDATE p SET val = val;` minium 3 or 4 times in new session, the
backend will use in my case like ~400MB and stay (!) like that for
infinite time:

$ grep ^Pss /proc/27421/smaps_rollup
Pss:              399291 kB
Pss_Dirty:        397351 kB
Pss_Anon:         353859 kB
Pss_File:           1939 kB
Pss_Shmem:         43492 kB

After injecting call to malloc_trim(0) it shows much lower Pss_Anon:
$ grep ^Pss /proc/27421/smaps_rollup
Pss:               65904 kB
Pss_Dirty:         64189 kB
Pss_Anon:          23231 kB
Pss_File:           1715 kB
Pss_Shmem:         40957 kB

NOTE: it is not depending on (maintenance_)work_mem variables, more
like PG version involved, extensions, encoding probably, partitions
count, triggers maybe. That's like ~353MB wasted above (but our
customer was hitting it in ~1.2 GB range but they were having
additional extensions loaded which could further amplify the effect)
with fully allocated memory without usage in memory contexts (pfree()
were successful, free() done nothing, it's just it's not returned back
to the OS), so before the trim it is like that this:

TopMemoryContext: 801664 total in 29 blocks; 498048 free (2033
chunks); 303616 used
[..]
Grand total: 22213784 bytes in 3129 blocks; 9674384 free (3393
chunks); 12539400 used

Such single UPDATE causes the following malloc frequency histogram of
sizes in malloc():

@:
[1]                    1 |                                                    |
[2, 4)                43 |                                                    |
[4, 8)                81 |                                                    |
[8, 16)              261 |@                                                   |
[16, 32)           10049 |@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@|
[32, 64)            8951 |@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@      |
[64, 128)            446 |@@                                                  |
[128, 256)           133 |                                                    |
[256, 512)           118 |                                                    |
[512, 1K)             11 |                                                    |
[1K, 2K)             134 |                                                    |
[2K, 4K)               5 |                                                    |
[4K, 8K)              94 |                                                    |
[8K, 16K)           1020 |@@@@@                                               |
[16K, 32K)          4122 |@@@@@@@@@@@@@@@@@@@@@                               |
[32K, 64K)            29 |                                                    |
[64K, 128K)           14 |                                                    |
[128K, 256K)        2196 |@@@@@@@@@@@                                         |
[256K, 512K)           2 |                                                    |
[..]

E.g one of the hot paths for this there is (remember it's still PG13)
heap_update->RelationGetBufferForTuple->GetPageWithFreeSpace->fsm_search->fsm_readbuf->mdopenfork->mdopenfork->PathNameOpenFile->PathNameOpenFilePerm->__GI___strdup
. Here's it's strdup() but it could be anything and that's the point.
This effect in libc is completley reproducible, please see attached,
any use of allocating small (<= 120 bytes) ends up not releasing
memory for the program.

$ gcc mwr.c -o mwr -DMALLOC_SIZE=120 && ./mwr
done
Rss:             1251460 kB
Pss:             1250136 kB
Pss_Dirty:       1250112 kB
Pss_Anon:        1250100 kB
Pss_File:             36 kB
Pss_Shmem:             0 kB

after malloc_trim:
Rss:                1460 kB
Pss:                 136 kB
Pss_Dirty:           100 kB
Pss_Anon:            100 kB
Pss_File:             36 kB
Pss_Shmem:             0 kB

$ gcc mwr.c -o mwr -DMALLOC_SIZE=121 && ./mwr # 120+8 >= 128
done
Rss:                1676 kB
Pss:                 259 kB
Pss_Dirty:           224 kB
Pss_Anon:            224 kB
Pss_File:             35 kB
Pss_Shmem:             0 kB

after malloc_trim:
Rss:                1548 kB
Pss:                 131 kB
Pss_Dirty:            96 kB
Pss_Anon:             96 kB
Pss_File:             35 kB
Pss_Shmem:             0 kB

Now, the current PG18 behaved much better in that regard without that
many small mallocs during runtime (strdup() is still there, it's just
that hotpath not exercised that often):

@:
[8, 16)             2697 |@@@@@@@                                             |
[16, 32)            2203 |@@@@@@                                              |
[32, 64)               0 |                                                    |
[64, 128)              0 |                                                    |
[128, 256)             0 |                                                    |
[256, 512)             0 |                                                    |
[512, 1K)              0 |                                                    |
[1K, 2K)            3014 |@@@@@@@@                                            |
[2K, 4K)               5 |                                                    |
[4K, 8K)               2 |                                                    |
[8K, 16K)           1107 |@@@                                                 |
[16K, 32K)         18112 |@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@|
[32K, 64K)            12 |                                                    |
[..]

Yet I still could drop Pss_Anon by using malloc_trim(0) from ~44MB to
~13MB. Assume we have 1k idle connections like this and you end up
wasting 30GB RAM theoretically.

So we basically we have two generic solutions to this class of
problems to avoid OOMs due to GNU libc's malloc() not releasing
memory:

0. Disconnecting the backend (I'm not counting it as it doesn't seem
to be a solid long term solution, but it explains why people push for
poolers with refreshable connection pools).

1. Call malloc_trim(0), but Tom stated it might be not portable. So
maybe there is a chance for extension or #ifdefs . I do think that
calling it after every query might be not ideal due to overheads, but
perhaps after query is done we could schedule interrupt aimed at
now()+X seconds (where X>= 5?), so execute it only when the backend
went really inactive (to avoid re-allocating the memory again), but
abort launching this it if we have started next query. I haven't
looked at the code so i don't know if that can be done cheaply.

2. Or use GLIBC_TUNABLES e.g. disable mxfast bin allocations shows
some promise even still with many small allocations

$ gcc mwr.c -o mwr -DMALLOC_SIZE=120 &&
GLIBC_TUNABLES=glibc.malloc.mxfast=0 ./mwr
done
Rss:                1680 kB
Pss:                 257 kB
Pss_Dirty:           236 kB # no need for malloc_trim()
Pss_Anon:            224 kB
Pss_File:             33 kB
Pss_Shmem:             0 kB
[..]

From my side also -1 to the idea of pg_trim_backend_heap_free_memory()
exposed function as per original patch proposal, as how is the user
supposed to embed this within his application?

I have not quantified the overhead for #1 and #2.

-J.

  18. Re: Trim the heap free memory

    shawn wang <shawn.wang.pg@gmail.com> — 2025-07-05T17:47:08Z

    Thank you very much for your response.


> To propose something less abstract / more tangible, I think we should do
> something like this:
>
> 1) add a bit of code for glibc-based systems, that adjusts selected
> malloc parameters using mallopt() during startup
>
> 2) add a GUC that enables this, with the default being the regular glibc
> behavior (with dynamic adjustment of various thresholds)
>

I believe that the issue here arises from design incompatibilities between
the complex engineering code and ptmalloc.
Modifying malloc parameters through mallopt is not user-friendly for
database users and can be overly complex.
Moreover, setting certain parameters may lead to performance issues.
Monitoring memory usage should be a common practice for all database users.
With my signal-based approach, we can trigger a trim operation
when high memory usage is detected or by setting up a scheduled task.
This reduces the complexity for users and also helps in lowering memory
consumption.
Of course, this solution is not perfect and does not address the problem
elegantly from a fundamental perspective.
However, it has proven effective in the user environment.
I have set up a scheduled task to execute a function every 10 minutes for
processes exceeding 50MB.
This has reduced memory usage from 87% to 30% on a 64GB system.

Best regards

Shawn