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, 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
