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
