9

I code a server side application with java run on linux server. I use hibernate to open session to database, use native sql to query it and always close this session by try, catch, finally.

My server query DB using hibernate with very high frequency.

I already define MaxHeapSize for it is 3000M but it usually use 2.7GB on RAM, it can decrease but slower than increase. Sometime it grow up to 3.6GB memory usage, more than my MaxHeapSize define when start.

When memory used is 3.6GB, i try to dump it with -jmap command and got a heapdump with size of 1.3GB only.

Im using eclipse MAT to analyse it, here is the dominator tree from MAT Dominator tree I think hibernate is the problem, i have so many org.apache.commons.collections.map.AbstractReferenceMap$ReferenceEntry like this. It maybe cant be dispose by garbage collection or can but slow.

How can i fix it?

Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
Viet
  • 443
  • 1
  • 5
  • 16

3 Answers3

16

You have 250k entries in your IN query list. Even a native query will put the database to its knees. Oracle limits the IN query listing to 1000 for performance reasons so you should do the same.

Giving it more RAM is not going to solve the problem, you need to limit your select/updates to batches of at most 1000 entries, by using pagination.

Streaming is an option as well, but, for such a large result set, keyset pagination is usually the best option.

If you can do all the processing in the database, then you will not have to move 250k records from the DB to the app. There's a very good reason why many RDBMS offer advanced procedural languages (e.g. PL/SQL, T-SQL).

Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
  • Can you teach me a little bit more? I have little experience about oracle database interaction. – Viet Jun 23 '14 at 07:26
  • 1
    You need to fetch N entries, update them then fetch the next batch and so on. – Vlad Mihalcea Jun 23 '14 at 07:32
  • i'm still confused, in my query "UPDATE .. WHERE .. IN (...)", IN query list is small and it cant reach 1000. And when i expand to leaf of Dominator tree in eclipse MAT, i find my select query ('sqlQueryCheckSet' in my code), many of it, and i think only it – Viet Jun 23 '14 at 08:41
  • Check this [Hibernate issue](https://hibernate.atlassian.net/browse/HHH-5300). Set: "hibernate.query.plan_cache_max_soft_references" and "hibernate.query.plan_cache_max_strong_references" to some appropriate values. – Vlad Mihalcea Jun 23 '14 at 09:01
  • Great. So the question is answered then. – Vlad Mihalcea Jun 30 '14 at 03:14
  • Does the hibernate issue hapen only for HQL queries or its applicable for native sql queries executed thru hibernate also? – mihirg Dec 22 '16 at 05:36
  • It applies to native SQL queries only if you explicitly select entities. For scalar results or DTO projections, you are fine. – Vlad Mihalcea Dec 22 '16 at 06:40
3

Notice that even although the number of object within the queryPlanCache can be configured and limited, it is probably not normal having that much.

In our case we were writing queries in hql similar to this:

hql = String.format("from Entity where msisdn='%s'", msisdn);

This resulted in N different queries going to the queryPlanCache. When we changed this query to:

hql = "from Blacklist where msisnd = :msisdn";
...
query.setParameter("msisdn", msisdn);

the size of queryPlanCache was dramatically reduced from 100Mb to almost 0. This second query is translated into a one single preparedStament resulting just one object inside the cache.

jalogar
  • 1,604
  • 19
  • 27
1

Thank you Vlad Mihalcea with your link to Hibernate issue, this is bug on hibernate, it fix on version 3.6. I just update my hibernate version 3.3.2 to version 3.6.10, use default value of "hibernate.query.plan_cache_max_soft_references" (2048), "hibernate.query.plan_cache_max_strong_references" (128) and my problem is gone. No more high memory usage.

Viet
  • 443
  • 1
  • 5
  • 16