Spring Batch-JdbcCursorItemReader使用大型MySQL表抛出OutOfMemoryError

问题描述

我正在使用Spring Batch编写程序来处理MySQL数据库表中的7,637,064行.我已经成功使用较小的表，但是当JdbcCursorItemReader尝试打开游标时，该表中的大量行导致OutOfMemoryError异常.

I am writing a program using Spring Batch to process 7,637,064 rows from a MySQL database table. I've had success with smaller tables, but the large number of rows in this table is causing OutOfMemoryError exceptions when the JdbcCursorItemReader attempts to open the cursor.

我可能可以通过向其扔一个更大的Xmx来解决此问题，但是在我看来，Spring Batch应该可以处理此问题，而我可能只是缺少关键的配置.

I could probably resolve this by throwing a larger Xmx at it, but it seems to me that Spring Batch should have a way to handle this and that I may simply be missing a key piece of configuration.

Spring Batch配置:

Spring Batch configuration:

  <job id="reportJob" xmlns="http://www.springframework.org/schema/batch">
    <step id="largeTableTransfer">
      <tasklet>
        <chunk reader="largeTableReader" processor="largeTableTransformer" writer="largeTableWriter"
          commit-interval="10" />
      </tasklet>
    </step>
  </job>

  <bean id="largeTableReader" class="org.springframework.batch.item.database.JdbcCursorItemReader">
    <property name="dataSource" ref="inputDataSource" />
    <property name="sql" value="select * from largeTable" />
    <property name="rowMapper">
      <bean class="myproject.reader.largeTableRowMapper" />
    </property>
  </bean>
  <bean id="largeTableTransformer" class="myproject.transformer.LargeTableTransformer" />
  <bean id="largeTableWriter" class="myproject.writer.JdbcLargeTableWriter">
    <property name="dataSource" ref="outputDataSource" />
  </bean>

在JdbcCursorItemReader上设置 fetchSize 似乎无效.允许它运行完成的唯一方法是将 maxRows 设置为一个较小的数字，但随后仅处理该数目的行.

Setting fetchSize on the JdbcCursorItemReader appears to have no effect. The only thing that allows it to run to completion is to set the maxRows to a small number, but then only that number of rows get processed.

相关的堆栈跟踪:

2012-11-21 11:25:29,931 DEBUG [org.springframework.batch.core.repository.dao.JdbcStepExecutionDao] - <Truncating long message before update of StepExecution, original message is: java.lang.OutOfMemoryError: Java heap space
    at java.util.Arrays.copyOf(Arrays.java:2734)
    at java.util.ArrayList.ensureCapacity(ArrayList.java:167)
    at java.util.ArrayList.add(ArrayList.java:351)
    at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:2821)
    at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:467)
    at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:2510)
    at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:1746)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2135)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1885)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
    at org.springframework.batch.item.database.JdbcCursorItemReader.openCursor(JdbcCursorItemReader.java:125)
    at org.springframework.batch.item.database.AbstractCursorItemReader.doOpen(AbstractCursorItemReader.java:401)
    at org.springframework.batch.item.support.AbstractItemCountingItemStreamItemReader.open(AbstractItemCountingItemStreamItemReader.java:134)
    at org.springframework.batch.item.support.CompositeItemStream.open(CompositeItemStream.java:93)
    at org.springframework.batch.core.step.tasklet.TaskletStep.open(TaskletStep.java:301)
    at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:192)
    at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:135)
    at org.springframework.batch.core.job.flow.JobFlowExecutor.executeStep(JobFlowExecutor.java:61)
    at org.springframework.batch.core.job.flow.support.state.StepState.handle(StepState.java:60)
    at org.springframework.batch.core.job.flow.support.SimpleFlow.resume(SimpleFlow.java:144)
    at org.springframework.batch.core.job.flow.support.SimpleFlow.start(SimpleFlow.java:124)
    at org.springframework.batch.core.job.flow.FlowJob.doExecute(FlowJob.java:135)
    at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:293)
    at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:120)
    at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:48)
    at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:114)
    at ifpress.ams2amx.ExampleJobConfigurationTests.testLaunchJob(ExampleJobConfigurationTests.java:32)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

推荐答案

MySql JDBC驱动程序中存在一个问题，该问题导致将整个数据集加载到内存中，而不管传递给语句创建方法的参数如何.参见 http://dev.mysql. com/doc/refman/5.0/en/connector-j-reference-implementation-notes.html 了解如何正确打开游标.

There is an issue in MySql JDBC driver which causes loading of the whole dataset into memory regardless of the parameters you have passed to the statement creation method. See http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-implementation-notes.html for how properly open a cursor.

这是我的方法:

<bean class="org.springframework.batch.item.database.JdbcCursorItemReader">
  <property name="verifyCursorPosition" value="false" />
   <property name="dataSource" ref="remoteDataSource" />
   <property name="rowMapper">
     <bean class="org.springframework.jdbc.core.SingleColumnRowMapper" />
   </property>
   <property name="fetchSize">
     <util:constant static-field="java.lang.Integer.MIN_VALUE" />
   </property>
   <property name="sql">
     <value>SELECT foo, bar FROM baz</value>
  </property>
</bean>

这篇关于Spring Batch-JdbcCursorItemReader使用大型MySQL表抛出OutOfMemoryError的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！