many sessions wait on LWlock WALWrite suddenly

[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 ?

Thanks,

James

Re: many sessions wait on LWlock WALWrite suddenly

[Laurenz Albe]

On Fri, 2025-04-11 at 22:36 +0800, James Pang wrote:
> 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 ? 

You should get a reasonably sized (much smaller) connection pool.
That will probably take care of the problem and will probably
improve your overall performance.

Yours,
Laurenz Albe

Re: many sessions wait on LWlock WALWrite suddenly

[MichaelDBA]

LWLock always shows up in the case where you have too many concurrent
active connections.  Do a select from the pg_stat_activity table where
state in ('idle in transaction','active'); Then count how many CPUs you
have. If the sql query count returned is greater than 2-3 times the
number of CPUs, you probably have a CPU overload problem and your
solution may be to add a connection pooler between the client and the DB
server.  This is all due to the nature of how PG is architected: every
connection is a process, not a thread.

Regards,
Michael Vitale

James Pang wrote on 4/11/2025 10:36 AM:

   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 ?

Thanks,

James



Regards,

Michael Vitale

michael...@sqlexec.com 

703-600-9343