MyBatis memory leak in a query which result is a list of avg. 30k rows

Question

I observed in NetBeans Profiler that Surviving Generations keeps on increasing after I execute the query:

@Select("SELECT * FROM ais_dynamic WHERE rep_time >= #{from} AND rep_time <= #{to} AND ais_system = #{sys}")    
@Options(useCache=false,fetchSize=8192)
List<AisDynamic> getRecords(
        @Param("from") Timestamp from,
        @Param("to") Timestamp to,
        @Param("sys") int sys);

It is as if the objects that are in the list are never released though they are not being used anywhere else and should die with the background thread running the query and processing its results.

Below are the Live Results returned by NetBeans Profiler: Live results from NetBeans Profiler

My questions:

How can I prevent the memory leak?
How can I optimize this query, as one can see I started playing with the Options though this didn't prevent the memory leak?

If anything is needed please do tell what and I will provide.

UPDATE:

After more testing I am more concerned that the problem lays with MyBatis holding a reference to the retrieved results thus they are not garbage collected over time. After doing 20 calls of the query then waiting I observe no garbage collection even after 30 minutes. All that I do is calling the method: List<AisDynamic> adList = mapper.getRecords(from, to, sys);

I also have a memory leak with MyBatis. Did you find any solution for your problem? — Marc
@Marc I observed that it happens only for situations when I am retrieving a large quantities of rows something larger than 10k. The solution which worked for me was to use a JDBC and code all by hand all together and the problem was gone. Tested even for 50k of rows. Though it solved the problem for me I would really preferred to stick to MyBatis all the way as it saves lots of coding but in this case it was not possible. Though I am waiting for a better solution, who knows maybe a fix to the API is needed? — Boro
Can you add this configuration to your MyBatis configuration file and see is there any difference: <settings><setting name="localCacheScope" value="STATEMENT"/></settings> — partlov

Boro Boro · Accepted Answer · 2013-04-25T15:50:33

I tested it over the weekend and it appears that I solved the problem. Thanks @partlov for the suggestion, though it is not the solution it got me to test the problem again and I have spotted the real issue.

The issue was that my client responsible for handling query requests from users was piling up the threads (which were executing the queries). Since the requests were coming very frequently when I stress tested the client thus when the previous query was not done the next one was starting even though I was cancelling them by setting and checking a flag within the run() method of a query. This was appearing in situations when the session of a query was still talking with the database e.g. when a select had 30k+ results. Therefore, though the cancel flag was raised it was not yet checked since the query was in the process of retrieving results from the database. This was enough time for the next query to start so if it also had many results the client was piling up the threads in effect consuming more and more memory.

Since it appears to be no way (that I know of) to cancel a session (e.g. a select query) which talks to the database (in MyBatis) I had to implement a mechanism protecting from it myself. The mechanism I implemented into my client makes sure that the next query will not start until the previous one (executed for the same user) is finished. Therefore, now a query informs the client when it exits its run() method and only then the next query for the same user might be started.

Update I learned from experience that the only and a bit dirty way (per my taste) to abort/cancel a long retrieving transaction is by calling close() method of the SqlSession instance that the transaction uses. This will result in an Exception (example below) which must be caught and handled as intended.

org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.lang.NullPointerException
### The error may exist in YourMapper.java (best guess)
### The error may involve methodOfTheHandlerInvolved
### The error occurred while handling results
### SQL: sqlOfYourQuery
### Cause: java.lang.NullPointerException
... (and a trace follows) ...

MyBatis memory leak in a query which result is a list of avg. 30k rows

1 Answers