How to use performance properties of MySQL when using HikariCP in Grails 4?

Question

On the HikariCP page on the page on MySQL performance tips.

https://github.com/brettwooldridge/HikariCP/wiki/MySQL-Configuration

It suggests using 4 properties namely

prepStmtCacheSize
prepStmtCacheSqlLimit
cachePrepStmts
useServerPrepStmts

I am confused whether these properties go to dataSource block at the same level as dbCreate and dialect or do they go inside properties block.

dataSource {
    pooled = true
    dbCreate = "none"
    url = "jdbc:mysql://localhost:3306/dev2?useUnicode=yes&characterEncoding=UTF-8"
    driverClassName = "com.mysql.cj.jdbc.Driver"
    dialect = org.hibernate.dialect.MySQL8Dialect

    prepStmtCacheSize = 400
    prepStmtCacheSqlLimit = 2048
    cachePrepStmts = true
    useServerPrepStmts = true

    type = "com.zaxxer.hikari.HikariDataSource"
    properties {

        connectionTimeout = 200000
        maximumPoolSize = 50

    }

}

or this

dataSource {
    pooled = true
    dbCreate = "none"
    url = "jdbc:mysql://localhost:3306/dev2?useUnicode=yes&characterEncoding=UTF-8"
    driverClassName = "com.mysql.cj.jdbc.Driver"
    dialect = org.hibernate.dialect.MySQL8Dialect


    type = "com.zaxxer.hikari.HikariDataSource"
    properties {

        connectionTimeout = 200000
        maximumPoolSize = 50
    prepStmtCacheSize = 400
    prepStmtCacheSqlLimit = 2048
        cachePrepStmts = true
        useServerPrepStmts = true


    }

}

I suspect those settings are for non-MySQL databases. They sound rather useless for MySQL. — Rick James, Mar 30 '23 at 16:53

score 0 · Accepted Answer · answered Mar 22 '23 at 16:52

If you tried both of those configuration options and they didn't work, then this may be worth trying...

dataSource {
    pooled = true
    dbCreate = "none"
    url = "jdbc:mysql://localhost:3306/dev2?useUnicode=yes&characterEncoding=UTF-8"
    driverClassName = "com.mysql.cj.jdbc.Driver"
    dialect = org.hibernate.dialect.MySQL8Dialect
    type = "com.zaxxer.hikari.HikariDataSource"
    properties {
        maximumPoolSize = 50
        connectionTimeout = 200000
        dataSourceProperties {
            prepStmtCacheSize = 400
            prepStmtCacheSqlLimit = 2048
            cachePrepStmts = true
            useServerPrepStmts = true
        }
    }
}

You can also turn on Hikari logging via logback:

<logger name="com.zaxxer.hikari" level="DEBUG" additivity="false">
    <appender-ref ref="STDOUT" />
</logger>

thank you very much. do you know if there is a way to print the database property value in console when the app is running? — kofhearts, Mar 23 '23 at 04:51

How to use performance properties of MySQL when using HikariCP in Grails 4?

1 Answers1