Database connection pooling on Phenoscape

[Cartik]

Hi,

I implemented a very basic connection management technique for the
data services on the Phenoscape application. Each data service (REST
resource) instantiates a shard, and connects this shard to the
database using the 'connect' method from AbstractSQLShard class. This
happens when the router invokes the data service. After execution of
the query, and just before returning the results, the resource
disconnects the shard from the database and dereferences the Shard
variable. I also set up this shard disconnection and dereferencing in
the finally block of the getRepresentation() method in each of the
data services.

However, on running stress tests on the application, which involved 30
different threads running very expensive queries and performing post-
retrieval processing on the server side, these threads began to
overload the server with Java heap space errors. After the stress
tests finished, I found a lot of PostgreSQL connections sitting idle
for several minutes before being shut down. Basically, PostgreSQL
gives out 100 connections and when they don't die out when heap space
errors occur, subsequent requests begin to hang and then time out. The
JBoss server pretty much goes nuts trying to find a connection for the
new requests long after they have timed out on the front end.

Is there a way to get around this problem? We've investigated using
the DataSource class for connecting pooling and management but the
connect() method is actually implemented in the OBDSQLDatabaseAdapter
class from OBO project and things will have to be changed here. Jim,
Vladimir (from our Informatics group), and I have discussed this at
length, and we would love to hear your thoughts on this. It is too
late for us to implement any long term solution to this issue before
our public release on Saturday, but it is high on my agenda to fix
right afterwards.

Cheers,

Cartik