MySQL Configuration

In order to get the best performance out of MySQL, these are some of our recommended settings. There are many other performance related settings available in MySQL and we recommend reviewing them all to ensure you are getting the best performance for your application.

prepStmtCacheSize
This sets the number of prepared statements that the MySQL driver will cache per connection. The default is a conservative 25. We recommend setting this to between 250-500.

prepStmtCacheSqlLimit
This is the maximum length of a prepared SQL statement that the driver will cache. The MySQL default is 256. In our experience, especially with ORM frameworks like Hibernate, this default is well below the threshold of generated statement lengths. Our recommended setting is 2048.

cachePrepStmts
Neither of the above parameters have any effect if the cache is in fact disabled, as it is by default. You must set this parameter to true.

~~useServerPrepStmts : Newer versions of MySQL support server-side prepared statements, this can provide a substantial performance boost. Set this property to true.

A typical MySQL configuration for HikariCP might look something like this:

dataSourceClassName=com.mysql.jdbc.jdbc2.optional.MysqlDataSource
dataSource.url=jdbc:mysql://localhost/database
dataSource.user=test
dataSource.password=test
dataSource.cachePrepStmts=true
dataSource.prepStmtCacheSize=250
dataSource.prepStmtCacheSqlLimit=2048

or in Hibernate:

<prop key="hibernate.connection.provider_class">com.zaxxer.hikari.hibernate.HikariConnectionProvider</prop>
<prop key="hibernate.hikari.dataSourceClassName">com.mysql.jdbc.jdbc2.optional.MysqlDataSource</prop>
<prop key="hibernate.hikari.dataSource.url">${database.connection}</prop>
<prop key="hibernate.hikari.dataSource.user">${database.username}</prop>
<prop key="hibernate.hikari.dataSource.password">${database.password}</prop>
<prop key="hibernate.hikari.dataSource.cachePrepStmts">true</prop>
<prop key="hibernate.hikari.dataSource.prepStmtCacheSize">250</prop>
<prop key="hibernate.hikari.dataSource.prepStmtCacheSqlLimit">2048</prop>

See Also

If you are using MySQL, you really need to read this slide-stack