You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by Saurabh Saraswat <ss...@pivotalindia.com> on 2014/03/31 13:05:47 UTC
Connection Pooling in Tomcat 6 using Java
Dear All,
I am doing connection pooling with tomcat 6. And i am doing this very first
time before today i had no idea about connection pooling. I want to ensure
that it is the correct way or not.
Please do me correct if i am doing wrong anywhere. I am explaining you all
steps done by me-
*1. Have created a context.xml*
<?xml version="1.0" encoding="UTF-8"?>
<Context>
<Resource name="jdbc/MaxDB" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000"
username="root" password="root"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:MySQL://localhost:3306/MaxDB?zeroDateTimeBehavior=convertToNull"/>
</Context>
*2. Mapping in web.xml*
<resource-ref>
<description>MySql DataSource</description>
<res-ref-name>jdbc/MaxDB</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
*3. Then on my servlet i am getting the object of connection like this-*
private static InitialContext ic;
protected static DataSource datasource;
private static Context ctx;
protected static Connection getConnection() throws DatabaseException
{
Connection conn = null;
try
{
ctx = new InitialContext();
datasource = (DataSource)
ctx.lookup("java:/comp/env/jdbc/MaxDB");
conn = datasource.getConnection();
}
catch (Exception ex)
{
}
return conn;
}
Is that it or we need to do anything else for connection pooling. As i
google then i found there is an API Commons DBCP so tomcat use it
internally or we have to do something with this.
Using this i am able to get the connection object.But at the second request
how i will validate that its taking the connection object from pool and not
creating the new con object. Even i am not sire that here i am using
connection pooling or getting object of connection simply using datasource.
Please assist me!
Thanking You!
*Best Regards, *
*Saurabh Sarasvat*
Re: Connection Pooling in Tomcat 6 using Java
Posted by Saurabh Saraswat <ss...@pivotalindia.com>.
Dear All,
Please accept my heartily thanks for your valuable responses.
*Daniel / Chris*,
Thank you so much. You both gave me a vary helpful explanation. I have read
many forums but still was confused but you guys have cleared my doubts and
also gave me new ideas to do better.
Thank you again.
*Best Regards, *
*Saurabh Sarasvat*
On Mon, Mar 31, 2014 at 6:50 PM, Daniel Mikusa <dm...@gopivotal.com>wrote:
> On Mar 31, 2014, at 7:05 AM, Saurabh Saraswat <ss...@pivotalindia.com>
> wrote:
>
> > Dear All,
> >
> > I am doing connection pooling with tomcat 6. And i am doing this very
> first
> > time before today i had no idea about connection pooling. I want to
> ensure
> > that it is the correct way or not.
> > Please do me correct if i am doing wrong anywhere. I am explaining you
> all
> > steps done by me-
> >
> > *1. Have created a context.xml*
>
> Using "conf/context.xml" works, but it will create the resource that you
> define for every application that you deploy to Tomcat. Sometimes this is
> the desired effect and sometimes this ends up creating a lot of extra pools
> that are not needed.
>
> If you want to create a pool for one app, you can put it in
> "conf/Catalina/localhost/<app>.xml" or inside your WAR file at
> "META-INF/context.xml". These are locations for context configuration that
> is specific to an application and when resources are placed in one of these
> two locations (don't put them in both), the resource will only be created
> once, for the specific app.
>
> Alternatively, you can put your resource definitions in "conf/server.xml"
> (inside the GlobalNamingResources block) and it'll allow you to create one
> pool and share it across multiple applications. The nice thing about this
> approach is that with multiple applications using the same pool, you can
> generally use your connections more efficiently.
>
> Which one you pick depends on your environment and what makes sense there.
>
> >
> > <?xml version="1.0" encoding="UTF-8"?>
> >
> > <Context>
> > <Resource name="jdbc/MaxDB" auth="Container" type="javax.sql.DataSource"
> > maxActive="100" maxIdle="30" maxWait="10000"
> > username="root" password="root"
> > driverClassName="com.mysql.jdbc.Driver"
> >
> >
> url="jdbc:MySQL://localhost:3306/MaxDB?zeroDateTimeBehavior=convertToNull"/>
> >
> > </Context>
>
> Looks OK.
>
> >
> > *2. Mapping in web.xml*
> >
> > <resource-ref>
> > <description>MySql DataSource</description>
> > <res-ref-name>jdbc/MaxDB</res-ref-name>
> > <res-type>javax.sql.DataSource</res-type>
> > <res-auth>Container</res-auth>
> > </resource-ref>
>
> I don't believe that this is needed by Tomcat.
>
> >
> > *3. Then on my servlet i am getting the object of connection like this-*
> >
> > private static InitialContext ic;
> > protected static DataSource datasource;
> > private static Context ctx;
> >
> > protected static Connection getConnection() throws DatabaseException
> > {
> > Connection conn = null;
> > try
> > {
> > ctx = new InitialContext();
> > datasource = (DataSource)
> > ctx.lookup("java:/comp/env/jdbc/MaxDB");
> > conn = datasource.getConnection();
> > }
>
> I didn't run this code, but at a glance it looks OK.
>
> > catch (Exception ex)
> > {
> >
> > }
> >
> > return conn;
> > }
> >
> > Is that it or we need to do anything else for connection pooling. As i
> > google then i found there is an API Commons DBCP so tomcat use it
> > internally or we have to do something with this.
>
> Yes. Tomcat will use DBCP internally. There's nothing additional you
> need to do, just define your resources.
>
> If you want to use a different connection pool, you can do that. You just
> need to specify the "factory" attribute and the class name of the factory
> to use to create the pool. Another commonly used pool is Tomcat's
> jdbc-pool, which ships as a second option in Tomcat 7.
>
> > Using this i am able to get the connection object.But at the second
> request
> > how i will validate that its taking the connection object from pool and
> not
> > creating the new con object. Even i am not sire that here i am using
> > connection pooling or getting object of connection simply using
> datasource.
>
> You can connect with jconsole / jvisualvm and look at the mbeans. Tomcat
> exports mbeans for the resources that you define. Through them you can see
> the stats for your connection pool.
>
> Dan
>
> >
> > Please assist me!
> >
> > Thanking You!
> >
> > *Best Regards, *
> >
> > *Saurabh Sarasvat*
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
Re: Connection Pooling in Tomcat 6 using Java
Posted by Daniel Mikusa <dm...@gopivotal.com>.
On Mar 31, 2014, at 7:05 AM, Saurabh Saraswat <ss...@pivotalindia.com> wrote:
> Dear All,
>
> I am doing connection pooling with tomcat 6. And i am doing this very first
> time before today i had no idea about connection pooling. I want to ensure
> that it is the correct way or not.
> Please do me correct if i am doing wrong anywhere. I am explaining you all
> steps done by me-
>
> *1. Have created a context.xml*
Using “conf/context.xml” works, but it will create the resource that you define for every application that you deploy to Tomcat. Sometimes this is the desired effect and sometimes this ends up creating a lot of extra pools that are not needed.
If you want to create a pool for one app, you can put it in “conf/Catalina/localhost/<app>.xml” or inside your WAR file at “META-INF/context.xml”. These are locations for context configuration that is specific to an application and when resources are placed in one of these two locations (don’t put them in both), the resource will only be created once, for the specific app.
Alternatively, you can put your resource definitions in “conf/server.xml” (inside the GlobalNamingResources block) and it’ll allow you to create one pool and share it across multiple applications. The nice thing about this approach is that with multiple applications using the same pool, you can generally use your connections more efficiently.
Which one you pick depends on your environment and what makes sense there.
>
> <?xml version="1.0" encoding="UTF-8"?>
>
> <Context>
> <Resource name="jdbc/MaxDB" auth="Container" type="javax.sql.DataSource"
> maxActive="100" maxIdle="30" maxWait="10000"
> username="root" password=“root"
> driverClassName="com.mysql.jdbc.Driver"
>
> url="jdbc:MySQL://localhost:3306/MaxDB?zeroDateTimeBehavior=convertToNull"/>
>
> </Context>
Looks OK.
>
> *2. Mapping in web.xml*
>
> <resource-ref>
> <description>MySql DataSource</description>
> <res-ref-name>jdbc/MaxDB</res-ref-name>
> <res-type>javax.sql.DataSource</res-type>
> <res-auth>Container</res-auth>
> </resource-ref>
I don’t believe that this is needed by Tomcat.
>
> *3. Then on my servlet i am getting the object of connection like this-*
>
> private static InitialContext ic;
> protected static DataSource datasource;
> private static Context ctx;
>
> protected static Connection getConnection() throws DatabaseException
> {
> Connection conn = null;
> try
> {
> ctx = new InitialContext();
> datasource = (DataSource)
> ctx.lookup("java:/comp/env/jdbc/MaxDB");
> conn = datasource.getConnection();
> }
I didn’t run this code, but at a glance it looks OK.
> catch (Exception ex)
> {
>
> }
>
> return conn;
> }
>
> Is that it or we need to do anything else for connection pooling. As i
> google then i found there is an API Commons DBCP so tomcat use it
> internally or we have to do something with this.
Yes. Tomcat will use DBCP internally. There’s nothing additional you need to do, just define your resources.
If you want to use a different connection pool, you can do that. You just need to specify the “factory” attribute and the class name of the factory to use to create the pool. Another commonly used pool is Tomcat’s jdbc-pool, which ships as a second option in Tomcat 7.
> Using this i am able to get the connection object.But at the second request
> how i will validate that its taking the connection object from pool and not
> creating the new con object. Even i am not sire that here i am using
> connection pooling or getting object of connection simply using datasource.
You can connect with jconsole / jvisualvm and look at the mbeans. Tomcat exports mbeans for the resources that you define. Through them you can see the stats for your connection pool.
Dan
>
> Please assist me!
>
> Thanking You!
>
> *Best Regards, *
>
> *Saurabh Sarasvat*
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Connection Pooling in Tomcat 6 using Java
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256
Saurabh,
On 3/31/14, 7:05 AM, Saurabh Saraswat wrote:
> I am doing connection pooling with tomcat 6. And i am doing this
> very first time before today i had no idea about connection
> pooling. I want to ensure that it is the correct way or not.
Glad to see you are taking a step in writing a scalable web
application. Connection pooling helps you scale better by both
reducing the time it takes to connect to your database and also by
limiting the number of concurrent connections to the database (which
keeps it healthy).
> Please do me correct if i am doing wrong anywhere. I am explaining
> you all steps done by me-
>
> *1. Have created a context.xml*
>
> <?xml version="1.0" encoding="UTF-8"?>
>
>
> <Context> <Resource name="jdbc/MaxDB" auth="Container"
> type="javax.sql.DataSource" maxActive="100" maxIdle="30"
> maxWait="10000" username="root" password="root"
> driverClassName="com.mysql.jdbc.Driver"
>
> url="jdbc:MySQL://localhost:3306/MaxDB?zeroDateTimeBehavior=convertToNull"/>
>
> </Context>
>
> *2. Mapping in web.xml*
>
> <resource-ref> <description>MySql DataSource</description>
> <res-ref-name>jdbc/MaxDB</res-ref-name>
> <res-type>javax.sql.DataSource</res-type>
> <res-auth>Container</res-auth> </resource-ref>
>
> *3. Then on my servlet i am getting the object of connection like
> this-*
>
> private static InitialContext ic; protected static DataSource
> datasource; private static Context ctx;
>
> protected static Connection getConnection() throws
> DatabaseException { Connection conn = null; try { ctx = new
> InitialContext(); datasource = (DataSource)
> ctx.lookup("java:/comp/env/jdbc/MaxDB"); conn =
> datasource.getConnection(); } catch (Exception ex) {
>
> }
>
> return conn; }
Looks good so far. When you're done with the Connection object, just
call close() on it and it will be returned to the pool. Make sure to
do it in a "finally" block. (I have lots more tips for JDBC
connections on an old blog post I wrote which can be found here:
http://blog.christopherschultz.net/index.php/2009/03/16/properly-handling-pooled-jdbc-connections/)
> Is that it or we need to do anything else for connection pooling.
> As i google then i found there is an API Commons DBCP so tomcat use
> it internally or we have to do something with this.
You do not need to use any special APIs beyond what you have above:
the JNDI stuff and JDBC.
> Using this i am able to get the connection object.But at the second
> request how i will validate that its taking the connection object
> from pool and not creating the new con object. Even i am not sire
> that here i am using connection pooling or getting object of
> connection simply using datasource.
MySQL/Maria support the SLEEP() function in queries. Try this.
Configure your pool with *only* a single connection (which you should
always do in development anyway), then issue a "SELECT SLEEP(3)" from
your web application. While the thread is sleeping, do a "SHOW
PROCESSLIST" from the MySQL CLI. You'll see the connection id along
with the query being executed.
Then when the request completes (after ~3 seconds), re-run "SHOW
PROCESSLIST" to convince yourself that there are no queries running.
Then, make the request again to execute the "SELECT SLEEP(3)" and do
"SHOW PROCESSLIST" in the MySQL CLI again: you should see the same
connection id running the query.
You could also try to load-test your web application with a single
connection in the pool and then go ask MySQL to SHOW PROCESSLIST, and
you should see only a single query executing at a time.
- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1
Comment: GPGTools - http://gpgtools.org
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/
iQIcBAEBCAAGBQJTOdppAAoJEBzwKT+lPKRYXioP/jza7PD2N+O0Y7VBCnA7UDob
aAkF0boQ242ZtZQh2LkdRISxSp03ZO/8x+w504so/hTJ/1nxHvl+RvRhuDqfaxYP
pzA5MjxN72h2NGf5IYNVmALNZCJI8HlC45UDQ762VnHAGy6ZKv4HZUcvKhkR++Xl
BPtVAUO6g9AI8BCbe+j9fgHpwMCd12KyD3bxFFUxLh1ZP1Y7FH8gThHyE9d0NzP8
iuJgaLvIs/Be1OAlogq95H45d4sO7MNMPqo4OsDafW4RNOAOHFfVGx5IIp2vVVRJ
QABUzZNUiQakCmOTJu5q/NMx6PpN71qxvakVofAk00ZzT9wzbuHZmE3vTnBH1gTQ
eyY6mJskqv8jKKeogtCtpeeLEPQxLpy/fWL289jsFJSDIq3HZJWkKcIbbOywjLnP
X7PQ/wAACQ4sm0ieUrz/Vytd9k59+bjiW/Q0GzmZvRPf7IZhHcjsY4Q/rIRRo7Y4
f1V8llfOdfDSv1kaD0oD1dsLoTEmsWGMKK05PoF4EcXRTpBxbNpg5H5cTLmuLHSr
U8zHnHayHJ/rJcj+raeYTj/dqLhYnTZGyCnn7gNdQZJssCoTzeUU7Ay8kw4gDghY
Vr1fbbz7XOz9cO9vm0M/ornb5DvU3QbFamQgymTjRUvLCQ9eMhsyH9qT5OtCh6PR
MIdAkqbnmCTSlqHMrPdj
=xz7b
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org