You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by Enrico Sasdelli <en...@xinet.it> on 2008/04/28 14:14:35 UTC
How to close idle connections
Hi all,
I use tomcat-6.0.14 and postgresql-8.1 with JNDI connection pool, after a
while, my web application (written in Java/JSP), creates many database
connections and postgres processes.
But all of these connections are signed as "Idle", I want to close this idle
connections to avoid getting to get many postrgres processes.
So, is there a way to tells tomcat to close an Idle connection after some
time of inactivity?
At the state of the art, I close the connections, statements and resultsets
in the finally {} blocks, I use JNDI context resource to get the connection
and this is my context.xml:
<?xml version="1.0" encoding="UTF-8"?>
<Context path="/ManodoriSite">
<Resource name="jdbc/mydb" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="20" maxWait="10000"
removeAbandoned="true"
removeAbandonedTimeout="60"
logAbandoned="true"
username="myUser" password="myPassword"
driverClassName="org.postgresql.Driver"
url="jdbc:postgresql://databaseHost/database"/>
</Context>
I've already read tons of documentation, from the official site, google and
searched through the mailing list archive but I haven't found answer.
Many thanks!
Enrico
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
R: R: How to close idle connections
Posted by Enrico Sasdelli <en...@xinet.it>.
Christopher Schultz wrote:
>| Enrico Sadelli wrote:
>| If so, I will set maxIdle="2" and everyone will be happy (especially me :)
>
>That's it! But I suspect that this setting will reduce your performance,
>especially if you have an application with lots of db access and
>sporadic traffic patterns.
>
>- -chris
Same suspect here :)
Okay, I will report your suggestions, thanks again!
Enrico
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: R: How to close idle connections
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Enrico,
Enrico Sasdelli wrote:
| No, I have at most 20 idle connections, that's goes right, but my boss
want
| [fewer] idle connections to avoid to overload the database server. So
there
| isn't way to close an idle connection to remove the relative process?
No, there is a way.
| If so, I will set maxIdle="2" and everyone will be happy (especially me :)
That's it! But I suspect that this setting will reduce your performance,
especially if you have an application with lots of db access and
sporadic traffic patterns.
- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkgV2JEACgkQ9CaO5/Lv0PCtQQCgqyQlrPYeAOvY3gc5YzXUuejQ
8XQAoMRpK2AYlv2Uh45Or7Y4sI7MGYSt
=yPPT
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: R: How to close idle connections
Posted by David Fisher <df...@jmlafferty.com>.
On Apr 28, 2008, at 9:59 AM, Alan Chaney wrote:
>
>
> David Smith wrote:
>>>
>>>
>>> No, I have at most 20 idle connections, that's goes right, but my
>>> boss want
>>> less idle connections to avoid to overload the database server. So
>>> there
>>> isn't way to close an idle connection to remove the relative
>>> process?
>>> If so, I will set maxIdle="2" and everyone will be happy
>>> (expecially me :)
>>>
>> I'll bet money the added, idle postgres connections are just
>> sleeping while they wait for work. Given idle connections
>> contribute virtually no additional load, don't see his argument
>> that idle connections contribute to a database overload. If the
>> minor increase in overhead due to sleeping threads actually
>> overloads the database, you need to let your boss know the server
>> hardware is way too frail for production use and needs to be
>> upgraded.
>> --David
> Agreed. I use postgres and with ps alx | grep postgres each
> connection shows as idle (when its not being used!) and consumes
> about 140k bytes of memory. On a modern server that can hardly be
> considered as any use at all!
The database server will typically have a maximum number of
connections. For example our Oracle server has been set at 220 for
sometime. We do well with a connection pool of 8 per tomcat server.
And with a database server the web side is not the only customer.
maxidle="2" is certainly too small, and maxidle="20" too large.
Enrico I would negotiate with your boss and dba about what that value
should be. They need to understand the trade-off. The dba may be able
to increase the number of connections on that end.
Regards,
Dave
>
>
>
> Alan Chaney
>
>
>
>
>> Enrico Sasdelli wrote:
>>> Thanks for reply,
>>>
>>>
>>>> I see you have maxIdle="20". Do you have more that 20
>>>> connections sitting idle? Is this really that much of a
>>>> concern? As long as the connections are released (closed) and
>>>> returned to the pool, I don't see the problem.
>>>>
>>>
>>> No, I have at most 20 idle connections, that's goes right, but my
>>> boss want
>>> less idle connections to avoid to overload the database server. So
>>> there
>>> isn't way to close an idle connection to remove the relative
>>> process?
>>> If so, I will set maxIdle="2" and everyone will be happy
>>> (expecially me :)
>>>
>>>
>>> Thank you,
>>> Enrico
>>>
>>>
>>>
>>>> The entire idea of a connection pool is the system keeps around a
>>>> set of idle connections for borrow. When you code get's a
>>>> connection, it comes from the pool of idle connections and
>>>> returned when you close the
>>>
>>>> connection. This speeds up your application by elliminating the
>>>> overhead of contacting the server and constructing a new
>>>> connection whenever you call for one. The pool just grabs a pre-
>>>> made one off the shelf for you.
>>>>
>>>
>>>
>>>> --David
>>>>
>>>
>>> ---------------------------------------------------------------------
>>> To start a new topic, e-mail: users@tomcat.apache.org
>>> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
>>> For additional commands, e-mail: users-help@tomcat.apache.org
>>>
>>>
>>>
>>>
>> ---------------------------------------------------------------------
>> To start a new topic, e-mail: users@tomcat.apache.org
>> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
>> For additional commands, e-mail: users-help@tomcat.apache.org
>> !DSPAM:4815ddca16721381456296!
>
> ---------------------------------------------------------------------
> To start a new topic, e-mail: users@tomcat.apache.org
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: R: How to close idle connections
Posted by Andrew Miehs <an...@2sheds.de>.
On 28/04/2008, at 4:59 PM, Alan Chaney wrote:
> David Smith wrote:
>>>
>>>
>>> No, I have at most 20 idle connections, that's goes right, but my
>>> boss want
>>> less idle connections to avoid to overload the database server. So
>>> there
>>> isn't way to close an idle connection to remove the relative
>>> process?
>>> If so, I will set maxIdle="2" and everyone will be happy
>>> (expecially me :)
>>>
>> I'll bet money the added, idle postgres connections are just
>> sleeping while they wait for work. Given idle connections
>> contribute virtually no additional load, don't see his argument
>> that idle connections contribute to a database overload. If the
>> minor increase in overhead due to sleeping threads actually
>> overloads the database, you need to let your boss know the server
>> hardware is way too frail for production use and needs to be
>> upgraded.
>> --David
> Agreed. I use postgres and with ps alx | grep postgres each
> connection shows as idle (when its not being used!) and consumes
> about 140k bytes of memory. On a modern server that can hardly be
> considered as any use at all!
This sounds as if it is out of a Dilbert comic.
Paint it red, that way it will go faster.
Actually, reducing it will if anything make it slower due to increased
latency when the application does actually need the additional
connections.
That being said and done, setting number of idle procs to 1000 isn't
such a good idea either. ^^
Andrew
Re: R: How to close idle connections
Posted by Alan Chaney <al...@compulsivecreative.com>.
David Smith wrote:
>>
>>
>> No, I have at most 20 idle connections, that's goes right, but my boss
>> want
>> less idle connections to avoid to overload the database server. So there
>> isn't way to close an idle connection to remove the relative process?
>> If so, I will set maxIdle="2" and everyone will be happy (expecially
>> me :)
>>
>>
>
>
> I'll bet money the added, idle postgres connections are just sleeping
> while they wait for work. Given idle connections contribute virtually
> no additional load, don't see his argument that idle connections
> contribute to a database overload. If the minor increase in overhead
> due to sleeping threads actually overloads the database, you need to let
> your boss know the server hardware is way too frail for production use
> and needs to be upgraded.
> --David
>
Agreed. I use postgres and with ps alx | grep postgres each connection
shows as idle (when its not being used!) and consumes about 140k bytes
of memory. On a modern server that can hardly be considered as any use
at all!
Alan Chaney
> Enrico Sasdelli wrote:
>
>> Thanks for reply,
>>
>>
>>
>>> I see you have maxIdle="20". Do you have more that 20 connections
>>> sitting idle? Is this really that much of a concern? As long as the
>>> connections are released (closed) and returned to the pool, I don't
>>> see the problem.
>>>
>>
>> No, I have at most 20 idle connections, that's goes right, but my boss
>> want
>> less idle connections to avoid to overload the database server. So there
>> isn't way to close an idle connection to remove the relative process?
>> If so, I will set maxIdle="2" and everyone will be happy (expecially
>> me :)
>>
>>
>> Thank you,
>> Enrico
>>
>>
>>
>>
>>> The entire idea of a connection pool is the system keeps around a set
>>> of idle connections for borrow. When you code get's a connection, it
>>> comes
>>> from the pool of idle connections and returned when you close the
>>
>>
>>> connection. This speeds up your application by elliminating the
>>> overhead of contacting the server and constructing a new connection
>>> whenever you call for one. The pool just grabs a pre-made one off
>>> the shelf for you.
>>>
>>
>>
>>
>>> --David
>>>
>>
>> ---------------------------------------------------------------------
>> To start a new topic, e-mail: users@tomcat.apache.org
>> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
>> For additional commands, e-mail: users-help@tomcat.apache.org
>>
>>
>>
>>
>>
>
>
> ---------------------------------------------------------------------
> To start a new topic, e-mail: users@tomcat.apache.org
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
>
> !DSPAM:4815ddca16721381456296!
>
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: R: How to close idle connections
Posted by Alan Chaney <al...@compulsivecreative.com>.
Christopher Schultz wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> David,
>
> David Smith wrote:
> | I'll bet money the added, idle postgres connections are just sleeping
> | while they wait for work. Given idle connections contribute virtually
> | no additional load, don't see his argument that idle connections
> | contribute to a database overload. If the minor increase in overhead
> | due to sleeping threads actually overloads the database, you need to let
> | your boss know the server hardware is way too frail for production use
> | and needs to be upgraded.
>
> It could be a memory issue, and perhaps with lots of connections from
> different machines (maybe a clustered application and a single database
> instance)... lots of connections == lots of memory allocated on the
> server, even when the connections are idle.
>
> - -chris
>
Chris,
but as in many resource allocation issues, its not the quiescent count
that's important. If the OP has a *maximum* requirement for N x 100
connections then its unlikely that the system could be so constrained as
to worry about the limited number of idle connections. In other words,
if you've allocated enough resource for the maximum expected load, then
any less load should be easily accomadated. If you haven't you've
probably got a problem...
Regards
Alan
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.9 (MingW32)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iEYEARECAAYFAkgWGZcACgkQ9CaO5/Lv0PCVVgCgiQHs4I8xJfbupkGXUHEBLgci
> qmQAn3yNwIsnO4jzF/GobTZyzXp8fe17
> =ma/c
> -----END PGP SIGNATURE-----
>
> ---------------------------------------------------------------------
> To start a new topic, e-mail: users@tomcat.apache.org
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
>
> !DSPAM:48161a6671411738514357!
>
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: R: How to close idle connections
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
David,
David Smith wrote:
| I'll bet money the added, idle postgres connections are just sleeping
| while they wait for work. Given idle connections contribute virtually
| no additional load, don't see his argument that idle connections
| contribute to a database overload. If the minor increase in overhead
| due to sleeping threads actually overloads the database, you need to let
| your boss know the server hardware is way too frail for production use
| and needs to be upgraded.
It could be a memory issue, and perhaps with lots of connections from
different machines (maybe a clustered application and a single database
instance)... lots of connections == lots of memory allocated on the
server, even when the connections are idle.
- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkgWGZcACgkQ9CaO5/Lv0PCVVgCgiQHs4I8xJfbupkGXUHEBLgci
qmQAn3yNwIsnO4jzF/GobTZyzXp8fe17
=ma/c
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
R: R: How to close idle connections
Posted by Enrico Sasdelli <en...@xinet.it>.
David Smith wrote:
>I'll bet money the added, idle postgres connections are just sleeping
>while they wait for work. Given idle connections contribute virtually
>no additional load, don't see his argument that idle connections
>contribute to a database overload. If the minor increase in overhead
>due to sleeping threads actually overloads the database, you need to let
>your boss know the server hardware is way too frail for production use
>and needs to be upgraded.
Ok, that's the trade-off: max 10 idle connections and no more fear :)
Thanks again to all
Bye,
Enrico
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: R: How to close idle connections
Posted by David Smith <dn...@cornell.edu>.
>
>
>No, I have at most 20 idle connections, that's goes right, but my boss want
>less idle connections to avoid to overload the database server. So there
>isn't way to close an idle connection to remove the relative process?
>If so, I will set maxIdle="2" and everyone will be happy (expecially me :)
>
>
I'll bet money the added, idle postgres connections are just sleeping
while they wait for work. Given idle connections contribute virtually
no additional load, don't see his argument that idle connections
contribute to a database overload. If the minor increase in overhead
due to sleeping threads actually overloads the database, you need to let
your boss know the server hardware is way too frail for production use
and needs to be upgraded.
--David
Enrico Sasdelli wrote:
>Thanks for reply,
>
>
>
>>I see you have maxIdle="20". Do you have more that 20 connections
>>sitting idle? Is this really that much of a concern? As long as the
>>connections are released (closed) and returned to the pool, I don't see
>>the problem.
>>
>>
>
>No, I have at most 20 idle connections, that's goes right, but my boss want
>less idle connections to avoid to overload the database server. So there
>isn't way to close an idle connection to remove the relative process?
>If so, I will set maxIdle="2" and everyone will be happy (expecially me :)
>
>
>Thank you,
>
>Enrico
>
>
>
>
>>The entire idea of a connection pool is the system keeps around a set of
>>idle connections for borrow. When you code get's a connection, it comes
>>
>>
>>from the pool of idle connections and returned when you close the
>
>
>>connection. This speeds up your application by elliminating the
>>overhead of contacting the server and constructing a new connection
>>whenever you call for one. The pool just grabs a pre-made one off the
>>shelf for you.
>>
>>
>
>
>
>>--David
>>
>>
>
>---------------------------------------------------------------------
>To start a new topic, e-mail: users@tomcat.apache.org
>To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
>For additional commands, e-mail: users-help@tomcat.apache.org
>
>
>
>
>
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
R: How to close idle connections
Posted by Enrico Sasdelli <en...@xinet.it>.
Thanks for reply,
>I see you have maxIdle="20". Do you have more that 20 connections
>sitting idle? Is this really that much of a concern? As long as the
>connections are released (closed) and returned to the pool, I don't see
>the problem.
No, I have at most 20 idle connections, that's goes right, but my boss want
less idle connections to avoid to overload the database server. So there
isn't way to close an idle connection to remove the relative process?
If so, I will set maxIdle="2" and everyone will be happy (expecially me :)
Thank you,
Enrico
>The entire idea of a connection pool is the system keeps around a set of
>idle connections for borrow. When you code get's a connection, it comes
>from the pool of idle connections and returned when you close the
>connection. This speeds up your application by elliminating the
>overhead of contacting the server and constructing a new connection
>whenever you call for one. The pool just grabs a pre-made one off the
>shelf for you.
>--David
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
--
No virus found in this incoming message.
Checked by AVG.
Version: 7.5.524 / Virus Database: 269.23.5/1400 - Release Date: 27/04/2008
9.39
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: How to close idle connections
Posted by David Smith <dn...@cornell.edu>.
I see you have maxIdle="20". Do you have more that 20 connections
sitting idle? Is this really that much of a concern? As long as the
connections are released (closed) and returned to the pool, I don't see
the problem.
The entire idea of a connection pool is the system keeps around a set of
idle connections for borrow. When you code get's a connection, it comes
from the pool of idle connections and returned when you close the
connection. This speeds up your application by elliminating the
overhead of contacting the server and constructing a new connection
whenever you call for one. The pool just grabs a pre-made one off the
shelf for you.
--David
Enrico Sasdelli wrote:
>Hi all,
>I use tomcat-6.0.14 and postgresql-8.1 with JNDI connection pool, after a
>while, my web application (written in Java/JSP), creates many database
>connections and postgres processes.
>But all of these connections are signed as "Idle", I want to close this idle
>connections to avoid getting to get many postrgres processes.
>So, is there a way to tells tomcat to close an Idle connection after some
>time of inactivity?
>
>At the state of the art, I close the connections, statements and resultsets
>in the finally {} blocks, I use JNDI context resource to get the connection
>and this is my context.xml:
>
><?xml version="1.0" encoding="UTF-8"?>
><Context path="/ManodoriSite">
><Resource name="jdbc/mydb" auth="Container" type="javax.sql.DataSource"
> maxActive="100" maxIdle="20" maxWait="10000"
> removeAbandoned="true"
> removeAbandonedTimeout="60"
> logAbandoned="true"
> username="myUser" password="myPassword"
>driverClassName="org.postgresql.Driver"
> url="jdbc:postgresql://databaseHost/database"/>
></Context>
>
>I've already read tons of documentation, from the official site, google and
>searched through the mailing list archive but I haven't found answer.
>
>Many thanks!
>
>Enrico
>
>
>---------------------------------------------------------------------
>To start a new topic, e-mail: users@tomcat.apache.org
>To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
>For additional commands, e-mail: users-help@tomcat.apache.org
>
>
>
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org