Spring batch JdbcCursorItemReader cause a memory not enough issue

3
votes

I am using the spring batch framework to do a data migration. The reader I use is JdbcCursorItemReader. I set chunk size as 500 and set the reader fetch size as 1000. But when run the service with spring batch it just seems to read all the data once in the memory and run out of the memory . then throw a memory not enough issue. Below is how I define the reader:

   private JdbcCursorItemReader<Map<String, Object>> buildItemReader(final DataSource dataSource, String tableName,String tenant) {
        String tenantName = tenantHelper.determineTenant(tableName);
        JdbcCursorItemReader<Map<String, Object>> itemReader = new JdbcCursorItemReader<>();
        itemReader.setDataSource(dataSource);
        itemReader.setSql("select * from " + tableName + " where " + tenantName + " ='" + tenant + "'");
        itemReader.setRowMapper(new ColumnMapRowMapper());
        itemReader.setFetchSize(100);
        return itemReader;
    }

Whats more, from the spring batch document here, we should be able to avoid the memory issue by using the jdbcCursorItemReader

3
javaspring-batch
Obviously the query will fetch full result only because you don't have a limit in size. I think you need Partitioner - soorapadman
Hi @soorapadman ,Does the Partitioner mean to split the process to more steps? For certain reasons I could not use that function because I have to create step dynamicly. I am also thinking about to use the jdbcPagingItemReader. But the paging also do not work for me , I also tried as described in this question :stackoverflow.com/questions/55369572/… - Ray
The fetch size is just a hint to the database driver and its implementation depends on the database product. Which database do you use? - Mahmoud Ben Hassine
I am using the postgresql. And I debuged the jdbcCursorItemReader, it did read/write by the fetch size and chunk size. But i am just confused why it use all the memory and load all the data to the memory - Ray
ok thanks for the update. I added an answer, hope it helps. - Mahmoud Ben Hassine

3 Answers

4
votes

You can try using a JdbcPagingItemReader instead of JdbcCursorItemReader where the page size can be set while configuring it

3
votes

Figured this out by using the jdbcPagingItemReader. The root cause that the the cursor reader consume massive memory is because it just read all the data into the memory and then process it, which will be considered as a big object by the JVM and it will be allocated into the old generation directlly, Until the whole process finished, it could not be collected.

1
votes

i am just confused why it use all the memory and load all the data to the memory

According to Postgresql's documentation, the driver collects all the results for a query at once.

You can probably try to turn the cursor off by setting the fetch size to 0. There are other constraints as explained in the aforementioned doc, please make sure your code meets all of them. Just for reference, this is similar to what might happen with MySQL where the fetch size should be set to Integer.MIN_VALUE to stream results (See here and here).

Hope this helps.