Re: Trim the heap free memory - Mailing list pgsql-hackers

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

diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 8bc6bea113..9efb4f7636 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -21,6 +21,7 @@
 
 #include <fcntl.h>
 #include <limits.h>
+#include <malloc.h>
 #include <signal.h>
 #include <unistd.h>
 #include <sys/resource.h>
@@ -2797,6 +2798,16 @@ finish_xact_command(void)
         MemoryContextStats(TopMemoryContext);
 #endif
 
+        {
+            char       *old_brk = sbrk(0);
+            char       *new_brk;
+
+            malloc_trim(0);
+            new_brk = sbrk(0);
+            elog(LOG, "malloc_trim saved %zu kB",
+                 (old_brk - new_brk + 1023) / 1024);
+        }
+
         xact_started = false;
     }
 }
  32293 LOG:  malloc_trim saved 0 kB
      4 LOG:  malloc_trim saved 4 kB
     12 LOG:  malloc_trim saved 8 kB
      7 LOG:  malloc_trim saved 12 kB
     57 LOG:  malloc_trim saved 16 kB
      3 LOG:  malloc_trim saved 20 kB
     22 LOG:  malloc_trim saved 24 kB
     14 LOG:  malloc_trim saved 28 kB
    288 LOG:  malloc_trim saved 32 kB
     20 LOG:  malloc_trim saved 36 kB
     26 LOG:  malloc_trim saved 40 kB
     18 LOG:  malloc_trim saved 44 kB
     26 LOG:  malloc_trim saved 48 kB
     27 LOG:  malloc_trim saved 52 kB
     37 LOG:  malloc_trim saved 56 kB
     26 LOG:  malloc_trim saved 60 kB
    218 LOG:  malloc_trim saved 64 kB
     20 LOG:  malloc_trim saved 68 kB
     44 LOG:  malloc_trim saved 72 kB
     44 LOG:  malloc_trim saved 76 kB
     45 LOG:  malloc_trim saved 80 kB
     29 LOG:  malloc_trim saved 84 kB
     91 LOG:  malloc_trim saved 88 kB
     31 LOG:  malloc_trim saved 92 kB
    191 LOG:  malloc_trim saved 96 kB
     30 LOG:  malloc_trim saved 100 kB
     81 LOG:  malloc_trim saved 104 kB
     24 LOG:  malloc_trim saved 108 kB
    214 LOG:  malloc_trim saved 112 kB
     32 LOG:  malloc_trim saved 116 kB
    178 LOG:  malloc_trim saved 120 kB
     86 LOG:  malloc_trim saved 124 kB
   4498 LOG:  malloc_trim saved 128 kB
     29 LOG:  malloc_trim saved 132 kB
    286 LOG:  malloc_trim saved 136 kB
     34 LOG:  malloc_trim saved 140 kB
    563 LOG:  malloc_trim saved 144 kB
     20 LOG:  malloc_trim saved 148 kB
    821 LOG:  malloc_trim saved 152 kB
    987 LOG:  malloc_trim saved 156 kB
    212 LOG:  malloc_trim saved 160 kB
      8 LOG:  malloc_trim saved 164 kB
     22 LOG:  malloc_trim saved 168 kB
      8 LOG:  malloc_trim saved 172 kB
     19 LOG:  malloc_trim saved 176 kB
      7 LOG:  malloc_trim saved 180 kB
     10 LOG:  malloc_trim saved 184 kB
      5 LOG:  malloc_trim saved 188 kB
    128 LOG:  malloc_trim saved 192 kB
      7 LOG:  malloc_trim saved 196 kB
     14 LOG:  malloc_trim saved 200 kB
     14 LOG:  malloc_trim saved 204 kB
      9 LOG:  malloc_trim saved 208 kB
     11 LOG:  malloc_trim saved 212 kB
     11 LOG:  malloc_trim saved 216 kB
     12 LOG:  malloc_trim saved 224 kB
      6 LOG:  malloc_trim saved 228 kB
      5 LOG:  malloc_trim saved 232 kB
      2 LOG:  malloc_trim saved 236 kB
      3 LOG:  malloc_trim saved 240 kB
      7 LOG:  malloc_trim saved 244 kB
      3 LOG:  malloc_trim saved 248 kB
      3 LOG:  malloc_trim saved 252 kB
    186 LOG:  malloc_trim saved 256 kB
     17 LOG:  malloc_trim saved 260 kB
     28 LOG:  malloc_trim saved 264 kB
     51 LOG:  malloc_trim saved 268 kB
     33 LOG:  malloc_trim saved 272 kB
     33 LOG:  malloc_trim saved 276 kB
     69 LOG:  malloc_trim saved 280 kB
     25 LOG:  malloc_trim saved 284 kB
     39 LOG:  malloc_trim saved 288 kB
     12 LOG:  malloc_trim saved 292 kB
     20 LOG:  malloc_trim saved 296 kB
     12 LOG:  malloc_trim saved 300 kB
     34 LOG:  malloc_trim saved 304 kB
      5 LOG:  malloc_trim saved 308 kB
      2 LOG:  malloc_trim saved 312 kB
      6 LOG:  malloc_trim saved 316 kB
     12 LOG:  malloc_trim saved 320 kB
      1 LOG:  malloc_trim saved 324 kB
      1 LOG:  malloc_trim saved 328 kB
      1 LOG:  malloc_trim saved 332 kB
      6 LOG:  malloc_trim saved 336 kB
      3 LOG:  malloc_trim saved 344 kB
      1 LOG:  malloc_trim saved 348 kB
      6 LOG:  malloc_trim saved 352 kB
      1 LOG:  malloc_trim saved 356 kB
     43 LOG:  malloc_trim saved 360 kB
    103 LOG:  malloc_trim saved 364 kB
      1 LOG:  malloc_trim saved 368 kB
    119 LOG:  malloc_trim saved 384 kB
      1 LOG:  malloc_trim saved 388 kB
      1 LOG:  malloc_trim saved 392 kB
      2 LOG:  malloc_trim saved 396 kB
      4 LOG:  malloc_trim saved 412 kB
      4 LOG:  malloc_trim saved 416 kB
      1 LOG:  malloc_trim saved 420 kB
      3 LOG:  malloc_trim saved 424 kB
      1 LOG:  malloc_trim saved 428 kB
      5 LOG:  malloc_trim saved 432 kB
      3 LOG:  malloc_trim saved 436 kB
      1 LOG:  malloc_trim saved 440 kB
     27 LOG:  malloc_trim saved 448 kB
      6 LOG:  malloc_trim saved 452 kB
      5 LOG:  malloc_trim saved 464 kB
     10 LOG:  malloc_trim saved 480 kB
      2 LOG:  malloc_trim saved 484 kB
      1 LOG:  malloc_trim saved 492 kB
      1 LOG:  malloc_trim saved 496 kB
      1 LOG:  malloc_trim saved 500 kB
      4 LOG:  malloc_trim saved 508 kB
     16 LOG:  malloc_trim saved 512 kB
      1 LOG:  malloc_trim saved 528 kB
      2 LOG:  malloc_trim saved 536 kB
      1 LOG:  malloc_trim saved 540 kB
      1 LOG:  malloc_trim saved 552 kB
      2 LOG:  malloc_trim saved 556 kB
      1 LOG:  malloc_trim saved 576 kB
      1 LOG:  malloc_trim saved 580 kB
      6 LOG:  malloc_trim saved 588 kB
      1 LOG:  malloc_trim saved 604 kB
      1 LOG:  malloc_trim saved 616 kB
      1 LOG:  malloc_trim saved 620 kB
      1 LOG:  malloc_trim saved 628 kB
      1 LOG:  malloc_trim saved 632 kB
      3 LOG:  malloc_trim saved 640 kB
      2 LOG:  malloc_trim saved 668 kB
      2 LOG:  malloc_trim saved 672 kB
      1 LOG:  malloc_trim saved 716 kB
      2 LOG:  malloc_trim saved 728 kB
      1 LOG:  malloc_trim saved 732 kB
      1 LOG:  malloc_trim saved 736 kB
      2 LOG:  malloc_trim saved 748 kB
      1 LOG:  malloc_trim saved 760 kB
     11 LOG:  malloc_trim saved 768 kB
      2 LOG:  malloc_trim saved 776 kB
      1 LOG:  malloc_trim saved 800 kB
     10 LOG:  malloc_trim saved 844 kB
      1 LOG:  malloc_trim saved 880 kB
      1 LOG:  malloc_trim saved 884 kB
      1 LOG:  malloc_trim saved 896 kB
      2 LOG:  malloc_trim saved 904 kB
      6 LOG:  malloc_trim saved 960 kB
      4 LOG:  malloc_trim saved 996 kB
      1 LOG:  malloc_trim saved 1024 kB
      3 LOG:  malloc_trim saved 1084 kB
      1 LOG:  malloc_trim saved 1088 kB
      1 LOG:  malloc_trim saved 1136 kB
      7 LOG:  malloc_trim saved 1152 kB
      1 LOG:  malloc_trim saved 1160 kB
      1 LOG:  malloc_trim saved 1248 kB
      1 LOG:  malloc_trim saved 1256 kB
      1 LOG:  malloc_trim saved 1280 kB
      1 LOG:  malloc_trim saved 1288 kB
      2 LOG:  malloc_trim saved 1296 kB
      3 LOG:  malloc_trim saved 1300 kB
      2 LOG:  malloc_trim saved 1412 kB
      2 LOG:  malloc_trim saved 1416 kB
      1 LOG:  malloc_trim saved 1512 kB
      3 LOG:  malloc_trim saved 1520 kB
      5 LOG:  malloc_trim saved 1536 kB
      2 LOG:  malloc_trim saved 1568 kB
      1 LOG:  malloc_trim saved 1608 kB
      2 LOG:  malloc_trim saved 1612 kB
      1 LOG:  malloc_trim saved 1648 kB
      1 LOG:  malloc_trim saved 1768 kB
      1 LOG:  malloc_trim saved 1772 kB
      1 LOG:  malloc_trim saved 1792 kB
      1 LOG:  malloc_trim saved 1860 kB
      1 LOG:  malloc_trim saved 1872 kB
      1 LOG:  malloc_trim saved 1908 kB
      1 LOG:  malloc_trim saved 1936 kB
      2 LOG:  malloc_trim saved 2004 kB
      1 LOG:  malloc_trim saved 2104 kB
      1 LOG:  malloc_trim saved 2112 kB
      1 LOG:  malloc_trim saved 2116 kB
      1 LOG:  malloc_trim saved 2176 kB
      1 LOG:  malloc_trim saved 2228 kB
      2 LOG:  malloc_trim saved 2240 kB
      1 LOG:  malloc_trim saved 2348 kB
      1 LOG:  malloc_trim saved 2496 kB
      1 LOG:  malloc_trim saved 2624 kB
      1 LOG:  malloc_trim saved 2676 kB
      1 LOG:  malloc_trim saved 3568 kB
      1 LOG:  malloc_trim saved 3712 kB
      1 LOG:  malloc_trim saved 3760 kB
      1 LOG:  malloc_trim saved 3836 kB
      1 LOG:  malloc_trim saved 7228 kB
      2 LOG:  malloc_trim saved 12488 kB
      1 LOG:  malloc_trim saved 12616 kB