Slightly OT: Ad hoc connection pools

[Daniel Jue <te...@gmail.com>]

Hi all, I'm hoping someone might have a suggestion for a strange
challenge.  I'm using both T5 and GWT for the app, but I imagine the
core solution to be independent of either, although for implementation
T5 IOC may be called in.

OK here it is:
I am working on a web app where the app's server side makes
connections to hundreds of databases.
Yes, that's right.

Use case is like this:
User logs into main app, gets a user ASO, etc.
User can enter their personal login/password info for a database, and
select a database from a list.
(The database list items correspond to some hard coded connection
info, like the machine and port, etc.)
Once the connection info has been verified to work by making a test
connection, the connection info is saved as an ASO.
Then for each request, whatever DAO is being used will create a
connection using the info, do stuff, and then close the connection.
A user can only be logged into one of these databases at a time, and
they can change the settings to log into a different db, etc.
The database username and password is different for each user and each database.
There would probably be about 20 users online on average, with a peak of 100.

Right now I'm making Ad Hoc connections using a handmade jdbc
connection factory, and then i close the connection after each
request.
It's painfully slow and error prone, since the connections sometimes
fail to get created.

What I am wondering about is a way to assign a (small) connection pool
for each user that's logged into the system, which at the very least
would be discarded when they timeout or logout.   Custom use of DBCP?
Custom connection pool?  I'm thinking one or two connections per pool,
because I fear the memory requirements.

I'm using Tomcat, which has a read only JNDI, afaict.

Please let me know if you've faced a problem like this before, or how
you would approach it.

Thanks, Dan

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tapestry.apache.org
For additional commands, e-mail: users-help@tapestry.apache.org

Re: Slightly OT: Ad hoc connection pools

[Ville Virtanen <vi...@cerion.fi>]

Hi,

I would probably create service that handles the connection pooling by using
multiple pools that are backed up by some third party lib.

Service would take care of closing pools when the registry shuts down or the
pool has remained unused for certain period. Don't know how heavy it is to
initialize pools / how much memory will those pools use? Also it could be
done so that the user can have only one pool at a time to limit resource
usage.

The service could use ApplicationStateManager to check the user and
connection data from session transparently. Pseudo service code for one pool
/ user:

public Connection getConnection() {
 1. get user object from session
 2. get connection data from session
 3. get pool from hashtable by user id 
 4. check if the pool matches the connection data
     - if it does, update last access time ->  return connection from pool
     - if it does NOT or is null -> closePool(); + create and test pool, add
to hashtable, update last access time ->  return
}

public void closePool() { 
 1. get user data from session
 2. get pool from hashtable by user id
 3. if exists -> close, remove from hashtable
}

Of course the pool must be enclosed in class that contains last access
milliseconds, creation milliseconds, the actual pool, and the connection
data.

Also make a method that loops through all pools and closes and discards all
pools that have been unused for 30 mins or something to close connections
for users that didn't do logout. Then use for an example quartz to run that
method every 5 minutes or something. (http://www.opensymphony.com/quartz/)

The last thing is to implement the registry shutdown method which closes all
pools.

Example how to use the commons pool in java code:
http://svn.apache.org/viewvc/commons/proper/dbcp/trunk/doc/ManualPoolingDriverExample.java?revision=821733&view=markup

Tapestry 5 Quartz:
http://www.chenillekit.org/chenillekit-quartz/index.html

 - Ville


Daniel Jue wrote:
> 
> Hi all, I'm hoping someone might have a suggestion for a strange
> challenge.  I'm using both T5 and GWT for the app, but I imagine the
> core solution to be independent of either, although for implementation
> T5 IOC may be called in.
> 
> OK here it is:
> I am working on a web app where the app's server side makes
> connections to hundreds of databases.
> Yes, that's right.
> 
> Use case is like this:
> User logs into main app, gets a user ASO, etc.
> User can enter their personal login/password info for a database, and
> select a database from a list.
> (The database list items correspond to some hard coded connection
> info, like the machine and port, etc.)
> Once the connection info has been verified to work by making a test
> connection, the connection info is saved as an ASO.
> Then for each request, whatever DAO is being used will create a
> connection using the info, do stuff, and then close the connection.
> A user can only be logged into one of these databases at a time, and
> they can change the settings to log into a different db, etc.
> The database username and password is different for each user and each
> database.
> There would probably be about 20 users online on average, with a peak of
> 100.
> 
> Right now I'm making Ad Hoc connections using a handmade jdbc
> connection factory, and then i close the connection after each
> request.
> It's painfully slow and error prone, since the connections sometimes
> fail to get created.
> 
> What I am wondering about is a way to assign a (small) connection pool
> for each user that's logged into the system, which at the very least
> would be discarded when they timeout or logout.   Custom use of DBCP?
> Custom connection pool?  I'm thinking one or two connections per pool,
> because I fear the memory requirements.
> 
> I'm using Tomcat, which has a read only JNDI, afaict.
> 
> Please let me know if you've faced a problem like this before, or how
> you would approach it.
> 
> Thanks, Dan
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tapestry.apache.org
> For additional commands, e-mail: users-help@tapestry.apache.org
> 
> 
> 

-- 
View this message in context: http://www.nabble.com/Slightly-OT%3A-Ad-hoc-connection-pools-tp25931113p25938928.html
Sent from the Tapestry - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tapestry.apache.org
For additional commands, e-mail: users-help@tapestry.apache.org