Thread: Idle connections

Hi,
I have some issue where
SELECT * from pg_stat_activity WHERE current_query='<IDLE>';
returns too many rows .

What is the meaning of IDLE ?
I suspect that the server is overloaded with idle connections, but i don't know why .
Sometimes i am forced to do
 SELECT pg_terminate_backend(procpid) from pg_stat_activity where current_query='<IDLE>';


Queries comes from 2 web nodes .

Any ideas why is this happening ?

On 06/10/2010 14:26, Georgi Ivanov wrote:
> Hi,
> I have some issue where
> SELECT * from pg_stat_activity WHERE current_query='<IDLE>';
> returns too many rows .

What do you mean by "too many"?

> What is the meaning of IDLE ?

It means a client is keeping a connection open, but not executing any
queries.

> I suspect that the server is overloaded with idle connections, but i
> don't know why .
> Sometimes i am forced to do
>   SELECT pg_terminate_backend(procpid) from pg_stat_activity where
> current_query='<IDLE>';
>
>
> Queries comes from 2 web nodes .
>
> Any ideas why is this happening ?

Are you using any kind of connection pooler?

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On Wed, Oct 6, 2010 at 3:33 PM, Raymond O'Donnell <rod@iol.ie> wrote:
> On 06/10/2010 14:26, Georgi Ivanov wrote:
>>
>> Hi,
>> I have some issue where
>> SELECT * from pg_stat_activity WHERE current_query='<IDLE>';
>> returns too many rows .
>> Any ideas why is this happening ?
>
> Are you using any kind of connection pooler?

Maybe persistent DB connections on the webserver?

Regards,
Mathieu

On 06/10/2010 20:26, Mathieu De Zutter wrote:
> On Wed, Oct 6, 2010 at 3:33 PM, Raymond O'Donnell<rod@iol.ie>  wrote:
>> On 06/10/2010 14:26, Georgi Ivanov wrote:
>>>
>>> Hi,
>>> I have some issue where
>>> SELECT * from pg_stat_activity WHERE current_query='<IDLE>';
>>> returns too many rows .
>>> Any ideas why is this happening ?
>>
>> Are you using any kind of connection pooler?
>
> Maybe persistent DB connections on the webserver?

I was wondering about that too... I sent an email about it that doesn't
seem to have made it to the list. A little googling shows that PDO does
support persistent connections, so that could be it.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

Just something I have observed recently and I don't know how accurate my
findings are. They might relate to your 'overloaded with idle connection'
issues.


If you get to many persistent or otherwise idle connections you might be
inducing a "thundering herd" condition. Seems like on our servers we hit a
wall with just having a lot of persistent connections from various apps. I
don't really understand everything involved here but....

It seems that a high number of idle connections processes will sleep on the
same semaphore. When this becomes run-able all the idle connections that
were sleeping on it become run-able at the same time. This means hundreds
(in our case) of idle processes do some work even though they are idle at
the same time. This eats all available cpu time for a few seconds then
everything goes back to sleep.


(well that might be over simplified and I might not be accurately
interpreting what I am seeing but that is the current working theory based
on what I have seen and feedback I gotten from people who know the guts of
the Linux kernel far better than I)


I was planning on making a new thread about this but with all the info I am
trying to collect but figured I would toss it out the list now see if anyone
else has seen something similar.


The answer for us will be to move to a dedicated connection pooler but this
will take a while before we can regression test our code using a pooler in
the mix. We don't need hundreds of open connections all the time, so better
connection management should give us some more head room before we have to
figure out the next scaling hurdle.

..: Mark



-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Raymond O'Donnell
Sent: Wednesday, October 06, 2010 1:33 PM
To: Mathieu De Zutter
Cc: Georgi Ivanov; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Idle connections

On 06/10/2010 20:26, Mathieu De Zutter wrote:
> On Wed, Oct 6, 2010 at 3:33 PM, Raymond O'Donnell<rod@iol.ie>  wrote:
>> On 06/10/2010 14:26, Georgi Ivanov wrote:
>>>
>>> Hi,
>>> I have some issue where
>>> SELECT * from pg_stat_activity WHERE current_query='<IDLE>';
>>> returns too many rows .
>>> Any ideas why is this happening ?
>>
>> Are you using any kind of connection pooler?
>
> Maybe persistent DB connections on the webserver?

I was wondering about that too... I sent an email about it that doesn't
seem to have made it to the list. A little googling shows that PDO does
support persistent connections, so that could be it.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

"mark" <dvlhntr@gmail.com> writes:
> If you get to many persistent or otherwise idle connections you might be
> inducing a "thundering herd" condition. Seems like on our servers we hit a
> wall with just having a lot of persistent connections from various apps. I
> don't really understand everything involved here but....

> It seems that a high number of idle connections processes will sleep on the
> same semaphore. When this becomes run-able all the idle connections that
> were sleeping on it become run-able at the same time. This means hundreds
> (in our case) of idle processes do some work even though they are idle at
> the same time. This eats all available cpu time for a few seconds then
> everything goes back to sleep.

What you're describing sounds a lot like the known issue with sinval
queue overflow response ... but that was fixed in 8.4.  What version
is this?

            regards, tom lane

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, October 06, 2010 11:14 PM
To: mark
Cc: rod@iol.ie; 'Mathieu De Zutter'; 'Georgi Ivanov';
pgsql-general@postgresql.org
Subject: Re: [GENERAL] Idle connections

>What you're describing sounds a lot like the known issue with sinval
>queue overflow response ... but that was fixed in 8.4.  What version
>is this?
>
>            regards, tom lane



These are 8.3.7 instances, so that could be the answer. Thanks for letting
me know!


9.0 for us is probably a q1 thing...

............Ah the joys of being so far behind.

..: Mark

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Wednesday, October 06, 2010 11:14 PM
> To: mark
> Cc: rod@iol.ie; 'Mathieu De Zutter'; 'Georgi Ivanov'; pgsql-
> general@postgresql.org
> Subject: Re: [GENERAL] Idle connections
>
> "mark" <dvlhntr@gmail.com> writes:
> > If you get to many persistent or otherwise idle connections you might
> be
> > inducing a "thundering herd" condition. Seems like on our servers we
> hit a
> > wall with just having a lot of persistent connections from various
> apps. I
> > don't really understand everything involved here but....
>
> > It seems that a high number of idle connections processes will sleep
> on the
> > same semaphore. When this becomes run-able all the idle connections
> that
> > were sleeping on it become run-able at the same time. This means
> hundreds
> > (in our case) of idle processes do some work even though they are
> idle at
> > the same time. This eats all available cpu time for a few seconds
> then
> > everything goes back to sleep.
>
> What you're describing sounds a lot like the known issue with sinval
> queue overflow response ... but that was fixed in 8.4.  What version
> is this?
>


I Wanted to follow up on this, we upgraded to PG 9.0 (from 8.3) and it
appears this greatly improved our average CPU load. I am not seeing the
extremely large load spikes I used to.


Awesome job - thank you tom and everyone else on the core team.

mark



>             regards, tom lane