pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedConnection hike
4+ messages / 2 participants
[nested] [flat]
* Connection hike
@ 2024-03-13 12:44 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Rajesh Kumar @ 2024-03-13 12:44 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi all,
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?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Connection hike
@ 2024-03-13 12:56 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Laurenz Albe @ 2024-03-13 12:56 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Connection hike
@ 2024-03-13 12:59 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 4+ messages in thread
From: Rajesh Kumar @ 2024-03-13 12:59 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Connection hike
@ 2024-03-13 13:02 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Rajesh Kumar @ 2024-03-13 13:02 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
>>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-03-13 13:02 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-03-13 12:44 Connection hike Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-03-13 12:56 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-03-13 12:59 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-03-13 13:02 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox