MySQL JDBC Statement Caching
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
Introduction
In this article, we are going to see how we can enable the MySQL JDBC Driver Statement Caching mechanism, which, unfortunately, is not enabled by default.
No matter what data access framework you are using, you still need to configure the JDBC Driver if you want to get the most out of the MySQL database engine.
MySQL JDBC client-side PreparedStatement
As I explained in this article, by default, the MySQL JDBC Driver emulates prepared statements.
So, no matter if you are executing a plain Statement
or a PreparedStatement
, the SQL statement execution will look like this:
So, when executing the following statement:
try(PreparedStatement statement = connection.prepareStatement(""" SELECT balance FROM account WHERE iban = ? """) ) { statement.setString(1, iban); ResultSet resultSet = statement.executeQuery(); if(resultSet.next()) { return resultSet.getLong(1); } }
By default, the ConnectionImpl#clientPrepareStatement
method is called, and the statement is prepared on the client-side only.
The bind parameters are stored locally and inlined prior to the statement execution, so there’s a single database roundtrip that gets executed.
MySQL JDBC server-side PreparedStatement – useServerPrepStmts
To enable server-side prepared statements, you need to set the useServerPrepStmts
setting to true
.
useServerPrepStmts=true
Now, when calling the prepareStatement
JDBC Connection
method, a ServerPreparedStatement
will be created instead, and the statement will be prepared on the database server:
When the statement is prepared on the server, the Parser will create an AST (Abstract Syntax Tree) that can be traversed by the Optimizer during statement execution.
MySQL JDBC Statement Caching setting – cachePrepStmts
Since it’s good practice to use a database connection pool, the Connection
objects are going to be reused from one transaction to another, so it makes sense to cache either the client-side or the server-side parsed statements.
This can be done for both client-side and server-side prepared statements by setting the cachePrepStmts
configuration property to the value of true
:
cachePrepStmts=true
MySQL JDBC client-side PreparedStatement Caching
For client-side prepared statements, the MySQL JDBC Driver will cache the ParseInfo
object:
ParseInfo pStmtInfo = this.cachedPreparedStatementParams.get(nativeSql);
The ParseInfo
object provides the statement length, the number of bind parameters, whether the statement contains the ON DUPLICATE KEY UPDATE
.
MySQL JDBC server-side PreparedStatement Caching
For server-side prepared statements, without caching prepared statements, we are going to execute two network roundtrips every time we want to execute a statement.
This is done by the serverPrepare
method call in the ServerPreparedStatement
object.
So, to reduce the number of network roundtrips, it makes sense to enable the statement caching mechanism when using server-side prepared statements.
MySQL JDBC Statement Caching – prepStmtCacheSize
After you enabled statement caching via the cachePrepStmts
property, you need to increase the cache size value because the default size is just 25
.
Therefore, you need to set the prepStmtCacheSize
to a reasonably larger value:
prepStmtCacheSize=500
The prepStmtCacheSize
can be even larger than that. You need to set it to accommodate the most common SQL statements your application is using.
To determine what is your application SQL working set, use Percona PMM and look in the Query Analyzer view to see what are the most common SQL statements you are using.
Internally, the MySQL JDBC Driver will store the cached ServerPreparedStatement
objects in the LRUCache
. The LRUCache
extends the Java LinkedHashMap
, which provides the LRU (Least Recently Used) cache replacement policy.
MySQL JDBC Statement Caching setting – prepStmtCacheSqlLimit
By default, SQL statements whose length is longer than 256
characters are not stored in the cache. This is done to limit the amount of RAM used by the Statement Caching mechanism.
However, if your application uses long statements, then you might want to increase this limit via the prepStmtCacheSqlLimit
setting:
prepStmtCacheSqlLimit=1024
MySQL JDBC Statement Caching – Performance Gain
To see what’s the performance gain obtained by enabling statement caching, consider the following test case:
long ttlNanos = System.nanoTime() + getRunNanos(); while (System.nanoTime() < ttlNanos) { long startNanos = System.nanoTime(); try (PreparedStatement statement = connection.prepareStatement(""" SELECT p.title, pd.created_on FROM post p LEFT JOIN post_details pd ON p.id = pd.id WHERE EXISTS ( SELECT 1 FROM post_comment WHERE post_id = p.id ) ORDER BY p.id LIMIT ? OFFSET ? """ )) { statement.setInt(1, 1); statement.setInt(2, 100); try(ResultSet resultSet = statement.executeQuery()) { queryCount.incrementAndGet(); } finally { queryTimer.update( System.nanoTime() - startNanos, TimeUnit.NANOSECONDS ); } } }
When running the test case above for 1 minute using the MySQL 8.0.22 Connector/J JDBC Driver for client-side and server-side prepared statements, we get the following results.
MySQL JDBC Client-Side Statement Caching – Performance Gain
The query throughput for client-side prepared statements looks like this:
The higher, the better, because more throughput means we can execute more queries in a unit of time.
And the query execution time 99 percentile looks as follows:
The lower, the better, because a shorter query execution time means we get better transaction response time as well.
MySQL JDBC Server-Side Statement Caching – Performance Gain
The query throughput for server-side prepared statements looks like this:
The higher, the better, because more throughput means we can execute more queries in a unit of time.
And the query execution time 99 percentile looks as follows:
The lower, the better, because a shorter query execution time means we get better transaction response time as well.
So, the Statement Caching mechanism works for both client-side and server-side prepared statements as well.
I'm running an online workshop on the 11th of October about High-Performance SQL.If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
Conclusion
For MySQL, the statement caching mechanism shows some improvements, but not as significant as with other database systems.
In all my tests, on both MySQL 8.0.22 and 8.0.18, using either single-statement or multiple-statement transactions, the client-side prepared statements performed better than server-side prepared statements.
All in all, the following configuration options seem to yield the best results:
useServerPrepStmts=false cachePrepStmts=true
Don’t forget to increase the cache limits as well as the default values are way too low:
prepStmtCacheSize=500 prepStmtCacheSqlLimit=1024
