pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Arthurs <tom@jobflash.com>
To: Daniel Rubio <drubior@tinet.org>
Cc: pgsql-admin@postgresql.org
Subject: Re: Problem with permanent connections from Tomcat
Date: Mon, 07 Feb 2005 03:04:16 -0800
Message-ID: <42074B30.1050400@jobflash.com> (raw)
In-Reply-To: <42074420.80502@tinet.org>
References: <42074420.80502@tinet.org>

Hi, Daniel

I run a similar environment and I've seen this problem a lot.

It's a java code problem -- your developers need to run a profiler to see where the resource leaks are -- they need to make sure that
everytime they open a connection, they also close it as well -- and don't forget to to close the statements.  We use borland's optimizeit
which is not free (by a long shot) but it's probably worth the investment -- you can't promote code to production that is leaking
resources like this.  (Are you using connection pooling on tomcat?)

If they all shutdown their tomcat instances, it should release the connection, don't know how popular that suggestion would be though.

If you have the berkely version of ps (on solaris I use /usr/ucb/ps -auxww | grep post) you can see where each of these connections is
coming from and some indication of what it's doing, and you can do a kill -15 on each one of them -- which is a more graceful recovery.

For a quick and dirty fix -- do pg_ctl stop -m i
That's an immediate shutdown -- though it's still a "dirty" shutdown and the db must recover on startup, so make sure your backups and
txlogs are good -- *don't* run out of room for the txlogs.

Daniel Rubio wrote:
> Hi all
> 
> Our developers are making some kind of DoS on my postgres installation.
> They use Tomcat to develop in java and they have an instance on every 
> computer (only 3).
> 
> It seems that tomcat makes permanent connections to the configured 
> database, but they have this number limited to 5, and max_connections in 
> postgresql.conf is 40, and no more connections are made to the database 
> because is a development environment.
> 
> The number of connections starts increasing and when arrives to 40 ( ps 
> -ef | grep postm | wc -l ) the server doesn't accept more connections 
> but it seems that neither finish these that are open, and no one can 
> acces to the database (here is the DoS I say).
> 
> Here is what the log says
> 
> 2005-02-07 10:13:26 LOG:  connection received: host=127.0.0.1 port=55834
> 2005-02-07 10:13:26 LOG:  connection authorized: user=test database=test
> 2005-02-07 10:13:26 FATAL:  connection limit exceeded for non-superusers
> 
> I try to stop the postmaster with pg_ctl but it says that postmaster 
> doesn't stops and I have to make kill -9 for the processes to stop :(
> 
> What could I make to solve this?
> 
> I've tried with:
> 
> statement_timeout = 100000
> 
> but nothing happens ... :(
> 



view thread (4+ messages)  latest in thread

Message-ID: <42074B30.1050400@jobflash.com>
Permalink:  ../42074B30.1050400@jobflash.com/
Also on:    postgresql.org/message-id/42074B30.1050400@jobflash.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: tom@jobflash.com, drubior@tinet.org
  Subject: Re: Problem with permanent connections from Tomcat
  In-Reply-To: <42074B30.1050400@jobflash.com>
  References: <42074420.80502@tinet.org> <42074B30.1050400@jobflash.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox