Re: Connection hike

I use one single query that gets all, there were times connections are
high, but no blocking query or long running query 90% like that only.

Is this query enough

select ((total - idle) - idle_in_txn) as active
, total
, idle
, idle_in_txn
, (select coalesce(extract(epoch from (max(clock_timestamp() -
state_change))),0) from pg_catalog.pg_stat_activity where state = 'idle in
transaction') as max_idle_in_txn_time
, (select coalesce(extract(epoch from (max(clock_timestamp() -
query_start))),0) from pg_catalog.pg_stat_activity where backend_type =
'client backend' and state <> 'idle' ) as max_query_time
, (select coalesce(extract(epoch from (max(clock_timestamp() -
query_start))),0) from pg_catalog.pg_stat_activity where backend_type =
'client backend' and wait_event_type = 'Lock' ) as max_blocked_query_time
, max_connections
from (
select count(*) as total
, coalesce(sum(case when state = 'idle' then 1 else
0 end),0) as idle
, coalesce(sum(case when state = 'idle in
transaction' then 1 else 0 end),0) as idle_in_txn from
pg_catalog.pg_stat_activity) x
join (select setting::float AS max_connections FROM pg_settings
WHERE name = 'max_connections') xx ON (true);

On Wed, 13 Mar 2024, 18:26 Laurenz Albe, <laurenz(dot)albe(at)cybertec(dot)at> wrote:

> On Wed, 2024-03-13 at 18:14 +0530, Rajesh Kumar wrote:
> > There were connections hike and I used to check blocking sessions and
> long
> > running queries using pg_stat_activity.
> >
> > My manager gave me less rating during performance meeting, because he
> says
> > even a kid can do this. He said my job is to analyse the cause of
> connection hike.
> >
> > Any idea how to analyse?
>
> I assume that you mean "hang", not "hike".
>
> You can diagnose locked sessions fron the "wait_event" and
> "wait_event_type" in
> "pg_stat_activity". For the process ID of a blocked session, you can call
> the
> "pg_blocking_pids()" function to get the process IDs of the sessions
> blocking it.
>
> You can look at "pg_locks" to see on which object the lock is (a lock
> waiting
> for a transaction is waiting for a row lock).
>
> That's about all you can analyze in the database. To figure out which
> statements
> took the locks that block others, you'd have to debug the application.
>
> Yours,
> Laurenz Albe
>