Why Hibernate slows down after while?

Question

What could be a reason that hibernate slows down after while executing the same queries (read only)?

logs:

   368588 nanoseconds spent acquiring 1 JDBC connections;
   0 nanoseconds spent releasing 0 JDBC connections;
   158339 nanoseconds spent preparing 3 JDBC statements;
   33855736 nanoseconds spent executing 3 JDBC statements;
   0 nanoseconds spent executing 0 JDBC batches;
   0 nanoseconds spent performing 0 L2C puts;
   0 nanoseconds spent performing 0 L2C hits;
   0 nanoseconds spent performing 0 L2C misses;
   0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
   9437 nanoseconds spent executing 3 partial-flushes (flushing a total of 0 entities and 0 collections)
}
2020-03-24 09:02:54.488  INFO 12024 --- [io-8064-exec-10] i.StatisticalLoggingSessionEventListener : Session Metrics {
   44640 nanoseconds spent acquiring 1 JDBC connections;
   0 nanoseconds spent releasing 0 JDBC connections;
   134337 nanoseconds spent preparing 3 JDBC statements;
   33256438 nanoseconds spent executing 3 JDBC statements;
   0 nanoseconds spent executing 0 JDBC batches;
   0 nanoseconds spent performing 0 L2C puts;
   0 nanoseconds spent performing 0 L2C hits;
   0 nanoseconds spent performing 0 L2C misses;
   0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
   9156 nanoseconds spent executing 3 partial-flushes (flushing a total of 0 entities and 0 collections)
}
2020-03-24 09:02:56.143  INFO 12024 --- [nio-8064-exec-1] i.StatisticalLoggingSessionEventListener : Session Metrics {
   47060 nanoseconds spent acquiring 1 JDBC connections;
   0 nanoseconds spent releasing 0 JDBC connections;
   123461 nanoseconds spent preparing 3 JDBC statements;
   468759882 nanoseconds spent executing 3 JDBC statements;
   0 nanoseconds spent executing 0 JDBC batches;
   0 nanoseconds spent performing 0 L2C puts;
   0 nanoseconds spent performing 0 L2C hits;
   0 nanoseconds spent performing 0 L2C misses;
   0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
   9680 nanoseconds spent executing 3 partial-flushes (flushing a total of 0 entities and 0 collections)
}

as you can see from the logs, the execution time increases tenfold

Does it go back down, could it be that the database is just busier? — 123, Mar 24 '20 at 08:11
Database is not busy. After while it slows down and stays that way (first 10 times execution time is about 100ms then every next ~500ms). — Wenaro, Mar 24 '20 at 08:15
How fast are the requests sent? Might be the database throttles rapid subsequent requests. Seems very unlikely the spring app is slowing it down. Try querying the database manually from the same IP whilst the tests are running and see if its slow. — 123, Mar 24 '20 at 08:20
I did it and the execution time was around 30ms, it does not increase even once to more than 30ms — Wenaro, Mar 24 '20 at 08:26
Might be this then, https://stackoverflow.com/questions/1791050/hibernate-queries-slow-down-drastically-after-an-entity-is-loaded-in-the-session — 123, Mar 24 '20 at 08:35
Can you post a minimal example of code to reproduce this? Also what db are you using? — 123, Mar 24 '20 at 08:54
I'm unable to reproduce, so not really sure what else to suggest, other than to make a minimal example which still exhibits this behaviour. — 123, Mar 24 '20 at 09:05
it happend after extracting filtering from json field to column also index was added, and the query is built using specifications — Wenaro, Mar 24 '20 at 09:25

score 1 · Answer 1 · answered Mar 24 '20 at 10:46

Hibernate c3p0 can be configured in order to increase efficiency. Connection pool is good for performance, as it prevents Java application create a connection each time when interact with database and minimizes the cost of opening and closing connections.

Try this link for configuring hibernate c3p0 in your application :

https://mkyong.com/hibernate/how-to-configure-the-c3p0-connection-pool-in-hibernate/

Why Hibernate slows down after while?

1 Answers1