Message-ID: <c73a8abb-afd8-41f5-abcc-77f9a27d99d2@vondra.me>
Date: Tue, 13 Jan 2026 02:25:57 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Adding basic NUMA awareness
To: Andres Freund <andres@anarazel.de>
Cc: Jakub Wartak <jakub.wartak@enterprisedb.com>,
 Alexey Makhmutov <a.makhmutov@postgrespro.ru>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: <33d0da22-1400-4b7a-b4c8-1867b8a2cae0@vondra.me>
 <5bc6d309-61fe-4d8b-9e1f-a2961564a559@vondra.me>
 <CAKZiRmwPVxi1H23pNZ4_Vc=mtMaNgY1z79s6SwjuUZD3EaOPeA@mail.gmail.com>
 <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>
 <06c7e398-3336-49f2-a011-cc89ee28b9c0@vondra.me>
 <4jqqz454it4ims43k5ftzjfci5l4klhw5rw54xqx4pyirm7pqr@kdyywhl2zro6>
Content-Language: en-US
From: Tomas Vondra <tomas@vondra.me>
In-Reply-To: <4jqqz454it4ims43k5ftzjfci5l4klhw5rw54xqx4pyirm7pqr@kdyywhl2zro6>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/c73a8abb-afd8-41f5-abcc-77f9a27d99d2%40vondra.me>
Precedence: bulk


On 1/13/26 02:08, Andres Freund wrote:
> Hi,
> 
> On 2026-01-13 01:51:09 +0100, Tomas Vondra wrote:
>> On 1/13/26 01:10, Andres Freund wrote:
>>> Hi,
>>>
>>> On 2026-01-13 00:58:49 +0100, Tomas Vondra wrote:
>>>> On 1/10/26 02:42, Andres Freund wrote:
>>>>> psql -Xq -c 'SELECT pg_buffercache_evict_all();' -c 'SELECT numa_node, sum(size) FROM pg_shmem_allocations_numa GROUP BY 1;' && perf stat --per-socket  -M memory_bandwidth_read,memory_bandwidth_write -a psql -c 'SELECT sum(abalance) FROM pgbench_accounts;'
>>>
>>>> And then I initialized pgbench with scale that is much larger than
>>>> shared buffers, but fits into RAM. So cached, but definitely > NB/4. And
>>>> then I ran
>>>>
>>>>   select * from pgbench_accounts offset 1000000000;
>>>>
>>>> which does a sequential scan with the circular buffer you mention abobe
>>>
>>> Did you try it with the query I suggested? One plausible reason why you did
>>> not see an effect with your query is that with a huge offset you actually
>>> never deform the tuple, which is an important and rather latency sensitive
>>> path.
>>>
>>
>> I did try with the agg query too, and there's still no difference on
>> either machine.
> 
> Could you provide numactl --hardware for both?  There may be more than two
> numa nodes on a system with 2 sockets, due to one socket being split into two
> - in which case the latency between 0,1 might be a lot lower than say 0 and 3.
> 

xeon:

available: 2 nodes (0-1)
node 0 cpus: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 44
45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65
node 0 size: 32066 MB
node 0 free: 13081 MB
node 1 cpus: 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
42 43 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87
node 1 size: 32210 MB
node 1 free: 17764 MB
node distances:
node     0    1
   0:   10   21
   1:   21   10

azure/epyc:

available: 2 nodes (0-1)
node 0 cpus: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47
node 0 size: 193412 MB
node 0 free: 147949 MB
node 1 cpus: 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67
68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91
92 93 94 95
node 1 size: 193513 MB
node 1 free: 151577 MB
node distances:
node   0   1
  0:  10  11
  1:  11  10


> 
>> I can't do the perf on the Azure VM, because the Ubuntu is image is
>> borked and does not allow installing the package. But on my xeon I can
>> do the perf, and that gives me this:
>>
>> numactl --membind=0 --cpunodebind=0 ~/builds/master-test/bin/pg_ctl
>> -----------------------------------------------------------------------
>> S0 1     24,677,226    UNC_M_CAS_COUNT.WR #    79.0 MB/s ... idth_write
>> S0 1 20,001,829,522 ns duration_time                     ...
>> S0 1    972,631,426    UNC_M_CAS_COUNT.RD #  3112.2 MB/s ... idth_read
>> S0 1 20,001,822,807 ns duration_time                     ...
>> S1 1     15,602,233    UNC_M_CAS_COUNT.WR #    49.9 MB/s ... idth_write
>> S1 1    712,431,146    UNC_M_CAS_COUNT.RD #  2279.6 MB/s ... idth_read
>>
>>
>> numactl --membind=0 --cpunodebind=1 ~/builds/master-test/bin/pg_ctl
>> -----------------------------------------------------------------------
>> S0 1     47,931,019    UNC_M_CAS_COUNT.WR #    153.4 MB/s ... idth_write
>> S0 1 20,002,933,380 ns duration_time                      ...
>> S0 1  1,007,386,994    UNC_M_CAS_COUNT.RD #   3223.2 MB/s ... idth_read
>> S0 1 20,002,927,341 ns duration_time                      ...
>> S1 1     10,310,201    UNC_M_CAS_COUNT.WR #     33.0 MB/s ... idth_write
>> S1 1    714,826,668    UNC_M_CAS_COUNT.RD #   2287.2 MB/s ... idth_read
>>
>> so there is a little bit of a difference for some stats, but not much.
>>
>>
>> FWIW this is from
>>
>> perf stat --per-socket  -M memory_bandwidth_read,memory_bandwidth_write
>> -a -- sleep 20
>>
>> while the agg query runs in a loop.
> 
> FWIW doing one perf stat for each execution is preferrable for comparison,
> because otherwise you can hide large differences in total number of memory
> accesses if the runtimes for the queries in the two "numa configurations" are
> different.
> 

Good point, I'll do that next time. But in this case they are not all
that different, I think.

regards

-- 
Tomas Vondra