Re: Make NUM_XLOGINSERT_LOCKS configurable

On Wed, Jan 10, 2024 at 11:43 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Bharath Rupireddy <bharath(dot)rupireddyforpostgres(at)gmail(dot)com> writes:
> > On Wed, Jan 10, 2024 at 10:00 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> Maybe. I bet just bumping up the constant by 2X or 4X or so would get
> >> most of the win for far less work; it's not like adding a few more
> >> LWLocks is expensive. But we need some evidence about what to set it to.
>
> > I previously made an attempt to improve WAL insertion performance with
> > varying NUM_XLOGINSERT_LOCKS. IIRC, we will lose what we get by
> > increasing insertion locks (reduction in WAL insertion lock
> > acquisition time) to the CPU overhead of flushing the WAL in
> > WaitXLogInsertionsToFinish as referred to by the following comment.
>
> Very interesting --- this is at variance with what the OP said, so
> we definitely need details about the test conditions in both cases.
>
> > Unfortunately, I've lost the test results, I'll run them up again and
> > come back.
>
> Please.

Okay, I'm back with some testing.

Test case:
./pgbench --initialize --scale=100 --username=ubuntu postgres
./pgbench --progress=10 --client=64 --time=300 --builtin=tpcb-like
--username=ubuntu postgres

Setup:
./configure --prefix=$PWD/inst/ CFLAGS="-ggdb3 -O3" > install.log &&
make -j 8 install > install.log 2>&1 &

shared_buffers = '8GB'
max_wal_size = '32GB'
track_wal_io_timing = on

Stats measured:
I've used the attached patch to measure WAL Insert Lock Acquire Time
(wal_insert_lock_acquire_time) and WAL Wait for In-progress Inserts
to Finish Time (wal_wait_for_insert_to_finish_time).

Results with varying NUM_XLOGINSERT_LOCKS (note that we can't allow it
be more than MAX_SIMUL_LWLOCKS):
Locks TPS WAL Insert Lock Acquire Time in Milliseconds WAL
Wait for In-progress Inserts to Finish Time in Milliseconds
8 18669 12532 8775
16 18076 10641 13491
32 18034 6635 13997
64 17582 3937 14718
128 17782 4563 20145

Also, check the attached graph. Clearly there's an increase in the
time spent in waiting for in-progress insertions to finish in
WaitXLogInsertionsToFinish from 8.7 seconds to 20 seconds. Whereas,
the time spent to acquire WAL insertion locks decreased from 12.5
seconds to 4.5 seconds. Overall, this hasn't resulted any improvement
in TPS, in fact observed slight reduction.

--
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com