Обсуждение: Locks analyze
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.
> 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 connectionsit seems. What lock issue? What do you mean leading to high connections?
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
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
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
> 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?
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?