Re: Read-Write optimistic lock (Re: sinvaladt.c: remove msgnumLock, use atomic operations on maxMsgNum)

Hi,

On 7/16/25 18:54, Andres Freund wrote:
> That was not in reply to the changed patch, but about the performance numbers
> you relayed. We had no repro, and even with the repro that Sergey has now
> delivered, we don't see similar levels of what you reported as contention.
We investigated this issue in detail and were able to reproduce the 
spinlock contention in SIGetDataEntries. The problem is most evident on 
multiprocessor systems with multiple NUMA nodes, but it also occurs on a 
single node, albeit less pronounced. This is probably also the case for 
high-frequency CPU.

We ran tests on two bare-metal servers:

4 NUMA nodes × 24 CPUs Intel(R) Xeon(R) Gold 6348H CPU @ 2.30GHz. 
PostgreSQL was running on 3 nodes (72 CPUs).

2 NUMA nodes × 32 CPUs Intel(R) Xeon(R) Gold 6338 CPU @ 2.00GHz. 
PostgreSQL was running on a single node (32 CPUs).

and two PostgreSQL builds: from master branch and the with the patch 
v5-0001-Read-Write-optimistic-spin-lock.patch.

To generate frequent cache invalidations, we executed a background 
workload that repeatedly created and dropped temporary tables with 
indexes in a loop.

do $$
begin
    for i in 1..1000000 loop
      create temp table tt1 (
     f0 bigserial primary key,
     f1 int,
     f2 int,
         f3 int,
         f4 int,
         f5 int,
         f6 int,
         f7 int,
         f8 int,
         f9 int,
         f10 int);
      CREATE INDEX ON tt1(f1);
      CREATE INDEX ON tt1(f2);
      CREATE INDEX ON tt1(f3);
      CREATE INDEX ON tt1(f4);
      CREATE INDEX ON tt1(f5);
      CREATE INDEX ON tt1(f6);
      CREATE INDEX ON tt1(f7);
      CREATE INDEX ON tt1(f8);
      CREATE INDEX ON tt1(f9);
      CREATE INDEX ON tt1(f10);
      drop table tt1;
      commit;
    end loop;
end;
$$;

As a benchmark, we used a pgbench select-only scenario with 64 clients:

pgbench -U postgres -c 64 -j 32 -T 200 -s 100 -M prepared -b select-only 
postgres -n

For convenience, the test is included as test.sh (attached), with 
description and setup instructions provided in the README.

During the test, we ran perf for 10 seconds using the command

perf record -F 99 -a -g --call-graph=dwarf -o perf_data sleep 10.

Аnd then generated flame graphs from the collected data

1. Three NUMA nodes (72 CPUs)

According to the flame graph (fg_3numa_nopatch.xml), about 34% of 
exec_bind_message is spent in SIGetDataEntries, >90% of which is 
spinlock wait (see fg_3numa_nopatch.xml).

With the patch the share of SIGetDataEntries decreases to ~6.6%, the 
main waiting shifts to LWLockAcquire, and RWOptSpinReadStart accounts 
for only ~1.1% (fg_3numa_patch.xml). TPS improvement: +6–8% (over 5 runs).

Without patch: TPS = 731171.336542
With patch: TPS = 786077.155196

2. Single NUMA node (32 CPUs)

In this case the problem is less pronounced, but still SIGetDataEntries 
takes 10.1% of exec_bind_message, of which 82.3% is spinlock wait 
(fg_1numa_nopatch.xml).

With the patch we observed a stable 1.5–2% TPS increase (5 runs).

Without patch: TPS = 518941.051825
With patch: TPS = 528768.641836

The flame graph does not show absolute time, but the relative 
distribution confirms contention on the spinlock in SIGetDataEntries.
The problem exists and is a bottleneck under high load, especially on 
multiprocessor NUMA systems. The patch mitigates this contention and 
improves performance.

---
Best regards,
Olga Antonova