Re: Make NUM_XLOGINSERT_LOCKS configurable

From: Bharath Rupireddy <bharath(dot)rupireddyforpostgres(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Michael Paquier <michael(at)paquier(dot)xyz>, 1111hqshj(at)sina(dot)com, pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Make NUM_XLOGINSERT_LOCKS configurable
Date: 2024-01-10 06:09:11
Message-ID: CALj2ACVMmnpUvmbBYhBsjzn3KpKtH99mYp+U_=fqqWMefUtRhQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Wed, Jan 10, 2024 at 10:00 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Michael Paquier <michael(at)paquier(dot)xyz> writes:
> > This suggestion has showed up more than once in the past, and WAL
> > insertion is a path that can become so hot under some workloads that
> > changing it to a GUC would not be wise from the point of view of
> > performance. Redesigning all that to not require a set of LWLocks
> > into something more scalable would lead to better result, whatever
> > this design may be.
>
> 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.
Unfortunately, I've lost the test results, I'll run them up again and
come back.

/*
* Number of WAL insertion locks to use. A higher value allows more insertions
* to happen concurrently, but adds some CPU overhead to flushing the WAL,
* which needs to iterate all the locks.
*/
#define NUM_XLOGINSERT_LOCKS 8

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2024-01-10 06:13:57 Re: Make NUM_XLOGINSERT_LOCKS configurable
Previous Message Michael Paquier 2024-01-10 05:24:59 Re: Show WAL write and fsync stats in pg_stat_io