Want to track how long Hibernate session is using DB connection from JDBC connection pool

[aldair ryer]

We are testing one Java app where we are using Oracle as database. Java app is simple Spring boot application which uses Hibernate to do db related queries. While doing load testing, we found out that we are facing db connection exhaustion error with a limited set of concurrent users.

To debug above problem, I am trying to see when Hibernate is acquiring a connection & when it is releasing it to JDBC connection pool. If there is any long running session, then we can tweak it. Else we will increase connection pool size. But I am not able to find a way to track actual time when a db connection is  getting used by Hibernate session. I couldn't find any option in Hibernate to log that or enable some feature to store the metrics. Any help would be appreciated.

Thanks.

[Tim Holloway]

That wouldn't be a Hibernate option. And what really matters isn't Hibernate as such as it is the amount of time a connection is checked out of the pool.

The usual solution to that problem is to switch on connection pool options for tracking orphaned (unclosed) connections. That causes the pool to construct an Exception (meaning a caller stack trace) and store it with a timer that, when expired, prints the stack trace to the server (not application) log. Since the stack track traces back to the point in the application where the connection was obtained, and since usually when you know where that is, you can figure out where the application used the connection, you can get an idea of where it should have been closed. And make sure that it does in fact get closed and not forgotten if some other exception bypassed the usual close logic.

Of course, you may actually just have so many users that it's not the time the connection is held so much as the availability. which you'd address by re-configuring the pool size.

And of course, never, absolutely never hold a connection between user requests.

[aldair ryer]

Tim, thanks for the suggestion. I mostly agree with what you suggested. I want to test one additional case. Ideally our app should have Hibernate session which lasts milliseconds. I want to check if there is any long lasting session which span seconds. That might not be caught with timeout exception handling as they might not be timed out (unless I set time out to 1-2 seconds). I found out this article & it shows how to enable logging when a db connection is acquired by Hibernate session & also when the connection gets released. I am planning to use this to rule out such case.

[Tim Holloway]

I see that this is your second post. Welcome to the Ranch!

Yes, that would have been my recommendation also. For stats, you could probably write a custom log formatter that stored timings directly in a database.

Due to latency, it's hard to get consistent timings to milliseconds. At that point, you might want to look at local caching options and have the DBA look for tuning possibilities. Usually a second or 3 isn't worth worrying about, but if you're running that quickly and still running out of connections, consider expanding the pool size and/or going elastic.