Re: Connection hike

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(dot)dba09(at)gmail(dot)com>
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(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
>>
>