0

I have a large amount of data that I wish to load into a DB (data for 28 days), so it is loaded into the DB via GORM using 28 asynchronous Promises.

After the app has been running for a while, I get a "Timeout: Pool empty. Unable to fetch a connection etc..." error. When I profile the app using Melody, I can see the number of jdbc connections steadily increasing, but he number of active connections seems to be very small.

You can see the connections here.

My datasource.groovy:

    dataSource {
        jmxExport = true
        driverClassName = "org.postgresql.Driver"
        username = "user"
        password = ""
        dbCreate = "update"
        url = "jdbc:postgresql://example.com:5432/testdb"
        properties {
            // See http://grails.org/doc/latest/guide/conf.html#dataSource for documentation
            jmxEnabled = true
            initialSize = 5
            maxActive = 250
            minIdle = 5
            maxIdle = 25
            maxWait = 20000
            maxAge = 10 * 60000
            timeBetweenEvictionRunsMillis = 5000
            minEvictableIdleTimeMillis = 10000
            validationQuery = "SELECT 1"
            validationQueryTimeout = 3
            validationInterval = 10000
            testOnBorrow = true
            testWhileIdle = true
            testOnReturn = false
            jdbcInterceptors = "ConnectionState"
            defaultTransactionIsolation = java.sql.Connection.TRANSACTION_READ_COMMITTED
            removeAbandoned = true
            removeAbandonedTimeout = 10
        }

For some reason, my connections don't seem to be being closed and I don't know why. Is there a way to force connections to be closed in Grails? When are connections closed in Grails?

Buildconfig uses:

runtime 'org.postgresql:postgresql:9.3-1102-jdbc41'
runtime ":hibernate4:4.3.5.5"

The loading logic for loading the last 28 days of data looks like (within main thread of execution):

    // this will start a thread that will initiate load of N previous days of data plus today
    Promise p = task {  asynchronousBulkMessageLoader(toDate)  }
    p.onComplete {
        // do some logging here
    }
    p.onError { Throwable err ->
        // logging
    }
    // return to calling method


// this thread creates N separate threads, each responsible for loading 24 hours of data.  This thread will wait for all to complete before returning.
def asynchronousBulkMessageLoader(Date toDate) {
    // ...
    // define & set variables daysToLoad, day
    List promiseList = []

    // we want to load daysToLoad days of data, plus today, hence the <=, not  <
    for (int i = 0; i <= daysToLoad; i++) {
            def promise = task { asynchronousLoadSingleDay(day) }
            promise.onComplete {
                // logging
            }
            promise.onError { Throwable err ->
                // logging
            }
            promiseList << promise

        }
    }
    // wait for all N threads to complete
    waitAll(promiseList)
    return
}

def asynchronousLoadSingleDay(Date dayToLoad) {
    /* actually perform the loading logic - this is done by a number of services
    The process revolves around breaking down a bulk message received from
Rabbitmq that contains a number of sub-messages, and then passing to a handler 
that process each individual sub-message.  The sub-message handler needs to call a 
number of sub-services several times whilst processing the sub-message. */

}
John
  • 10,837
  • 17
  • 78
  • 141
  • could you please add a (rough) code example, how the actual batch logic is implemented? what hibernate-plugin version are you using? – cfrick Feb 04 '15 at 14:02
  • I've added the extra detail, everything is done through GORM, there are no SQL queries, so something isn't releasing the connections as they are created and used, so I'm wondering is there a way of closing the connections manually. – John Feb 04 '15 at 14:19
  • 2
    There's some interesting reading here: https://forum.hibernate.org/viewtopic.php?f=1&t=1001942 and http://stackoverflow.com/questions/3924079/hibernate-session-close-not-returning-connection-to-pool. When I set hibernate.connection.release_mode=after_transaction within Datasource.groovy, the problem ceased and the number of connections stayed constant. – John Feb 04 '15 at 17:15

0 Answers0