You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@tomcat.apache.org by gl...@apache.org on 2002/08/18 02:55:25 UTC
cvs commit: jakarta-tomcat-4.0/webapps/tomcat-docs jndi-datasource-examples-howto.xml
glenn 2002/08/17 17:55:25
Modified: webapps/tomcat-docs jndi-datasource-examples-howto.xml
Log:
Update docs for DBCP 1.0 release, cleanup, and add more info
Revision Changes Path
1.4 +312 -150 jakarta-tomcat-4.0/webapps/tomcat-docs/jndi-datasource-examples-howto.xml
Index: jndi-datasource-examples-howto.xml
===================================================================
RCS file: /home/cvs/jakarta-tomcat-4.0/webapps/tomcat-docs/jndi-datasource-examples-howto.xml,v
retrieving revision 1.3
retrieving revision 1.4
diff -u -r1.3 -r1.4
--- jndi-datasource-examples-howto.xml 5 Jul 2002 13:17:46 -0000 1.3
+++ jndi-datasource-examples-howto.xml 18 Aug 2002 00:55:25 -0000 1.4
@@ -9,66 +9,162 @@
<properties>
<author email="leslie.hughes@rubus.com">Les Hughes</author>
<author email="david-tomcat@haraburda.com">David Haraburda</author>
- <title>JNDI Datasource Examples HOW-TO</title>
+ <author>Glenn Nielsen</author>
+ <title>JNDI Datasource HOW-TO</title>
</properties>
<body>
+<section name="Table of Contents">
+<p>
+<a href="#Introduction">Introduction</a><br />
+<a href="#Database Connection Pool (DBCP) Configurations">
+Database Connection Pool (DBCP) Configurations</a><br />
+<a href="#Tyrex Connection Pool">Tyrex Connection Pool</a><br />
+<a href="#Non DBCP Solutions">Non DBCP Solutions</a><br />
+<a href="#Oracle 8i with OCI client">Oracle 8i with OCI client</a><br />
+<a href="#Common Problems">Common Problems</a><br />
+</p>
+</section>
<section name="Introduction">
-<p>JNDI Datasource configuration is covered extensively in the JNDI-Resources-HOWTO
-however, feedback from <code>tomcat-user</code> has shown that specifics for individual
-configurations can be rather tricky.</p>
-<p>Here then are some example configurations that have posted to tomcat-user
-for popular databases.</p>
+<p>JNDI Datasource configuration is covered extensively in the
+JNDI-Resources-HOWTO however, feedback from <code>tomcat-user</code> has
+shown that specifics for individual configurations can be rather tricky.</p>
+
+<p>Here then are some example configurations that have been posted to
+tomcat-user for popular databases and some general tips for db useage.</p>
+
+<p>You should be aware that since these notes are derived from configuration
+and/or feedback posted to <code>tomcat-user</code> YMMV :-). Please let us
+know if you have any other tested configurations that you feel may be of use
+to the wider audience, or if you feel we can improve this section in anyway.</p>
+
</section>
-<section name="Jakarta DBCP Pooled Configurations">
-<p>For each of these configurations you will need the following Jakarta Commons projects
-Note that currently, these all employ connection pooling
-via the Jakarta-commons connection pool. Also, you should be aware that since these
-notes are derived from the mysql configuration and/or feedback from <code>tomcat-user</code>.
-YMMV :-). Please let us know if you have any other tested configurations
-that you feel may be of use to the wider audience, or if you feel we can
-improve this section
-in anyway.</p>
+
+<section name="Database Connection Pool (DBCP) Configurations">
+
+<p>DBCP provides support for JDBC 2.0. On systems using a 1.4 JVM DBCP
+will support JDBC 3.0. Please let us know if you have used DBCP and its
+JDBC 3.0 features with a 1.4 JVM.
+</p>
+
+<p>See the <a href="http://jakarta.apache.org/commons/dbcp/api/index.html">
+DBCP Javadocs</a> BasicDataSource class for a complete list
+of configuration parameters.
+</p>
+
+<subsection name="Installation">
+<p>DBCP uses the Jakarta-Commons Database Connection Pool. It relies on
+number of Jakarta-Commons componenets:
<ul>
-<li>DBCP Nightly build > 20020523</li>
-<li>collections 2.0</li>
-<li>pool 1.0</li>
+<li>Jakarta-Commons DBCP 1.0</li>
+<li>Jakarta-Commons Collections 2.0</li>
+<li>Jakarta-Commons Pool 1.0</li>
</ul>
-<subsection name="Common Requirements">
-<p>Here are some common gotchas to consider</p>
+These jar files along with your the jar file for your JDBC driver should
+be installed in <code>$CATALINA_HOME/common/lib</code>.
+<blockquote>
+<strong>NOTE:</strong>Third Party drivers should be in jarfiles, not zipfiles.
+Tomcat only adds <code>$CATALINA_HOME/common/lib/*.jar</code> to the classpath.
+</blockquote>
+<blockquote>
+<strong>NOTE:</strong>
+Do not install these jarfiles in your <code>/WEB-INF/lib</code>, or
+<code>$JAVA_HOME/jre/lib/ext</code>, or anywhere else. You will
+experience problems if you install them anyplace other than
+<code>$CATALINA_HOME/common/lib</code>.
+</blockquote>
+</p>
+
+</subsection>
+
+<subsection name="Preventing dB connection pool leaks">
+
+<p>
+A database connection pool creates and manages a pool of connections
+to a database. Recycling and reusing already existing connections
+to a dB is more efficient than opening a new connection.
+</p>
+
+<p>
+There is one problem with connection pooling. A web application has
+to explicetely close ResultSet's, Statement's, and Connection's.
+Failure of a web application to close these resources can result in
+them never being available again for reuse, a db connection pool "leak".
+This can eventually result in your web application db connections failing
+if there are no more available connections.</p>
+
+<p>
+There is a solution to this problem. The Jakarta-Commons DBCP can be
+configured to track and recover these abandoned dB connections. Not
+only can it recover them, but also generate a stack trace for the code
+which opened these resources and never closed them.</p>
+
+<p>
+To configure a DBCP DataSource so that abandoned dB connections are
+removed and recycled add the following <code>paramater</code> to the
+<code>ResourceParams</code> configuration for your DBCP DataSource
+<code>Resource</code>:
+<source>
+ <parameter>
+ <name>removeAbandoned</name>
+ <value>true</value>
+ </parameter>
+</source>
+When available db connections run low DBCP will recover and recyle
+any abandoned dB connections it finds. The default is <code>false</code>.
+</p>
+
+<p>
+Use the <code>removeAbandonedTimeout</code> parameter to set the number
+of seconds a dB connection has been idle before it is considered abandoned.
+<source>
+ <parameter>
+ <name>removeAbandonedTimeout</name>
+ <value>60</value>
+ </parameter>
+</source>
+The default timeout for removing abandoned connections is 300 seconds.
+</p>
+
+<p>
+The <code>logAbandoned</code> parameter can be set to <code>true</code>
+if you want DBCP to log a stack trace of the code which abandoned the
+dB connection resources.
+<source>
+ <parameter>
+ <name>logAbandoned</name>
+ <value>true</value>
+ </parameter>
+</source>
+The default is <code>false</code>.
+</p>
+
+</subsection>
+
+<subsection name="MySQL DBCP Example">
+
+<h3>0. Introduction</h3>
+<p>Versions of MySQL and the mm.mysql JDBC driver when have been
+reported to work:
<ul>
-<li>Datasource related classes (drivers, pools etc) should be installed in <code>$CATALINA_HOME/common/lib</code>
-to enable the server to find your classes when it creates your Datasources</li>
-<li>Third Party drivers should be in jarfiles, not zipfiles as by default, Tomcat
-only adds <code>$CATALINA_HOME/common/lib/*.jar</code> to the classpath</li>
+<li>MySQL 3.23.47, MySQL 3.23.47 using InnoDB, MySQL 4.0.1alpha</li>
+<li>mm.mysql 2.0.14 (JDBC Driver)</li>
</ul>
-</subsection>
+Please let us know if you have tested the new MySQL mm.mysql 3.0 driver.
+</p>
+<h3>1. MySQL configuration</h3>
+<p>
+Ensure that you follow these instructions as variations can cause problems.
+</p>
-<subsection name="mySQL using Jakarta Commons Connection Pool">
- <h3>0. Software Manifest</h3>
- <p>Starting with the correct sotftware is manifestly important, so here's a list of
- what we've found to work. Let us know of your success stories with other versions.</p>
- <ul>
- <li>Tomcat 4.0.3</li>
- <li>mySQL 4.0.1alpha</li>
- <li>mm.mysql 2.0.14 (JDBC Driver)</li>
- </ul>
-
- <h3>1. Installation</h3>
- <p>Ensure that you follow these instructions as variations can cause problems.</p>
- <ul>
- <li>Install mm.mysql driver, DBCP, collections and pool jarfiles into
- <code>$CATALINA_HOME/common/lib</code>. You will experience problems if you place
- these jarfiles in your webapp's <code>WEB-INF/lib</code> directory, in your
- <code>$JAVA_HOME/jre/lib/ext</code> or anywhere else, so dont.</li>
- <li>Create a new test user, a new database and a single test table.
- Your mySQL user <b>must</b> have a password assigned. The driver
- will fail if you try to connect with an empty password.</li></ul>
- <source>
+<p>Create a new test user, a new database and a single test table.
+Your MySQL user <strong>must</strong> have a password assigned. The driver
+will fail if you try to connect with an empty password.
+<source>
mysql> GRANT ALL PRIVILEGES ON *.* TO javauser@localhost
-> IDENTIFIED BY 'javadude' WITH GRANT OPTION;
mysql> create database javatest;
@@ -77,10 +173,15 @@
-> id int not null auto_increment primary key,
-> foo varchar(25),
-> bar int);
- </source>
- <p>Note: the above user should be removed once testing is complete!</p>
- <ul><li>Next insert some test data into the testdata table</li></ul>
- <source>
+</source>
+<blockquote>
+<strong>Note:</strong> the above user should be removed once testing is
+complete!
+</blockquote>
+</p>
+
+<p>Next insert some test data into the testdata table.
+<source>
mysql> insert into testdata values(null, 'hello', 12345);
Query OK, 1 row affected (0.00 sec)
@@ -94,8 +195,109 @@
mysql>
</source>
- <ul><li>Now create a simple test.jsp for use later.</li></ul>
- <source>
+</p>
+
+<h3>2. server.xml configuration</h3>
+<p>Configure the JNDI DataSource in Tomcat by adding a declaration for your
+resource to <code>$CATALINA_HOME/conf/server.xml</code>.</p>
+<p>Add this in between the <code></Context></code> tag of the examples
+context and the <code></Host></code> tag closing the localhost definition.<source>
+<Context path="/DBTest" docBase="DBTest"
+ debug="5" reloadable="true" crossContext="true">
+
+ <Logger className="org.apache.catalina.logger.FileLogger"
+ prefix="localhost_DBTest_log." suffix=".txt"
+ timestamp="true"/>
+
+ <Resource name="jdbc/TestDB"
+ auth="Container"
+ type="javax.sql.DataSource"/>
+
+ <ResourceParams name="jdbc/TestDB">
+ <parameter>
+ <name>factory</name>
+ <value>org.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>
+ <name>maxActive</name>
+ <value>100</value>
+ </parameter>
+
+ <!-- Maximum number of idle dB connections to retain in pool.
+ Set to 0 for no limit.
+ -->
+ <parameter>
+ <name>maxIdle</name>
+ <value>30</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>
+ <name>maxWait</name>
+ <value>10000</value>
+ </parameter>
+
+ <!-- MySQL dB username and password for dB connections -->
+ <parameter>
+ <name>username</name>
+ <value>javauser</value>
+ </parameter>
+ <parameter>
+ <name>password</name>
+ <value>javadude</value>
+ </parameter>
+
+ <!-- Class name for mm.mysql JDBC driver -->
+ <parameter>
+ <name>driverClassName</name>
+ <value>org.gjt.mm.mysql.Driver</value>
+ </parameter>
+
+ <!-- The JDBC connection url for connecting to your MySQL dB.
+ The autoReconnect=true argument to the url makes sure that the
+ mm.mysql JDBC Driver will automatically reconnect if mysqld closed the
+ connection. mysqld by default closes idle connections after 8 hours.
+ -->
+ <parameter>
+ <name>url</name>
+ <value>jdbc:mysql://localhost:3306/javatest?autoReconnect=true</value>
+ </parameter>
+ </ResourceParams>
+</Context>
+</source>
+</p>
+
+<h3>3. web.xml configuration</h3>
+
+<p>Now create a <code>WEB-INF/web.xml</code> for this test application.
+<source>
+<?xml version="1.0" encoding="ISO-8859-1"?>
+ <!DOCTYPE web-app PUBLIC
+ "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
+ "http://java.sun.com/dtd/web-app_2_3.dtd">
+<web-app>
+ <description>MySQL Test App</description>
+ <resource-ref>
+ <description>DB Connection</description>
+ <res-ref-name>jdbc/TestDB</res-ref-name>
+ <res-type>javax.sql.DataSource</res-type>
+ <res-auth>Container</res-auth>
+ </resource-ref>
+</web-app>
+</source>
+</p>
+
+<h3>4. Test code</h3>
+<p>Now create a simple test.jsp for use later.
+<source>
<html>
<head>
<title>DB Test</title>
@@ -114,9 +316,12 @@
</body>
</html>
</source>
- <ul><li>And create a Java class to actually use your new Datasource and connection pool. Note: this
- code isn't anywhere near production ready - it's only supposed to be used as a simple test :-)</li></ul>
- <source>
+</p>
+
+<p>And create a Java class to actually use your new Datasource and connection
+pool. Note: this code isn't anywhere near production ready - it's only
+supposed to be used as a simple test :-)
+<source>
package foo;
import javax.naming.*;
@@ -163,90 +368,21 @@
public int getBar() { return bar;}
}
</source>
- <ul><li>Now create a <code>WEB-INF/web.xml</code> for this test application</li></ul>
- <source>
-<?xml version="1.0" encoding="ISO-8859-1"?>
- <!DOCTYPE web-app PUBLIC
- "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
- "http://java.sun.com/dtd/web-app_2_3.dtd">
-<web-app>
- <description>mySQL Test App</description>
- <resource-ref>
- <description>DB Connection</description>
- <res-ref-name>jdbc/TestDB</res-ref-name>
- <res-type>javax.sql.DataSource</res-type>
- <res-auth>Container</res-auth>
- </resource-ref>
-</web-app>
-</source>
-<p>That completes the standard webapp aspects of the test application. Now to configure Tomcat.</p>
-<ul><li>Add a declaration of your resource to <code>$CATALINA_HOME/conf/server.xml</code>
-Add this in between the <code></Context></code> tag of the examples context and the
-<code></Host></code> tag closing the localhost definition. DONT ADD IT TO THE WARP CONNECTOR SECTION!!!
-</li></ul>
-<source>
-<Context path="/DBTest" docBase="DBTest"
- debug="5" reloadable="true" crossContext="true">
-
- <Logger className="org.apache.catalina.logger.FileLogger"
- prefix="localhost_DBTest_log." suffix=".txt"
- timestamp="true"/>
-
- <Resource name="jdbc/TestDB"
- auth="Container"
- type="javax.sql.DataSource"/>
-
- <ResourceParams name="jdbc/TestDB">
- <parameter>
- <name>factory</name>
- <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
- </parameter>
- <parameter>
- <name>maxActive</name>
- <value>100</value>
- </parameter>
- <parameter>
- <name>maxIdle</name>
- <value>30000</value>
- </parameter>
- <parameter>
- <name>maxWait</name>
- <value>100</value>
- </parameter>
- <parameter>
- <name>username</name>
- <value>javauser</value>
- </parameter>
- <parameter>
- <name>password</name>
- <value>javadude</value>
- </parameter>
-
- <parameter>
- <name>driverClassName</name>
- <value>org.gjt.mm.mysql.Driver</value>
- </parameter>
-
- <parameter>
- <name>url</name>
- <value>jdbc:mysql://localhost:3306/javatest</value>
- </parameter>
- </ResourceParams>
-</Context>
-</source>
+</p>
-<ul><li>Finally deploy your web app into <code>$CATALINA_HOME/webapps</code> either as a warfile called
-<code>DBTest.war</code> or into a sub-directory called <code>DBTest</code></li>
-<li>Once deployed, point a browser at <code>http://localhost:8080/DBTest/test.jsp</code> to view the fruits of your hard work.</li></ul>
+<p>Finally deploy your web app into <code>$CATALINA_HOME/webapps</code> either
+as a warfile called <code>DBTest.war</code> or into a sub-directory called
+<code>DBTest</code></p>
+<p>Once deployed, point a browser at
+<code>http://localhost:8080/DBTest/test.jsp</code> to view the fruits of
+your hard work.</p>
-<p><i>ToDo: Perhaps we could bundle a simple project and Ant buildfile to demonstrate?</i></p>
</subsection>
-
-<subsection name="Oracle 8i using Jakarta Commons Connection Pool">
+<subsection name="Oracle 8i">
<h3>0. Introduction</h3>
<p><i>We would appreciate comments on this section as I'm not an Oracle DBA :-)</i></p>
-<p>Oracle requires minimal changes from the mySQL configuration except for the usual gotchas :-) Firstly
+<p>Oracle requires minimal changes from the MySQL configuration except for the usual gotchas :-) Firstly
by default, Tomcat will only use <code>*.jar</code> files installed in <code>$CATALINA_HOME/common/lib</code>
therefore <code>classes111.zip</code> or <code>classes12.zip</code> will need to be renamed with a <code>.jar</code>
extension. Since jarfiles are zipfiles, there is no need to unzip and jar these files - a simple rename will suffice.
@@ -257,7 +393,7 @@
file. Here we define a Datasource called myoracle using the thin driver to connect as user scott, password tiger
to the schema called myschema in the sid called mysid. (Note: with the thin driver this sid is not the same as the tnsname)</p>
-<p>Use of the OCI driver should simply involve a changing thin to oci in the URL string.</p>
+<p>Use of the OCI driver should simply involve a changing thin to oci in the URL string.
<source>
<Resource name="jdbc/myoracle" auth="Container"
type="javax.sql.DataSource"/>
@@ -297,6 +433,8 @@
</parameter>
</ResourceParams>
</source>
+</p>
+
<h3>2. web.xml configuration</h3>
<p>You should ensure that you respect the elemeent ordering defined by the DTD when you
create you applications web.xml file.</p>
@@ -321,7 +459,7 @@
</subsection>
-<subsection name="PostgreSQL using Jakarta Commons Connection Pool">
+<subsection name="PostgreSQL">
<h3>0. Introduction</h3>
<p>PostgreSQL is configured in a similar manner to Oracle. Again, highlighting the differences.
These notes are untested as yet and we would appreciate feedback.</p>
@@ -377,18 +515,8 @@
</subsection>
</section>
-
-
-
-
-<section name="Non DBCP Solutions">
-<p>
-These solutions either utilise a single connection to the database (not recommended for anything other
-than testing!) or some other pooling technology.
-</p>
-</section>
-
<section name="Tyrex Connection Pool">
+
<subsection name="Introduction">
<p>
@@ -516,10 +644,14 @@
</p>
</subsection>
-
-
</section>
+<section name="Non DBCP Solutions">
+<p>
+These solutions either utilise a single connection to the database (not recommended for anything other
+than testing!) or some other pooling technology.
+</p>
+</section>
<section name="Oracle 8i with OCI client">
<subsection name="Introduction">
@@ -545,7 +677,8 @@
using <code>System.loadLibrary("ocijdbc8");</code>
</p>
<p>
-You should next create a simple test servlet or jsp that has these <b>critical lines</b>:
+You should next create a simple test servlet or jsp that has these
+<strong>critical lines</strong>:
</p>
<source>
DriverManager.registerDriver(new
@@ -588,6 +721,35 @@
</subsection>
</section>
+<section name="Common Problems">
+<p>Here are some common problems encountered with a web application which
+uses a database and tips for how to solve them.</p>
+
+<subsection name="Intermittent dB Connection Failures">
+<p>
+Tomcat runs within a JVM. The JVM periodically performs garbage collection
+(GC) to remove java objects which are no longer being used. When the JVM
+performs GC execution of code within Tomcat freezes. If the maximum time
+configured for establishment of a dB connection is less than the amount
+of time garbage collection took you can get a db conneciton failure.
+</p>
+
+<p>To collect data on how long garbage collection is taking add the
+<code>-verbose:gc</code> argument to your <code>CATALINA_OPTS</code>
+environment variable when starting Tomcat. When verbose gc is enabled
+your <code>$CATALINA_BASE/logs/catalina.out</code> log file will include
+data for every garbage collection including how long it took.</p>
+
+<p>When your JVM is tuned correctly 99% of the time a GC will take less
+than one second. The remainder will only take a few seconds. Rarely,
+if ever should a GC take more than 10 seconds.</p>
+
+<p>Make sure that the db connection timeout is set to 10-15 seconds.
+For the DBCP you set this using the parameter <code>maxWait</code>.</p>
+
+</subsection>
+
+</section>
+
</body>
</document>
-
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>