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:
Category:Connector / J Severity:S5 (Performance)
Version:5.1.44 OS:Any
Assigned to: Alexander Soklakov CPU Architecture:Any
Tags: performance gc

[7 Oct 2017 15:57] Johnathan Crawford
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].


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


private String makePreparedStatementCacheKey(String catalog, String query) {
    StringBuilder key = new StringBuilder();
    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.
[7 Oct 2017 21:05] Johnathan Crawford
[9 Oct 2017 9:39] Chiranjeevi Battula
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.

[9 Oct 2017 11:45] Johnathan Crawford
Hi Chiranjeevi, I sent the signed OCA on the weekend.
[10 Oct 2017 12:22] Johnathan Crawford
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. 


[10 Oct 2017 13:57] OCA Admin
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.

[12 Oct 2017 10:00] Alexander Soklakov
Hi Johnathan,

Nice catch! Thank you for the contribution.
[6 Nov 2017 10:48] Johnathan Crawford
Hi Alexander,

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


[6 Nov 2017 10:49] Johnathan Crawford
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.

[6 Nov 2017 10:50] Johnathan Crawford
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.

[6 Nov 2017 10:53] Johnathan Crawford
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.
[6 Nov 2017 10:53] Johnathan Crawford
Source for the JMH benchmarks can be found here https://github.com/mysql/mysql-connector-j/pull/25#issuecomment-335000056
[7 Nov 2017 9:26] Johnathan Crawford
I confirm the code submitted (attached patches) is offered under the terms of the OCA, and that I am authorized to contribute it.
[22 Nov 2017 20:55] Daniel So
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."