OOM when reading data from mybatis

user_3821387 0
Viewed 417

I am getting an OOM error when trying to read a large result set using MyBatis. MyBatis version 3.5 I want to iterate over the cursor instead of loading the entire query result. Does anyone have a working example of returning a cursor from mybatis select instead of entire result.

[java] SEVERE:Memory usage is low, parachute is non existent, your system may start failing.
[java] java.lang.OutOfMemoryError: Java heap space
[java] AsyncLogger error handling event seq=0, value='null':
[java] java.lang.OutOfMemoryError: Java heap space
[java] 06 Sep 2020 05:34:34,682 [ERROR]  (http-nio-0.0.0.0-8243-ClientPoller-1) org.apache.tomcat.util.net.NioEndpoint:
[java] java.lang.OutOfMemoryError: Java heap space
[java]  at java.util.Collections$UnmodifiableCollection.iterator(Collections.java:1043) ~[?:?]
[java]  at java.lang.Thread.run(Thread.java:834) [?:?]

ReaderDao.xml

<select id="downloadelements" resultMap="elementMap">
    select *
    from sample_table
    where element_timestamp between #{startTime} and #{endTime}
    and status=#{status}
</select>

ReaderDao.java

public interface ReaderDao {
Cursor<Element> downloadElements(final @NotNull @Param("startTime") ZonedDateTime startTime,
                                                final @NotNull @Param("endTime") ZonedDateTime endTime,
                                                final @NotNull @Param("status") String status);
}

Database: Aurora PostgreSQL

Cursor<Element> elementList = downloadElements(start, end, status);
Iterator<Element> elementIterator = elementList.iterator();
while (elementIterator.hasNext()) {
    Element element = ElementIterator.next();
    log.info(element.toString());
}

<resultMap id="elementMap" type="Element">
    <constructor>
        <arg javaType="String" column="status"/>
        <arg javaType="java.time.ZonedDateTime" column="start_time"/>
        <arg javaType="java.util.Optional" jdbcType="TIMESTAMP"  column="end_time"/>
    </constructor>
</resultMap>
mybatis java
1 Answers

Since PostgreSQL driver does not support streaming results (as pointed out by @ave) you most likely want to implement result pagination. This is usually done either by using ORDER BY element_timestamp LIMIT <n> OFFSET <k> or by using keyset pagination. Either of these methods will load smaller batches of results into memory hopefully avoiding OOME by allowing GC to free each batch separately.

However it should be noted that sometimes increasing the available heap memory is the easiest solution. RAM is usually cheaper than programmers time so perhaps you should start by estimating how many records are you trying to process and how much memory is needed.

user_16025550
Related