public inbox for [email protected]
help / color / mirror / Atom feedlwlock:LockManager wait_events
5+ messages / 5 participants
[nested] [flat]
* lwlock:LockManager wait_events
@ 2024-10-25 06:36 James Pang <[email protected]>
0 siblings, 2 replies; 5+ messages in thread
From: James Pang @ 2024-10-25 06:36 UTC (permalink / raw)
To: [email protected]
experts,
we faced into a lot of lwlock:LockManager wait-events , all of these
queries are "select ..." , there are other several session are doing DML,
insert/update/delete on same table. Did these DML transactions holding
"transactionid" and "tuple" lock blocking "select" on lwlock:LockManager ?
Thanks,
James
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: lwlock:LockManager wait_events
@ 2024-10-25 07:36 Laurenz Albe <[email protected]>
parent: James Pang <[email protected]>
1 sibling, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2024-10-25 07:36 UTC (permalink / raw)
To: James Pang <[email protected]>; [email protected]
On Fri, 2024-10-25 at 14:36 +0800, James Pang wrote:
> we faced into a lot of lwlock:LockManager wait-events , all of these queries are "select ..." ,
> there are other several session are doing DML, insert/update/delete on same table.
> Did these DML transactions holding "transactionid" and "tuple" lock blocking "select" on lwlock:LockManager ?
This is not about waiting for a lock. Rather, it means that very many sessions are trying
to take a lock. They have to grab the lock manager to take a lock, and the competition for
that resource is the bottleneck.
It is hard to say what is the root cause without further analysis, but very often the
cause is that you have too many connections to the database. Using an effective connection
pool *might* solve that particular problem.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: lwlock:LockManager wait_events
@ 2024-10-25 08:37 Sean Massey <[email protected]>
parent: Laurenz Albe <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Sean Massey @ 2024-10-25 08:37 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: James Pang <[email protected]>; [email protected]
You may find this helpful, the advice is not specific to RDS.
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/wait-event.lw-lock-manager.html
HTH.
On Fri, 25 Oct 2024 at 18:36, Laurenz Albe <[email protected]> wrote:
> On Fri, 2024-10-25 at 14:36 +0800, James Pang wrote:
> > we faced into a lot of lwlock:LockManager wait-events , all of these
> queries are "select ..." ,
> > there are other several session are doing DML, insert/update/delete on
> same table.
> > Did these DML transactions holding "transactionid" and "tuple" lock
> blocking "select" on lwlock:LockManager ?
>
> This is not about waiting for a lock. Rather, it means that very many
> sessions are trying
> to take a lock. They have to grab the lock manager to take a lock, and
> the competition for
> that resource is the bottleneck.
>
> It is hard to say what is the root cause without further analysis, but
> very often the
> cause is that you have too many connections to the database. Using an
> effective connection
> pool *might* solve that particular problem.
>
> Yours,
> Laurenz Albe
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: lwlock:LockManager wait_events
@ 2024-10-25 12:09 SAMEER KUMAR <[email protected]>
parent: James Pang <[email protected]>
1 sibling, 1 reply; 5+ messages in thread
From: SAMEER KUMAR @ 2024-10-25 12:09 UTC (permalink / raw)
To: James Pang <[email protected]>; +Cc: [email protected]
On Fri, 25 Oct 2024, 14:36 James Pang, <[email protected]> wrote:
> experts,
> we faced into a lot of lwlock:LockManager wait-events , all of these
> queries are "select ..." , there are other several session are doing DML,
> insert/update/delete on same table. Did these DML transactions holding
> "transactionid" and "tuple" lock blocking "select" on lwlock:LockManager ?
>
Which version of PostgreSQL?
Are these all same query or variant of same query (with different
parameters)?
What's the schema (you can redact column names) for the tables involved in
the select query which is blocked/waiting on lock_manager?
> Thanks,
>
> James
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: lwlock:LockManager wait_events
@ 2025-02-08 19:41 kyle Hailey <[email protected]>
parent: SAMEER KUMAR <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: kyle Hailey @ 2025-02-08 19:41 UTC (permalink / raw)
To: SAMEER KUMAR <[email protected]>; +Cc: James Pang <[email protected]>; [email protected]
late to the game here but this might be of interest:
https://ardentperf.com/2024/03/03/postgres-indexes-partitioning-and-lwlocklockmanager-scalability/
On Fri, Oct 25, 2024 at 5:10 AM SAMEER KUMAR <[email protected]>
wrote:
>
>
> On Fri, 25 Oct 2024, 14:36 James Pang, <[email protected]> wrote:
>
>> experts,
>> we faced into a lot of lwlock:LockManager wait-events , all of these
>> queries are "select ..." , there are other several session are doing DML,
>> insert/update/delete on same table. Did these DML transactions holding
>> "transactionid" and "tuple" lock blocking "select" on lwlock:LockManager ?
>>
>
>
> Which version of PostgreSQL?
> Are these all same query or variant of same query (with different
> parameters)?
>
> What's the schema (you can redact column names) for the tables involved in
> the select query which is blocked/waiting on lock_manager?
>
>
>> Thanks,
>>
>> James
>>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-02-08 19:41 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-10-25 06:36 lwlock:LockManager wait_events James Pang <[email protected]>
2024-10-25 07:36 ` Laurenz Albe <[email protected]>
2024-10-25 08:37 ` Sean Massey <[email protected]>
2024-10-25 12:09 ` SAMEER KUMAR <[email protected]>
2025-02-08 19:41 ` kyle Hailey <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox