ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Poitras Christian" <Christian.Poit...@ircm.qc.ca>
Subject RE: autoReconnect=true doesn't solve closed connection problem
Date Fri, 12 May 2006 16:28:54 GMT
Hi Tony.
 
Your solution seems ok.
In fact, the settings must be made to minimise the impact of ping query
on performance depending on your specific situation.
It depends mostly on the stability of your MySQL server.
The settings I use seems to have a good impact on my system, but my
system doesn't require to have optimal performance all the time, which
makes my settings ok. You will have to find the best settings for you.
 
Christian

________________________________

From: Tony Qian [mailto:daqiqian2@aol.com] 
Sent: Friday, 12 May 2006 11:49
To: user-java@ibatis.apache.org
Subject: RE: autoReconnect=true doesn't solve closed connection problem


Poitras,

Thanks for response. If we enable pingQuery and have following setting:

   <property name="Pool.PingConnectionsOlderThan" value="2000"/>
   <property name="Pool.PingConnectionsNotUsedFor" value="2000"/>


my understanding is that iBATIS will use pingQurey first for every query
if the connection is older than 2000 (millisec) or NotUsedFor 2000. It
will have some impact on performance. 


I believe that autoReconnect will have less impact than pingQuery, even
though autoReconnect has its other impact.

If we have to use pingQuery, my question is what are the optimum
settings for Pool.PingConnectionsOlderThan and PingConnectionsNotUsedFor
so that it has minimum impact on performance( Let's say I know that
connection will be closed if connection idles for more than  8 hours).

my believe is to set "Pool.PingConnectionsOlderThan" value="0" and
PingConnectionsNotUsedFor" value="28800000" ( 28800000 is 8 hours). if
connection idles more than 8 hours, i know it is closed by MySql server.
I need to use pingQuery to remove it from connection pool. Otherwise,
don't do pingQuery.

Thanks,
Tony

Poitras Christian wrote on 5/11/2006, 1:19 PM: 

	
	iBatis automatically handle reconnection to databse on
connection failure.
	
	 
	
	To use this possibiliy, user can define a certain number of
variables in your sqlmapconfig.
	
	 
	
	Here is an example.
	
	<transactionManager type="JDBC">

	<dataSource type="SIMPLE">

	<property value="${driver}" name="JDBC.Driver"/>

	<property value="${url}" name="JDBC.ConnectionURL"/>

	<property value="${username}" name="JDBC.Username"/>

	<property value="${password}" name="JDBC.Password"/>

	<property value="${pingquery}" name="Pool.PingQuery"/>

	<property value="${pingenable}" name="Pool.PingEnabled"/>

	<property value="${pingoldertime}"
name="Pool.PingConnectionsOlderThan"/>

	</dataSource>

	</transactionManager>

	
	
	 
	
	 
	
	Make sure the property files contains all the properties
specified. I use.
	
	
	driver=com.mysql.jdbc.Driver

	url=jdbc:mysql://localhost:3306/data

	username=root

	password=

	pingquery=SELECT 1

	pingenable=true

	pingoldertime=2000

	
	 
	
	
	
	
	
________________________________

	From: Tony Qian [mailto:daqiqian2@aol.com] 
	Sent: Thursday, 11 May 2006 12:22
	To: user-java@ibatis.apache.org
	Subject: autoReconnect=true doesn't solve closed connection
problem
	
	
	All,
	
	I set following in SglMapConfig.xml file:
	
	 <property name="JDBC.ConnectionURL"
value="jdbc:mysql://hostName:3306/dbName?autoReconnect=true"/>
	
	It doesn't solve Communication Failure problem due to closed
connection. Setting autoReconnection=true works when connection pool is
managed by Tomcat (DBCP). (btw, PingQuery solves the problem)
	
	database is MySql 5.019, JDBC diver is
mysql-connector-java-3.1.11.
	
	Did I do something wrong? any suggestion?
	
	thanks,
	Tony
	
	
	


Mime
View raw message