Thread

1. lwlock:LockManager wait_events

   James Pang <jamespang886@gmail.com> — 2024-10-25T06:36:26Z

   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
   

2. Re: lwlock:LockManager wait_events

   Laurenz Albe <laurenz.albe@cybertec.at> — 2024-10-25T07:36:45Z

   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
   
   
   
   

3. Re: lwlock:LockManager wait_events

   Sean M <sean.f.massey@gmail.com> — 2024-10-25T08:37:25Z

   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 <laurenz.albe@cybertec.at> 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
   >
   >
   >
   

4. Re: lwlock:LockManager wait_events

   SAMEER KUMAR <sameer.kasi200x@gmail.com> — 2024-10-25T12:09:45Z

   On Fri, 25 Oct 2024, 14:36 James Pang, <jamespang886@gmail.com> 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
   >
   

5. Re: lwlock:LockManager wait_events

   kyle Hailey <kylelf@gmail.com> — 2025-02-08T19:41:18Z

   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 <sameer.kasi200x@gmail.com>
   wrote:
   
   >
   >
   > On Fri, 25 Oct 2024, 14:36 James Pang, <jamespang886@gmail.com> 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
   >>
   >