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

11.04.2025 17:36, James Pang пишет:
>    pgv14.8 , during peak time, we suddenly see hundreds of active sessions
> waiting on LWlock  WALWrite at the same time, but we did not find any issue
> on storage .
> any suggestions ?

No real suggestions...

There is single WALWrite lock.
So only single process may actually write WAL to disk. There're no any
parallelism.

And the process also does fsync. One file after other. There're no any
parallelism.

And all other backend that needs to be sure their transactions settled on
the disk waits for this process.

And this process is gready: it collects farest position in WAL buffers
ready to be written, and write and fsync whole those buffers. And only
after than it releases WALWrite lock and other backends awoken.

So when many backends wrote to WAL buffers and now need to wait buffers are
settled on disc, they wait for this single process, which will write
buffers for all that backends.

So:
- many backends wrote WAL buffers,
- one backend calculated how many buffers were written,
- and then the backend write and fsync those buffers serially,
- all other backends waits for this backend,
In the results, backends waits each other, or, in other words, they waits
latest of them!!! All backends waits until WAL record written by latest of
them will be written and fsynced to disk.

(Andres, iiuc it looks to be main bottleneck on the way of increasing
NUM_XLOGINSERT_LOCKS. Right?)

-- 
regards
Yura Sokolov aka funny-falcon