File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Tomcat and the fly likes Tomcat connection pooling and multiple dbs Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Products » Tomcat
Bookmark "Tomcat connection pooling and multiple dbs" Watch "Tomcat connection pooling and multiple dbs" New topic
Author

Tomcat connection pooling and multiple dbs

Michael J. Makunas
Ranch Hand

Joined: Mar 11, 2002
Posts: 37
I currently have a system where we're using Tomcat's db connection pooling (with MySql). We're still having db performance issues so we're considering having multiple instances of our db. Is there a way to set up Tomcat to use a pool that connects to multiple dbs (assuming, for now, that keeping db instances in sync isn't a problem)?
[ September 14, 2004: Message edited by: Michael J. Makunas ]
somkiat puisungnoen
Ranch Hand

Joined: Jul 04, 2003
Posts: 1312
You refer to one pool can have connection to all database or not ???

or


you want to create connection pool for all database ??

Such pool 1 refer to MYSQL
pool 2 refer to Oracle


SCJA,SCJP,SCWCD,SCBCD,SCEA I
Java Developer, Thailand
Michael J. Makunas
Ranch Hand

Joined: Mar 11, 2002
Posts: 37
What I mean is, that given the following assumptions:

1) I have 2 identical MySql databases running on two seperate servers.
2) These databases will allways be in sync and identical


Is there a way to set up Tomcat's conntion pooling so that when the app gets a connection from the pool it could be a connection to either of the dbs? Thus, distributing the load between 2 db servers.
friso dejonge
Ranch Hand

Joined: Jul 11, 2002
Posts: 162
maybe in this case you are suggesting that the connection is the problem, but how did you come to that conclusion ? Normally it is not just the connection that is the problem, but the joins (or whole table scans) of the db queries.
So how did you get to that conclusion ?


swimming certificate (A & B), shoelaces diploma, and some useless java ones.
Michael J. Makunas
Ranch Hand

Joined: Mar 11, 2002
Posts: 37
Originally posted by friso dejonge:
maybe in this case you are suggesting that the connection is the problem, but how did you come to that conclusion ? Normally it is not just the connection that is the problem, but the joins (or whole table scans) of the db queries.
So how did you get to that conclusion ?


I'm not asking for help with optimizing the db. I'm trying to find out if there is a way to set up Tomcat's connection pool to contain connections to multiple identical databases.
gayathri sridhar
Greenhorn

Joined: Sep 15, 2004
Posts: 13
Originally posted by Michael J. Makunas:
What I mean is, that given the following assumptions:

1) I have 2 identical MySql databases running on two seperate servers.
2) These databases will allways be in sync and identical


Is there a way to set up Tomcat's conntion pooling so that when the app gets a connection from the pool it could be a connection to either of the dbs? Thus, distributing the load between 2 db servers.


uhhm, i have seen something close to this called multipools in weblogic where u have a primary pool and then you can bring one database server down(and hence the pool) and the datasource automatically points to the back up pool after that.
The datasource through which you connect to the database needs to be configured with a multipool. But the one you are looking for..
best left to the author / tomcat experts to answer.

So you want to distribute load only at the db level and not cluster the tomcat servers and distribute it that way. What you are looking for should be possible within a tomcat cluster.
somkiat puisungnoen
Ranch Hand

Joined: Jul 04, 2003
Posts: 1312
Is there a way to set up Tomcat's conntion pooling so that when the app gets a connection from the pool it could be a connection to either of the dbs? Thus, distributing the load between 2 db servers.


In my experience,

I never set Connection Pool in Tomcat hold connection of multi-database.

But i have some solution to solve this problem.

You can manage connection pool in your code.

Connection Pool Pattern
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Tomcat connection pooling and multiple dbs