Handling big data on Mybatis

Sometimes, we need to query big data from database. In that case, OutOfMemory error or frequent full GC can happen.

Suppose that there are several million records and we need to query all data to process some logic.

Example sql map

big_data_query_mapper

Example dao

	public List selectBigData1() {
		return this.sqlSession.selectList("test.selectBigData1");
	}

When this method is called, OutOfMemory Exception can happen. The following heap dump shows that ArrayList is causing the exception. (This screen is from IBM Heap Analyzer)

outofmemory_error01

Solution 1 – Using ResultHandler

Mybatis supports ResultHandler for handling big ResultSet. When query result is processed, ResultHandler’s callback is called for each record instead of making ArrayList.

Sample ResultHandler

import org.apache.ibatis.session.ResultContext;
import org.apache.ibatis.session.ResultHandler;
import java.util.Map;

public class BigDataResultHandler implements ResultHandler<Map>{

	private int count = 0;

	@Override
	public void handleResult(ResultContext<? extends Map> resultContext) {
		if(resultContext.getResultObject() != null) {
			count++;
		}
	}

	public int getTotalCount() {
		return this.count;
	}
}

Sample dao

	public void setResultHandler(BigDataResultHandler resultHandler) {
		this.resultHandler = resultHandler;
	}

	public void selectBigData2() {
		this.sqlSession.select("test.selectBigData1", this.resultHandler);
	}

Notice that this dao return type is void.

Solution 2 – Tuning fetch size

Sometimes, ResultHandler might not be sufficient to avoid memory issue. For some database, program with ResultHandler could cause OutOfMemory error.

outofmemory_error02

Above heap dump shows that OutOfMemory is occured at JDBC driver’s PreparedStatement. (Default fetch size of Oracle JDBC driver is “10”. Above error is made by changing default value) In this case, tuning fetch size of ResultSet solves memory issue. ResultSet’s fetch size can be set at JDBC driver property or Mybatis mapper.

mapper_fetch_size

Above mapper has “fetchSize” attribute, which is used as a hint to JDBC driver

Conclusion

When querying data, memory is allocated at 1) JDBC driver and 2) Mybatis result handler.

JDBC driver’s memory usage is dependent on each database’s default fetch size and need to be changed.

Mybatis result handler’s memory can be tuned by implementing ResultHandler.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.