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 Dirk 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 pools? > 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
