[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-admin
Subject: Re: Connection hike
From: Rajesh Kumar <rajeshkumar.dba09 () gmail ! com>
Date: 2024-03-13 13:14:39
Message-ID: CAJk5AtZH3Av7_hFp+zX8DaT2EPH492-ut+Eo2zqc-HNJFsYPzw () mail ! gmail ! com
[Download RAW message or body]
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@gmail.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.albe@cybertec.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
>>
>
[Attachment #3 (text/html)]
<div dir="auto">I have an other query, that fetches which user is taking high \
connections. I will also tell which user to app team..<div dir="auto"><br></div><div \
dir="auto">But manager is still asking reason for sudden high number of connections \
</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, 13 \
Mar 2024, 18:29 Rajesh Kumar, <<a \
href="mailto:rajeshkumar.dba09@gmail.com">rajeshkumar.dba09@gmail.com</a>> \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 \
.8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="auto">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.<div dir="auto"><br></div><div dir="auto">Is \
this query enough<br><div dir="auto"><br></div><div dir="auto"><div dir="auto"> \
<p>select ((total - idle) - idle_in_txn) as active<br> , total<br> \
, idle<br> , idle_in_txn<br> , (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<br> , (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<br> , (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<br> , max_connections<br> from \
(<br> select count(*) as total<br> \
, coalesce(sum(case when state = 'idle' then 1 else 0 end),0) as idle<br> \
, 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<br> join (select \
setting::float AS max_connections FROM pg_settings WHERE name = \
'max_connections') xx ON (true);</p></div></div></div></div><br><div \
class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, 13 Mar 2024, 18:26 \
Laurenz Albe, <<a href="mailto:laurenz.albe@cybertec.at" target="_blank" \
rel="noreferrer">laurenz.albe@cybertec.at</a>> wrote:<br></div><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex">On Wed, 2024-03-13 at 18:14 +0530, Rajesh Kumar wrote:<br> \
> There were connections hike and I used to check blocking sessions and long<br> \
> running queries using pg_stat_activity.<br> > <br>
> My manager gave me less rating during performance meeting, because he says<br>
> even a kid can do this. He said my job is to analyse the cause of connection \
hike. <br> > <br>
> Any idea how to analyse?<br>
<br>
I assume that you mean "hang", not "hike".<br>
<br>
You can diagnose locked sessions fron the "wait_event" and \
"wait_event_type" in<br> "pg_stat_activity". For the process ID \
of a blocked session, you can call the<br> "pg_blocking_pids()" function to \
get the process IDs of the sessions blocking it.<br> <br>
You can look at "pg_locks" to see on which object the lock is (a lock \
waiting<br> for a transaction is waiting for a row lock).<br>
<br>
That's about all you can analyze in the database. To figure out which \
statements<br> took the locks that block others, you'd have to debug the \
application.<br> <br>
Yours,<br>
Laurenz Albe<br>
</blockquote></div>
</blockquote></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic