Hi Yura, Andres and all, I would like to follow up on the insertion-lock discussion. Andres's large-record counterexample remains relevant: increasing the lock count can reduce throughput, and removing the reservation spinlock does not eliminate the cost of checking and waiting for insertions.
Attached is the v1 wal_insert_locks GUC patch for CF 6193, based on master 4280abf. Instead of v0's fixed increase to 64, it keeps the default at 8 and accepts powers of two from 4 to 128 at server start. Selection uses a bitmask, addressing the earlier concern about integer division in the hot path. Invalid values are rejected rather than rounded. The upper bound is below MAX_SIMUL_LWLOCKS (200), since exclusive WAL operations acquire all insertion locks. This patch applies independently and does not require CAS128. The CAS128 reservation patch is being submitted separately in its existing thread [1], tracked by CF 5633. Each CF entry has its own attachment. Andrew Kim reviewed and tested both patches off-list on 5ffc821, including the GUC validation hook. This patch carries his Reviewed-by and Tested-by trailers. With the separate CAS128 patch applied, his HammerDB comparison with unmodified master8 measured -0.2% at the default 8 locks and +40.4% at 64 locks. These are combined CAS128 + GUC results. He used Intel Xeon CPUs, 2 sockets / 384 threads, GCC 14.2 with -O2 -mcx16, 250 warehouses / 256 VUs, 2 minutes ramp-up and 3 minutes measurement, and 3 mirrored blocks. WAL was on tmpfs, with no checkpoints in the measured windows. The reservation post includes the compile-time-64 and earlier hash/CAS comparisons. The 1 MiB regression remains with higher lock counts. A separate writer diagnostic limits both the wait and actual write target to the same batch boundary; I would like to discuss that independently. Would a bounded startup setting be preferable to increasing the fixed default, given the gains and counterexamples? I would appreciate review of the setting, its bounds and its validation hook here; CAS128 review can continue in [1]. Regards, Zhiguo [1] https://postgr.es/m/flat/[email protected] ________________________________________ From: Yura Sokolov <[email protected]> Sent: Saturday, January 18, 2025 7:53 PM To: Andres Freund <[email protected]> Cc: [email protected] <[email protected]>; Zhou, Zhiguo <[email protected]>; wenhui qiu <[email protected]> Subject: Re: Increase NUM_XLOGINSERT_LOCKS Since it seems Andres missed my request to send answer's copy, here it is: On 2025-01-16 18:55:47 +0300, Yura Sokolov wrote: > 16.01.2025 18:36, Andres Freund пишет: >> Hi, >> >> On 2025-01-16 16:52:46 +0300, Yura Sokolov wrote: >>> Good day, hackers. >>> >>> Zhiguo Zhow proposed to transform xlog reservation to lock-free algorighm to >>> increment NUM_XLOGINSERT_LOCKS on very huge (480vCPU) servers. [1] >>> >>> While I believe lock-free reservation make sense on huge server, it is hard >>> to measure on small servers and personal computers/notebooks. >>> >>> But increase of NUM_XLOGINSERT_LOCKS have measurable performance gain (using >>> synthetic test) even on my working notebook: >>> >>> Ryzen-5825U (8 cores, 16 threads) limited to 2GHz , Ubuntu 24.04 >> >> I've experimented with this in the past. >> >> >> Unfortunately increasing it substantially can make the contention on the >> spinlock *substantially* worse. >> >> c=80 && psql -c checkpoint -c 'select pg_switch_wal()' && pgbench -n -M prepared -c$c -j$c -f <(echo "SELECT pg_logical_emit_message(true, 'test', repeat('0', 1024*1024));";) -P1 -T15 >> >> On a 2x Xeon Gold 5215, with max_wal_size = 150GB and the workload ran a few >> times to ensure WAL is already allocated. >> >> With >> NUM_XLOGINSERT_LOCKS = 8: 1459 tps >> NUM_XLOGINSERT_LOCKS = 80: 2163 tps > > So, even in your test you have +50% gain from increasing > NUM_XLOGINSERT_LOCKS. > > (And that is why I'm keen on smaller increase, like upto 64, not 128). Oops, I swapped the results around when reformatting the results, sorry! It's the opposite way. I.e. increasing the locks hurts. Here's that issue fixed and a few more NUM_XLOGINSERT_LOCKS. This is a slightly different disk (the other seems to have to go the way of the dodo), so the results aren't expected to be exactly the same. NUM_XLOGINSERT_LOCKS TPS 1 2583 2 2524 4 2711 8 2788 16 1938 32 1834 64 1865 128 1543 >> >> The main reason is that the increase in insert locks puts a lot more pressure >> on the spinlock. > > That it addressed by Zhiguo Zhow and me in other thread [1]. But increasing > NUM_XLOGINSERT_LOCKS gives benefits right now (at least on smaller > installations), and "lock-free reservation" should be measured against it. I know that there's that thread, I just don't see how we can increase NUM_XLOGINSERT_LOCKS due to the regressions it can cause. >> Secondarily it's also that we spend more time iterating >> through the insert locks when waiting, and that that causes a lot of cacheline >> pingpong. > > Waiting is done with LWLockWaitForVar, and there is no wait if `insertingAt` > is in future. It looks very efficient in master branch code. But LWLockWaitForVar is called from WaitXLogInsertionsToFinish, which just iterates over all locks. Greetings, Andres Freund
v1-0001-wal-insert-locks-4280abf.patch
Description: v1-0001-wal-insert-locks-4280abf.patch
