Message-ID: <c81611f5-ad93-420b-9bc2-d1576028c337@postgrespro.ru>
Date: Tue, 15 Apr 2025 13:58:55 +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>
 <4f101390-560c-4bd4-a547-e1c6ad90fe7f@postgrespro.ru>
 <ve2vjj4oamrwt2tjjd3kpq52rtrq7z2ir3tejrrfelgrabaz7h@koynlan6rvxp>
Content-Language: en-US
From: Yura Sokolov <y.sokolov@postgrespro.ru>
In-Reply-To: <ve2vjj4oamrwt2tjjd3kpq52rtrq7z2ir3tejrrfelgrabaz7h@koynlan6rvxp>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/c81611f5-ad93-420b-9bc2-d1576028c337%40postgrespro.ru>
Precedence: bulk

15.04.2025 13:53, Andres Freund пишет:
> Hi,
> 
> On 2025-04-15 13:44:09 +0300, Yura Sokolov wrote:
>> 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.
> 
> I don't find that approach particularly promising - I do think we want this to
> be an ordered datastructure, not something as fundamentally unordered as a
> hashtable.

I've tried to construct such thing. But "Switch WAL" record thing didn't
allow me to finish the design. Because "Switch WAL" have no fixed size, and
it is allowed to not be inserted. It breaks ordering.

Probably, I just didn't think hard enough to work around.

And certainly I though about it only for log reservation, not for waiting
on insertion to complete, nor for waiting writing to complete.

>> And then all stuck in WALWrite lock.
> 
> That will often, but not always, mean that you're just hitting the IO
> throughput of the storage device.  Right now it's too hard to tell the
> difference, hence the suggestion to make the wait events more informative.
> 
> 
>>> 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.
> 
> I think we should simply get rid of the mechanism alltogether :)
> 
> 
>> 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.
> 
> Yes, that's a problem - but it also adds some overhead.  I think we'll be
> better off going with the ringbuffer approach where insertions are naturally
> ordered and we can wait for precisely the insertions that we need to.

-- 
regards
Yura Sokolov aka funny-falcon