Have been dealing with a war based app which basically a legacy RESTful API consists of:
- Java 1.7
- Spring 4.0.3.RELEASE
- Tomcat 7
- MySQL 5
We're using Spring JDBC and not closing any connections through try / catch / finally statements.
Our datasource configuration file looks like this:
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd">
<bean id="propertyPlaceholder"
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="ignoreResourceNotFound" value="true" />
<property name="systemPropertiesModeName" value="SYSTEM_PROPERTIES_MODE_OVERRIDE" />
<property name="location" value="file:/opt/db/database.properties"></property>
</bean>
<!-- Initialization for data source dbcp -->
<bean id="dataSourceStore" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName"><value>${db.driver}</value></property>
<property name="url"><value>${db.mydb.url}</value></property>
<property name="username"><value>${db.username}</value></property>
<property name="password"><value>${db.password}</value></property>
<property name="maxIdle" value="10" />
<property name="maxActive" value="50" />
<property name="maxWait" value="100" />
<property name="defaultAutoCommit" value="false" />
<property name="removeAbandoned" value="true" />
<property name="removeAbandonedTimeout" value="1" />
<property name="minIdle" value="0"></property>
<property name="timeBetweenEvictionRunsMillis" value="1000"></property>
<property name="minEvictableIdleTimeMillis" value="1000"></property>
</bean>
</beans>
We import our configuration like this:
@Repository
public class StoreDAO {
String errorCode = "";
private JdbcTemplate jdbcTemplate;
@Autowired
public StoreDAO(@Qualifier("dataSourceStore") DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
// methods which use Spring JDBC calls (such as SQL Select Statements)
}
When running this in production tomcat7 server which is clustered we get the following exception (all over the place in like four different DAOs):
DAO::org.springframework.jdbc.CannotGetJdbcConnectionException:
Could not get JDBC Connection; nested exception is
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:
Data source rejected establishment of connection,
message from server: "Too many connections"
Question(s):
How can I change the existing code (importing the local Spring XML datasource file) into a way where I can implement a JDBC Connection Pool?
Should I be closing the ResultStatement in a try / catch / finally clause after each successful SQL Select call?
e.g.
finally {
if (stat != null) stat.close();
if (conn != null) conn.close();
}
Should I also increase the number of max connection in the MySQL database by editing the my.cnf file (what would be the implications of increasing the max number of connections?):
https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_max_connections
Inside the Spring datasource.xml file can I simply increase the values inside the following properties?
< property name = "maxIdle" value = "10" />
< property name = "maxActive" value = "50" />
< property name = "maxWait" value = "100" />
Would really appreciate if someone could provide some direction because this is a legacy app which is experience severe performance problems due to the "Too Many Connections" to the MySQL Database.