Re: Increase NUM_XLOGINSERT_LOCKS

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

The main reason is that the increase in insert locks puts a lot more pressure
on the spinlock. 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.

On much larger machines this gets considerably worse. IIRC I saw something
like an 8x regression on a large machine in the past, but I couldn't find the
actual numbers anymore, so I wouldn't want to bet on it.

Greetings,

Andres Freund