james-server-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Danny Angus <Danny_An...@slc.co.uk>
Subject Re: keeping a JDBC connection active
Date Thu, 15 Apr 2004 08:42:16 GMT




Rich,

> Anyhow, I have the impression that the Connection keeps track of its
> many individual PreparedStatements, like a mama knows her children
> apart, and never mixes up their interactions.  Do you think it might?
> I've never noticed any jumbling of data.

If you don't use transactions and transaction locking you'll most likely
never see a data issue.
If you do you may find your commits, rollbacks and timeouts messing with
the wrong things.

Even if you make no use of transactions, like James using MySQL, you will
find that using a single connection is a bottle neck as well as a single
point of failure.
Each request will be executed serially by the db, whereas using multiple
connections would allow requests to be executed in parallel.
If you have your DB and James on different boxes you may well find that the
network latency, rather than your inability to exploit DB multi-threading,
limits the rate at which James can execute statements.

Put simply using a single connection will mean that a statement returing a
large result set will block the connection while the data is transmitted,
whereas using multiple connections allows a second statement to be executed
while transmission of the first data-set is still occuring.

The solution this suggests is that you should use many connections. Of
course that means that you either open a whole lot of connections when you
start up, or that you have the added delay of waiting for a connection to
be made just-in-time. A set of timings I made for a former employer
suggests that the time to create a connection is of the order of 10-100
milliseconds (on the h/w we were using).

The solution to both of these is to use a pool of connections, the pool
should be set up in such a way as to maintain the optimum number of
connections for your use, often fewer than you might expect, and I favour a
pool which always carries an unused-spare or two to cope with sudden peaks
in demand. The pool should be capable of identifying bad connections and
removing them, and of pruning unused connections to keep the size to the
required amount. This last is to please DBA's who don't like you to just
grab hundreds of connections you don't actually need, especially if your
application is only one of many connecting to the db. My timings suggested
that getting a connection from a pool tuned to deal with the load it was
under took 1 millisecond or less, potentially 1000% improvement in
performance over creating new connections, without any loss of capacity or
resilience.

I suspect that not all of these issues are relevant to your situation, but
I hope that this insight will help you understand why pools are pretty much
de-rigeur these days.

d. (Who once wrote his own JDBC connection pooling :-)



***************************************************************************
The information in this e-mail is confidential and for use by the addressee(s) only. If you
are not the intended recipient (or responsible for delivery of the message to the intended
recipient) please notify us immediately on 0141 306 2050 and delete the message from your
computer. You may not copy or forward it or use or disclose its contents to any other person.
As Internet communications are capable of data corruption Student Loans Company Limited does
not accept any  responsibility for changes made to this message after it was sent. For this
reason it may be inappropriate to rely on advice or opinions contained in an e-mail without
obtaining written confirmation of it. Neither Student Loans Company Limited or the sender
accepts any liability or responsibility for viruses as it is your responsibility to scan attachments
(if any). Opinions and views expressed in this e-mail are those of the sender and may not
reflect the opinions and views of The Student Loans Company Limited.

This footnote also confirms that this email message has been swept for the presence of computer
viruses.

**************************************************************************


---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org


Mime
View raw message