Re: PostgreSQL hanging on new connections? - Mailing list pgsql-general

From Dusan Misic
Subject Re: PostgreSQL hanging on new connections?
Date
Msg-id AANLkTinPGHDY+jX8KYfAzgP=u0_eEV6LQZPFCyXayO1f@mail.gmail.com
Whole thread Raw
In response to Re: PostgreSQL hanging on new connections?  (Ben Chobot <bench@silentmedia.com>)
Responses Re: PostgreSQL hanging on new connections?
List pgsql-general


On Mon, Nov 29, 2010 at 10:03 PM, Ben Chobot <bench@silentmedia.com> wrote:
On Nov 29, 2010, at 12:57 PM, Vick Khera wrote:

> On Mon, Nov 29, 2010 at 1:23 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> hubert depesz lubaczewski <depesz@depesz.com> writes:
>>> straced postmaster when the problem was happening, and I was opening new
>>> connections. strace looks like this:
>>> [ backend hangs on semop immediately after reading global/pg_database ]
>>
>> It looks like something had exclusive lock on the database that new
>> connections wanted to connect to.  AFAICS the only action in 8.3 that
>> would do that would be a DROP DATABASE or RENAME DATABASE.  What was
>> that other session doing?
>
> Every once in a while when I connect to my big DB it feels like it
> stalls the connection for a few seconds.  I have no idea what causes
> it, but it does feel like it has to do with the load.  The load is not
> all that high relative to what my box can handle.  It doesn't happen
> often enough for me to track it down, though.  It instinct is that it
> is waiting on a lock, but clearly there is no rename/drop happening on
> my main db else i'd be out of business :-)  I too run 8.3 as primary
> right now... testing 9.0 for deployment soon-ish.

We're having similar issues on 8.4.[245]... occasionally psql takes anywhere from a few to several dozen seconds to connect. I've been unsuccessfully trying to blame spikes in the OS run queue (we desperately need some connection pooling) but if it's something to do with locks I can't see in pg_locks, that would explain why I haven't been able to figure out what's going on yet....
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

This is normal. PostgreSQL needs to create new server process to handle your requested connection. 

Then it needs to allocate resources to that new connection. It initializes shared memory for that connection. That is the stall you are mentioning. 

If you are time sensitive on connection times, then use connection pooling software like pg-bouncer or pg-pool2. Pooling will keep your server instance "hot" and those lags won't happen.

pgsql-general by date:

Previous
From: John R Pierce
Date:
Subject: Re: Pgadmin for Fedora 14?
Next
From: AI Rumman
Date:
Subject: Full Text Partial Match at begining