英文:
OOM when reading data from mybatis
问题
以下是翻译好的内容：
我在尝试使用 MyBatis 读取大型结果集时遇到了内存溢出错误。
MyBatis 版本 3.5。
我想要遍历游标，而不是加载整个查询结果。
是否有人有从 MyBatis 查询中返回游标的可用示例？
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);
}
数据库：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>
英文:
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>
答案1
得分: 0
由于PostgreSQL驱动程序不支持流式结果（正如@ave指出的那样），您很可能希望实现结果分页。这通常通过使用ORDER BY element_timestamp LIMIT <n> OFFSET <k>或使用键集分页来完成。这些方法中的任何一种都将将较小的结果批次加载到内存中，希望通过允许GC单独释放每个批次来避免OOME。
然而，应该注意有时增加可用的堆内存是最简单的解决方案。内存通常比程序员的时间更便宜，因此也许您应该首先估计要处理多少条记录以及需要多少内存。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论