Im trying to switch the pool for my Grails 2.5.4 app for Hikari and found it to make a pretty big performance boost compared to the default Tomcat jdbc!
However, Ive stumbled upon a problem when running some integration tests. This is the test method that is now failing:
def 'Returns the bar with the least foo'() { given:
def foobar = Foobar.build()
and:
def bar1 = Bar.build(foo: 25, foobar: foobar)
def item1 = BarItem.build(state: AVAILABLE)
item1.addToBars(bar1)
and:
def bar2 = Bar.build(foo: 12, foobar: foobar)
def item2 = BarItem.build(state: AVAILABLE)
item2.addToBars(bar2)
when:
def bestBar = foobar.getBestBar()
then:
bestBar.id == bar2.id
when:
item2.state = State.BLACKED_OUT
item2.save(flush: true)
def refreshedFoobar
Foobar.withNewSession {
refreshedFoobar = Foobar.get(foobar.id) //This is returning null
}
and:
bestBar = refreshedFoobar.getBestBar() //null pointer exception here
then:
bestBar.id == bar1.id
}
Why is this happening? Seems like things arent being pushed into the database properly, like its just holding them in session waiting to send them later.
Here is my Hikari config:
def hp = new Properties()
hp.username = ds.username
hp.password = ds.password
hp.connectionTimeout = ds.maxWait
hp.maximumPoolSize = ds.maxActive
hp.minimumIdle = ds.minIdle
hp.jdbcUrl = ds.url
hp.driverClassName = ds.driverClassName
HikariConfig hc = new HikariConfig(hp)
hc.with{
addDataSourceProperty("prepStmtCacheSize", 500)
addDataSourceProperty("prepStmtCacheSqlLimit", 2048)
addDataSourceProperty("cachePrepStmts", true)
addDataSourceProperty("useServerPrepStmts", true)
}
values being
dialect = org.hibernate.dialect.MySQL5InnoDBDialect
driverClassName = 'com.mysql.jdbc.Driver'
username = 'user'
password = 'pass'
maxActive = 250
minIdle = 5
maxWait = 10000
Log:
2016-08-04 17:49:28,070 [main] DEBUG hikari.HikariConfig - HikariPool-1 - configuration:
2016-08-04 17:49:28,071 [main] DEBUG hikari.HikariConfig - allowPoolSuspension.............false
2016-08-04 17:49:28,072 [main] DEBUG hikari.HikariConfig - autoCommit......................true
2016-08-04 17:49:28,072 [main] DEBUG hikari.HikariConfig - catalog.........................null
2016-08-04 17:49:28,072 [main] DEBUG hikari.HikariConfig - connectionInitSql...............null
2016-08-04 17:49:28,072 [main] DEBUG hikari.HikariConfig - connectionTestQuery.............null
2016-08-04 17:49:28,072 [main] DEBUG hikari.HikariConfig - connectionTimeout...............10000
2016-08-04 17:49:28,072 [main] DEBUG hikari.HikariConfig - dataSource......................null
2016-08-04 17:49:28,072 [main] DEBUG hikari.HikariConfig - dataSourceClassName.............null
2016-08-04 17:49:28,073 [main] DEBUG hikari.HikariConfig - dataSourceJNDI..................null
2016-08-04 17:49:28,073 [main] DEBUG hikari.HikariConfig - dataSourceProperties............{password=<masked>}
2016-08-04 17:49:28,073 [main] DEBUG hikari.HikariConfig - driverClassName................."com.mysql.jdbc.Driver"
2016-08-04 17:49:28,073 [main] DEBUG hikari.HikariConfig - healthCheckProperties...........{}
2016-08-04 17:49:28,073 [main] DEBUG hikari.HikariConfig - healthCheckRegistry.............null
2016-08-04 17:49:28,073 [main] DEBUG hikari.HikariConfig - idleTimeout.....................600000
2016-08-04 17:49:28,074 [main] DEBUG hikari.HikariConfig - initializationFailFast..........true
2016-08-04 17:49:28,074 [main] DEBUG hikari.HikariConfig - isolateInternalQueries..........false
2016-08-04 17:49:28,074 [main] DEBUG hikari.HikariConfig - jdbc4ConnectionTest.............false
2016-08-04 17:49:28,074 [main] DEBUG hikari.HikariConfig - jdbcUrl........................."jdbc:mysql://localhost:3306/foo_test?autoReconnect=true"
2016-08-04 17:49:28,074 [main] DEBUG hikari.HikariConfig - leakDetectionThreshold..........0
2016-08-04 17:49:28,074 [main] DEBUG hikari.HikariConfig - maxLifetime.....................1800000
2016-08-04 17:49:28,074 [main] DEBUG hikari.HikariConfig - maximumPoolSize.................250
2016-08-04 17:49:28,074 [main] DEBUG hikari.HikariConfig - metricRegistry..................null
2016-08-04 17:49:28,075 [main] DEBUG hikari.HikariConfig - metricsTrackerFactory...........null
2016-08-04 17:49:28,075 [main] DEBUG hikari.HikariConfig - minimumIdle.....................250
2016-08-04 17:49:28,075 [main] DEBUG hikari.HikariConfig - password........................<masked>
2016-08-04 17:49:28,075 [main] DEBUG hikari.HikariConfig - poolName........................"HikariPool-1"
2016-08-04 17:49:28,075 [main] DEBUG hikari.HikariConfig - readOnly........................false
2016-08-04 17:49:28,075 [main] DEBUG hikari.HikariConfig - registerMbeans..................false
2016-08-04 17:49:28,075 [main] DEBUG hikari.HikariConfig - scheduledExecutorService........null
2016-08-04 17:49:28,075 [main] DEBUG hikari.HikariConfig - threadFactory...................null
2016-08-04 17:49:28,076 [main] DEBUG hikari.HikariConfig - transactionIsolation............null
2016-08-04 17:49:28,076 [main] DEBUG hikari.HikariConfig - username........................"root"
2016-08-04 17:49:28,076 [main] DEBUG hikari.HikariConfig - validationTimeout...............5000
2016-08-04 17:49:28,077 [main] INFO hikari.HikariDataSource - HikariPool-1 - Started.
2016-08-04 17:49:28,287 [main] INFO pool.PoolBase - HikariPool-1 - Driver does not support get/set network timeout for connections. (com.mysql.jdbc.JDBC4Connection.getNetworkTimeout()I)
2016-08-04 17:49:28,312 [HikariPool-1 housekeeper] DEBUG pool.HikariPool - HikariPool-1 - Pool stats (total=0, active=0, idle=0, waiting=0)
2016-08-04 17:49:28,381 [HikariPool-1 connection adder] DEBUG pool.HikariPool - HikariPool-1 - Added connection com.mysql.jdbc.JDBC4Connection@2929ca0e
....
Then I get a lot of these:
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
...
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: "Too many connections"
But I think thats just because of my local mysql settings, since I am using production pool size in settings
