Lots of hibernate queries in heap dump. How can I limit?

Question

I'm dealing with a big application that have hundreds of hibernate entities and EAGER associations. When I start this application the memory consumption is very high (more than 1 GB).

Looking in the JVM heap dump of the application just after started, I analyzed and understood that the most of the memory is occupied by char[] with different SELECT statements from different entities of the application.

I suppose that this SELECTs are generated by the query plan cache of the Hibernate.

I would like to limit the size of this cache for the development environment. I tried to limit the size with some properties:

"hibernate.query.plan_cache_max_size", "16" //default is 2048
"hibernate.query.plan_parameter_metadata_max_size", "16" //default is 128

But there is no difference in the memory consumption.

What I'm supposed to do to limit the cache of Hibernate queries?

I'm using Hibernate 5.0.10.

Answer 1

There are really a lot of issues which were related with "Query Plan Cache Memory usage". (1, 2, 3, 4). But as a solution right now we can setup some settings, one of them - hibernate.query.plan_cache_max_size. It's weird, that it didn't help, in my case it worked. Perhaps the issue is hiding somewhere else.

If you have a heap dump, try to check your assumption by using Memory Analyzer (MAT), run this OQL query (if you haven't done it yet):

SELECT l.query.toString() FROM INSTANCEOF org.hibernate.engine.query.spi.QueryPlanCache$HQLQueryPlanKey l

It will give you an additional information.

And just in case. Did you try to change a value for all these settings?

"hibernate.cache.query_cache_factory"
"hibernate.query.plan_cache_max_size"
"hibernate.query.plan_cache_max_soft_references"
"hibernate.query.plan_cache_max_strong_references"
"hibernate.query.plan_parameter_metadata_max_size"

Answer 2

The "hibernate.query.plan_cache_max_size" works for me. This hibernate query cache can be reduced from the implementation by trying to avoid dynamic parameters sent to "IN" condition in the query.

SELECT * FROM TEST_TABLE WHERE A_COLUNN IN ($param1, $param2, ....)

Each call to the hibernate query which generated above sample query with a specific number of parameter would store 1 row in the cache plan.

Instead hibernate can be configured to send a fixed number of parameters by power of two by setting the property

hibernate.query.in_clause_parameter_padding: true

As an example if there are 5,6,7, or 8 parameters then it will use in ($param1,$param2,$param3,$param4,$param5,$param6,$param7,$param8), in the case that there are only 5 parameters, then the last parameter is repeated 4 times.

In this way the number of query plans in the hibernate cache is greatly reduced. As well as reducing the amount of ram required for these very large queries, it should also increase the hit rate. This benefit extends to the database SQL query plan cache if your RDBMS features one.

Reference: https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#configurations-query