How to remove the Exception in thread "http-bio-8080-exec-5" java.lang.OutOfMemoryError: GC overhead limit exceeded?

Question

In my application I am reading 30mb size xlsx file data that contains 500 000 rows and inserting into database.

When I run the application after some time I get the below exception. I searched for solution, but I am not able to understand how to do it.

Exception in thread "http-bio-8080-exec-5" java.lang.OutOfMemoryError: GC overhead limit exceeded
at org.apache.xmlbeans.impl.store.Cur$CurLoadContext.attr(Cur.java:3044)
at org.apache.xmlbeans.impl.store.Cur$CurLoadContext.attr(Cur.java:3065)
at org.apache.xmlbeans.impl.store.Locale$SaxHandler.startElement(Locale.java:3263)
at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.reportStartTag(Piccolo.java:1082)
at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseAttributesNS(PiccoloLexer.java:1802)
at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseOpenTagNS(PiccoloLexer.java:1521)
at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseTagNS(PiccoloLexer.java:1362)
at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseXMLNS(PiccoloLexer.java:1293)
at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseXML(PiccoloLexer.java:1261)
at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.yylex(PiccoloLexer.java:4812)
at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.yylex(Piccolo.java:1290)
at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.yyparse(Piccolo.java:1400)
at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.parse(Piccolo.java:714)
at org.apache.xmlbeans.impl.store.Locale$SaxLoader.load(Locale.java:3479)
at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1277)
at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1264)
at org.apache.xmlbeans.impl.schema.SchemaTypeLoaderBase.parse(SchemaTypeLoaderBase.java:345)
at org.apache.poi.POIXMLTypeLoader.parse(POIXMLTypeLoader.java:92)
at org.openxmlformats.schemas.spreadsheetml.x2006.main.WorksheetDocument$Factory.parse(Unknown Source)
at org.apache.poi.xssf.usermodel.XSSFSheet.read(XSSFSheet.java:173)
at org.apache.poi.xssf.usermodel.XSSFSheet.onDocumentRead(XSSFSheet.java:165)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.parseSheet(XSSFWorkbook.java:417)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.onDocumentRead(XSSFWorkbook.java:382)
at org.apache.poi.POIXMLDocument.load(POIXMLDocument.java:178)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:279)
at com.dip.SendXlsxToDb.doPost(SendXlsxToDb.java:44)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:650)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:747)
at org.apache.catalina.core.ApplicationDispatcher.doInclude(ApplicationDispatcher.java:603)

My code:

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Iterator;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.swing.text.ZoneView;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class SendXlsxToDb extends HttpServlet{

@Override
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    try
    {
        RetriveData rdata= new RetriveData();
        //System.out.println("ZoneId is :"+rdata.zoneId);
        //System.out.println("Location is :"+rdata.location);
        HttpSession hs = request.getSession(false);
        System.out.println("=======================SendXlsxToDb========================");
        //creating db connection
        Class.forName("com.mysql.jdbc.Driver");
        Connection con1 = DriverManager.getConnection("jdbc:mysql://localhost:3306/xlsx","root","Inf123#");
        PreparedStatement ps = con1.prepareStatement("INSERT INTO userdetails(ZONEID, LOCATION, ID, NAME, AGE, GENDER, ADDRESS) VALUES(?, ?, ?, ?, ?, ?, ?)");


        FileInputStream file = new FileInputStream(new File("C:/Users/Desktop/New folder/"+hs.getAttribute("filename1")));

        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        //Get first/desired sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(0);

        //Iterate through each rows one by one
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) 
        {
            int i = 3;
            Row row = rowIterator.next();
            //For each row, iterate through all the columns
            Iterator<Cell> cellIterator = row.cellIterator();
            ps.setString(1, (String)hs.getAttribute("zoneId1"));
            ps.setString(2, (String)hs.getAttribute("location1"));
            while (cellIterator.hasNext()) 
            {
                Cell cell = cellIterator.next();
                ps.setString(i, cell.toString());
                i++;

                //Check the cell type and format accordingly
               /* switch (cell.getCellType()) 
                {
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.print(cell.getNumericCellValue() + "\t");

                        break;
                    case Cell.CELL_TYPE_STRING:
                        System.out.print(cell.getStringCellValue() + "\t");
                        break;
                }*/
            }
            ps.executeUpdate();
            //System.out.println("");
        }
        file.close();
        ps.close();
        con1.close();
        System.out.println("THE EXECUTION OF THE PROGRAM IS COMPLETED");
        request.setAttribute("message","The XLSX File Data Transferred Successfully");
    } 
    catch (Exception e) 
    {
        e.printStackTrace();
    }

}


}

How to remove this error? I am using java 1.7 and windows system

Check this out http://stackoverflow.com/questions/5839359/java-lang-outofmemoryerror-gc-overhead-limit-exceeded — 11thdimension, Apr 20 '16 at 12:59
As you already use an iterator, you can remove an already processed row at the end of the loop using `rowIterator.remove()` and therefore make the data eligible for garbage collection. This may spare you from having to set certain GC or heap-size tunings. Also, taking heapdumps and verifying what is available in memory at given timepoints may help to understand the actual cause of the OOM error — Roman Vottner, Apr 20 '16 at 13:52
i am getting exception in this line only XSSFWorkbook workbook = new XSSFWorkbook(file); — honey1, Apr 21 '16 at 06:49

score 0 · Answer 1 · answered Apr 20 '16 at 12:59

0

This exception tells you that the VM spends more than 98% of its time garbage collecting. You can try to user other collectors (e.g., -XX:+UseG1GC is very well suited for big heaps) but it is far more likely you have a memory leak somewhere. Examine your code closely, maybe with a tool like VisualVM or AntTracks to find out what is in your heap.

EDIT: in case you really need that much heap, try increasing the heap size with -Xmx4G or even more ...

answered Apr 20 '16 at 12:59

loonytune

1,568
8
22

where can i increase the heap size with -xmx4G . and where can i find xmx4g. give me step wise – honey1 Apr 20 '16 at 13:19
@honey1 You can increase heap size on your VM arguments. But as loonytune said, you are more likely to have a memory leak. So increasing heap size is just a temporal solution. I'm pretty sure that memory leak occurs when parsing your file with Apache POI. – Adrián Apr 20 '16 at 13:46
what is memory leak ? why it's happen? how to increase heap size? – honey1 Apr 20 '16 at 13:49
Actually the GC is not collecting but trying to find collectable objects. As it will therefore may stop-the-world this will noticably slow down the application. As it can't free up enough memory (due to traceable hard-references from root to the respective objects), it will try harder (=more often) to find collectable references. If the GC though exceeds a certain amount of execution time compared to running the actual code, the exception is raised – Roman Vottner Apr 20 '16 at 14:03
How increase heap size? in wndows – honey1 Apr 21 '16 at 04:28
You can increase the heap size by adding -Xmx4G when executing the java command, e.g., java -Xmx4G -jar myapplication.jar. But as I've said, and the comments also, a memory leak is more likely. That means that you keep referencing objects you do not need anymore. – loonytune Apr 21 '16 at 10:49

Adrián · Answer 2 · 2016-04-20T13:18:22.003

0

Try to use a File instead of a FileInputStream when constructing your workbook:

https://poi.apache.org/spreadsheet/quick-guide.html#FileInputStream

This should consume less memory.

Also try to use SXSSFWorkbook instead of XSSFWorkbook to have a small memory footprint. Please see this link:

http://poi.apache.org/spreadsheet/how-to.html#sxssf

edited Apr 20 '16 at 13:18

answered Apr 20 '16 at 13:07

Adrián

409
2
14

https://poi.apache.org/spreadsheet/quick-guide.html#FileInputStream : i tried both but i am not able to solve – honey1 Apr 20 '16 at 13:50
You may try this as well: http://poi.apache.org/spreadsheet/how-to.html#xssf_sax_api – Adrián Apr 20 '16 at 13:55
poi.apache.org/spreadsheet/how-to.html#xssf_sax_api :- in this i am not getting my complete row data it giving some reference. how to get exact row data. – honey1 Apr 21 '16 at 05:45
poi.apache.org/spreadsheet/how-to.html#xssf_sax_api -by using this i able to get data cell by cell, but how to separate row wise. – honey1 Apr 21 '16 at 06:53
poi.apache.org/spreadsheet/how-to.html#xssf_sax_api - in this how can i separate data by row wise. – honey1 Apr 21 '16 at 07:20

How to remove the Exception in thread "http-bio-8080-exec-5" java.lang.OutOfMemoryError: GC overhead limit exceeded?

2 Answers2