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

Insert details about how the information is going to be processed

DOWNLOAD NOW