Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Hypersistence Optimizer is that tool!
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.
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.
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.
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
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.
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.
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.
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
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.
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.
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.
Online Workshops
If you enjoyed this article, I bet you are going to love my upcoming 4-day Online Workshop!
- High-Performance Java Persistence Online Workshop (4 hours x 4 days) between the 19th and 22nd of April
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
