Stijn de Witt wrote:
We have developed a web application using OJB. All seems to work fine,
but after some hours of being deployed we run into a problem.
<snip/>
java.io.EOFException
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1394)
<snip/>
I have seen a number of solutions mentioned:
1) Specify the MySQL autoReconnect="true" property in the connection
string:
jdbc:mysql://localhost:3306/<database name>?autoReconnect=true
This is a feature specific to the MySQL JDBC-driver ("MySQL Connector/J"),
since you specify this in the JDBC URL it will work for every JDBC-
client connecting to your MySQL server (including OJB).
To relate to the Commons Pool configuration options, this would be
equivalent to something like "testJustAfterBorrow=true".
2) Make sure the used pool drops idle connections before MySQL does
This is the so called evictor thread solution, and is best for optimal
response times when something is about to happen in your application.
You configure this with the following options in Commons Pool:
validationQuery=SELECT 1 (for MySQL, a simple statement that returns 1 row)
testWhileIdle=true (activates the evictor thread)
timeBetweenEvictionRunsMillis=<no of millisecs between eviction runs>
numTestsPerEvictionRun=<no of Connection to check each run>
minEvictableIdleTimeMillis=<min no of passive msecs before eligable for
eviction>
When you set testWhilIdle=true and activate the separate evictor thread,
an event will be triggered each [timeBetweenEvictionRunsMillis] millisecons.
When this event ("eviction run") is triggered, the following will happen:
1. the evictor thread grabs [numTestsPerEvictionRun] Connection instances
from the pool
2. for each Connection that has been idle in the pool more than
[minEvictableIdleTimeMillis], the validation query will be performed
3. for each Connection where the validation query failed, discard the
instance from pool and re-evaluate minIdle
4. if idle threshold is now below minIdle, new Connection instances are
fed into the pool
If you don't specify minIdle, step #4 is not performed.
3) Make sure the connection is validated before being used with a
validation query
This is the testOnBorrow=true setting, validationQuery is specified as
above.
The exact same procedure as in the idle object evictor thread will happen
when OJB tries to borrow/use a Connection from the pool.
4) Disable pooling with ConnectionFactoryNotPooledImpl
Bad! Imagine that your OJB application in "rapid fire" needs to open and
close Connection instances. If, for each borrow of a Connection the TCP/IP
socket + MySQL overhead has to be performed your application performance
will be degraded severely.
Additional to this, when creating new Connection instances the Java virtual
machine has to manage more garbage collection / memory allocation and the JDBC
driver has to perform static initializer code when creating the connections.
Not using connection pooling is more for testing or debugging I would say,
always use some sort of pooling in a production environment.
5) Use a DataSource managed by the AppServer (Tomcat / JBoss) i.c.w JNDI
lookup
This is a re-iteration of your suggestion 1-4, only that you move
configuration
responsibility from OJB properties and repository to the AppServer.
The obvious advantage is that several apps can use the same pool, centrally
configured in the AppServer.
Now I am getting a bit lost in all these options, and testing them is
very hard. Does anyone here have experience with this issue and know of
an easy way to fix it? Option 1) with autoReconnect seems most
attractive to me, but how should I set such a connection parameter using
OJB? Is it even possible?
Like Danilo pointed out, this is well possible with OJB and is almost
identical to setting:
validationQuery=SELECT 1
testOnBorrow=true
So you should never combine autoReconnect for MySQL JDBC URL and testOnBorrow
for a Commons Pool-based Connection pool (like OJB's default- and DBCP-based
pools). If you do, you will just create additional overhead by checking the
same things twice.
The advantage of mastering the Commons Pool options is that this will work
with all database servers that OJB supports, not just MySQL.
A few general hints to choose between all those different options:
1. You consider Connection management overhead to be a minimal bottleneck
in your application and don't want to care too much. You just want
to guarantee that the Connection instance OJB gets from the pool is
not broken after 8 idle hrs to avoid exceptions.
Use:
testOnBorrow=true testOnReturn=false testWhileIdle=false
2. You are aware that Connection instances will often be idle long enough
for MySQL to drop the connection and you want instant response times
in your application once it "wakes up" again.
Use:
testOnBorrow=true testOnReturn=false testWhileIdle=true
Just don't set the timeBetweenEvictionRunsMillis to low, since the
evictor thread will then become a bottleneck when it issues the
validation query the whole time.
I think the key issue is to set testOnBorrow=true or the MySQL auto-
reconnect so that you don't have to change your Java logic to re-try
OJB operations when connections fail.
If blazing fast response times are a priority and you can live with
the optimistic approach (ie you believe connections will seldom be
dropped) and your program can take the occasional exception, then
you want to turn testOnBorrow off and set testOnReturn=true instead.
Hope this was not too much info and that it did not confuse you more!
Regards,
Martin
P.S. The difference between the default OJB connection pool and the
DBCP-based one is minimal, since both are based on the Jakarta Commons Pool
package for the actual object pool management. DBCP has some additional
configuration for detecting bad programming patterns (connections
not returned to pool, which in the case of OJB means you don't close
your brokers). See the abandoned config stuff at the bottom of:
http://jakarta.apache.org/commons/dbcp/configuration.html
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
