You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by "DE VINZELLES, Guillaume (ext.)" <Gu...@neufcegetel.fr> on 2006/10/11 16:02:40 UTC
JDBC Pool exhaustion
Hello there,
We are running a Tomcat 5.5.17 on a Sun Solaris system (SunFire V240) with a Sun JVM 1.5, and we are facing unavoidable JDBC pool exhaustions. We are using an Oracle 9i database.
Here is the JDBC entry from our server.xml:
<Resource name="jdbc/Signup" auth="Container" type="javax.sql.DataSource"
driverClassName="oracle.jdbc.driver.OracleDriver" url="jdbc:oracle:thin:@address:port:schema"
username="user" password="pass" maxActive="120" maxIdle="10" maxWait="5000" removeAbandoned="true" removeAbandonedTimeout="60" />
The Tomcat manager shows around 1000 sessions on the server, which seems to be pertinent.
We can see with the JMXProxy that the JDBC pool connections are never recycled, e.g. the active connections number is going higher and higher and never stays at the same level, which is strange because the amount of active sessions isn't growing.
Do you have any idea about what we can do to 'force' the Tomcat server to release the unused JDBC connections?
Thanks in advance,
Best Regards,
Guillaume de Vinzelles
DSI/PFS Neuf Cegetel
Altran Technologies
guillaume.devinzelles@neufcegetel.fr
01 70 18 21 64
---------------------------------------------------------------------
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: JDBC Pool exhaustion
Posted by Pascal Alberty <pa...@albertyorban.be>.
Seems to be good except:
> finally {
> try {
> if (result != null) {
> result.close();
> result = null;
> }
> if (oCmd != null) {
> oCmd.close();
> oCmd = null;
> }
> if (myConnection != null) {
> myConnection.close();
> myConnection = null;
> }
> } catch (SQLException e) {}
> }
If there is an exception during result or oCmd closing, your
connection is never closed ...
--
Pascal Alberty
http://pascal.albertyorban.be
---------------------------------------------------------------------
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: JDBC Pool exhaustion
Posted by "DE VINZELLES, Guillaume (ext.)" <Gu...@neufcegetel.fr>.
I will consider this option, thanks !
Guillaume de Vinzelles
DSI/PFS Neuf Cegetel
Altran Technologies
guillaume.devinzelles@neufcegetel.fr
01 70 18 21 64
-----Message d'origine-----
De : Mikolaj Rydzewski [mailto:miki@ceti.pl]
Envoyé : mercredi 11 octobre 2006 16:46
À : Tomcat Users List
Objet : Re: JDBC Pool exhaustion
DE VINZELLES, Guillaume (ext.) wrote:
> finally {
> try {
> if (result != null) {
> result.close();
> result = null;
> }
> if (oCmd != null) {
> oCmd.close();
> oCmd = null;
> }
> if (myConnection != null) {
> myConnection.close();
> myConnection = null;
> }
> } catch (SQLException e) {}
> }
>
You use plain JDBC calls. Have you considered jakarta commons dbutils
here? It simplifies a lot: no more need to do nasty finally{} calls.
--
Mikolaj Rydzewski <mi...@ceti.pl>
---------------------------------------------------------------------
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: JDBC Pool exhaustion
Posted by Mikolaj Rydzewski <mi...@ceti.pl>.
DE VINZELLES, Guillaume (ext.) wrote:
> finally {
> try {
> if (result != null) {
> result.close();
> result = null;
> }
> if (oCmd != null) {
> oCmd.close();
> oCmd = null;
> }
> if (myConnection != null) {
> myConnection.close();
> myConnection = null;
> }
> } catch (SQLException e) {}
> }
>
You use plain JDBC calls. Have you considered jakarta commons dbutils
here? It simplifies a lot: no more need to do nasty finally{} calls.
--
Mikolaj Rydzewski <mi...@ceti.pl>
Re: JDBC Pool exhaustion
Posted by Pascal Alberty <pa...@albertyorban.be>.
On 10/12/06, DE VINZELLES, Guillaume (ext.)
<Gu...@neufcegetel.fr> wrote:
> Hello,
>
> One of our connections wasn't properly closed, as you said.
> You fixed the problem and now everything is running fine.
Great !
"Un grand classique" ;-)
Pascal
---------------------------------------------------------------------
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: JDBC Pool exhaustion
Posted by "DE VINZELLES, Guillaume (ext.)" <Gu...@neufcegetel.fr>.
Hello,
One of our connections wasn't properly closed, as you said.
You fixed the problem and now everything is running fine.
Thanks to all the people who answered for your help!
Guillaume de Vinzelles
DSI/PFS Neuf Cegetel
Altran Technologies
guillaume.devinzelles@neufcegetel.fr
01 70 18 21 64
-----Message d'origine-----
De : Richard DeGrande [mailto:RDegrand@co.jefferson.co.us]
Envoyé : mercredi 11 octobre 2006 17:58
À : Tomcat Users List
Objet : Re: JDBC Pool exhaustion
Martin,
Isn't dbcp connection pooling ?? I think I am missing something, I am
not clear on why you are advocating a separate pooling mechanism.
Can you elaborate on this a little ?
thanks
rick
>>> "Martin Gainty" <mg...@hotmail.com> 10/11/2006 9:15 AM >>>
If you have 1000 connections then you would be well advised to use
connection pooling
more specifically you need to separate the activities of
settingup a connection from
getting the connection
you also need to separate the activities of
freeing the connection from
connection teardown
connection setup and teardown are expensive operations and should be
used judiciously
continous connection setup and teardown will not only bog down the app
but slow the DB as it furiously tries to keep up with these resource
requests
A vert good primer for setting up connection pools is available at
http://www.webdevelopersjournal.com/columns/connection_pool.html
A word on SEH
for non oo single threaded environments you may swallow the exception
if you have deemed the execution of the statement as optional
If the statement is required then I would always raise a meaningful
exception and not swallow it
If you have a OO env such as Java you should raise the exception and
handle it by the outermost handler in a structured way (definition of
SEH)
as an aside I usually pass error_code and description
Feel free to Contact me offline for particulars of how to implement,
M-
----- Original Message -----
From: "Christopher Schultz" <ch...@christopherschultz.net>
To: "Tomcat Users List" <us...@tomcat.apache.org>
Sent: Wednesday, October 11, 2006 10:54 AM
Subject: Re: JDBC Pool exhaustion
---------------------------------------------------------------------
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: JDBC Pool exhaustion
Posted by Richard DeGrande <RD...@co.jefferson.co.us>.
Martin,
Isn't dbcp connection pooling ?? I think I am missing something, I am
not clear on why you are advocating a separate pooling mechanism.
Can you elaborate on this a little ?
thanks
rick
>>> "Martin Gainty" <mg...@hotmail.com> 10/11/2006 9:15 AM >>>
If you have 1000 connections then you would be well advised to use
connection pooling
more specifically you need to separate the activities of
settingup a connection from
getting the connection
you also need to separate the activities of
freeing the connection from
connection teardown
connection setup and teardown are expensive operations and should be
used judiciously
continous connection setup and teardown will not only bog down the app
but slow the DB as it furiously tries to keep up with these resource
requests
A vert good primer for setting up connection pools is available at
http://www.webdevelopersjournal.com/columns/connection_pool.html
A word on SEH
for non oo single threaded environments you may swallow the exception
if you have deemed the execution of the statement as optional
If the statement is required then I would always raise a meaningful
exception and not swallow it
If you have a OO env such as Java you should raise the exception and
handle it by the outermost handler in a structured way (definition of
SEH)
as an aside I usually pass error_code and description
Feel free to Contact me offline for particulars of how to implement,
M-
----- Original Message -----
From: "Christopher Schultz" <ch...@christopherschultz.net>
To: "Tomcat Users List" <us...@tomcat.apache.org>
Sent: Wednesday, October 11, 2006 10:54 AM
Subject: Re: JDBC Pool exhaustion
---------------------------------------------------------------------
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: JDBC Pool exhaustion
Posted by "DE VINZELLES, Guillaume (ext.)" <Gu...@neufcegetel.fr>.
Thanks a lot all of you!
I'll keep you posted.
Guillaume de Vinzelles
DSI/PFS Neuf Cegetel
Altran Technologies
guillaume.devinzelles@neufcegetel.fr
01 70 18 21 64
-----Message d'origine-----
De : Martin Gainty [mailto:mgainty@hotmail.com]
Envoyé : mercredi 11 octobre 2006 17:16
À : Tomcat Users List
Objet : Re: JDBC Pool exhaustion
If you have 1000 connections then you would be well advised to use connection pooling
more specifically you need to separate the activities of
settingup a connection from
getting the connection
you also need to separate the activities of
freeing the connection from
connection teardown
connection setup and teardown are expensive operations and should be used judiciously
continous connection setup and teardown will not only bog down the app but slow the DB as it furiously tries to keep up with these resource requests
A vert good primer for setting up connection pools is available at
http://www.webdevelopersjournal.com/columns/connection_pool.html
A word on SEH
for non oo single threaded environments you may swallow the exception if you have deemed the execution of the statement as optional
If the statement is required then I would always raise a meaningful exception and not swallow it
If you have a OO env such as Java you should raise the exception and handle it by the outermost handler in a structured way (definition of SEH)
as an aside I usually pass error_code and description
Feel free to Contact me offline for particulars of how to implement,
M-
----- Original Message -----
From: "Christopher Schultz" <ch...@christopherschultz.net>
To: "Tomcat Users List" <us...@tomcat.apache.org>
Sent: Wednesday, October 11, 2006 10:54 AM
Subject: Re: JDBC Pool exhaustion
---------------------------------------------------------------------
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: JDBC Pool exhaustion
Posted by Martin Gainty <mg...@hotmail.com>.
If you have 1000 connections then you would be well advised to use connection pooling
more specifically you need to separate the activities of
settingup a connection from
getting the connection
you also need to separate the activities of
freeing the connection from
connection teardown
connection setup and teardown are expensive operations and should be used judiciously
continous connection setup and teardown will not only bog down the app but slow the DB as it furiously tries to keep up with these resource requests
A vert good primer for setting up connection pools is available at
http://www.webdevelopersjournal.com/columns/connection_pool.html
A word on SEH
for non oo single threaded environments you may swallow the exception if you have deemed the execution of the statement as optional
If the statement is required then I would always raise a meaningful exception and not swallow it
If you have a OO env such as Java you should raise the exception and handle it by the outermost handler in a structured way (definition of SEH)
as an aside I usually pass error_code and description
Feel free to Contact me offline for particulars of how to implement,
M-
----- Original Message -----
From: "Christopher Schultz" <ch...@christopherschultz.net>
To: "Tomcat Users List" <us...@tomcat.apache.org>
Sent: Wednesday, October 11, 2006 10:54 AM
Subject: Re: JDBC Pool exhaustion
Re: JDBC Pool exhaustion
Posted by David Smith <dn...@cornell.edu>.
Under the covers, tomcat does use DBCP , but refractors the package name
to avoid collisions with the real DBCP package.
--David
Mikolaj Rydzewski wrote:
> Christopher Schultz wrote:
>> From your DataSource configuration, it looks like you are using the
>> Oracle driver directly. Does that have built-in pooling?
> The original problem is solved, but I'm a little confused now.
>
> Posted configuration was exactly the same like the examples on Tomcat
> website:
> http://tomcat.apache.org/tomcat-5.5-doc/jndi-datasource-examples-howto.html
>
>
> They say, that this configuration uses connection pooling (jakarta dbcp).
>
> But, reading the config for the second time I can not find any
> references to dbcp classes! Just DataSource interface and jdbc driver
> class name. So - does this configuration uses connection pooling (how
> could it be?) or are there wrong examples on Tomcat website?
>
> I'd vote for the second option - DBCP wiki shows different
> configurations for Tomcat: http://wiki.apache.org/jakarta-commons/DBCP
>
>
---------------------------------------------------------------------
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: JDBC Pool exhaustion
Posted by Mikolaj Rydzewski <mi...@ceti.pl>.
Mikolaj Rydzewski wrote:
> But, reading the config for the second time I can not find any
> references to dbcp classes! Just DataSource interface and jdbc driver
> class name. So - does this configuration uses connection pooling (how
> could it be?)
I'll answer to my question ;-)
Quoting DBCP wiki:
"Saying what class will actually create instances of the above, i.e.
give the factory. This is actually not necessary if you want
'org.apache.commons.dbcp.BasicDataSourceFactory' as that is the default
factory used by Tomcat whenever 'javax.sql.DataSource' objects should be
created."
--
Mikolaj Rydzewski <mi...@ceti.pl>
Re: JDBC Pool exhaustion
Posted by Mikolaj Rydzewski <mi...@ceti.pl>.
Christopher Schultz wrote:
> From your DataSource configuration, it looks like you are using the
> Oracle driver directly. Does that have built-in pooling?
The original problem is solved, but I'm a little confused now.
Posted configuration was exactly the same like the examples on Tomcat
website:
http://tomcat.apache.org/tomcat-5.5-doc/jndi-datasource-examples-howto.html
They say, that this configuration uses connection pooling (jakarta dbcp).
But, reading the config for the second time I can not find any
references to dbcp classes! Just DataSource interface and jdbc driver
class name. So - does this configuration uses connection pooling (how
could it be?) or are there wrong examples on Tomcat website?
I'd vote for the second option - DBCP wiki shows different
configurations for Tomcat: http://wiki.apache.org/jakarta-commons/DBCP
--
Mikolaj Rydzewski <mi...@ceti.pl>
RE: JDBC Pool exhaustion
Posted by "DE VINZELLES, Guillaume (ext.)" <Gu...@neufcegetel.fr>.
Wow, thanks for all those advices, we're going to clean up our code following your guidelines. I'll also check the Oracle driver capabilities. Hope it will fix our problem.
Thanks again!
Guillaume de Vinzelles
DSI/PFS Neuf Cegetel
Altran Technologies
guillaume.devinzelles@neufcegetel.fr
01 70 18 21 64
-----Message d'origine-----
De : Christopher Schultz [mailto:chris@christopherschultz.net]
Envoyé : mercredi 11 octobre 2006 16:55
À : Tomcat Users List
Objet : Re: JDBC Pool exhaustion
Guillaume,
> For Christopher and Alberty, here is an example of how we connect to the database.
> I'm quite sure we are using the JDBC pool.
>From your DataSource configuration, it looks like you are using the
Oracle driver directly. Does that have built-in pooling? If not, it
looks like you might be creating a new connection every time. Sorry, I'm
totally ignorant of the Oracle driver capabilities.
> Connection myConnection = null;
> CallableStatement oCmd = null;
> ResultSet result = null;
>
> try {
> /* SNIP */
> }
> catch (Exception e) {
> logger.error(e.getMessage(), e);
> throw new DepotGarantieNotFoundException("Erreur SQL", e);
> }
> finally {
> try {
> if (result != null) {
> result.close();
> result = null;
> }
> if (oCmd != null) {
> oCmd.close();
> oCmd = null;
> }
> if (myConnection != null) {
> myConnection.close();
> myConnection = null;
> }
> } catch (SQLException e) {}
> }
Although this is probably okay, it's not air tight. You should change
your finally clause to this:
finally {
if (result != null) try { result.close(); }
catch (SQLException e) { logger.error(":(", e); }
if (oCmd != null) try { oCmd.close(); }
catch (SQLException e) { logger.error(":(", e); }
if (myConnection != null) try { myConnection.close(); }
catch (SQLException e) { logger.error(":(", e); }
}
This will attempt to close each resource separately, so if there is some
kind of exception causing your ResultSet to fail to close, you still
have a chance at closing the statement and connection.
Also, I saw that you were swallowing the SQLException at the end of the
finally block. This is a mistake, as you'll never know when you have a
problem! You should always log exceptions if they occur. You never
know... you might be getting exceptions all the time which would lead
you to your connection leak, but never know it because they are being
ignored.
All of the database code in my projects goes into several service
classes to isolate it from the rest of the code. All of the service
classes extend from a BaseService which has the following method:
protected void close(ResultSet rs, PerparedStatement ps,
Connection conn)
{
if (rs != null) try { rs.close(); }
catch (SQLException e) { logger.error(":(", e); }
if (ps != null) try { ps.close(); }
catch (SQLException e) { logger.error(":(", e); }
if (conn != null) try { conn.close(); }
catch (SQLException e) { logger.error(":(", e); }
}
This helps me by putting all of this code together in one place (in case
I needed to change it for whatever reason), and making the methods that
use JDBC a little cleaner because the finally block becomes:
finally
{
super.close(rs, ps, conn);
}
I'm not sure if this will help you too much, but it's an idea that I
have always used.
On a final note, you should be careful when using pooled connections and
transactions. If you don't handle rollbacks properly, you can (at least
with the Jakarta Commons DBCP) end up committing transactions that
should have been rolled back (yikes!).
You need to make sure that you catch /everything/ that can occur, like this:
catch (SQLException) (obvious)
catch (anything in the throws clause of your method)
catch (java.lang.Error)
catch (java.lang.RuntimeException)
This doesn't have anything to do with connection leaks, really, but I
figured I'd mention it.
-chris
---------------------------------------------------------------------
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: JDBC Pool exhaustion
Posted by Christopher Schultz <ch...@christopherschultz.net>.
Guillaume,
> For Christopher and Alberty, here is an example of how we connect to the database.
> I'm quite sure we are using the JDBC pool.
From your DataSource configuration, it looks like you are using the
Oracle driver directly. Does that have built-in pooling? If not, it
looks like you might be creating a new connection every time. Sorry, I'm
totally ignorant of the Oracle driver capabilities.
> Connection myConnection = null;
> CallableStatement oCmd = null;
> ResultSet result = null;
>
> try {
> /* SNIP */
> }
> catch (Exception e) {
> logger.error(e.getMessage(), e);
> throw new DepotGarantieNotFoundException("Erreur SQL", e);
> }
> finally {
> try {
> if (result != null) {
> result.close();
> result = null;
> }
> if (oCmd != null) {
> oCmd.close();
> oCmd = null;
> }
> if (myConnection != null) {
> myConnection.close();
> myConnection = null;
> }
> } catch (SQLException e) {}
> }
Although this is probably okay, it's not air tight. You should change
your finally clause to this:
finally {
if (result != null) try { result.close(); }
catch (SQLException e) { logger.error(":(", e); }
if (oCmd != null) try { oCmd.close(); }
catch (SQLException e) { logger.error(":(", e); }
if (myConnection != null) try { myConnection.close(); }
catch (SQLException e) { logger.error(":(", e); }
}
This will attempt to close each resource separately, so if there is some
kind of exception causing your ResultSet to fail to close, you still
have a chance at closing the statement and connection.
Also, I saw that you were swallowing the SQLException at the end of the
finally block. This is a mistake, as you'll never know when you have a
problem! You should always log exceptions if they occur. You never
know... you might be getting exceptions all the time which would lead
you to your connection leak, but never know it because they are being
ignored.
All of the database code in my projects goes into several service
classes to isolate it from the rest of the code. All of the service
classes extend from a BaseService which has the following method:
protected void close(ResultSet rs, PerparedStatement ps,
Connection conn)
{
if (rs != null) try { rs.close(); }
catch (SQLException e) { logger.error(":(", e); }
if (ps != null) try { ps.close(); }
catch (SQLException e) { logger.error(":(", e); }
if (conn != null) try { conn.close(); }
catch (SQLException e) { logger.error(":(", e); }
}
This helps me by putting all of this code together in one place (in case
I needed to change it for whatever reason), and making the methods that
use JDBC a little cleaner because the finally block becomes:
finally
{
super.close(rs, ps, conn);
}
I'm not sure if this will help you too much, but it's an idea that I
have always used.
On a final note, you should be careful when using pooled connections and
transactions. If you don't handle rollbacks properly, you can (at least
with the Jakarta Commons DBCP) end up committing transactions that
should have been rolled back (yikes!).
You need to make sure that you catch /everything/ that can occur, like this:
catch (SQLException) (obvious)
catch (anything in the throws clause of your method)
catch (java.lang.Error)
catch (java.lang.RuntimeException)
This doesn't have anything to do with connection leaks, really, but I
figured I'd mention it.
-chris
RE: JDBC Pool exhaustion
Posted by "DE VINZELLES, Guillaume (ext.)" <Gu...@neufcegetel.fr>.
For Christopher and Alberty, here is an example of how we connect to the database. I'm quite sure we are using the JDBC pool.
My next step is to try to log the Connection objects life, and I hope to find some memory leaks!
I got to say that we were using the same application with a Tomcat 4 and a JVM 1.4.08 without any problem.
Thanks for your previous answers!
DataSource ds;
String datasource = "jdbc/Signup";
try {
Context ic = new InitialContext();
Context envCtx = (Context) ic.lookup("java:/comp/env");
ds = (DataSource) envCtx.lookup(datasource);
}
catch (NamingException ne) {
logger.error("Error looking up datasource jdbc/Signup : " + e.getMessage());
}
Connection myConnection = null;
CallableStatement oCmd = null;
ResultSet result = null;
try {
myConnection = ds.getConnection();
oCmd = myConnection.prepareCall("{call STOREDPROC(?, ?)}");
oCmd.registerOutParameter(1, OracleTypes.CURSOR);
oCmd.setString(2, critere);
oCmd.execute();
result = (ResultSet)oCmd.getObject(1);
/* SNIP */
}
catch (Exception e) {
logger.error(e.getMessage(), e);
throw new DepotGarantieNotFoundException("Erreur SQL", e);
}
finally {
try {
if (result != null) {
result.close();
result = null;
}
if (oCmd != null) {
oCmd.close();
oCmd = null;
}
if (myConnection != null) {
myConnection.close();
myConnection = null;
}
} catch (SQLException e) {}
}
Guillaume de Vinzelles
DSI/PFS Neuf Cegetel
Altran Technologies
guillaume.devinzelles@neufcegetel.fr
01 70 18 21 64
-----Message d'origine-----
De : Christopher Schultz [mailto:chris@christopherschultz.net]
Envoyé : mercredi 11 octobre 2006 16:20
À : Tomcat Users List
Objet : Re: JDBC Pool exhaustion
Guillaume,
> <Resource name="jdbc/Signup" auth="Container"
> type="javax.sql.DataSource"
> driverClassName="oracle.jdbc.driver.OracleDriver"
>
> The Tomcat manager shows around 1000 sessions on the server, which
> seems to be pertinent.
What is your connection allocation strategy? Meaning: do you allocate a
Connection object to each session for the life of the session, or do you
use some kind of pooling mechanism and only fetch Connection objects
when you need to execute a query?
If you associate a Connection with each session, I would strongly
recommend that you use pooling as described above.
If you are already using pooling (which it looks like you /are/ given
your configuration), then you might have a connection leak in one or
more places.
How are you actually getting and releasing your connections?
> We can see with the JMXProxy that the JDBC pool connections are never
> recycled, e.g. the active connections number is going higher and
> higher and never stays at the same level, which is strange because
> the amount of active sessions isn't growing.
Does your connection count grow larger than 150 (your maxActive number)?
Perhaps you really do need that number of connections if you have 1000
sessions.
> Do you have any idea about what we can do to 'force' the Tomcat
> server to release the unused JDBC connections?
Tomcat isn't in charge of your connections... the Oracle driver is.
You'd have to look at how they are being used.
-chris
---------------------------------------------------------------------
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: JDBC Pool exhaustion
Posted by Christopher Schultz <ch...@christopherschultz.net>.
Guillaume,
> <Resource name="jdbc/Signup" auth="Container"
> type="javax.sql.DataSource"
> driverClassName="oracle.jdbc.driver.OracleDriver"
>
> The Tomcat manager shows around 1000 sessions on the server, which
> seems to be pertinent.
What is your connection allocation strategy? Meaning: do you allocate a
Connection object to each session for the life of the session, or do you
use some kind of pooling mechanism and only fetch Connection objects
when you need to execute a query?
If you associate a Connection with each session, I would strongly
recommend that you use pooling as described above.
If you are already using pooling (which it looks like you /are/ given
your configuration), then you might have a connection leak in one or
more places.
How are you actually getting and releasing your connections?
> We can see with the JMXProxy that the JDBC pool connections are never
> recycled, e.g. the active connections number is going higher and
> higher and never stays at the same level, which is strange because
> the amount of active sessions isn't growing.
Does your connection count grow larger than 150 (your maxActive number)?
Perhaps you really do need that number of connections if you have 1000
sessions.
> Do you have any idea about what we can do to 'force' the Tomcat
> server to release the unused JDBC connections?
Tomcat isn't in charge of your connections... the Oracle driver is.
You'd have to look at how they are being used.
-chris
Re: JDBC Pool exhaustion
Posted by Martin Gainty <mg...@hotmail.com>.
Guillaume-
Use Connection Pool
an excellent tutorial available at
http://www.webdevelopersjournal.com/columns/connection_pool.html
config max connnections to suit your requirement
M-
This e-mail communication and any attachments may contain confidential and privileged information for the use of the
designated recipients named above. If you are not the intended recipient, you are hereby notified that you have received
this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its
contents
----- Original Message -----
From: "DE VINZELLES, Guillaume (ext.)" <Gu...@neufcegetel.fr>
To: <us...@tomcat.apache.org>
Sent: Wednesday, October 11, 2006 10:02 AM
Subject: JDBC Pool exhaustion
Hello there,
We are running a Tomcat 5.5.17 on a Sun Solaris system (SunFire V240) with a Sun JVM 1.5, and we are facing unavoidable JDBC pool exhaustions. We are using an Oracle 9i database.
Here is the JDBC entry from our server.xml:
<Resource name="jdbc/Signup" auth="Container" type="javax.sql.DataSource"
driverClassName="oracle.jdbc.driver.OracleDriver" url="jdbc:oracle:thin:@address:port:schema"
username="user" password="pass" maxActive="120" maxIdle="10" maxWait="5000" removeAbandoned="true" removeAbandonedTimeout="60" />
The Tomcat manager shows around 1000 sessions on the server, which seems to be pertinent.
We can see with the JMXProxy that the JDBC pool connections are never recycled, e.g. the active connections number is going higher and higher and never stays at the same level, which is strange because the amount of active sessions isn't growing.
Do you have any idea about what we can do to 'force' the Tomcat server to release the unused JDBC connections?
Thanks in advance,
Best Regards,
Guillaume de Vinzelles
DSI/PFS Neuf Cegetel
Altran Technologies
guillaume.devinzelles@neufcegetel.fr
01 70 18 21 64
---------------------------------------------------------------------
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: JDBC Pool exhaustion
Posted by Pascal Alberty <pa...@albertyorban.be>.
Stupid questions (sorry):
- Are you sure that you are using the tomcat connection pool ?
- have you dump (by logging or by a simple system.out.println) your
connection objets to see if you are reusing the same or a new one ?
Good luck
On 10/11/06, DE VINZELLES, Guillaume (ext.)
<Gu...@neufcegetel.fr> wrote:
> We've checked it two weeks ago, but we got to be sure, so we are doing it again today (and tomorrow, I expect).
>
> Thanks for the advice.
>
> Guillaume de Vinzelles
> DSI/PFS Neuf Cegetel
> Altran Technologies
>
> guillaume.devinzelles@neufcegetel.fr
>
> 01 70 18 21 64
> -----Message d'origine-----
> De: alberty.pascal@gmail.com [mailto:alberty.pascal@gmail.com] De la part de Pascal Alberty
> Envoyé: mercredi 11 octobre 2006 16:10
> À: Tomcat Users List
> Objet: Re: JDBC Pool exhaustion
>
> Check your code to unclosed connections (and statments) first !
>
> On 10/11/06, DE VINZELLES, Guillaume (ext.)
> <Gu...@neufcegetel.fr> wrote:
> > Hello there,
> >
> > We are running a Tomcat 5.5.17 on a Sun Solaris system (SunFire V240) with a Sun JVM 1.5, and we are facing unavoidable JDBC pool exhaustions. We are using an Oracle 9i database.
> >
> > Here is the JDBC entry from our server.xml:
> >
> > <Resource name="jdbc/Signup" auth="Container" type="javax.sql.DataSource"
> > driverClassName="oracle.jdbc.driver.OracleDriver" url="jdbc:oracle:thin:@address:port:schema"
> > username="user" password="pass" maxActive="120" maxIdle="10" maxWait="5000" removeAbandoned="true" removeAbandonedTimeout="60" />
> >
> > The Tomcat manager shows around 1000 sessions on the server, which seems to be pertinent.
> >
> > We can see with the JMXProxy that the JDBC pool connections are never recycled, e.g. the active connections number is going higher and higher and never stays at the same level, which is strange because the amount of active sessions isn't growing.
> >
> > Do you have any idea about what we can do to 'force' the Tomcat server to release the unused JDBC connections?
> >
> > Thanks in advance,
> >
> > Best Regards,
> >
> > Guillaume de Vinzelles
> > DSI/PFS Neuf Cegetel
> > Altran Technologies
> >
> > guillaume.devinzelles@neufcegetel.fr
> >
> > 01 70 18 21 64
> >
> >
> > ---------------------------------------------------------------------
> > 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
> >
> >
>
>
> --
> Pascal Alberty
> http://pascal.albertyorban.be
>
> ---------------------------------------------------------------------
> 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
>
>
--
Pascal Alberty
http://pascal.albertyorban.be
---------------------------------------------------------------------
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: JDBC Pool exhaustion
Posted by "DE VINZELLES, Guillaume (ext.)" <Gu...@neufcegetel.fr>.
We've checked it two weeks ago, but we got to be sure, so we are doing it again today (and tomorrow, I expect).
Thanks for the advice.
Guillaume de Vinzelles
DSI/PFS Neuf Cegetel
Altran Technologies
guillaume.devinzelles@neufcegetel.fr
01 70 18 21 64
-----Message d'origine-----
De : alberty.pascal@gmail.com [mailto:alberty.pascal@gmail.com] De la part de Pascal Alberty
Envoyé : mercredi 11 octobre 2006 16:10
À : Tomcat Users List
Objet : Re: JDBC Pool exhaustion
Check your code to unclosed connections (and statments) first !
On 10/11/06, DE VINZELLES, Guillaume (ext.)
<Gu...@neufcegetel.fr> wrote:
> Hello there,
>
> We are running a Tomcat 5.5.17 on a Sun Solaris system (SunFire V240) with a Sun JVM 1.5, and we are facing unavoidable JDBC pool exhaustions. We are using an Oracle 9i database.
>
> Here is the JDBC entry from our server.xml:
>
> <Resource name="jdbc/Signup" auth="Container" type="javax.sql.DataSource"
> driverClassName="oracle.jdbc.driver.OracleDriver" url="jdbc:oracle:thin:@address:port:schema"
> username="user" password="pass" maxActive="120" maxIdle="10" maxWait="5000" removeAbandoned="true" removeAbandonedTimeout="60" />
>
> The Tomcat manager shows around 1000 sessions on the server, which seems to be pertinent.
>
> We can see with the JMXProxy that the JDBC pool connections are never recycled, e.g. the active connections number is going higher and higher and never stays at the same level, which is strange because the amount of active sessions isn't growing.
>
> Do you have any idea about what we can do to 'force' the Tomcat server to release the unused JDBC connections?
>
> Thanks in advance,
>
> Best Regards,
>
> Guillaume de Vinzelles
> DSI/PFS Neuf Cegetel
> Altran Technologies
>
> guillaume.devinzelles@neufcegetel.fr
>
> 01 70 18 21 64
>
>
> ---------------------------------------------------------------------
> 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
>
>
--
Pascal Alberty
http://pascal.albertyorban.be
---------------------------------------------------------------------
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: JDBC Pool exhaustion
Posted by Pascal Alberty <pa...@albertyorban.be>.
Check your code to unclosed connections (and statments) first !
On 10/11/06, DE VINZELLES, Guillaume (ext.)
<Gu...@neufcegetel.fr> wrote:
> Hello there,
>
> We are running a Tomcat 5.5.17 on a Sun Solaris system (SunFire V240) with a Sun JVM 1.5, and we are facing unavoidable JDBC pool exhaustions. We are using an Oracle 9i database.
>
> Here is the JDBC entry from our server.xml:
>
> <Resource name="jdbc/Signup" auth="Container" type="javax.sql.DataSource"
> driverClassName="oracle.jdbc.driver.OracleDriver" url="jdbc:oracle:thin:@address:port:schema"
> username="user" password="pass" maxActive="120" maxIdle="10" maxWait="5000" removeAbandoned="true" removeAbandonedTimeout="60" />
>
> The Tomcat manager shows around 1000 sessions on the server, which seems to be pertinent.
>
> We can see with the JMXProxy that the JDBC pool connections are never recycled, e.g. the active connections number is going higher and higher and never stays at the same level, which is strange because the amount of active sessions isn't growing.
>
> Do you have any idea about what we can do to 'force' the Tomcat server to release the unused JDBC connections?
>
> Thanks in advance,
>
> Best Regards,
>
> Guillaume de Vinzelles
> DSI/PFS Neuf Cegetel
> Altran Technologies
>
> guillaume.devinzelles@neufcegetel.fr
>
> 01 70 18 21 64
>
>
> ---------------------------------------------------------------------
> 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
>
>
--
Pascal Alberty
http://pascal.albertyorban.be
---------------------------------------------------------------------
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: JDBC Pool exhaustion
Posted by Davide Romanini <d....@cineca.it>.
Il giorno mer, 11/10/2006 alle 16.02 +0200, DE VINZELLES, Guillaume
(ext.) ha scritto:
> Hello there,
>
> We are running a Tomcat 5.5.17 on a Sun Solaris system (SunFire V240) with a Sun JVM 1.5, and we are facing unavoidable JDBC pool exhaustions. We are using an Oracle 9i database.
>
> Here is the JDBC entry from our server.xml:
>
> <Resource name="jdbc/Signup" auth="Container" type="javax.sql.DataSource"
> driverClassName="oracle.jdbc.driver.OracleDriver" url="jdbc:oracle:thin:@address:port:schema"
> username="user" password="pass" maxActive="120" maxIdle="10" maxWait="5000" removeAbandoned="true" removeAbandonedTimeout="60" />
>
> The Tomcat manager shows around 1000 sessions on the server, which seems to be pertinent.
>
> We can see with the JMXProxy that the JDBC pool connections are never recycled, e.g. the active connections number is going higher and higher and never stays at the same level, which is strange because the amount of active sessions isn't growing.
>
From my understanding of the DBCP docs I see that the removeAbandoned
mechanism is triggered only when numIdle < 2 && ( numActive > maxActive
- 3 ). So I think that if some application code doesn't release a
connection you *could* see the numActive grow until maxActive but you
*shouldn't* have a pool exahustion (that force you to manually close()
the connection pool or restart Tomcat) because when you reach the limit
the pool will check if some active connection is eligible for removal.
But I'm not sure of this... the docs are not so clear...
Recently I've experienced a pool exhaustion problem with Oracle JDBC
thin driver 10.2.0.1.0. The problem arised when for some unexpected
network problem all idle connections was resetted. At the time I made a
getConnection() the (unusable) connection became active and then... no
more released by the pool (still the application throwed an Exception
and returned, cleaning up in finally blocks).
I solved using the validationQuery and testOnBorrow=true options of
DBCP.
> Do you have any idea about what we can do to 'force' the Tomcat server to release the unused JDBC connections?
Via JMX you can do a close() on the pool that will close all active and
idle connections.
Ciao,
Davide Romanini