Poor read-performance with mssql-jdbc 6.4.0 JDBC driver statement cache under Hikari

[Roger D]

We have been converting our server from using Hibernate 4.2 to Hibernate 5.2. Hibernate 5.2 requires JDBC 4.2 (Java 8), which requires us to switch to Hikari CP connection pool which is a branch from the (now deprecated) Bone CP we were previously using, since Bone CP only supports up to JDBC 4.1 (Java 7). Unlike Bone CP, Hikari CP no longer provides prepared statement caching in the connection pool, so that now needs to be done in the JDBC driver, which for MS SQL Server means we need to move to a JDBC driver version that provides prepared statement caching in the driver, and mssql-jdbc 6.4.0 (released in January 2018) is the first one with that (thanks to Brett Wooldridge contributing it). So the full change is from Hibernate 4.2 + Bone CP 0.8.0 + sqljdbc42 4.2.6420.100 MS SQL Server JDBC driver to Hibernate 5.2 + Hikari CP 2.7.8 + mssql-jdbc 6.4.0.jre8

Unfortunately as a result of this switchover we're seeing about average of a 20-30% slowdown for read query performance -- which is unacceptable. However, in the corresponding results for Oracle and MySQL with Hibernate 5.3 + Hikari + their JDBC drivers, we actually saw about a 5-15% performance improvement -- so we're reasonably sure that this isn't due to Hibernate (and it can't be Hikari directly, since that's out of the picture once the connection is handed to us). Thus we're investigating issues around the switch from Bone CP 0.8.0 prepared statement caching to mssql-jdbc 6.4 prepared statement caching.

We've confirmed that performance gets another 10% worse if we turn the mssql-jdbc 6.4 driver prepared statement caching off, so it's doing a little good (we also confirmed in the debugger that it's actually caching things). We've also tried tuning all the obvious available cache tuning parameters for it: statementPoolingCacheSize, serverPreparedStatementDiscardThreshold, enablePrepareOnFirstPreparedStatementCall (and also useCursors) with very little effect.

Does anyone have any experience with the combination of Hikari CP + mssql-jdbc 6.4, or ideally with Hibernate 5 + Hikari CP + mssql-jdbc 6.4? Is poor prepared statement caching performance from Hikari CP + mssql-jdbc 6.4 (in comparison what to Bone CP used to provide) a known issue? Or is mssql-jdbc just slower overall for read queries than sqljdbc42 was? Are there any other tuning parameters for mssql-jdbc that we've missed? Can anyone suggest anything else we could try -- for example, is there another MS SQL Server JDBC driver we could try instead? (jDTS isn't an option since it's not compatible even with JDBC 4.0)

[Brett Wooldridge]

I suspect the performance difference is in the driver.  Do you have a way to run your performance tests with the "old" stack (Hibernate 4.2) but with the new mssql driver?  That would basically verify whether that is the case.

-Brett

[Roger D]

I'd have to confirm, but that sounds doable, and it would definitely help reduce the number of moving parts here. Also that way I believe we could experiment with BoneCP caching vs. mssql-jdbc 6.4 caching, and see how the speeds compare -- we'll try that and get back to you.

On the subject of the mssql-jdbc 6.4 cache code that you contributed to them, I pointed the YourKit profiler at our perf test setup (so far just the Hibernate 5 + Hikari + mssql-jdbc version), and found that O(5-10%) of the test time was spent inside the mssql-jdbc 6.4 cache code doing hash calculations! The mssql-jdbc 6.4 cache is asking for an SHA1 hash (which sounds like overkill to me -- wouldn't a faster hash followed by an equality check, or just a long faster hash, be sufficient for cache checking?) It is delegating the SHA1 hash to the Java crypto layer, which in turn is delegating it to our preferred crypto provider which is the Bouncy Castle crypto library (so I hope the issue isn't simply that Bouncy Castle's SHA1 implementation is somehow extremely slow). So I think there's an O(5-10%) speedup possible here by using a more efficient hash algorithm (I assume we don't need to worry about a malevolent opponent trying to DoS us via inducing phony cache collisions?). However, that still leaves O(10-15%) of slowdown to track down -- so I don't think it can be the entire solution

[Roger D]

On the subject of fast non-cryptographic-grade 128-bit hash functions in Java, a quick web search turned up e.g. http://greenrobot.org/essentials/features/performant-hash-functions-for-java/ which apparently is available on the Apache 2.0 license .

[Brett Wooldridge]

Strange. Pretty sure my original contribution used CityHash64, with substantially higher throughput (millions of hashes per second)...

[Brett Wooldridge]

I found the commit, and comment here - CityHash128.  Not sure how it got lost in the merge, or why it was altered back to SHA1.  I suggest that you open an issue in their tracker, referencing your results as well as that link.

-Brett

[Roger D]

Brett:

Thanks for looking it to this, and I'm sorry to hear that someone stomped on your submission. Yes, I'll open an issue with them.

[I guess I can see an argument for making the SHA1 vs fast hash choice configurable, since there could be use cases for a JDBC driver where an untrusted party can actually sent you raw SQL, not just SQL parameters (though that's just asking for trouble -- they could send you a DROP TABLES command!), but in our case that absolutely doesn't apply -- it's all Hibernate queries, users can only control parameter values -- and I suspect that should be true for the great majority of secure systems (since the alternative leaves you wide open to SQL injection attacks).]

[Brett Wooldridge]

Only a hash collision, for either SHA1 or CityHash128, poses a risk of incorrect SQL being executed. That is true whether the SQL is parameterized or not. The Earth is more likely to be destroyed by an asteroid sooner than a hash collision in CityHash128.

-Brett

[Roger D]

Understood -- the only functional differences between CiryHash128 and SHA-1 are that CItyHash128 is much faster, but that unlike SHA-1 someone malicious who was able to arbitrarily control the query structure (not just the query parameters) -- say via a SQL injection attack -- could carefully engineer a pair of queries to force an asteroid strike to happen. But then they could presumably also do a DROP TABLES query, so such a system would have much bigger security concerns. Anyway, in our scenario the only party able to pick query structure is Hibernate, who isn't actively trying to crash our server.

I submitted this issue to the mssql-jdbc GitHub:

https://github.com/Microsoft/mssql-jdbc/issues/716

[Roger D]

I have some more accurate YourKit performance analysis measurments: the SHA-1 issue is actually responsible for about a 10-15% slowdown -- a little more than the 5-10% I'd estimated previously.

As Brett suggested, we also tested Hibernate 4 + BoneCP (with caching on) with the old sqljdbc42 4.2.6420.100 MS SQL Server JDBC driver vs same but with the new mssql-jdbc 6.4 driver (not caching): we saw about a 10-15% slowdown for that as well. Then we swapped the caching from BoneCP to the new JDBC driver (thus triggering the SHA-1 issue), and it went to ~25%. So Brett was correct above: the new MS JDBC driver is already slow, even without the SHA-1 hashing issue. So between 10-15% for SHA-1 caching and 10-15% for general slowness, that explains our observed 20-30% slowdown entirely in the new MS JDBC driver, without Hikari or Hibernate 5 having to take any of the blame.