MySQL Bugs: #104247: Connector / J performance is terrible when stored procedures are called at scale

Bug #104247	Connector / J performance is terrible when stored procedures are called at scale
Submitted:	8 Jul 2021 5:38	Modified:	28 Oct 2021 11:19
Reporter:	Yoseph Phillips	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S5 (Performance)
Version:	8.0.25	OS:	Any
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
Connector / J performance is terrible when stored procedures are called at scale.

The issue is caused by the DatabaseMetaDataUsingInfoSchema.getProcedureColumns(...) method which is querying the database for information about parameters every single time a stored procedure is called. 
One of our clients using MySQL 8.0.25 EE is making calls to more than 10,000,000 stored procedures every day, which makes this issue very noticeable as during peak times the process list is full of queries fetching this meta data, which are also causing lots of locking.

How to repeat:
Do load testing calling a stored procedure via the Connector / J, a huge number of times concurrently. We used JMeter for the load testing and could easily reproduce this.

Suggested fix:
Cache the ResultSets returned from the getProcedureColumns(...) methods, so that this only needs to be fetched from the database just once per stored procedure. We have load tested this and can see excellent results of doing this. 

Optionally Oracle might want to add a configurable setting to clear this cache periodically or to enable or disable this enhancement. For us this is not required as we restart the web app whenever we make meta data changes to the stored procedures and hence it will restart with an empty cache.

Here are the simple changes we did:
Import the following:
import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetFactory;
import javax.sql.rowset.RowSetProvider;

At the top of the class add two new fields:
private static final Map<MultiValueTriplet<String, String, String>, CachedRowSet> cache = new HashMap<>();
private static RowSetFactory rowSetFactory;

MultiValueTriplet is our own class which we also provide on request - it just stores 3 values which can be null, and has simple hashCode, compareTo, and equals methods.

The inside the getProcedureColumns(...) method add:
MultiValueTriplet<String, String, String> cacheKey = new MultiValueTriplet<>(db, procedureNamePattern, columnNamePattern);
CachedRowSet cachedResultSet;
synchronized (cache) {
  if (rowSetFactory == null) {
    rowSetFactory = RowSetProvider.newFactory();
  }

  cachedResultSet = cache.get(cacheKey);
  if (cachedResultSet == null) {
    StringBuilder sqlBuf .... // start of existing code 1

    ResultSet rs = executeMetadataQuery(pStmt);
          ((com.mysql.cj.jdbc.result.ResultSetInternalMethods) rs)
              .getColumnDefinition()
              .setFields(createProcedureColumnsFields()); // end of existing code 1

        cachedResultSet = rowSetFactory.createCachedRowSet();
        cachedResultSet.populate(rs);
        cache.put(cacheKey, cachedResultSet);
      } finally { // start of existing code 2
        if (pStmt != null) {
          pStmt.close();
        }
      }
    }
  } // end of existing code 2
  return cachedResultSet.createCopy();
}

Hi Yoseph,

Connector/J has the caching functionality you're looking for since version 3.1.2. Please check connection properties "cacheCallableStmts" and "callableStmtCacheSize".

The "cacheCallableStmts" description says: "Should the driver cache the parsing stage of CallableStatements", here the "parsing stage" inclused not only the string parsing but also a call to metadata to fill in parameter types.

You could check the test case testsuite.simple.CallableStatementTest.testSPCache() that's calling prepareCall() 10 times with and without cacheCallableStmts=true. With cacheCallableStmts=true there is only one I_S call in the general server log.

Hi,

We have done extensive testing with both cacheCallableStmts/callableStmtCacheSize and the global cache way described above, along with many other combinations.

We have now been using the cacheCallableStmts/callableStmtCacheSize way in production for a couple of months and it is proving to be satisfactory. 
As this is a satisfactory solution this is no longer such a high priority for us.
For anyone using stored procedures with a database pool of connections this is essential for acceptable performance.

Some observations from our extensive load testing:
* The global cache method described previously generally performs better than the cacheCallableStmts/callableStmtCacheSize way, however can have issues with this simple implementation when lots of threads are bottle necked waiting on the Java lock. Also the global cache way uses less RAM as it only needs one copy of information, not one copy per connection, however for us we have enough RAM for the cacheCallableStmts/callableStmtCacheSize way.
* Doing both the global cache method and the cacheCallableStmts/callableStmtCacheSize method together gave the best overall performance - however we won't do this in production as Connector / J doesn't have the global cache method
* Using a ThreadLocal was another option, which avoided the global Java lock, and had a copy of the Meta Data for each Java Thread, instead of a copy for each Connection
* Bottom line was all of these methods were significantly better than no caching at all, and the difference between the above methods were not significant enough that users would notice a difference.

Thanks

Posted by developer:
 
Thank you for the detailed feedback.

I'm closing this issue as "not a bug". Please re-open it of file a new one if the problem arises again due to a different reason.