Saturday, February 14, 2015

Hibernate, In-clause, OutOfMemory

Using Hibernate 4.2 and MySQL in a project with an in-clause query such as:
select t from Thing t where in (?)

Hibernate caches these parsed HQL queries.
Specifically the Hibernate SessionFactoryImpl has QueryPlanCache with queryPlanCache and parameterMetadataCache.
But this proved to be a problem when the number of parameters for the in-clause is large and varies.

These caches grow for every distinct query.
So this query with 6000 parameters is not the same as 6001.

The in-clause query is expanded to the number of parameters in the collection.
Metadata is included in the query plan for each parameter in the query, 
including a generated name like x10_, x11_ , etc.

Imagine 4000 different variations in the number of in-clause parameter counts, each of these with an average of 4000 parameters.
The query metadata for each parameter quickly adds up in memory, filling up the heap, since it can't be garbage collected.

This continues until all different variations in the query parameter count is cached or the JVM runs out of heap memory and starts throwing java.lang.OutOfMemoryError: Java heap space.

Avoiding in-clauses is an option, as well as using a fixed collection size for the parameter (or at least a smaller size).


Check out this great post with more info.

For configuring the query plan cache max size, see the property 'hibernate.query.plan_cache_max_size', defaulting to 2048 (easily too large for queries with many parameters)