Grails connection pool - connections not closing?

Question

I have a grails/groovy web-app with database connection pooling. The settings are set-up like this:

dataSource:
        url: "jdbc:postgresql://192.168.100.53:5432/bhub_dev"
        properties:
          jmxEnabled: true
          initialSize: 5
          maxActive: 25
          minIdle: 5
          maxIdle: 15
          maxWait: 10000
          maxAge: 600000
          timeBetweenEvictionRunsMillis: 5000
          minEvictableIdleTimeMillis: 60000
          validationQuery: SELECT 1
          validationQueryTimeout: 3
          validationInterval: 15000
          testOnBorrow: true
          testWhileIdle: true
          testOnReturn: false
          jdbcInterceptors: ConnectionState
          defaultTransactionIsolation: 2 # TRANSACTION_READ_COMMITTED

I'm using java-melody for diagnostics and monitoring, and noticing some weird behavior. For example, when executing jobs that query the DB, the connections can get over the maxActive property. Why is this even possible?

Do I need to explicitly close the Grails connections? The job calls a service method that simply does a DB query via a withCriteria Grails call, like:

def activities = WorkActivity.withCriteria{
     eq("workCategoryActive", true)
     order("id", "desc");
}

and it seems everytime this is run, 2 new connections are opened up, and they do not close everytime properly.

Also, on every page refresh there's some calls to the backend that execute queries, and sometimes even on refresh 2 new connections seem to open up.

I'm pretty new to Grails dev, so I have no idea if I have to/can even close this withCriteria database connection.

Any bit of help is appreciated. The database is PGSQL.

EDIT: Ok, so now I'm looking at Thread diagnostic in java-melody, and it seems like the Tomcat pool-cleaner is in waiting state and that's why the connection count isn't going down? Also it seems that every time that job is run, 2 threads start, and one gets stuck in waiting? What the hell is going on.

Bedfordshire

how the WorkActivity is defined? – Trueman

Answer 1

You should take a look how is connection pool is working, basically connections don't closed, they are reusing and opens. So when it's reach max(in your case 50) connection will wait for free on. It's work so, because open connection it's a complex work. Regarding docs they said that connection is closed, that mean that it goes back to pool, but DON'T close. The same but more description

Proser