I am trying to export data in a CSV file, there are 100k rows to be exported. I am getting java.lang.OutOfMemoryError: GC overhead limit exceeded error and other warning messages.

I have increased the heap memory to 8GB but still getting the out-of-memory error. Please help if any code change needs to be done or to look at the server configuration settings.

I am using SpringMVC4, Java 1.8 and Tomcat version 7 for the development.

I have a UI page with an image icon (export to excel) to download the report

Steps for loading data into CSV file:

1. Data pull from database table using PreparedStatement.
2. Condition applied to skip the rows.
3. Data are appended in an object array.
4. Header columns are formed using a PreparedStatement (the header columns vary based on step1).
5. Data load to CSV file from the object array and writing to the file using XSSFWorkbook.

Getting out of memory error in step5 while writing the data into the CSV file.

Code Snippet

//fetch data
String query="select partid, partnumber, partname from tparts";
      List<Map<String, Object>> rows = jdbcTemplate.queryForList(query);
       for(Map<String, Object> row : rows){
          String strPartId = row.get("partid").toString();
          String strPartName = (String)row.get("partname");
          }
 
//xls download
public byte[] ExportPartsData(PartsRequestVO reqVo, PartsDataVo partsDataVo) throws Exception {
    XSSFWorkbook xssWrkBook = new XSSFWorkbook();
    XSSFSheet xssSheet = xssWrkBook.createSheet();
     
    List<Object[]> lstObj = partsDataVo.getPartsData();
     
    ByteArrayOutputStream outStream = new ByteArrayOutputStream();
    xssWrkBook.write(outStream);
    return outStream.toByteArray();

Error Log

Handler processing failed; nested exception is java.lang.OutOfMemoryError: GC overhead limit exceeded at org.springframework.web.servlet.DispatcherServlet.triggerAfterCompletionWithError(DispatcherServlet.java:1303) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:977) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970) at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872) at javax.servlet.http.HttpServlet.service(HttpServlet.java:660) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846) at javax.servlet.http.HttpServlet.serviceInternal(HttpServlet.java:741) at sun.reflect.GeneratedMethodAccessor76.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498)

WARN JDBC Connection to reset not identical to originally prepared Connection - please make sure to use connection release mode ON_CLOSE (the default) and to run against Hibernate 4.2 (or switch HibernateJpaDialect’s prepareConnection flag to false

Exception: java.lang.OutOfMemoryError thrown from the UncaughtExceptionHandler in thread

CodePudding user response：

Try using Spring batch excel: https://github.com/spring-projects/spring-batch-extensions/tree/main/spring-batch-excel

You can use StreamingXlsxItemReader it's made for reading big data files.

Quote from project README below:

To reduce the memory footprint the StreamingXlsxItemReader can be used, this will only keep the current row in memory and discard it afterward. Not everything is supported while streaming the XLSX file. It can be that formulas don’t get evaluated or lead to an error.

CodePudding user response：

From your log it's not visible, which call the exception invokes. Assume it is

xssWrkBook.write(outStream);

Consider, that ByteArrayOutputStream is limited to 2GB and in case of the SUN runtime it's allocation concept is not optimized. You anyway want to write to a CSV file!?