I am using poi 3.9-20121203. Is the java version that I am using OK ? ----------- java version "1.7.0_13" Java(TM) SE Runtime Environment (build 1.7.0_13-b20) Java HotSpot(TM) 64-Bit Server VM (build 23.7-b01, mixed mode) ------------
Regards, -Mohan On Tue, Oct 28, 2014 at 9:51 AM, Mohan Nuggehally < [email protected]> wrote: > David, > > I did 'cell = null' at the end of inner loop and 'row = null' at the end > of the outer loop. It did not help. > > Nick, > > I tried SSPerformance Test with inputs "XSSF 152434 40 1" (I have a total > of close to 6,100,000 data values) and failed with the GC overhead limit > reached error for both -Xmx1G and -Xmx4G. Please see the error stacks below. > > For -Xmx1G > ------------------- > Exception in thread "main" java.lang.OutOfMemoryError: GC overhead limit > exceeded > at org.apache.xmlbeans.impl.store.Cur.createElementXobj(Cur.java:257) > at org.apache.xmlbeans.impl.store.Cur.createElement(Cur.java:228) > at org.apache.xmlbeans.impl.store.Cur.createElement(Cur.java:223) > at org.apache.xmlbeans.impl.store.Xobj.insertElement(Xobj.java:2098) > at org.apache.xmlbeans.impl.store.Xobj.add_element_user(Xobj.java:2179) > at > org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTRowImpl.addNewC(Unknown > Source) > at org.apache.poi.xssf.usermodel.XSSFRow.createCell(XSSFRow.java:172) > at org.apache.poi.xssf.usermodel.XSSFRow.createCell(XSSFRow.java:147) > at org.apache.poi.xssf.usermodel.XSSFRow.createCell(XSSFRow.java:39) > at SSPerformanceTest.main(SSPerformanceTest.java:70) > ----------------- > > For -Xmx4G > ------------------ > Exception in thread "main" java.lang.OutOfMemoryError: GC overhead limit > exceeded > at org.apache.xmlbeans.impl.store.Cur.createAttr(Cur.java:208) > at org.apache.xmlbeans.impl.store.Xobj.setAttr(Xobj.java:546) > at > org.apache.xmlbeans.impl.store.Xobj.add_attribute_user(Xobj.java:2254) > at > org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTCellImpl.setR(Unknown > Source) > at org.apache.poi.xssf.usermodel.XSSFCell.setCellNum(XSSFCell.java:711) > at org.apache.poi.xssf.usermodel.XSSFRow.createCell(XSSFRow.java:175) > at org.apache.poi.xssf.usermodel.XSSFRow.createCell(XSSFRow.java:147) > at org.apache.poi.xssf.usermodel.XSSFRow.createCell(XSSFRow.java:39) > at SSPerformanceTest.main(SSPerformanceTest.java:70) > ------------------- > > It worked fine with -Xmx6G which is exactly what I see with my program > too. It took 71 seconds as reported by the program on my machine. > > Regards, > -Mohan > > On Tue, Oct 28, 2014 at 8:58 AM, David kerber <[email protected]> > wrote: > >> On 10/28/2014 8:47 AM, Mohan Nuggehally wrote: >> >>> Nick, >>> >>> Thanks for that link. I had not read this. I looked at the SSPerformance >>> Test code and it seems pretty similar to what I have. The only difference >>> is that my code is reading a file to populate the rows and columns. I am >>> not an expert in Java so I am suspecting my code rather than apache POI. >>> But I am not able to figure out so far. I will run the SSPerformance Test >>> and see what happens. >>> >> >> Your issue appears to be memory usage rather than speed. Have you tried >> nulling out the cell object at the end of each inner loop iteration, and >> the row object at the end of the outer loop? >> >> >> >> >>> Regards, >>> -Mohan >>> >>> On Tue, Oct 28, 2014 at 7:27 AM, Nick Burch <[email protected]> >>> wrote: >>> >>> Did you try reading the FAQ entry we have on this very topic, and trying >>>> what that suggests? http://poi.apache.org/faq.html#faq-N10109 >>>> >>>> You should be able to use those tools and approaches to work out the >>>> minimum memory needed by POI for a given number of rows and columns, >>>> then >>>> any use beyond that is going to be down to your own code. Lots of people >>>> reporting POI memory issues seem to turn out to be using all the memory >>>> fetching the data they want to write, rather than in the writing, for >>>> example! >>>> >>>> Nick >>>> >>>> >>>> On Tue, 28 Oct 2014, Mohan Nuggehally wrote: >>>> >>>> Mark and David, >>>> >>>>> >>>>> Thanks for your inputs. I will try that. >>>>> >>>>> Adrian, >>>>> >>>>> This is what my code looks like >>>>> ------------- >>>>> for (int data_row = count_row; data_row < num_data_rows + >>>>> count_row; >>>>> ++data_row) >>>>> { >>>>> XSSFRow row = sheet.createRow(data_row); >>>>> for (int column = 0; column < num_columns; ++column) >>>>> { >>>>> XSSFCell cell = row.createCell(column); >>>>> if (st.nextToken() != double_quote_char) >>>>> throw new IOException("Invalid data in line " + st.lineno() >>>>> + " >>>>> of the input file"); >>>>> Double data_value = Double.valueOf(st.sval); >>>>> cell.setCellValue(data_value.doubleValue()); >>>>> } >>>>> } >>>>> ------------ >>>>> >>>>> Regards, >>>>> -Mohan >>>>> >>>>> On Mon, Oct 27, 2014 at 6:26 PM, Adrian Lynch < >>>>> [email protected]> wrote: >>>>> >>>>> Throw up some code. It'll be easier to see if you're doing anything >>>>> >>>>>> wrong. >>>>>> >>>>>> Adrian >>>>>> >>>>>> On 27 October 2014 19:43, Mohan Nuggehally >>>>>> <mohan.nuggehally@cd-adapco. >>>>>> com >>>>>> >>>>>> >>>>>>> wrote: >>>>>>> >>>>>> >>>>>> Mark, >>>>>> >>>>>>> >>>>>>> Thanks for the response. >>>>>>> >>>>>>> I have a loop going over rows and columns where in I do "XSSFRow row >>>>>>> = >>>>>>> sheet.createRow(row_i)" and "XSSFCell cell = >>>>>>> row.createCell(column_j)". >>>>>>> >>>>>>> I >>>>>> >>>>>> am under the impression that the "row" and "cell" objects are >>>>>>> destroyed >>>>>>> automatically once it comes out the scope of the loop. Am I >>>>>>> misunderstanding something ? Do I need to explicitly destroy them ? >>>>>>> If >>>>>>> so >>>>>>> how ? >>>>>>> >>>>>>> Regards, >>>>>>> -Mohan >>>>>>> >>>>>>> On Mon, Oct 27, 2014 at 3:27 PM, MARK GOLDEN < >>>>>>> >>>>>>> [email protected]> >>>>>> >>>>>> wrote: >>>>>>> >>>>>>> Hi Mohan, >>>>>>> >>>>>>>> >>>>>>>> Are you cleaning up unwanted objects? >>>>>>>> >>>>>>>> The JVM will simply expand, surprising quickly, if you do not >>>>>>>> 'Destroy' >>>>>>>> objects you no longer need. >>>>>>>> >>>>>>>> Regards, >>>>>>>> Mark. >>>>>>>> >>>>>>>> >>>>>>>> ________________________________ >>>>>>>> From: Mohan Nuggehally <[email protected]> >>>>>>>> To: [email protected] >>>>>>>> Sent: Monday, 27 October 2014, 19:14 >>>>>>>> Subject: out of memory and GC overhead limit error >>>>>>>> >>>>>>>> >>>>>>>> I get this error when I try to create an excel sheet using xssf. >>>>>>>> All I >>>>>>>> >>>>>>>> am >>>>>>> >>>>>> >>>>>> doing is creating rows and columns by reading contents from a file. >>>>>>> Of >>>>>>> >>>>>>>> course when I change the max heap size from 1GB to 6GB it works. I >>>>>>>> >>>>>>>> wonder >>>>>>> >>>>>> >>>>>> if 1GB is really too small or is the code bad and therefore requires >>>>>>> a >>>>>>> >>>>>>>> >>>>>>>> huge >>>>>>> >>>>>>> heap. Any experiences ? Here is the error stack. >>>>>>>> ------------- >>>>>>>> Exception in thread "main" java.lang.OutOfMemoryError: Java heap >>>>>>>> space >>>>>>>> at >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> org.apache.poi.ss.util.CellReference.convertNumToColString( >>>>>>> >>>>>> CellReference.java:433) >>>>>> >>>>>> at >>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> org.apache.poi.ss.util.CellReference.appendCellReference( >>>>>>> >>>>>> CellReference.java:492) >>>>>> >>>>>> at >>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> org.apache.poi.ss.util.CellReference.formatAsString( >>>>>>> >>>>>> CellReference.java:455) >>>>>> >>>>>> at >>>>>>> >>>>>>>> >>>>>>>> org.apache.poi.xssf.usermodel.XSSFCell.setCellNum(XSSFCell. >>>>>>> java:710) >>>>>>> >>>>>>> at >>>>>>>> >>>>>>>> org.apache.poi.xssf.usermodel.XSSFRow.createCell(XSSFRow.java:175) >>>>>>> >>>>>> >>>>>> at >>>>>>> >>>>>>>> >>>>>>>> org.apache.poi.xssf.usermodel.XSSFRow.createCell(XSSFRow.java:147) >>>>>>> >>>>>> >>>>>> at >>>>>>> >>>>>>>> >>>>>>>> org.apache.poi.xssf.usermodel.XSSFRow.createCell(XSSFRow.java:39) >>>>>>> >>>>>> >>>>>> at >>>>>>> >>>>>>>> GraphWriteFull_to_Excel.create_sheet(GraphWriteFull_ >>>>>>>> to_Excel.java:150) >>>>>>>> at GraphWriteFull_to_Excel.main(GraphWriteFull_to_Excel.java: >>>>>>>> 79) >>>>>>>> ---------------- >>>>>>>> The error stack is different as below when I set the max heap size >>>>>>>> to >>>>>>>> >>>>>>>> 4GB >>>>>>> >>>>>> >>>>>> -------------- >>>>>>> >>>>>>>> Exception in thread "main" java.lang.OutOfMemoryError: GC overhead >>>>>>>> >>>>>>>> limit >>>>>>> >>>>>> >>>>>> exceeded >>>>>>> >>>>>>>> at java.util.Arrays.copyOfRange(Arrays.java:2694) >>>>>>>> at java.lang.String.<init>(String.java:203) >>>>>>>> at java.lang.String.copyValueOf(String.java:2908) >>>>>>>> at java.io.StreamTokenizer.nextToken(StreamTokenizer.java:702) >>>>>>>> at >>>>>>>> GraphWriteFull_to_Excel.create_sheet(GraphWriteFull_ >>>>>>>> to_Excel.java:151) >>>>>>>> at GraphWriteFull_to_Excel.main(GraphWriteFull_to_Excel.java: >>>>>>>> 79) >>>>>>>> --------------- >>>>>>>> I appreciate any feedback. >>>>>>>> >>>>>>>> Regards, >>>>>>>> -Mohan >>>>>>>> >>>>>>>> -- >>>>>>>> Mohan Nuggehally >>>>>>>> Senior Developer, Es-ice >>>>>>>> Office: Direct dial: 1-631-760-3244 >>>>>>>> Internal extension: 25172 >>>>>>>> New York >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> -- >>>>>>> Mohan Nuggehally >>>>>>> Senior Developer, Es-ice >>>>>>> Office: Direct dial: 1-631-760-3244 >>>>>>> Internal extension: 25172 >>>>>>> New York >>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>> >>>>> >>>>> >>>>> --------------------------------------------------------------------- >>>> To unsubscribe, e-mail: [email protected] >>>> For additional commands, e-mail: [email protected] >>>> >>>> >>>> >>> >>> >> >> --------------------------------------------------------------------- >> To unsubscribe, e-mail: [email protected] >> For additional commands, e-mail: [email protected] >> >> > > > -- > Mohan Nuggehally > Senior Developer, Es-ice > Office: Direct dial: 1-631-760-3244 > Internal extension: 25172 > New York > -- Mohan Nuggehally Senior Developer, Es-ice Office: Direct dial: 1-631-760-3244 Internal extension: 25172 New York
