Re: Ran out of connections

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,