Feedback-ID: i675e48f3:Fastmail
MIME-Version: 1.0
Date: Thu, 10 Sep 2026 10:50:47 -0400
From: "Greg Burd" <greg@burd.me>
To: "Jakub Wartak" <jakub.wartak@enterprisedb.com>,
 "Tomas Vondra" <tomas@vondra.me>
Cc: "Andres Freund" <andres@anarazel.de>,
 "Alexey Makhmutov" <a.makhmutov@postgrespro.ru>,
 "PostgreSQL Hackers" <pgsql-hackers@lists.postgresql.org>
Message-Id: <f78dfe7d-4a37-40ec-8e60-c2cc47c48364@app.fastmail.com>
In-Reply-To: 
 <CAKZiRmzT1M1fEFf5fRq2Qg3Vz0wLbdY+uNdF-0ORhDTUOUVhcA@mail.gmail.com>
References: <5bc6d309-61fe-4d8b-9e1f-a2961564a559@vondra.me>
 <05df16f8-025a-43cd-9636-3194464012ed@vondra.me>
 <CAKZiRmxwN+qMpbijCLPix_y6mwSjgus2CPPj=1+uFo9fQG-Knw@mail.gmail.com>
 <403c813e-7d63-4ee8-a95f-4e5c1e310f4d@vondra.me>
 <uezi46xhhbvdjgdi6wl7iqgfcdh4jmnnyzbfovdcrck6ywqa7j@fj3yimxvekk6>
 <0e1b997d-99c8-40f4-bc32-6c044bc7ed9a@vondra.me>
 <zndz3dlmp7xlypczxjbwdfrey3masto6vuwpnzjvgunslprv25@rucfkdgfpb4p>
 <rsjxzhnxxfq5i5yxv66mhinb42o3vzmqpkbfpexpkk5prreh2l@jyp73gsyyfzn>
 <2db78610-b480-4aa0-a1b6-57f1c2dcb708@vondra.me>
 <clx4zzd7kau4vvh5ynu5ssxg3jqfqzurgcbtotytzgzkhb3nis@qfl5xwv44yad>
 <2e1be441-5608-4bc8-9ac4-6fad9a060db4@vondra.me>
 <334f7644-6716-4e63-a7c4-13c5cbcdd210@vondra.me>
 <CAKZiRmzo9xnJSgO4b26DTZqPuObcQ-6ncay+mOEKs9rzCkegUA@mail.gmail.com>
 <86e7e218-9013-4cdb-9ed2-dfda49640b4d@vondra.me>
 <CAKZiRmzumFJ8+Bqz5mfX+QGkrKEaH32zz7kPgEShHWXE4QzyQw@mail.gmail.com>
 <c3954e28-0c38-4df8-b76e-ec09f6c04021@vondra.me>
 <CAKZiRmwsjwDatuV5JfUaAbkTxjnUaL_UK3SkFTJee-jo-u_N7Q@mail.gmail.com>
 <2b6abd8f-3659-4be1-9499-b4f00f973f22@vondra.me>
 <CAKZiRmzigM9XOz6m0K070ZVSmaKpjnVR4CZZENkfeH-rNv78wA@mail.gmail.com>
 <CAKZiRmz+0tYkoMAKq4Qoc6M1-ZhYFEJnJO23Q2Kf8eyhx3S4og@mail.gmail.com>
 <CAKZiRmzYM_5e8wStGQVVr7_v62br-EbUbTecK6HBFAKOGMBCfQ@mail.gmail.com>
 <CAKZiRmzT1M1fEFf5fRq2Qg3Vz0wLbdY+uNdF-0ORhDTUOUVhcA@mail.gmail.com>
Subject: Re: Adding basic NUMA awareness
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/f78dfe7d-4a37-40ec-8e60-c2cc47c48364%40app.fastmail.com>
Precedence: bulk


On Wed, Sep 2, 2026, at 8:21 AM, Jakub Wartak wrote:
> On Thu, Jul 2, 2026 at 11:24=E2=80=AFAM Jakub Wartak
> <jakub.wartak@enterprisedb.com> wrote:
>>
>> On Tue, Jun 30, 2026 at 2:51=E2=80=AFPM Jakub Wartak
>> <jakub.wartak@enterprisedb.com> wrote:
>> >
>> > On Mon, Jun 29, 2026 at 9:42=E2=80=AFAM Jakub Wartak
>> > <jakub.wartak@enterprisedb.com> wrote:
>> > >
>> > > On Thu, Jun 25, 2026 at 3:49=E2=80=AFPM Tomas Vondra <tomas@vondr=
a.me> wrote:
>> > > >
>> > > > >> I have some results from a new round of benchmarks, and it's=
 a bit
>> > > > >> disappointing. Or rather, there seem to be some issues that =
I can't
>> > > > >> figure out, causing regressions.
>> > > > > [..]
>> > > > >> This chart is for median latency (in milliseconds):
>> > > > >>
>> > > > >>   clients       master     0003      0004    0003/on    0004=
/on
>> > > > >>   ----------------------------------------------------------=
---
>> > > > >>         1        12767    12582     14509      12807      15=
307
>> > > > >>         8        14383    14355     14149      14069      16=
165
>> > > > >>        32        14756    15198     14836      14984      17=
128
>> > > > >>        -----------------------------------------------------=
---
>> > > > >>         1                  103%      114%       100%       1=
20%
>> > > > >>         8                  101%       98%        98%       1=
12%
>> > > > >>        32                  102%      101%       102%       1=
16%
>> > > > >>
>> >
>> > [..lots of variables..]
>> >
>> > > > I'll try, but if you could try running some experiments on your=
 own,
>> > > > that might be helpful.
>> > > [..]
>> > > > > Hopefully next week I'll try to repro those numbers to see if=
 I can
>> > > > > help more.
>> > > > >
>> > > >
>> > > > Thank you! That'd be great.
>> > >
>> > > Yeah, I'll try my best, we'll see how it goes. Right now I've jus=
t dropped
>> > > that fscachenuma proggie to aid us in troubleshooting.
>> > >
>> > > -J.
>> > >
>> > > [0] - https://github.com/jakubwartakEDB/fscachenuma
>> >
>> > Hi Tomas,
>> >
>> > OK, so I've run couple of tests and modified run.sh and also tried =
to fix
>> > some inefficiencies spotted while testing this. Note the attached
>> > performance matrix is in TPS (so more is better). Raw results/CSV a=
nd
>> > scripts are attached too.
>> >
>> > * run2 =3D 2 workloads, partitioned pgbench_accounts
>> > * run3 =3D just pgbenchS w/o partitioning + warmup
>> > * run4 =3D semi-like pgbenchS w/o partitioning but 100k rows + warm=
up
>> >
>> [..]
>> >
>> > Stil on the table:
>> >
>> > 1. maybe even better balancing is possible (?), but this one is see=
ms enough?
>> >    I'm out of other ideas, well other than the
>> >    "shared-relation-use-by-foreign-node" idea described much earlie=
r (but
>> >    I won't be able to pull that off), so I'm not entering this rabb=
it hole
>> >    any deeper.
>>
>> See below, seems like not needed (?)
>>
>> > 2. Digging into io_method=3Dworker optimizations (answering questio=
n: are they
>> >    necessary?) Maybe I'll throw in run5 quite soon, this is going t=
o be
>> >    crucial to answer.
>>
>> OK, I'm attaching are results from mine runs 5 and 6:
>> - only seqconcurrscans was tested, well because for other workloads i=
o_worker
>>   method was not getting load for those workers (only seq scans were =
offloaded)
>> - checksums were disabled, because IMHO that would be unfair comparis=
ion
>>   (AFAIR there are offloaded)
>> - those optimizations for 0008 "optimized (numa=3Don, bal=3Don)" easi=
ly beat
>>   "patched (numa=3Don, bal=3Don)" and seem to be crucial. We get like=
 1.2x-1.4x
>>   across every io_method, but only with 0008.
>> - even when then doing just those logical fully cached reads from ful=
ly VFS
>>   cached case, io_urings shines (I've added raw TPS number to show th=
is,
>>   compare across tables e.g. io_uring vs sync  13.378/8.993=3D1.487x =
for
>>   io_uring with NUMA, but for master's for io_uring:sync it was just =
8.79/7.389
>>   =3D 1.189x without NUMA); seems like io_uring is more lightweight t=
o show
>>   more benefits of remote memory latencies
>> - there's some more juice to get out of the balancer for 0-reuse work=
loads
>>   (but IMHO it's pointless to squeeze more, it's hard already)
>> - I was probably wrong when expecting that io_worker's worker process=
es/queues
>>   should get NUMA affinity. They don't need to be apparently for me t=
o see
>>   benefits (maybe they could  be and it would even better, but meh).
>>   So with ruling io_method impact (I speculated earlier that his coul=
d be it),
>>   this means that you were either hitting lack of opimizations needed=
 from
>>   0008 or were impacted by lack of drop_caches before the runs
>>
>> > Maybe You could run those run*.sh with master vs inst-patchset/opti=
mized?
>> > (I'm not sure, maybe there's even different factor at play too...)
>>
>> This is seems to be crucial now, to double confirm the results / load=
ed-tested
>> on your hw with 0008. (but that hardware really needs to have effecti=
ve latency
>> difference between at least 2 NUMA nodes -- Intel's mlc is good for t=
his);
>> maybe also tweak those 125% inside 0008 to some other values, I've go=
t 4 nodes,
>> so 100/4=3D25%)
>>
>> > 3. Potentially mentioned earlier BAS strategies (forcing just use o=
f local
>> >    partitions for known-to-be-only-local-users: CTAS/VACCUM/etc), b=
ut I'm
>> >    afarid that's not for me as I would certainly break/violate some
>> >    invisible to me boundary.
>>
>> And this one is still potentially on the table as nice thing to have.
>
> Hi Tomas,
>
> I've took another look at this thread and got some new further progres=
s:

Jakub, thanks for your tenacity on this thread. I hope we can get it
into shape soon and land these changes early in v20.

Your run15 crosscheck prompted me to stop quoting numbers from a
different box and workload, so I ran your harness myself. Everything
below uses your methodology verbatim. Your run2.sh structure, your
seqconcurrscans.sql and pgbenchS.sql unchanged, scale 1000
(pgbench_accounts 13GB) against shared_buffers=3D8GB, huge_pages=3Don,
io_method=3Dsync, synchronize_seqscans=3Doff, max_parallel_*=3D0, 8
partitions, 20s x 3 runs, drop_caches before every start, datadir on
local NVMe (real filesystem, not tmpfs). Box is i4i.metal, 2s64c128t / 2
NUMA nodes, the same class as your run7/run12.

Two builds: master (feddcbc3b2d) and our two patches (batched clock
sweep + a HOT/COOL cooling-stage evictor). No placement code, no new
GUCs, no partitioning.

  seqconcurrscans, median TPS of 3 runs
    clients   master     bcs     delta    p99 master   p99 bcs
         1      1.67    1.71     +2.3%      582,902    567,538
         8      5.70    6.45    +13.2%    1,733,853  1,634,787
        32     18.88   20.95    +11.0%    2,158,899  1,934,950
        64     35.46   39.24    +10.7%    2,252,605  2,101,901

  pgbenchS, median TPS of 3 runs
    clients      master        bcs     delta
         1      21,826     21,961     +0.6%
         8     169,901    169,058     -0.5%
        32     660,081    662,650     +0.4%
        64   1,026,765  1,028,672     +0.2%

Comments inline below.

> 1. We seem to crash on CXL setups and need some auto-detection of syst=
em
>    [...] the code today just did blind mbind() to that CPU-less NUMA-n=
ode
>    (e.g. node1) where we do not have hugepages, so it crashes with SIG=
BUS.

Good catch, and the qemu CPU-less-node recipe is a genuinely useful test
vector, maybe cheap enough that it could live in the tree eventually. Yo=
ur
(a) "detect CPU-less nodes and don't mbind them" reads like the right
default given Tomas wants to avoid configuration, with the caveat you
raise that nodes can't be assumed contiguous or static. Worth handling
sched_getaffinity()/NUMAMask too, as you say: if the operator already
constrained us with numactl or systemd, we should respect it rather than
mbind() over the top.

>    In essence the patchset (w/ 0008) couldn't give pgbenchS boost, but
>    gives ~9-10% in seqconcurrscans.

That matches what I see, from the other direction: +11-13% on
seqconcurrscans at c>=3D8, and flat on pgbenchS. Two independent changes
landing in the same ballpark on the same workload and both doing
nothing for pgbenchS, together a useful negative. A single-row -S lookup
just isn't buffer-sweep-bound, so nothing in the replacement path can
move it.

> 3a. mini-problem A): IMHO the c=3D1 regressions are worrying [...] Onc=
e a
>    backend is locked to a partition smaller than 1.6 GB (8 parts) it c=
an
>    never retain the table and stuff degrades.

Your diagnosis looks right to me, and it's a residency argument rather
than a contention one, which is why batching doesn't hit it: it
doesn't subdivide the pool, so there's nothing to fall out of. We
measure +2.3% at c=3D1 (i.e. noise), against your -25%/-47%/-49% at 4/8/=
16
parts. I don't think that's an argument against partitioning, but it
does suggest a floor on partition size relative to the largest relation
you expect to stay resident, rather than scaling partitions purely with
node or CPU count.

> 3b. bigger doubt B): why we are not getting more juice here [...] Greg=
 &
>    Jim reported up to +16..20% for __pgbenchS(RO)__, but here we cannot
>    even do so for seqconcurrscans?

Fair challenge, and I should be straight about it: that +16-20% pgbenchS
number was on r8i.metal-96xl (384 vCPU, 6 NUMA nodes via SNC3). On
2-node i4i.metal I get pgbenchS flat, exactly like you. So node count,
not just socket count, seems to be doing the work there; I'd treat our
pgbenchS figure as a 6-node result and not expect it at 2 nodes. That
probably also bears on your "why not 2-3x" question. Tomas's original
2-3x and my 16-20% may both be artifacts of much wider NUMA topologies
than either of our current boxes.

>    I couldn't see any increase of pgbenchS (RO), nor even 2x for
>    seqconcurrscans, but I could see that this patchset more or less gi=
ves
>    1.2x today, while their batched-atomic-fetches yield ~1.3x.

I can confirm your reading.

What I can add that isn't in the thread yet is the mechanism. perf c2c
during those same seqconcurrscans runs:

  master, c=3D32: top shared cache line is StrategyGetBuffer's atomic
                fetch-add (arch-x86.h:132, i.e. nextVictimBuffer), at
                59.8% / 62.6% cross-node HITM
  bcs,    c=3D32: 0.71% / 0.61%
  master, c=3D64: 56.0% / 60.6%
  bcs,    c=3D64: 0.66% / 0.45%

That's the whole of what batching does. The hand stays a single
global counter, it just gets touched ~1/N as often, so the line stops
ping-ponging. It also says something about your 0.32%
StrategyGetBuffer CPU% at 16 partitions, CPU share and cross-node HITM
share are different things, and c2c is the one that tracks the
interconnect cost.

Which brings me to why I'm posting here rather than only on my thread: I
don't think these compete. Partitioning makes the hand node-local;
batching reduces how often any hand is touched. Even with a per-node
hand, that counter is still shared by every backend on the node, and c2c
should still show it, so the two should compose. The obvious experiment
is a 3-way on one box: master / your optimized / your optimized + our
batched claim. I have your harness working now and I'm happy to run that
on both a 2-node and a 6-node box and post the numbers. If you'd rather
I test a particular partition count or the aligned variant alongside it,
say so and I'll fold it in.  Too late, I've started the test... ;-)

> 4. My earlier BAS strategy idea =3D=3D meh. [...] everything is rarely
>    touching remote DRAM, most of stuff is simply cached by L1 [...]

For what it's worth this lines up with something we found from the
opposite direction. I tried removing the read strategies outright and
had to put them back. On local NVMe the BAS_BULKREAD ring is
measurably better at keeping a concurrent scan from evicting the OLTP
working set than admission policy alone, and BAS_BULKWRITE is doing real
backpressure on bulk writers rather than just pollution avoidance. So
"leave the rings alone" seems right from both angles, and your result
that affinity buys nothing on top is a useful reason not to add code
there.

best.

-greg