Database Connection Pooling

[jboss_ashutosh]

We have an application which is NOT running of a connection pool right now. The setup is as below.

There could potentially be upto 1000 clients with upto 20-30 users per client. There is one database for every client. This architectural decision is a legacy and cannot be undone. Only one instance of application server serving all the clients.

For the above setup, we need to pool the database connections in the application server. However, we are foreseeing some problems

1. If we setup one pool for every client, we would end up with 1000 pools and if every pool is caped at 20 connections, we may end up with 20K connections across various pools in the application server. We are worried that this may take too many Application server resources to bring it down. Are there any statistics available on how many connections can JBoss pool without degraring? Or is it purely a function of hardware resources?

2. Is there a way in JBoss to setup a single pool of connections for multiple databases? This would allow better usage of connections and we may be able to deal with fewer connections as it can be assumed that not all the clients will be active all the time.

Any ideas on above would be appreciated.

Regards,

Ashutosh

[wdfink]

ATM I don't understand your requirement in total.

What you mean by 1000clients with up to 20-30 users?

-> 20-30 different logins to the database?

-> different databases?

-> do you run transactional accross the connections

-> where do you open the connection? within an EJB?

[peterj]

Even if you have 20-30 users per client you will not need that many connections for each client. Possibly 1 or 2 connections will be sufficient per client. This is assuming that the app doesn't hold the connection for very long. For example, if the app does a quick lookup (or update) and replies, then that is OK. If, however, the app sends many seconds pulling lots of information out of the database to do a report, then setting a low connection count will not work.

Let's assume that 2 connections per client are OK. This means about 2000 database connections. You never said which database you are using, but I suspect that this should not be that much of an issue. But it all depends on the database and the hardware on which it is running. Your database server will need lots of RAM and multiple CPUs. (About 10 years ago I saw a recommendation to limit connections to MySQL to around 500. But performance has improved dramatically since then so I suspect that on decent hardware that MySQL could handle 2000 connections. Especially since not all 2000 will be used simultaneously.)

I have seen other posts where people have figured out how to connect to a single database using differnet user ids, and place that all into the same pool. I don't recall anything about using different databases. But either your database itself, or the JDBC driver for the database might support this. For example, with MySQL you could use the "use xxx" command to change which database is being used by a connection when the connection is obtained from the pool.