CODEWITHSUNDEEP
javaspring-batch
Ray
Ray

Reputation: 537

Spring batch JdbcCursorItemReader cause a memory not enough issue

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

Upvotes: 7

Views: 6122

Answers (3)

Ray
Ray

Reputation: 537

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.

Upvotes: 4

Anirudh Simha
Anirudh Simha

Reputation: 360

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

Upvotes: 6

Mahmoud Ben Hassine
Mahmoud Ben Hassine

Reputation: 31600

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.

Upvotes: 2

Related Questions