Re: Idle connections - Mailing list pgsql-general

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