HikariCP、MySQL Configuration 性能优化

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.

maintainTimeStats=false
 Some platforms have expensive getTimeOfDay()
 We have “friendly” error messages that include elapsed times
 Trade ease of use for performance?
 3% (or so) throughput increase

 “useUnbufferedIO=false” and “useReadAheadInput=false”
 Can reduce system calls 3-5x
 More useful when MySQLd can’t fill the pipe fast enough

 “rewriteBatchedStatements=true”
 Affects (Prepared)Statement.add/executeBatch()
 Core concept - remove latency
 Special treatment for prepared INSERT statements

 “useLocalSessionState=true”
 Transaction isolation, auto commit and catalog
(database)
 “useLocalTransactionState=true” (5.1.7)
 Saves commit/rollback
 Doesn’t work with Query Cache < 6.0

“cacheServerConfiguration=true”
 Requires restart of application if you change these MySQL variables: language, wait_timeout, interactive_timeout, net_write_timeout, character_set_*, timezone, query_cache
 Causes some contention when creating new connections

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

jdbcUrl=jdbc:mysql://localhost:3306/simpsonsuser=testpassword=testdataSource.cachePrepStmts=truedataSource.prepStmtCacheSize=250dataSource.prepStmtCacheSqlLimit=2048dataSource.useServerPrepStmts=truedataSource.useLocalSessionState=truedataSource.useLocalTransactionState=truedataSource.rewriteBatchedStatements=truedataSource.cacheResultSetMetadata=truedataSource.cacheServerConfiguration=truedataSource.elideSetAutoCommits=truedataSource.maintainTimeStats=false

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