| From: | Greg Burd <greg(at)burd(dot)me> |
|---|---|
| To: | David Geier <geidav(dot)pg(at)gmail(dot)com> |
| Cc: | John Naylor <johncnaylorls(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | Re: Keep TIDBitmap pagetable memory across bitmap scans |
| Date: | 2026-11-17 20:11:48 |
| Message-ID: | avyzF86nOSpMTP0c@werk |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
On Mon, 16 Nov 2026 08:47:33 +0100, David Geier <geidav(dot)pg(at)gmail(dot)com> wrote:
> Thanks for confirming this. So it's the soft page faults that install
> the fresh page in the LRU.
Hi David,
> Yeah, glibc's malloc seems inadequate to make that work properly. You
> can always have a big enough TIDBitmap for the trim threshold to be too
> small. Out of curiosity: why is only specifying the trim threshold not
> enough? It shouldn't matter if it gets the memory via mmap() or sbrk().
> The page faults is what hurts.
Right, the faults are what hurts, and with only the trim threshold set
they still happen, from munmap instead of brk. Setting it also sets
no_dyn_threshold (do_set_trim_threshold() in malloc.c), so the mmap
threshold stops adapting and stays at 128 kB. The pagetable and the
iteration arrays are each well over that, so they are mmapped, and free()
unmaps an mmapped chunk right away. The trim threshold only decides when
to shrink the top of the brk heap, which those chunks are no longer on.
> If we use 2 MiB huge pages for the allocations that problem should go
> away. Even if the Bitmap Index Scan or Hash Join needs less memory than
> that, the waste is fairly limited and only temporary. With that, we
> would also not need to keep the memory around for longer than necessary
> because doing the allocation should be very cheap now.
I tried it with glibc's own switches. Same setup as before, stock TPS
(faults per query), then patched:
btree bitmapand
glibc defaults 2,734 (692) 4,351 2,201 (1,092) 3,672
THP enabled=always 2,736 (732) 4,341 2,203 (1,090) 3,672
hugetlb=1 (MADV_HUGEPAGE) 3,825 (2) 4,355 3,038 (2) 3,667
hugetlb=2 (MAP_HUGETLB) 4,091 (0) 4,407 3,259 (0) 3,729
both thresholds set 4,055 (0) 4,342 3,244 (0) 3,670
THP alone does nothing: no huge pages get used at all, because the brk
heap grows and shrinks in small steps and never lines up with a 2 MB
boundary I'd imagine.
glibc.malloc.hugetlb=1 rounds the heap to 2 MB and madvises it, which
gets rid of nearly all the faults, but the memory is still given back
each query, so every first touch is now a 2 MB page to zero (about
41,000 THP faults in an 8 s window).
It's also the noisiest arm: stock btree varied 4.7% between runs,
against under 1% for the others. hugetlb=2, with pages from a reserved
pool, gives no faults and matches setting both thresholds. It needs
vm.nr_hugepages sized for the backends' heaps as well as shared_buffers
(about 290 pages in use at 96 clients here), and glibc quietly falls
back to small pages when the pool runs out.
So yes, 2 MB pages do fix the faults. Hash join and sort stop faulting
too (1,198 and 1,020 per query with defaults; 0 under hugetlb=2). What
they don't remove is the rest of the doubling: on top of hugetlb=2 the
patch is still +7.7% on btree and +14.4% on bitmapand, about what it
gives over jemalloc or with both thresholds set. That part is the
rehash and the copy, not the faults.
> But how does that apply to jemalloc? We're using jemalloc with
> PostgreSQL in production without any problems.
It doesn't, as far as I can tell; I should have been clearer. The
problem was specific to mimalloc, which reserves a 1 GiB arena in the
postmaster, so every backend allocates from the same inherited range.
jemalloc maps memory as each backend needs it, after the fork, and
showed nothing like it: 4,046 TPS and 7 faults per query on Linux, and
on FreeBSD, where libc's malloc is jemalloc, 3,982 and 1.
> Not necessarily. The allocations have better chance to stick around in
> the allocator or the memory contexts.
I measured that too, and you're right that it's not just half the
faults. I rebased John's PoC v2 onto master, which needed only trivial
conflict fixes, and ran it in the same matrix:
btree bitmapand
stock radix stock radix
glibc defaults 2,728 4,914 2,201 3,388
both thresholds set 4,046 5,460 3,247 4,481
Under glibc defaults it halves the faults (692 to 382 per query on
btree), as I guessed, but that alone already gives +80% on btree; it's
noisier there, varying 4.2% between runs. With memory kept, the radix
tree comes out 26% ahead of my patch on btree (5,460 against 4,342) and
22% on bitmapand (4,481 against 3,670). Most of that is the sort:
building the iteration arrays and sorting them is about 16% of the
patched profile, against about 1% for the radix tree with memory kept,
since it iterates in key order anyway.
The caveat is that John's PoC still has lossification turned off (his
0002), so this measures the data structure, not a finished patch. None
of the bitmaps in this test go lossy, so that doesn't change these
numbers, but it would matter under a small work_mem.
So I think the right direction is the radix tree, plus something at the
process level for the faults. 2 MB pages from a pool, or keeping memory
in the allocator, both work for hash join and sort as well. Keeping the
hash table's memory, as my 0002 does, fixes only one consumer of a
structure that is better replaced. I'd rather put the effort into
helping John's series along: the test module from my 0001 can serve as
its test suite, and I can measure lossify once it's back.
John, would that help?
best.
-greg
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Greg Burd | 2026-11-17 20:21:09 | Re: Hash index: stop registering pin-only bucket pages in WAL (remove REGBUF_NO_CHANGE) |
| Previous Message | Greg Burd | 2026-11-17 20:02:23 | Re: Tepid: selective index updates for heap relations |