pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Problem with permanent connections from Tomcat
4+ messages / 4 participants
[nested] [flat]

* Problem with permanent connections from Tomcat
@ 2005-02-07 10:34  Daniel Rubio <drubior@tinet.org>
  0 siblings, 2 replies; 4+ messages in thread

From: Daniel Rubio @ 2005-02-07 10:34 UTC (permalink / raw)
  To: pgsql-admin

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 ... :(

-- 
********************************************************
Daniel Rubio Rodríguez
OASI (Organisme Autònom Per la Societat de la Informació)
c/ Assalt, 12
43003 - Tarragona
Tef.: 977.244.007 - Fax: 977.224.517
e-mail: drubio a oasi.org
********************************************************




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Problem with permanent connections from Tomcat
@ 2005-02-07 11:04  Tom Arthurs <tom@jobflash.com>
  parent: Daniel Rubio <drubior@tinet.org>
  1 sibling, 0 replies; 4+ messages in thread

From: Tom Arthurs @ 2005-02-07 11:04 UTC (permalink / raw)
  To: Daniel Rubio <drubior@tinet.org>; +Cc: pgsql-admin

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 ... :(
> 



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Problem with permanent connections from Tomcat
@ 2005-02-07 11:22  Soeren Laursen <sl@scrooge.dk>
  parent: Daniel Rubio <drubior@tinet.org>
  1 sibling, 0 replies; 4+ messages in thread

From: Soeren Laursen @ 2005-02-07 11:22 UTC (permalink / raw)
  To: Daniel Rubio <drubior@tinet.org>; +Cc: pgsql-admin

Hi,

Had the same problem. If they test an application and restart the 
application - not tomcat - it will keep the old connections open. A 
restart of tomcat should release all the connections from the 
developer.

For example, if the code on the one of  servlets/jsp do not reuse a 
connection the servlet/jsp will open a new connection.

Regards,

Søren
> 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 ... :(
> 
> -- 
> ********************************************************
> Daniel Rubio Rodríguez
> OASI (Organisme Autònom Per la Societat de la Informació)
> c/ Assalt, 12
> 43003 - Tarragona
> Tef.: 977.244.007 - Fax: 977.224.517
> e-mail: drubio a oasi.org
> ********************************************************
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
> 





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Problem with permanent connections from Tomcat
@ 2005-02-07 11:37  John Jensen <JRJ@ft.fo>
  0 siblings, 0 replies; 4+ messages in thread

From: John Jensen @ 2005-02-07 11:37 UTC (permalink / raw)
  To: tom@jobflash.com; drubior@tinet.org; +Cc: pgsql-admin

Hi,
An elegant solution to this problem is to instruct the Tomcat
developers
to use conection-pooling datasources. That way their Tomcat server
takes care of the connection pool and terminates idle connections
when they time out. This way Tomcat also tear down its connections
when it's shut down.

I run this setup with Tomcat 5.0.24 and it works great for me.

/John

>>> Tom Arthurs <tom@jobflash.com> 07-02-2005 11:04:16 >>>
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 ... :(
> 

---------------------------(end of
broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings




^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2005-02-07 11:37 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-07 10:34 Problem with permanent connections from Tomcat Daniel Rubio <drubior@tinet.org>
2005-02-07 11:04 ` Tom Arthurs <tom@jobflash.com>
2005-02-07 11:22 ` Soeren Laursen <sl@scrooge.dk>
2005-02-07 11:37 Re: Problem with permanent connections from Tomcat John Jensen <JRJ@ft.fo>

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