Mr. Freedman is a seasoned architect and developer with over twenty years experience in the software industry. In the last ten years, he has focused on Java technologies and has led many successful projects, from web applications to Swing-based rich clients, back-end systems and large integration efforts. ! Richard is a DZone MVB and is not an employee of DZone and has posted 6 posts at DZone. You can read more from them at their website. View Full User Profile

Database Connection Pooling In Grails – solving the idle timeout issue

01.23.2011
| 23161 views |
  • submit to reddit

Grails turns database connection pooling on by default, and uses the Apache Commons DBCP library to do the pooling.

With many of the popular JDBC drivers, this can cause a failure when the application is accessed after a long idle time, commonly when a business application sits unused overnight, and then is accessed first-thing in the morning. The first user in the morning gets an HTTP 500 error, and then for subsequent users, everything is ok.

There are several ways to solve this, including changing to a different connection pool, but Sudarshan Acharya has a blog post from 2009 that explains a fairly easy way to configure DBCP to resolve this issue. Of course, there’s a “gotcha” here, or I wouldn’t be blogging about it:

As Tom Eastman points out, if you use Sudarshan’s example with Grails 1.3.x, you get an exception something like:

ERROR context.GrailsContextLoader – Error executing bootstraps: groovy.lang.MissingMethodException: No signature of method: org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy.setMinEvictableIdleTimeMillis() is applicable for argument types: (java.lang.Integer) values: [900000]
org.codehaus.groovy.runtime.InvokerInvocationException: groovy.lang.MissingMethodException: No signature of method: org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy.setMinEvictableIdleTimeMillis() is applicable for argument types: (java.lang.Integer) values: [900000]

As Graeme Rocher helpfully points out, this is because the properties of org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy changed with Grails 1.3.0, and you must now set the properties on ‘dataSourceUnproxied’ instead of setting them on ‘dataSource’. With this change, everything works as expected.

So, here’s an example of the new configuration of BootStrap.groovy, with Graeme’s suggested change:

def ctx = servletContext.getAttribute(
ApplicationAttributes.APPLICATION_CONTEXT
)
def dataSource = ctx.dataSourceUnproxied

println "configuring database connection pool"

dataSource.setMinEvictableIdleTimeMillis(1000 * 60 * 30)
dataSource.setTimeBetweenEvictionRunsMillis(1000 * 60 * 30)
dataSource.setNumTestsPerEvictionRun(3)
dataSource.setTestOnBorrow(true)
dataSource.setTestWhileIdle(false)
dataSource.setTestOnReturn(false)
dataSource.setValidationQuery("SELECT 1")

dataSource.properties.each { println it }

 

References
Published at DZone with permission of Richard Freedman, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Tags:

Comments

Blazej Bucko replied on Mon, 2011/01/24 - 4:17am

You could always try this (taken from: http://www.grails.org/doc/latest/guide/3.%20Configuration.html#3.3%20The%20DataSource)

dataSource {
    pooled = true
    dbCreate = "update"
    url = "jdbc:mysql://localhost/yourDB"
    driverClassName = "com.mysql.jdbc.Driver"
    dialect = org.hibernate.dialect.MySQL5InnoDBDialect
    username = "yourUser"
    password = "yourPassword"
    properties {
        maxActive = 50
        maxIdle = 25
        minIdle = 5
        initialSize = 5
        minEvictableIdleTimeMillis = 60000
        timeBetweenEvictionRunsMillis = 60000
        maxWait = 10000
        validationQuery = "/* ping */"
    }
}

Dean Del Ponte replied on Mon, 2011/01/24 - 8:22am

I second Blazej Bucko's suggestion. I've never had this issue and I've been using grails since the 1.0 days. My typical datasource (DataSource.groovy) configuration is below:
    production {
        dataSource {
            pooled = true
            dbCreate = "update"
            url = "jdbc:mysql://localhost/myUrl"
            driverClassName = "com.mysql.jdbc.Driver"
            username = "username"
            password = "password"
            properties {
                maxActive = 100
                maxIdle = 25
                minIdle = 5
                initialSize = 5
                minEvictableIdleTimeMillis = 60000
                timeBetweenEvictionRunsMillis = 60000
                maxWait = 10000
            }
        }
    }

Lucifer Williams replied on Thu, 2012/11/15 - 4:31pm

The first user in the morning gets an HTTP 500 error in hvac houston, and then for subsequent users, everything is ok.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.