Re: Ran out of connections - Mailing list pgsql-sql

You probably didn't need to reboot - I suspect you could have probably 
restarted PostgreSQL and Apache (quick version) or killed the extra postgres 
processes.

I suspect you need to look carefully at your code and method of connecting 
(ie. are you using mod-perl, plain old cgi perl, PHP or what). A problem with 
Apache 1.x is that of connection pooling/persistence. If you don't use 
persistent connections you suffer the overhead of opening and closing the 
database connection as required by your web process. You can use mod-perl and 
keep your connections open but if your pool of web processes that have open 
connections exceeds the number of connections allowed by PostgreSQL you will 
have problems (pooling of database connections between processes is 
problematic so each Apache process has its own connection).

Be sure that there isn't a bug causing a cgi to abort leaving a stranded 
connection. I don't have experience with local unix socket connections where 
the client has died but when I have windoze users reboot when they had an 
open connection it will wait till the TCP/IP connection times out (frequently 
for an hour) before the PostgreSQL connection is closed. If the timeout is 
similar for local unix sockets then a failure in the cgi could leave open 
connections and you will run out quickly.

If you are doing lots of database backed work you may want to check out 
AOLserver (http://www.aolserver.com/). It has a multi-threaded architecture 
featuring connection pooling and persistence "out of the box." Oh, it's 
free/open-source as well.

Of course you can also get pooling/persistence with enterprise Java solutions 
such as JBoss (www.jboss.org).

Cheers,
Steve


On Wednesday 04 December 2002 2:08 pm, Mike Diehl wrote:
> Hi all.
>
> Twice this week, I've come to work to find my Postgres server out of
> connections... effectively freezing my web server.
>
> Today, before I rebooted the entire system, I did a ps -auxw and kept the
> file to study.  I didn't find too many clients running.  But I did find a
> whole LOT of postgres processes running, idle.  BTW, one of the postgres
> processes was doing a vacuum analyze.  I'm running 7.2.
>
> Can anyone tell me how to fix this?  The out put of the ps command can be
> seen at http://dominion.dyndns.org/~mdiehl/ps.txt
>
> Thanx in advance,