How to Improve Performance of Legacy Spring App: MySQL MySQLNonTransientConnectionException

Question

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.

JB Nizet JB Nizet · Accepted Answer · 2017-09-03T07:45:15

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?

You are already using a connection pool.Apache DBCP (Data Base Connection Pool) is a connection pool.

Should I be closing the ResultStatement in a try / catch / finally clause after each successful SQL Select call?

That's what JdbcTemplate is all about. You're not showing any JDBC code using it, but the whole point of this class is precisely to handle the closing of JDBC resources for you. So, unless you're using it in a really weird way, resources are being closed.

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?)

We can't answer that, since we have no idea of what the current number is, of how many instances of your application are using the database, and of how many additional connections you might need (not used by this application, but necessary for other applications, or administration purposes). You connection pool can open up to 50 active connections to this database, so you should allow at least 50 connections in the database, assuming you have a single instance of the app, and it's the only thing opening connections to this database.

Inside the Spring datasource.xml file can I simply increase the values inside the following properties?

Given the exception you got (but you didn't post its stack trace, so it's harder to know exactly what happened), that would only make things worse: the database is alreadyrefusing to open connections, and that would tell the pool to try opening even more of them. If anything, you should reduce the max number of active connections.

How to Improve Performance of Legacy Spring App: MySQL MySQLNonTransientConnectionException

1 Answers