commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dirk Verbeeck <>
Subject Re: [DBCP]: How to obtain the best performance using DBCP when there are many databases.
Date Thu, 11 Mar 2004 21:57:22 GMT
Very interesting problem, 3,000,000 tables for one application is a lot.

In this case I would create the 100 BasicDataSources manually (one for
each database) and put all of them in a hashtable.
(databasename => datasource)
You also need to keep track of in witch database each user is.
(username => databasename)

Now if you need a connection for a user you first lookup the
databasename and then the datasource and call getconnection on it.

In theory this should be possible but you may reach some OS limits
(max open files/sockets).
If this happens then a more complex solution is needed.
Using a KeyedObjectPool you can set a maximum number of connection
accross all databases but this will require more work.

The biggest challenge will be keeping memory usage of all those
databases under control.

PS: if you can switch to oracle as database then you have more options
to solve this probem (but maybe this legacy system requires MySQL)

Good luck

Mark.he wrote:

> Hi All,
> I have been suffering for several days because of an database problem.
> There is a legacy system need to be extended. The system creates 50 tables when creates
a new user. 
> That is, if 60,000 users are required, 3,000,000 tables will be created. I'm using MySQL,
so it's impossible to create so many tables in one database.
> Because the system data structure can't be modified, I'd like to create multiple databases
to solve this problem. (Any suggestion about this problem are appreciated)
> But another problem arises. If I create 100 databases, how can I maintain so many connection
> Is maintaining 100 connection pools feasible?
> How can I obtain the best performance in this case?
> Any suggestion, any similar experience will be valuable to me. 
> I'm looking forward to your reply.
> Thanks a lot,
> Mark.Ho

To unsubscribe, e-mail:
For additional commands, e-mail:

View raw message