On Wed, Jan 10, 2024 at 11:43 AM Tom Lane <t...@sss.pgh.pa.us> wrote: > > Bharath Rupireddy <bharath.rupireddyforpostg...@gmail.com> writes: > > On Wed, Jan 10, 2024 at 10:00 AM Tom Lane <t...@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
v1-0001-WAL-insertion-stats.patch
Description: Binary data