Tomcat connection pooling and multiple dbs

[Michael J. Makunas]

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]

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

[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.

[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 ?

[Michael J. Makunas]

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]

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]

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