Re: Make NUM_XLOGINSERT_LOCKS configurable - Mailing list pgsql-hackers

From Bharath Rupireddy
Subject Re: Make NUM_XLOGINSERT_LOCKS configurable
Date
Msg-id CALj2ACUQg4UpmNrD5-jnMiizEZiFo-V8rtpQYcKAktR4jNN23w@mail.gmail.com
Whole thread Raw
In response to Re: Make NUM_XLOGINSERT_LOCKS configurable  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Make NUM_XLOGINSERT_LOCKS configurable
Re: Make NUM_XLOGINSERT_LOCKS configurable
List pgsql-hackers
On Wed, Jan 10, 2024 at 11:43 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com> writes:
> > On Wed, Jan 10, 2024 at 10:00 AM Tom Lane <tgl@sss.pgh.pa.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

Attachment

pgsql-hackers by date:

Previous
From: "Hayato Kuroda (Fujitsu)"
Date:
Subject: RE: speed up a logical replica setup
Next
From: Bertrand Drouvot
Date:
Subject: Re: Synchronizing slots from primary to standby