From: | Frits Hoogland <frits(dot)hoogland(at)gmail(dot)com> |
---|---|
To: | James Pang <jamespang886(at)gmail(dot)com> |
Cc: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: LWlock:LockManager waits |
Date: | 2024-04-09 09:36:41 |
Message-ID: | A179FC2F-254B-48F0-AD79-7D4A9DCC0528@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
I need to rectify myself: LWLock is not a spinlock (anymore).
The documentation in lwlock.c makes it clear that it used to be spinlock, but now is a counter modified by atomic instructions.
Oh, I forgot to answer:
> you mean too many concurrent sessions trying to acquire lock on same relation , then waiting on "LockManager" LWlock,right?
This is the point: no, it’s not about the same relation.
The LWLock:LockManager is a wait event that is raised when competing for the LWLock that protects the shared Lock structure, which holds all of the locks of the database.
Frits Hoogland
> On 9 Apr 2024, at 09:54, James Pang <jamespang886(at)gmail(dot)com> wrote:
>
> you mean too many concurrent sessions trying to acquire lock on same relation , then waiting on "LockManager" LWlock,right? this contention occurred on parsing ,planning, or execute step ?
>
> Thanks,
>
> James
>
> Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at <mailto:laurenz(dot)albe(at)cybertec(dot)at>> 於 2024年4月9日週二 下午12:31寫道:
>> On Tue, 2024-04-09 at 11:07 +0800, James Pang wrote:
>> > we found sometimes , with many sessions running same query "select ..." at the same time, saw many sessions waiting on "LockManager". for example, pg_stat_activity show. It's a production server, so no enable trace_lwlocks flag. could you direct me what's the possible reason and how to reduce this "lockmanager" lock? all the sql statement are "select " ,no DML.
>> >
>> > time wait_event count(pid)
>> > 2024-04-08 09:00:06.043996+00 | DataFileRead | 42
>> > 2024-04-08 09:00:06.043996+00 | | 15
>> > 2024-04-08 09:00:06.043996+00 | LockManager | 31
>> > 2024-04-08 09:00:06.043996+00 | BufferMapping | 46
>> > 2024-04-08 09:00:07.114015+00 | LockManager | 43
>> > 2024-04-08 09:00:07.114015+00 | DataFileRead | 28
>> > 2024-04-08 09:00:07.114015+00 | ClientRead | 11
>> > 2024-04-08 09:00:07.114015+00 | | 11
>>
>> That's quite obvious: too many connections cause internal contention in the database.
>>
>> Reduce the number of connections by using a reasonably sized connection pool.
>>
>> Yours,
>> Laurenz Albe
From | Date | Subject | |
---|---|---|---|
Next Message | Luiz Fernando G. Verona | 2024-04-09 10:33:36 | Re: LWlock:LockManager waits |
Previous Message | Frits Hoogland | 2024-04-09 08:36:40 | Re: LWlock:LockManager waits |