Re: Connection Pool setup.
Hello,
I'd like to know how to implement connection pooling in my
application.
This is what I had done so far. Pls let me know what I need to change
to use the pooling mechanism.
1. I created a singleton class: DatabaseOperations.
2. It has an Connection instance: conn.
3. During initialization, it gets connection as follows:
DataSource ds = (DataSource)
ctx.lookup(java:comp/env/jdbc/mySQLDatabase);
conn = ds.getConnection();
4. I have several public operations method (e.g selectOperations(...),
insertOperations(...)).
5. Let's say I'd like to perform a select statement. Inside my
selectOperations(...):
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(...);
process the result
In my finally block, I have:
rslt.close();
stmt.close();
Questions:
1. Do I need to code any different to enable the connection pooling?
2. When I called ds.getConnection(), does it opens up several
connections for pooling
or just one connection?
3. Do I need to close conn every time as well? Since conn is an
instance,
how does it gets connection everytime an operation method is
called? (insert, select, update).
That's all I have for now. Thanks,
--Kawthar
Confidential information may be contained in this e-mail and any files transmitted
with it ('Message'). If you are not the addressee indicated in this Message (or
responsible for delivery of this Message to such person), you are hereby notified that
any dissemination, distribution, printing or copying of this Message or any part
thereof is strictly prohibited. In such a case, you should delete this Message
immediately and advise the sender by return e-mail. Opinions, conclusions and other
information in this Message that do not relate to the official business of Maxis shall
be understood as neither given nor endorsed by Maxis.
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
RE: Connection Pool setup.
Hi,
Hello,
I'd like to know how to implement connection pooling in my
application.
This is what I had done so far. Pls let me know what I need to change
to use the pooling mechanism.
1. I created a singleton class: DatabaseOperations.
2. It has an Connection instance: conn.
3. During initialization, it gets connection as follows:
DataSource ds = (DataSource)
ctx.lookup(java:comp/env/jdbc/mySQLDatabase);
conn = ds.getConnection();
4. I have several public operations method (e.g selectOperations(...),
insertOperations(...)).
5. Let's say I'd like to perform a select statement. Inside my
selectOperations(...):
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(...);
process the result
In my finally block, I have:
rslt.close();
stmt.close();
Questions:
1. Do I need to code any different to enable the connection pooling?
NO
2. When I called ds.getConnection(), does it opens up several
connections for pooling
or just one connection?
Depends on how you have it configured. maxActive and maxIdle settings for the
resource.
3. Do I need to close conn every time as well? Since conn is an
instance,
how does it gets connection everytime an operation method is
called? (insert, select, update).
Yes, you have to close the connection each time, which returns the connection
to the pool for
other processes to use.
I have the following methods in my generic DataBase Operations class.
static public Connection getConnection() throws SQLException {
Connection conn = null;
try{
conn = ds.getConnection();
}
catch (Exception e) {
e.printStackTrace();
}
return conn;
}
static public void releaseConnection(Connection con){
try{
if (con != null)
con.close();
}
catch (Exception e) {
e.printStackTrace();
}
}
which get and close my connections for me. So, when I am in the selectOperations(..) I
call the getConnetion()
to get a connection and in the finally block I call the releaseConnection() method.
Hope this helps.
Kal.
CONFIDENTIALITY NOTE: All e-mail sent to or from this address will be received by the
Waterfield Group corporate e-mail system and is subject to archival, monitoring,
and/or review by someone other than the recipient or the sender.
This e-mail and any of its attachments may contain proprietary information, which is
privileged and confidential. This e-mail is intended solely for the use of the
individual or entity to which it is addressed. If you are not the intended recipient
of this e-mail, you are hereby notified that any dissemination, distribution, copying,
or action taken in relation to the contents of and attachments to this e-mail is
strictly prohibited and may be unlawful. If you have received this e-mail in error,
please notify the sender immediately and permanently delete the original and any copy
of this e-mail and any printout. Thank you.
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
RE: Connection Pool setup.
On Fri, 2004-04-02 at 13:47, Kal Govindu wrote:
I'd like to know how to implement connection pooling in my
application.
This is what I had done so far. Pls let me know what I need to change
you don't have to write any database pooling functions, it comes built
in!
http://jakarta.apache.org//tomcat/tomcat-5.0-doc/jndi-datasource-examples-howto.html
basically, add some stuff to the server.xml file (and restart)
then in your app (note paranoid cleaning up of database handles!):
Connection con = null;
Statement stmt = null;
ResultSet rst = null;
try
{
Context ctx = new InitialContext();
if(ctx == null )
{
System.err.println(No Context\n);
}
else
{
DataSource ds =
(DataSource)ctx.lookup(java:comp/env/jdbc/MySQLDB);
if (ds != null)
{
con = ds.getConnection();
stmt = con.createStatement();
String sql = select ;
rst = stmt.executeQuery(sqlStmt.toString());
while rst.next()
{
blah blah
}
rst.close(); rst = null;
stmt.close(); stmt = null;
}
}
}
catch (java.sql.SQLException sqle)
{
if (rst != null)
try { rst.close(); rst = null; } catch (SQLException
sqle3) { }
if (stmt != null)
try { stmt.close(); stmt = null;} catch (SQLException
sqle2) { }
}
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
RE: Connection Pool setup.
Hi Kal,
Thank you... your getConnection() and releaseConnection() also very
helpful.
Will implement and test them...
--Kawthar
[EMAIL PROTECTED] 02/04/2004 08:47:43 PM
Hi,
Hello,
I'd like to know how to implement connection pooling in my
application.
This is what I had done so far. Pls let me know what I need to change
to use the pooling mechanism.
1. I created a singleton class: DatabaseOperations.
2. It has an Connection instance: conn.
3. During initialization, it gets connection as follows:
DataSource ds = (DataSource)
ctx.lookup(java:comp/env/jdbc/mySQLDatabase);
conn = ds.getConnection();
4. I have several public operations method (e.g
selectOperations(...),
insertOperations(...)).
5. Let's say I'd like to perform a select statement. Inside my
selectOperations(...):
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(...);
process the result
In my finally block, I have:
rslt.close();
stmt.close();
Questions:
1. Do I need to code any different to enable the connection pooling?
NO
2. When I called ds.getConnection(), does it opens up several
connections for pooling
or just one connection?
Depends on how you have it configured. maxActive and maxIdle
settings for the resource.
3. Do I need to close conn every time as well? Since conn is an
instance,
how does it gets connection everytime an operation method is
called? (insert, select, update).
Yes, you have to close the connection each time, which returns
the connection to the pool for
other processes to use.
I have the following methods in my generic DataBase Operations class.
static public Connection getConnection() throws SQLException {
Connection conn = null;
try{
conn = ds.getConnection();
}
catch (Exception e) {
e.printStackTrace();
}
return conn;
}
static public void releaseConnection(Connection con){
try{
if (con != null)
con.close();
}
catch (Exception e) {
e.printStackTrace();
}
}
which get and close my connections for me. So, when I am in the
selectOperations(..) I call the getConnetion()
to get a connection and in the finally block I call the
releaseConnection() method.
Hope this helps.
Kal.
CONFIDENTIALITY NOTE: All e-mail sent to or from this address will be
received by the Waterfield Group corporate e-mail system and is subject
to archival, monitoring, and/or review by someone other than the
recipient or the sender.
This e-mail and any of its attachments may contain proprietary
information, which is privileged and confidential. This e-mail is
intended solely for the use of the individual or entity to which it is
addressed. If you are not the intended recipient of this e-mail, you
are hereby notified that any dissemination, distribution, copying, or
action taken in relation to the contents of and attachments to this
e-mail is strictly prohibited and may be unlawful. If you have received
this e-mail in error, please notify the sender immediately and
permanently delete the original and any copy of this e-mail and any
printout. Thank you.
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Confidential information may be contained in this e-mail and any files transmitted
with it ('Message'). If you are not the addressee indicated in this Message (or
responsible for delivery of this Message to such person), you are hereby notified that
any dissemination, distribution, printing or copying of this Message or any part
thereof is strictly prohibited. In such a case, you should delete this Message
immediately and advise the sender by return e-mail. Opinions, conclusions and other
information in this Message that do not relate to the official business of Maxis shall
be understood as neither given nor endorsed by Maxis.
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: Connection Pool setup.
Hi Gordon, IMHO, all of them should be off for production. Your app is supposed to not leak any connections when it goes into production. And if you are not 100% sure of that, you'd better also have the log so that you can find and correct it. So, again IMHO, these parameters mostly make sense when used together. Antonio Fiol Gordon Luk wrote: Hi Antonio Fiol, Right, it should be helpful for development, we all want know what IT's doing? But for production, i think it should be off. Gordon Antonio Fiol Bonnín wrote: Suggestion: Also add the logAbandoned parameter and set it to true. I found it very useful at hard times. Antonio Fiol Gordon Luk wrote: Hi Doug, Thank for your advise, indeed my project state at begining. So every thing is simple right now. BTW, i already experience on connection pool on my previus ejb project. I will take care on it. As a simple mind, take and go. :-) Gordon Parsons Technical Services wrote: Gordon, What about resultset and statement? Since this fixes it then you DO have a leak. Break it down and check each step to make sure that they are returned, even if an exception is thrown. I have it in finally clauses as a last resort if it fails normally. There is something leaving the connection hanging. Doug - Original Message - From: Gordon Luk [EMAIL PROTECTED] To: Tomcat Users List [EMAIL PROTECTED] Sent: Friday, March 26, 2004 2:31 AM Subject: Re: Connection Pool setup. Hi Doug, O, thanks, it's work... BTW, thanks for remind, and i am the good citizen, allway return connection back to pool. ;-) Gordon Parsons Technical Services wrote: Gordon, Just for grins and giggles try adding this as a test: parameter nameremoveAbandoned/name valuetrue/value /parameter parameter nameremoveAbandonedTimeout/name value60/value /parameter To reclaim abandoned connections. If it drops you back to the min then you have a leak in you app. Check that connections, resultsets and statements are all closed. Doug - Original Message - From: Gordon Luk [EMAIL PROTECTED] To: 'Tomcat Users List' [EMAIL PROTECTED] Sent: Thursday, March 25, 2004 11:18 PM Subject: RE: Connection Pool setup. Here my setting... My problem is ... Connection pool look like don't open 10 connection at start-up, and when my servlet run for a while... Let it open up more connections(over 30) and then wait... (after a night .. :-D)... When I check database server, it still open more connections, I expect min. for 10 only. My setup anything got wrong? Thx. ---8 ?xml version='1.0' encoding='utf-8'? ... ResourceParams name=jdbc/myjdniname parameter namefactory/name valueorg.apache.commons.dbcp.BasicDataSourceFactory/value /parameter !-- Maximum number of dB connections in pool. Make sure you configure your mysqld max_connections large enough to handle all of your db connections. Set to 0 for no limit. -- parameter namemaxActive/name value100/value /parameter !-- Maximum number of idle dB connections to retain in pool. Set to 0 for no limit. -- parameter namemaxIdle/name value10/value /parameter parameter nameminIdle/name value10/value /parameter !-- Maximum time to wait for a dB connection to become available in ms, in this example 10 seconds. An Exception is thrown if this timeout is exceeded. Set to -1 to wait indefinitely. -- parameter namemaxWait/name value1/value /parameter parameter nameusername/name valuemyuserid/value /parameter parameter namepassword/name valuemypassword/value /parameter !-- Class name for mm.mysql JDBC driver -- parameter namedriverClassName/name valuenet.sourceforge.jtds.jdbc.Driver/value /parameter parameter nameurl/name value jdbc:jtds:sqlserver://myserver/mydatabase;TDS=4.2;charset=big5/value /parameter parameter namevalidationQuery/name valueselect count(*) from tablename/value /parameter parameter nametestOnBorrow/name valuetrue/value /parameter parameter nametestWhileIdle/name valuetrue/value /parameter parameter nametimeBetweenEvictionRunsMillis/name value5000/value /parameter parameter nameminEvictableIdleTimeMillis/name value1/value /parameter /ResourceParams /Context ---8 Regards, Gordon Luk -Original Message- From: D'Alessandro, Arthur [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 7:03 PM To: Tomcat Users List; Tomcat Users List Subject: RE: Connection Pool setup. May help if you post your resource snipplet (replacing any
Re: Connection Pool setup.
Hi Doug, Thank for your advise, indeed my project state at begining. So every thing is simple right now. BTW, i already experience on connection pool on my previus ejb project. I will take care on it. As a simple mind, take and go. :-) Gordon Parsons Technical Services wrote: Gordon, What about resultset and statement? Since this fixes it then you DO have a leak. Break it down and check each step to make sure that they are returned, even if an exception is thrown. I have it in finally clauses as a last resort if it fails normally. There is something leaving the connection hanging. Doug - Original Message - From: Gordon Luk [EMAIL PROTECTED] To: Tomcat Users List [EMAIL PROTECTED] Sent: Friday, March 26, 2004 2:31 AM Subject: Re: Connection Pool setup. Hi Doug, O, thanks, it's work... BTW, thanks for remind, and i am the good citizen, allway return connection back to pool. ;-) Gordon Parsons Technical Services wrote: Gordon, Just for grins and giggles try adding this as a test: parameter nameremoveAbandoned/name valuetrue/value /parameter parameter nameremoveAbandonedTimeout/name value60/value /parameter To reclaim abandoned connections. If it drops you back to the min then you have a leak in you app. Check that connections, resultsets and statements are all closed. Doug - Original Message - From: Gordon Luk [EMAIL PROTECTED] To: 'Tomcat Users List' [EMAIL PROTECTED] Sent: Thursday, March 25, 2004 11:18 PM Subject: RE: Connection Pool setup. Here my setting... My problem is ... Connection pool look like don't open 10 connection at start-up, and when my servlet run for a while... Let it open up more connections(over 30) and then wait... (after a night .. :-D)... When I check database server, it still open more connections, I expect min. for 10 only. My setup anything got wrong? Thx. ---8 ?xml version='1.0' encoding='utf-8'? ... ResourceParams name=jdbc/myjdniname parameter namefactory/name valueorg.apache.commons.dbcp.BasicDataSourceFactory/value /parameter !-- Maximum number of dB connections in pool. Make sure you configure your mysqld max_connections large enough to handle all of your db connections. Set to 0 for no limit. -- parameter namemaxActive/name value100/value /parameter !-- Maximum number of idle dB connections to retain in pool. Set to 0 for no limit. -- parameter namemaxIdle/name value10/value /parameter parameter nameminIdle/name value10/value /parameter !-- Maximum time to wait for a dB connection to become available in ms, in this example 10 seconds. An Exception is thrown if this timeout is exceeded. Set to -1 to wait indefinitely. -- parameter namemaxWait/name value1/value /parameter parameter nameusername/name valuemyuserid/value /parameter parameter namepassword/name valuemypassword/value /parameter !-- Class name for mm.mysql JDBC driver -- parameter namedriverClassName/name valuenet.sourceforge.jtds.jdbc.Driver/value /parameter parameter nameurl/name value jdbc:jtds:sqlserver://myserver/mydatabase;TDS=4.2;charset=big5/value /parameter parameter namevalidationQuery/name valueselect count(*) from tablename/value /parameter parameter nametestOnBorrow/name valuetrue/value /parameter parameter nametestWhileIdle/name valuetrue/value /parameter parameter nametimeBetweenEvictionRunsMillis/name value5000/value /parameter parameter nameminEvictableIdleTimeMillis/name value1/value /parameter /ResourceParams /Context ---8 Regards, Gordon Luk -Original Message- From: D'Alessandro, Arthur [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 7:03 PM To: Tomcat Users List; Tomcat Users List Subject: RE: Connection Pool setup. May help if you post your resource snipplet (replacing any host /user/passwd info) -Original Message- From: Gordon Luk [mailto:[EMAIL PROTECTED] Sent: Thu Mar 25 05:30:56 2004 To: Tomcat Users List Subject: Connection Pool setup. Hi All, May be I missing understand the DBCP configuration. Anyone could help ? I want my connection pool are Max 100 connection, 10 conn. At start-up, max idle 10, when idle for 10 min then kill it. Something like that. Thanks. Regards, Gordon Luk --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http
Re: Connection Pool setup.
Suggestion: Also add the logAbandoned parameter and set it to true. I found it very useful at hard times. Antonio Fiol Gordon Luk wrote: Hi Doug, Thank for your advise, indeed my project state at begining. So every thing is simple right now. BTW, i already experience on connection pool on my previus ejb project. I will take care on it. As a simple mind, take and go. :-) Gordon Parsons Technical Services wrote: Gordon, What about resultset and statement? Since this fixes it then you DO have a leak. Break it down and check each step to make sure that they are returned, even if an exception is thrown. I have it in finally clauses as a last resort if it fails normally. There is something leaving the connection hanging. Doug - Original Message - From: Gordon Luk [EMAIL PROTECTED] To: Tomcat Users List [EMAIL PROTECTED] Sent: Friday, March 26, 2004 2:31 AM Subject: Re: Connection Pool setup. Hi Doug, O, thanks, it's work... BTW, thanks for remind, and i am the good citizen, allway return connection back to pool. ;-) Gordon Parsons Technical Services wrote: Gordon, Just for grins and giggles try adding this as a test: parameter nameremoveAbandoned/name valuetrue/value /parameter parameter nameremoveAbandonedTimeout/name value60/value /parameter To reclaim abandoned connections. If it drops you back to the min then you have a leak in you app. Check that connections, resultsets and statements are all closed. Doug - Original Message - From: Gordon Luk [EMAIL PROTECTED] To: 'Tomcat Users List' [EMAIL PROTECTED] Sent: Thursday, March 25, 2004 11:18 PM Subject: RE: Connection Pool setup. Here my setting... My problem is ... Connection pool look like don't open 10 connection at start-up, and when my servlet run for a while... Let it open up more connections(over 30) and then wait... (after a night .. :-D)... When I check database server, it still open more connections, I expect min. for 10 only. My setup anything got wrong? Thx. ---8 ?xml version='1.0' encoding='utf-8'? ... ResourceParams name=jdbc/myjdniname parameter namefactory/name valueorg.apache.commons.dbcp.BasicDataSourceFactory/value /parameter !-- Maximum number of dB connections in pool. Make sure you configure your mysqld max_connections large enough to handle all of your db connections. Set to 0 for no limit. -- parameter namemaxActive/name value100/value /parameter !-- Maximum number of idle dB connections to retain in pool. Set to 0 for no limit. -- parameter namemaxIdle/name value10/value /parameter parameter nameminIdle/name value10/value /parameter !-- Maximum time to wait for a dB connection to become available in ms, in this example 10 seconds. An Exception is thrown if this timeout is exceeded. Set to -1 to wait indefinitely. -- parameter namemaxWait/name value1/value /parameter parameter nameusername/name valuemyuserid/value /parameter parameter namepassword/name valuemypassword/value /parameter !-- Class name for mm.mysql JDBC driver -- parameter namedriverClassName/name valuenet.sourceforge.jtds.jdbc.Driver/value /parameter parameter nameurl/name value jdbc:jtds:sqlserver://myserver/mydatabase;TDS=4.2;charset=big5/value /parameter parameter namevalidationQuery/name valueselect count(*) from tablename/value /parameter parameter nametestOnBorrow/name valuetrue/value /parameter parameter nametestWhileIdle/name valuetrue/value /parameter parameter nametimeBetweenEvictionRunsMillis/name value5000/value /parameter parameter nameminEvictableIdleTimeMillis/name value1/value /parameter /ResourceParams /Context ---8 Regards, Gordon Luk -Original Message- From: D'Alessandro, Arthur [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 7:03 PM To: Tomcat Users List; Tomcat Users List Subject: RE: Connection Pool setup. May help if you post your resource snipplet (replacing any host /user/passwd info) -Original Message- From: Gordon Luk [mailto:[EMAIL PROTECTED] Sent: Thu Mar 25 05:30:56 2004 To: Tomcat Users List Subject: Connection Pool setup. Hi All, May be I missing understand the DBCP configuration. Anyone could help ? I want my connection pool are Max 100 connection, 10 conn. At start-up, max idle 10, when idle for 10 min then kill it. Something like that. Thanks. Regards, Gordon Luk - To unsubscribe, e-mail
Re: Connection Pool setup.
Hi Antonio Fiol, Right, it should be helpful for development, we all want know what IT's doing? But for production, i think it should be off. Gordon Antonio Fiol Bonnín wrote: Suggestion: Also add the logAbandoned parameter and set it to true. I found it very useful at hard times. Antonio Fiol Gordon Luk wrote: Hi Doug, Thank for your advise, indeed my project state at begining. So every thing is simple right now. BTW, i already experience on connection pool on my previus ejb project. I will take care on it. As a simple mind, take and go. :-) Gordon Parsons Technical Services wrote: Gordon, What about resultset and statement? Since this fixes it then you DO have a leak. Break it down and check each step to make sure that they are returned, even if an exception is thrown. I have it in finally clauses as a last resort if it fails normally. There is something leaving the connection hanging. Doug - Original Message - From: Gordon Luk [EMAIL PROTECTED] To: Tomcat Users List [EMAIL PROTECTED] Sent: Friday, March 26, 2004 2:31 AM Subject: Re: Connection Pool setup. Hi Doug, O, thanks, it's work... BTW, thanks for remind, and i am the good citizen, allway return connection back to pool. ;-) Gordon Parsons Technical Services wrote: Gordon, Just for grins and giggles try adding this as a test: parameter nameremoveAbandoned/name valuetrue/value /parameter parameter nameremoveAbandonedTimeout/name value60/value /parameter To reclaim abandoned connections. If it drops you back to the min then you have a leak in you app. Check that connections, resultsets and statements are all closed. Doug - Original Message - From: Gordon Luk [EMAIL PROTECTED] To: 'Tomcat Users List' [EMAIL PROTECTED] Sent: Thursday, March 25, 2004 11:18 PM Subject: RE: Connection Pool setup. Here my setting... My problem is ... Connection pool look like don't open 10 connection at start-up, and when my servlet run for a while... Let it open up more connections(over 30) and then wait... (after a night .. :-D)... When I check database server, it still open more connections, I expect min. for 10 only. My setup anything got wrong? Thx. ---8 ?xml version='1.0' encoding='utf-8'? ... ResourceParams name=jdbc/myjdniname parameter namefactory/name valueorg.apache.commons.dbcp.BasicDataSourceFactory/value /parameter !-- Maximum number of dB connections in pool. Make sure you configure your mysqld max_connections large enough to handle all of your db connections. Set to 0 for no limit. -- parameter namemaxActive/name value100/value /parameter !-- Maximum number of idle dB connections to retain in pool. Set to 0 for no limit. -- parameter namemaxIdle/name value10/value /parameter parameter nameminIdle/name value10/value /parameter !-- Maximum time to wait for a dB connection to become available in ms, in this example 10 seconds. An Exception is thrown if this timeout is exceeded. Set to -1 to wait indefinitely. -- parameter namemaxWait/name value1/value /parameter parameter nameusername/name valuemyuserid/value /parameter parameter namepassword/name valuemypassword/value /parameter !-- Class name for mm.mysql JDBC driver -- parameter namedriverClassName/name valuenet.sourceforge.jtds.jdbc.Driver/value /parameter parameter nameurl/name value jdbc:jtds:sqlserver://myserver/mydatabase;TDS=4.2;charset=big5/value /parameter parameter namevalidationQuery/name valueselect count(*) from tablename/value /parameter parameter nametestOnBorrow/name valuetrue/value /parameter parameter nametestWhileIdle/name valuetrue/value /parameter parameter nametimeBetweenEvictionRunsMillis/name value5000/value /parameter parameter nameminEvictableIdleTimeMillis/name value1/value /parameter /ResourceParams /Context ---8 Regards, Gordon Luk -Original Message- From: D'Alessandro, Arthur [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 7:03 PM To: Tomcat Users List; Tomcat Users List Subject: RE: Connection Pool setup. May help if you post your resource snipplet (replacing any host /user/passwd info) -Original Message- From: Gordon Luk [mailto:[EMAIL PROTECTED] Sent: Thu Mar 25 05:30:56 2004 To: Tomcat Users List Subject: Connection Pool setup. Hi All, May be I missing understand the DBCP configuration. Anyone could help ? I want my connection pool are Max 100 connection, 10 conn. At start-up, max idle 10, when
RE: Connection Pool setup.
May help if you post your resource snipplet (replacing any host /user/passwd info) -Original Message- From: Gordon Luk [mailto:[EMAIL PROTECTED] Sent: Thu Mar 25 05:30:56 2004 To: Tomcat Users List Subject:Connection Pool setup. Hi All, May be I missing understand the DBCP configuration. Anyone could help ? I want my connection pool are Max 100 connection, 10 conn. At start-up, max idle 10, when idle for 10 min then kill it. Something like that. Thanks. Regards, Gordon Luk --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.639 / Virus Database: 408 - Release Date: 3/22/2004 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Connection Pool setup.
That easy, look at the DBCP Documentation http://jakarta.apache.org/commons/dbcp/configuration.html and used the following Resource name=jdbc/EmployeeDatabase auth=Container type=javax.sql.DataSource / ResourceParams name=jdbc/EmployeeDatabase parameter nameusername/name valueBlah/value /parameter parameter namepassword/name valueBlah or empty/value /parameter parameter namefactory/name valueorg.apache.commons.dbcp.BasicDataSourceFactory/value /parameter parameter namedriverClassName/name valueorg.hsqldb.jdbcDriver/value /parameter parameter nameurl/name valuejdbc:hsqldb:hsql://localhost/value /parameter parameter namemaxActive/name value8/value /parameter parameter namemaxIdle/name value4/value /parameter parameter namevalidationQuery/name valueSELECT id FROM tomcat_validation WHERE id = 1/value /parameter /ResourceParams As your Database have timeouts or admin downtimes set up the tomcat_validation table with one dummy row and use also Datasoucre at Realms :-) DBCP has a lot of options to control the connections (s. detail configuration description) Regards Peter http://tomcat.objektpark.org/ D'Alessandro, Arthur schrieb: May help if you post your resource snipplet (replacing any host /user/passwd info) -Original Message- From: Gordon Luk [mailto:[EMAIL PROTECTED] Sent: Thu Mar 25 05:30:56 2004 To: Tomcat Users List Subject:Connection Pool setup. Hi All, May be I missing understand the DBCP configuration. Anyone could help ? I want my connection pool are Max 100 connection, 10 conn. At start-up, max idle 10, when idle for 10 min then kill it. Something like that. Thanks. Regards, Gordon Luk --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.639 / Virus Database: 408 - Release Date: 3/22/2004 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: Connection Pool setup.
Here my setting... My problem is ... Connection pool look like don't open 10 connection at start-up, and when my servlet run for a while... Let it open up more connections(over 30) and then wait... (after a night .. :-D)... When I check database server, it still open more connections, I expect min. for 10 only. My setup anything got wrong? Thx. ---8 ?xml version='1.0' encoding='utf-8'? ... ResourceParams name=jdbc/myjdniname parameter namefactory/name valueorg.apache.commons.dbcp.BasicDataSourceFactory/value /parameter !-- Maximum number of dB connections in pool. Make sure you configure your mysqld max_connections large enough to handle all of your db connections. Set to 0 for no limit. -- parameter namemaxActive/name value100/value /parameter !-- Maximum number of idle dB connections to retain in pool. Set to 0 for no limit. -- parameter namemaxIdle/name value10/value /parameter parameter nameminIdle/name value10/value /parameter !-- Maximum time to wait for a dB connection to become available in ms, in this example 10 seconds. An Exception is thrown if this timeout is exceeded. Set to -1 to wait indefinitely. -- parameter namemaxWait/name value1/value /parameter parameter nameusername/name valuemyuserid/value /parameter parameter namepassword/name valuemypassword/value /parameter !-- Class name for mm.mysql JDBC driver -- parameter namedriverClassName/name valuenet.sourceforge.jtds.jdbc.Driver/value /parameter parameter nameurl/name value jdbc:jtds:sqlserver://myserver/mydatabase;TDS=4.2;charset=big5/value /parameter parameter namevalidationQuery/name valueselect count(*) from tablename/value /parameter parameter nametestOnBorrow/name valuetrue/value /parameter parameter nametestWhileIdle/name valuetrue/value /parameter parameter nametimeBetweenEvictionRunsMillis/name value5000/value /parameter parameter nameminEvictableIdleTimeMillis/name value1/value /parameter /ResourceParams /Context ---8 Regards, Gordon Luk -Original Message- From: D'Alessandro, Arthur [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 7:03 PM To: Tomcat Users List; Tomcat Users List Subject: RE: Connection Pool setup. May help if you post your resource snipplet (replacing any host /user/passwd info) -Original Message- From: Gordon Luk [mailto:[EMAIL PROTECTED] Sent: Thu Mar 25 05:30:56 2004 To: Tomcat Users List Subject:Connection Pool setup. Hi All, May be I missing understand the DBCP configuration. Anyone could help ? I want my connection pool are Max 100 connection, 10 conn. At start-up, max idle 10, when idle for 10 min then kill it. Something like that. Thanks. Regards, Gordon Luk --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.639 / Virus Database: 408 - Release Date: 3/22/2004 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.639 / Virus Database: 408 - Release Date: 3/22/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.639 / Virus Database: 408 - Release Date: 3/22/2004 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Connection Pool setup.
Gordon, Just for grins and giggles try adding this as a test: parameter nameremoveAbandoned/name valuetrue/value /parameter parameter nameremoveAbandonedTimeout/name value60/value /parameter To reclaim abandoned connections. If it drops you back to the min then you have a leak in you app. Check that connections, resultsets and statements are all closed. Doug - Original Message - From: Gordon Luk [EMAIL PROTECTED] To: 'Tomcat Users List' [EMAIL PROTECTED] Sent: Thursday, March 25, 2004 11:18 PM Subject: RE: Connection Pool setup. Here my setting... My problem is ... Connection pool look like don't open 10 connection at start-up, and when my servlet run for a while... Let it open up more connections(over 30) and then wait... (after a night .. :-D)... When I check database server, it still open more connections, I expect min. for 10 only. My setup anything got wrong? Thx. ---8 ?xml version='1.0' encoding='utf-8'? ... ResourceParams name=jdbc/myjdniname parameter namefactory/name valueorg.apache.commons.dbcp.BasicDataSourceFactory/value /parameter !-- Maximum number of dB connections in pool. Make sure you configure your mysqld max_connections large enough to handle all of your db connections. Set to 0 for no limit. -- parameter namemaxActive/name value100/value /parameter !-- Maximum number of idle dB connections to retain in pool. Set to 0 for no limit. -- parameter namemaxIdle/name value10/value /parameter parameter nameminIdle/name value10/value /parameter !-- Maximum time to wait for a dB connection to become available in ms, in this example 10 seconds. An Exception is thrown if this timeout is exceeded. Set to -1 to wait indefinitely. -- parameter namemaxWait/name value1/value /parameter parameter nameusername/name valuemyuserid/value /parameter parameter namepassword/name valuemypassword/value /parameter !-- Class name for mm.mysql JDBC driver -- parameter namedriverClassName/name valuenet.sourceforge.jtds.jdbc.Driver/value /parameter parameter nameurl/name value jdbc:jtds:sqlserver://myserver/mydatabase;TDS=4.2;charset=big5/value /parameter parameter namevalidationQuery/name valueselect count(*) from tablename/value /parameter parameter nametestOnBorrow/name valuetrue/value /parameter parameter nametestWhileIdle/name valuetrue/value /parameter parameter nametimeBetweenEvictionRunsMillis/name value5000/value /parameter parameter nameminEvictableIdleTimeMillis/name value1/value /parameter /ResourceParams /Context ---8 Regards, Gordon Luk -Original Message- From: D'Alessandro, Arthur [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 7:03 PM To: Tomcat Users List; Tomcat Users List Subject: RE: Connection Pool setup. May help if you post your resource snipplet (replacing any host /user/passwd info) -Original Message- From: Gordon Luk [mailto:[EMAIL PROTECTED] Sent: Thu Mar 25 05:30:56 2004 To: Tomcat Users List Subject: Connection Pool setup. Hi All, May be I missing understand the DBCP configuration. Anyone could help ? I want my connection pool are Max 100 connection, 10 conn. At start-up, max idle 10, when idle for 10 min then kill it. Something like that. Thanks. Regards, Gordon Luk --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.639 / Virus Database: 408 - Release Date: 3/22/2004 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.639 / Virus Database: 408 - Release Date: 3/22/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.639 / Virus Database: 408 - Release Date: 3/22/2004 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail
Re: Connection Pool setup.
Hi Doug, O, thanks, it's work... BTW, thanks for remind, and i am the good citizen, allway return connection back to pool. ;-) Gordon Parsons Technical Services wrote: Gordon, Just for grins and giggles try adding this as a test: parameter nameremoveAbandoned/name valuetrue/value /parameter parameter nameremoveAbandonedTimeout/name value60/value /parameter To reclaim abandoned connections. If it drops you back to the min then you have a leak in you app. Check that connections, resultsets and statements are all closed. Doug - Original Message - From: Gordon Luk [EMAIL PROTECTED] To: 'Tomcat Users List' [EMAIL PROTECTED] Sent: Thursday, March 25, 2004 11:18 PM Subject: RE: Connection Pool setup. Here my setting... My problem is ... Connection pool look like don't open 10 connection at start-up, and when my servlet run for a while... Let it open up more connections(over 30) and then wait... (after a night .. :-D)... When I check database server, it still open more connections, I expect min. for 10 only. My setup anything got wrong? Thx. ---8 ?xml version='1.0' encoding='utf-8'? ... ResourceParams name=jdbc/myjdniname parameter namefactory/name valueorg.apache.commons.dbcp.BasicDataSourceFactory/value /parameter !-- Maximum number of dB connections in pool. Make sure you configure your mysqld max_connections large enough to handle all of your db connections. Set to 0 for no limit. -- parameter namemaxActive/name value100/value /parameter !-- Maximum number of idle dB connections to retain in pool. Set to 0 for no limit. -- parameter namemaxIdle/name value10/value /parameter parameter nameminIdle/name value10/value /parameter !-- Maximum time to wait for a dB connection to become available in ms, in this example 10 seconds. An Exception is thrown if this timeout is exceeded. Set to -1 to wait indefinitely. -- parameter namemaxWait/name value1/value /parameter parameter nameusername/name valuemyuserid/value /parameter parameter namepassword/name valuemypassword/value /parameter !-- Class name for mm.mysql JDBC driver -- parameter namedriverClassName/name valuenet.sourceforge.jtds.jdbc.Driver/value /parameter parameter nameurl/name value jdbc:jtds:sqlserver://myserver/mydatabase;TDS=4.2;charset=big5/value /parameter parameter namevalidationQuery/name valueselect count(*) from tablename/value /parameter parameter nametestOnBorrow/name valuetrue/value /parameter parameter nametestWhileIdle/name valuetrue/value /parameter parameter nametimeBetweenEvictionRunsMillis/name value5000/value /parameter parameter nameminEvictableIdleTimeMillis/name value1/value /parameter /ResourceParams /Context ---8 Regards, Gordon Luk -Original Message- From: D'Alessandro, Arthur [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 7:03 PM To: Tomcat Users List; Tomcat Users List Subject: RE: Connection Pool setup. May help if you post your resource snipplet (replacing any host /user/passwd info) -Original Message- From: Gordon Luk [mailto:[EMAIL PROTECTED] Sent: Thu Mar 25 05:30:56 2004 To: Tomcat Users List Subject: Connection Pool setup. Hi All, May be I missing understand the DBCP configuration. Anyone could help ? I want my connection pool are Max 100 connection, 10 conn. At start-up, max idle 10, when idle for 10 min then kill it. Something like that. Thanks. Regards, Gordon Luk --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.639 / Virus Database: 408 - Release Date: 3/22/2004 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.639 / Virus Database: 408 - Release Date: 3/22/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.639 / Virus Database: 408 - Release Date: 3/22/2004 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED
Re: Connection Pool setup.
Gordon, What about resultset and statement? Since this fixes it then you DO have a leak. Break it down and check each step to make sure that they are returned, even if an exception is thrown. I have it in finally clauses as a last resort if it fails normally. There is something leaving the connection hanging. Doug - Original Message - From: Gordon Luk [EMAIL PROTECTED] To: Tomcat Users List [EMAIL PROTECTED] Sent: Friday, March 26, 2004 2:31 AM Subject: Re: Connection Pool setup. Hi Doug, O, thanks, it's work... BTW, thanks for remind, and i am the good citizen, allway return connection back to pool. ;-) Gordon Parsons Technical Services wrote: Gordon, Just for grins and giggles try adding this as a test: parameter nameremoveAbandoned/name valuetrue/value /parameter parameter nameremoveAbandonedTimeout/name value60/value /parameter To reclaim abandoned connections. If it drops you back to the min then you have a leak in you app. Check that connections, resultsets and statements are all closed. Doug - Original Message - From: Gordon Luk [EMAIL PROTECTED] To: 'Tomcat Users List' [EMAIL PROTECTED] Sent: Thursday, March 25, 2004 11:18 PM Subject: RE: Connection Pool setup. Here my setting... My problem is ... Connection pool look like don't open 10 connection at start-up, and when my servlet run for a while... Let it open up more connections(over 30) and then wait... (after a night .. :-D)... When I check database server, it still open more connections, I expect min. for 10 only. My setup anything got wrong? Thx. ---8 ?xml version='1.0' encoding='utf-8'? ... ResourceParams name=jdbc/myjdniname parameter namefactory/name valueorg.apache.commons.dbcp.BasicDataSourceFactory/value /parameter !-- Maximum number of dB connections in pool. Make sure you configure your mysqld max_connections large enough to handle all of your db connections. Set to 0 for no limit. -- parameter namemaxActive/name value100/value /parameter !-- Maximum number of idle dB connections to retain in pool. Set to 0 for no limit. -- parameter namemaxIdle/name value10/value /parameter parameter nameminIdle/name value10/value /parameter !-- Maximum time to wait for a dB connection to become available in ms, in this example 10 seconds. An Exception is thrown if this timeout is exceeded. Set to -1 to wait indefinitely. -- parameter namemaxWait/name value1/value /parameter parameter nameusername/name valuemyuserid/value /parameter parameter namepassword/name valuemypassword/value /parameter !-- Class name for mm.mysql JDBC driver -- parameter namedriverClassName/name valuenet.sourceforge.jtds.jdbc.Driver/value /parameter parameter nameurl/name value jdbc:jtds:sqlserver://myserver/mydatabase;TDS=4.2;charset=big5/value /parameter parameter namevalidationQuery/name valueselect count(*) from tablename/value /parameter parameter nametestOnBorrow/name valuetrue/value /parameter parameter nametestWhileIdle/name valuetrue/value /parameter parameter nametimeBetweenEvictionRunsMillis/name value5000/value /parameter parameter nameminEvictableIdleTimeMillis/name value1/value /parameter /ResourceParams /Context ---8 Regards, Gordon Luk -Original Message- From: D'Alessandro, Arthur [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 7:03 PM To: Tomcat Users List; Tomcat Users List Subject: RE: Connection Pool setup. May help if you post your resource snipplet (replacing any host /user/passwd info) -Original Message- From: Gordon Luk [mailto:[EMAIL PROTECTED] Sent: Thu Mar 25 05:30:56 2004 To: Tomcat Users List Subject: Connection Pool setup. Hi All, May be I missing understand the DBCP configuration. Anyone could help ? I want my connection pool are Max 100 connection, 10 conn. At start-up, max idle 10, when idle for 10 min then kill it. Something like that. Thanks. Regards, Gordon Luk --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.639 / Virus Database
