MySQL Bugs: #88021: High GC pressure when driver configured with serversideprepared statements

Bug #88021	High GC pressure when driver configured with serversideprepared statements
Submitted:	7 Oct 2017 15:57	Modified:	22 Nov 2017 20:55
Reporter:	Johnathan Crawford (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S5 (Performance)
Version:	5.1.44	OS:	Any
Assigned to:	Alexander Soklakov	CPU Architecture:	Any
Tags:	performance gc

Description:
While profiling our application I found high GC pressure coming from the mysql driver [1]. The issue arises when the driver is configured to use serverside prepared statements and the driver recreates the cache key for every query (sb.toString() invoking Arrays.copyOfRange) [2].

[1]

Stack Trace    TLABs    Total TLAB Size(bytes)    Pressure(%)
com.mysql.jdbc.ConnectionImpl.makePreparedStatementCacheKey(String, String)    16,810    1,519,493,352

[2]

private String makePreparedStatementCacheKey(String catalog, String query) {
    StringBuilder key = new StringBuilder();
    key.append("/*").append(catalog).append("*/");
    key.append(query);
    return key.toString();
}

How to repeat:
Configure driver to use serverside prepared statements, execute lots of queries with prepared statements and profile the application with your favourite profiler eg. JMC.

https://github.com/mysql/mysql-connector-j/pull/25

Hello Johnathan,

Thank you for the report and contribution.
In order to submit contributions you must first sign the Oracle Contribution Agreement (OCA). For additional information please check http://www.oracle.com/technetwork/community/oca-486395.html.
If you have any questions, please contact the MySQL community team.

Thanks,
Chiranjeevi.

Hi Chiranjeevi, I sent the signed OCA on the weekend.

Hi Chiranjeevi,

I received confirmation for the OCA and my name is now on the contributor list, if there is anything else required to push this forward please let me know. 

Cheers,

Johnathan

Contribution submitted via Github - Fix for #88021 high GC pressure when driver configured with serversid… (*) Contribution by Johnathan Crawford (Github johnou, mysql-connector-j/pull/25#issuecomment-335449341): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: git_patch_145303703.txt (text/plain), 7.61 KiB.

Hi Johnathan,

Nice catch! Thank you for the contribution.

Hi Alexander,

I have gone ahead and split the changes I made into multiple patches, hope this helps.

Cheers,

Johno

Updated patch for fixing high GC pressure when driver configured with server side prepared statements

Attachment: 0001-Fix-for-88021-high-GC-pressure-when-driver-configure.patch (text/x-patch), 3.39 KiB.

Add generics to LRUCache to avoid typed exceptions

Attachment: 0001-Add-generics-to-LRUCache-to-avoid-typed-exceptions.patch (text/x-patch), 6.48 KiB.

The reason why I added generics to LRUCache is because the first patch changes the type of the key and I wanted compile time checking that the types are correct in the entire project.

Source for the JMH benchmarks can be found here https://github.com/mysql/mysql-connector-j/pull/25#issuecomment-335000056

I confirm the code submitted (attached patches) is offered under the terms of the OCA, and that I am authorized to contribute it.

Posted by developer:
 
Added the following entry to the Connector/J 5.1.45 changelog:

"High garbage collection pressure was observed when there were a lot of queries performed using server-side prepared statements. This patch reduces the pressure by optimizing the generation process of the cache keys for the prepared statements. Thanks to Johnathan Crawford for contributing the patch."