pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedLocks analyze
7+ messages / 3 participants
[nested] [flat]
* Locks analyze
@ 2023-10-12 23:46 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Rajesh Kumar @ 2023-10-12 23:46 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi team
How do I analyze locks and resolve it. Is there only one query that helps?
This lock issue leading to high connections it seems.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Locks analyze
@ 2023-10-13 02:21 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Scott Ribe @ 2023-10-13 02:21 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
> On Oct 12, 2023, at 5:46 PM, Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
>
> How do I analyze locks and resolve it. Is there only one query that helps? This lock issue leading to high connections it seems.
What lock issue? What do you mean leading to high connections?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Locks analyze
@ 2023-10-13 06:02 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Laurenz Albe @ 2023-10-13 06:02 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Fri, 2023-10-13 at 05:16 +0530, Rajesh Kumar wrote:
> How do I analyze locks and resolve it. Is there only one query that helps?
> This lock issue leading to high connections it seems.
Once you have identified a session that is blocked, get its process ID
from pg_stat_activity and SELECT pg_blocking_pids(12345);
with that process number as argument.
Then you get all the other sessions that block that session.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Locks analyze
@ 2023-10-13 09:39 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 7+ messages in thread
From: Rajesh Kumar @ 2023-10-13 09:39 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
How do I identify a session that is blocked?
Can blocking sessions increase idle sessions?
I see something like waitdurarion in pg_locks..is that helpful anyway?
I am often facing idle connection spike. That is why I am checking all
these.
On Fri, 13 Oct, 2023, 11:32 AM Laurenz Albe, <laurenz.albe@cybertec.at>
wrote:
> On Fri, 2023-10-13 at 05:16 +0530, Rajesh Kumar wrote:
> > How do I analyze locks and resolve it. Is there only one query that
> helps?
> > This lock issue leading to high connections it seems.
>
> Once you have identified a session that is blocked, get its process ID
> from pg_stat_activity and SELECT pg_blocking_pids(12345);
> with that process number as argument.
>
> Then you get all the other sessions that block that session.
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Locks analyze
@ 2023-10-13 11:59 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Laurenz Albe @ 2023-10-13 11:59 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Fri, 2023-10-13 at 15:09 +0530, Rajesh Kumar wrote:
> How do I identify a session that is blocked?
See "wait_event_type" in "pg_stat_activity" (or see "pg_locks").
> Can blocking sessions increase idle sessions?
Only within certain limits, unless your applicaton is doing something wrong.
> I see something like waitdurarion in pg_locks..is that helpful anyway?
Yes, that means that the session has been waiting for the lock for a while.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Locks analyze
@ 2023-10-13 13:14 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Scott Ribe @ 2023-10-13 13:14 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
> On Oct 13, 2023, at 3:39 AM, Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
>
> I am often facing idle connection spike. That is why I am checking all these.
Do total sessions increase along with idle sessions? Or does the total remain the same, but more are idle?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Locks analyze
@ 2023-10-13 14:57 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 0 replies; 7+ messages in thread
From: Rajesh Kumar @ 2023-10-13 14:57 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
95% if times idle sessions spikes thus lead to spike in total
sessions..active or idle in transaction increases less than 5 coñnectiins
On Fri, 13 Oct, 2023, 6:44 PM Scott Ribe, <scott_ribe@elevated-dev.com>
wrote:
> > On Oct 13, 2023, at 3:39 AM, Rajesh Kumar <rajeshkumar.dba09@gmail.com>
> wrote:
> >
> > I am often facing idle connection spike. That is why I am checking all
> these.
>
> Do total sessions increase along with idle sessions? Or does the total
> remain the same, but more are idle?
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2023-10-13 14:57 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-12 23:46 Locks analyze Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-10-13 02:21 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-10-13 06:02 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-10-13 09:39 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-10-13 11:59 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-10-13 13:14 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-10-13 14:57 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox