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 08:36:40 |
Message-ID: | E944F8CB-183E-4DF5-BDFC-67D947AF9760@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
James,
A lock can be obtained in the parse, plan and execute step, depending on cache, state and type of object.
A LWLock is a spinlock, a low level access mechanism that is supposed to be extremely quickly. It is used to serialise access to elementary structures mostly for changes.
A Lock is an higher level lock that is much more sophisticated, contains multiple states and can order multiple requests. It is used to safeguard transaction intention for objects.
The wait LWLock:LockManager is documented to have two common reasons: too many locks being acquired, exceeding the fastpath slots number (16) and/or exceeding CPU capacity.
What is happening if you are waiting for a LWLock is that the number of processes trying to access the structure (the lock manager) is higher than one. Because the LWLock is meant to be held so briefly that there should no waiting, it means that if you are waiting for it, there must be a reason it’s held so long. An obvious reason for holding a LWLock too long is if there are more tasks on the OS than CPU’s, as Laurenz indicates. If such a situation happens, it’s possible a tasks is put off CPU by the operating system whilst holding the LWLock, which will greatly increase the time waiting for it, because the LWLock can only be released if the task manages to get back on CPU.
Regards,
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 | Frits Hoogland | 2024-04-09 09:36:41 | Re: LWlock:LockManager waits |
Previous Message | James Pang | 2024-04-09 07:54:45 | Re: LWlock:LockManager waits |