Re: Connection hike

[Rajesh Kumar <rajeshkumar.dba09@xxxxxxxxx>]

I have an other query, that fetches which user is taking high connections. I will also tell which user to app team..

But manager is still asking reason for sudden high number of connections 

On Wed, 13 Mar 2024, 18:29 Rajesh Kumar, <rajeshkumar.dba09@xxxxxxxxx> wrote:

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.albe@xxxxxxxxxxx> 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