Message-ID: <4f101390-560c-4bd4-a547-e1c6ad90fe7f@postgrespro.ru>
Date: Tue, 15 Apr 2025 13:44:09 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: many sessions wait on LWlock WALWrite suddenly
To: Andres Freund <andres@anarazel.de>
Cc: James Pang <jamespang886@gmail.com>,
 pgsql-performance@lists.postgresql.org
References: 
 <CAHgTRffKZ8DvcFJHRpwkBMQZg5XLRbd5yWe+pSGeVRqbqXDsDg@mail.gmail.com>
 <20ee7c44-bb93-4590-a984-b7fe4e4a9b14@postgrespro.ru>
 <l23ybh777m5u7goxpzznqtobbgxanm65k7yxqvstwh3lze5mn4@3x5dkdtwzpj6>
Content-Language: en-US
From: Yura Sokolov <y.sokolov@postgrespro.ru>
In-Reply-To: <l23ybh777m5u7goxpzznqtobbgxanm65k7yxqvstwh3lze5mn4@3x5dkdtwzpj6>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/4f101390-560c-4bd4-a547-e1c6ad90fe7f%40postgrespro.ru>
Precedence: bulk

15.04.2025 13:00, Andres Freund пишет:
> 1) Increasing NUM_XLOGINSERT_LOCKS allows more contention on insertpos_lck and
>    spinlocks scale really badly under heavy contention
> 
> I think we can redesign the mechanism so that there's an LSN ordered
> ringbuffer of in-progress insertions, with the reservation being a single
> 64bit atomic increment, without the need for a low limit like
> NUM_XLOGINSERT_LOCKS (the ring size needs to be limited, but I didn't see a
> disadvantage with using something like MaxConnections * 2).

There is such attempt at [1]. And Zhiguo tells it really shows promising
results.

No, I did it not with "ring-buffer", but rather with hash-table. But it is
still lock-free.

But after implementing that I found WALBufMappingLock [2] (which is already
removed).

And then all stuck in WALWrite lock.

> However, I think there's somewhat of an *inverse* relationship.  To
> efficiently flush WAL in smaller increments, we need a cheap way of
> identifying the number of backends that need to wait up to a certain LSN.

I believe, LWLockWaitForVar should be redone:
- currently it waits for variable to change (ie to be disctinct from
provided value).
- but I believe, it should wait for variable to be greater than provided value.

This way:
- WALInsertLock waiter will not awake for every change of insertingAt
- process, which writes and fsync WAL, will be able to awake waiters on
every fsync, instead of end of whole write.

It will reduce overhead of waiting WALInsertLock a lot, and will greately
reduce time spend on waiting WALWrite lock.

Btw, insertingAt have to be filled at the start of copying wal record to
wal buffers. Yes, we believe copying of small wal record is fast, but when
a lot of wal inserters does their job, we needlessly sleep on their
WALInsertLock although they are already in the future.

[1] https://commitfest.postgresql.org/patch/5633/
[2] https://commitfest.postgresql.org/patch/5511/

-- 
regards
Yura Sokolov aka funny-falcon