Skip to main content
J
jfb00
Inspiring
May 17, 2014
Answered

GC overhead limit exceeded

  • May 17, 2014
  • 3 replies
  • 4764 views

Hi All,

org.apache.poi java library helps to create an excel files as:

<cfset setFile = createObject("java","java.io.FileOutputStream") />

        <cfset xlsFile = setFile.init(filepath) />

        <cfset writeableWorkbook = createObject("java","org.apache.poi.xssf.usermodel.XSSFWorkbook") />

        <cfset cellStyleStatic = createObject("java","org.apache.poi.xssf.usermodel.XSSFCellStyle") />

        <cfset region = createObject("java","org.apache.poi.ss.util.CellRangeAddress") />

<!--- Insert cells and rows --->

        <cfset temp = writeableWorkbook.write(setFile)>

        <cfset temp = setFile.close()>

The process fail with out of memory error for 17,000 plus rows: GC overhead limit exceeded.

Anybody knows how to fix this issue?

Thanks!

    This topic has been closed for replies.
    Correct answer jfb00

    It works fine now. The key was in this line:

    <cfset writeableWorkbook = createObject("java","org.apache.poi.xssf.streaming.SXSSFWorkbook").init(1000) /> 

    I didn't have the 1000 buffer before. Thanks!

    3 replies

    BKBK
    BKBK
    Brainiac
    May 20, 2014

    <cfset cellStyleStatic = createObject("java","org.apache.poi.xssf.usermodel.XSSFCellStyle") />
    <cfset region = createObject("java","org.apache.poi.ss.util.CellRangeAddress") />

    These objects are taking memory space, but it is unclear what they are there for. As they stand, they are redundant.

      J
      jfb00Author
      Inspiring
      May 20, 2014

      YES, it is an java excel poi. Here is the code, maybe you can help us to find out the memory leak.

      The code is part of a function that I pass parameter such as the data, the column names and formats.

      Thanks for all comments and help!

      <cfif not DirectoryExists("#getDirectoryFromPath(getCurrentTemplatePath())#excelDumpFiles")>
      

                  <cfdirectory action="create" directory="#getDirectoryFromPath(getCurrentTemplatePath())#excelDumpFiles">
      

              </cfif>  
      

              <cfset filepath = "#getDirectoryFromPath(getCurrentTemplatePath())#excelDumpFiles\#fileName#" />
      

              <cfset xlsFile = createObject("java","java.io.FileOutputStream").init(filepath) />
      

              <cfset writeableWorkbook = createObject("java","org.apache.poi.xssf.streaming.SXSSFWorkbook").init(1000) />
      

              <cfset cellStyleStatic = createObject("java","org.apache.poi.xssf.usermodel.XSSFCellStyle") />
      

              <cfset region = createObject("java","org.apache.poi.ss.util.CellRangeAddress") />
      

              
      

              <!--- set up data formats --->
      

              <cfset moneyFormat = writeableWorkbook.createDataFormat() />
      

              <cfset wholeNumMoneyFormat = writeableWorkbook.createDataFormat() />
      

              <cfset dec1Format = writeableWorkbook.createDataFormat() />
      

              <cfset dec2Format = writeableWorkbook.createDataFormat() />
      

              <cfset dec3Format = writeableWorkbook.createDataFormat() />
      

              <cfset percentFormat = writeableWorkbook.createDataFormat() />
      

              <cfset wholeNumFormat = writeableWorkbook.createDataFormat() />   
      

                         
      

              <cfset moneyStyle = writeableWorkbook.createCellStyle() />
      

              <cfset moneyStyle.setDataFormat(moneyFormat.getFormat("$##,####0.00_);[Red]($##,####0.00)")) />
      

                  
      

              <cfset wholeNumMoneyStyle = writeableWorkbook.createCellStyle() />
      

              <cfset wholeNumMoneyStyle.setDataFormat(wholeNumMoneyFormat.getFormat("$##,####0_);[Red]($##,####0)")) />          
      

              
      

              <cfset dec1Style = writeableWorkbook.createCellStyle() />
      

              <cfset dec1Style.setDataFormat(dec1Format.getFormat("##,####0.0_);[Red](##,####0.0)")) />
      

                    
      

              <cfset dec2Style = writeableWorkbook.createCellStyle() />
      

              <cfset dec2Style.setDataFormat(dec2Format.getFormat("##,####0.00_);[Red](##,####0.00)")) />
      

                  
      

              <cfset dec3Style = writeableWorkbook.createCellStyle() />
      

              <cfset dec3Style.setDataFormat(dec3Format.getFormat("##,####0.000_);[Red](##,####0.000)")) />          
      

                  
      

              <cfset percentStyle = writeableWorkbook.createCellStyle() />
      

              <cfset percentStyle.setDataFormat(percentFormat.getFormat("##,####0.00_)%;[Red](##,####0.00)%")) />    
      

              
      

              <cfset wholeNumStyle = writeableWorkbook.createCellStyle() />
      

              <cfset wholeNumStyle.setDataFormat(wholeNumFormat.getFormat("##,####0_);[Red](##,####0)")) />
      

           
      

              <cfset numberOfLoops = (ArrayLen(arguments) - 1) / 5 />
      

              <cfset argTemp = 1 />
      

              
      

              <cfloop from="1" to="#numberOfLoops#" index="argIndex">
      

                  <cfif arguments[argIndex + argTemp] >
      

                          <cfset argTemp = argTemp + 1 />
      

                          <cfset rowCount = 0>  
      

                          <!--- NEW SHEET --->
      

                          <cfset newSheet = writeableWorkbook.createSheet() />
      

                          <cfset temp = writeableWorkbook.setSheetName(sheetNumber, arguments[argIndex + argTemp]) />
      

                          
      

                          <!--- add meta data only for the first workbook or worksheet --->
      

                          <cfif sheetNumber eq 0>
      

                              <cfset newSheet.addMergedRegion(region.init(0,0,0,20))> <!--- first row, last row, first col, last col --->
      

                              <cfset newRow = newSheet.createRow(javaCast("int",0))>
      

                              <cfset newCell = newRow.createCell(javaCast("int",0))>
      

                              <cfset temp = newCell.setCellType(javaCast("int",1))> <!--- string --->
      

                              <cfset temp = newCell.setCellValue(javaCast("string",reqString))>
      

                              <cfset rowCount = rowCount + 1 />
      

                          </cfif>
      

                      
      

                          <!--- Get Data --->
      

                          <cfset argTemp = argTemp + 1  />
      

                          <cfset qryExcelData = arguments[argIndex + argTemp] /> 
      

      

                          <!--- create column span headers --->
      

                          <cfset argTemp = argTemp + 1 />
      

                          <cfset newRow = newSheet.createRow(javaCast("int",rowCount)) /> 
      

                          <cfset styleAlignCenter = writeableWorkbook.createCellStyle() />
      

                          <cfset styleAlignCenter.setAlignment(cellStyleStatic.ALIGN_CENTER) />             
      

                          <cfloop from="1" to="#ArrayLen(arguments[argIndex + argTemp])#" index="index">
      

                              <cfset newSheet.addMergedRegion(region.init(rowCount,rowCount,javaCast("int",StructFind(arguments[argIndex + argTemp][index],"spanStart")),javaCast("int",StructFind(arguments[argIndex + argTemp][index],"spanEnd"))))> <!--- first row, last row, first col, last col ---> 
      

                              <cfset newCell = newRow.createCell(javaCast("int",StructFind(arguments[argIndex + argTemp][index],"spanStart")))>
      

                              <cfset newCell.setCellStyle(styleAlignCenter)>
      

                              <cfset temp = newCell.setCellType(javaCast("int",1))> <!--- string --->
      

                              <cfset temp = newCell.setCellValue(javaCast("string",StructFind(arguments[argIndex + argTemp][index],"columnHeader")))>
      

                          </cfloop>            
      

              
      

                          <!--- create column headers ---> 
      

                          <cfset argTemp = argTemp + 1 />
      

                          <cfset rowCount = rowCount + 1>  
      

                          <cfset newRow = newSheet.createRow(javaCast("int",rowCount))>   
      

                          <cfloop from="1" to="#ArrayLen(arguments[argIndex + argTemp])#" index="index">
      

                              <cfset newCell = newRow.createCell(javaCast("int",index-1))>
      

                              <cfset temp = newCell.setCellType(javaCast("int",1))> <!--- string --->
      

                              <cfset temp = newCell.setCellValue(javaCast("string",StructFind(arguments[argIndex + argTemp][index],"columnHeader")))>
      

                          </cfloop> 
      

      

                          <!--- Loop thru the data --->
      

                          <cfset colLength = ArrayLen(arguments[argIndex + argTemp]) />
      

                          <!--- <cfset colLength = 7 /> --->
      

                          <cfoutput query="qryExcelData">
      

                              <cfset rowCount = rowCount + 1>
      

                              <cfset newRow = newSheet.createRow(javaCast("int",rowCount))>
      

                              <cfloop from="1" to="#colLength#" index="index">
      

                                  <cfset tempCol = StructFind(arguments[argIndex + argTemp][index],'columnName') />
      

                                  <cfset tempFormat = StructFind(arguments[argIndex + argTemp][index],'excelFormat') />
      

                                  <cfset newCell = newRow.createCell(javaCast("int",index-1))>
      

                                  <cfif isNumeric(qryExcelData["#tempCol#"][qryExcelData.currentRow])>
      

                                      <cfif FindNoCase(".",tempFormat)>
      

                                          <cfset decimalLen = Len(Trim(REReplace(tempFormat,"[$,.]","","ALL"))) />
      

                                      </cfif>
      

                                      <cfif tempCol eq "ColumnNameA">
      

                                          <cfset newCell.setCellType(javaCast("int",1)) /> <!--- string --->
      

                                          <cfset Evaluate("qryExcelData.#tempCol#") />
      

                                          <cfset newCell.setCellValue(javaCast("string",IIF(temp,'TRUE','FALSE'))) />
      

                                      <cfelse>
      

                                          <cfset temp = newCell.setCellType(javaCast("int",0))> <!--- numeric --->
      

                                          <cfif FindNoCase(",",tempFormat) and not FindNoCase("$",tempFormat)>
      

                                              <cfif decimalLen eq 1>
      

                                                  <cfset temp = newCell.setCellStyle(dec1Style)>
      

                                              <cfelseif decimalLen eq 2>
      

                                                  <cfset temp = newCell.setCellStyle(dec2Style)>
      

                                              <cfelseif decimalLen eq 3>
      

                                                  <cfset temp = newCell.setCellStyle(dec3Style)>                  
      

                                              <cfelse>
      

                                                  <cfset temp = newCell.setCellStyle(wholeNumStyle)>
      

                                              </cfif>
      

                                          <cfelseif FindNoCase("$",tempFormat)>
      

                                              <cfif NOT FindNoCase(".",tempFormat)>
      

                                                  <cfset temp = newCell.setCellStyle(wholeNumMoneyStyle)>
      

                                              <cfelse>
      

                                                  <cfset temp = newCell.setCellStyle(moneyStyle)>
      

                                              </cfif>
      

                                          </cfif>                               
      

                                          <cfset temp = newCell.setCellValue(javaCast("double",qryExcelData["#tempCol#"][qryExcelData.currentRow]))>           
      

                                      </cfif>
      

                                  <cfelseif isDate(qryExcelData["#tempCol#"][qryExcelData.currentRow])>
      

                                      <cfset tempVal = qryExcelData["#tempCol#"][qryExcelData.currentRow] />
      

                                      <cfif second(tempVal) gt 0>
      

                                          <cfset tempVal = '#DateFormat(tempVal,"MM/DD/YYYY")# #TimeFormat(tempVal,"HH:mm:ss")#' />
      

                                      <cfelse>
      

                                          <cfset tempVal = DateFormat(tempVal,"MM/DD/YYYY") />
      

                                      </cfif>
      

                                      <cfset newCell.setCellType(javaCast("int",1)) /> <!--- string --->
      

                                      <cfset newCell.setCellValue(javaCast("string",tempVal))>             
      

                                  <cfelse>
      

                                      <cfif tempCol neq ""> 
      

                                          <cfset temp = newCell.setCellValue(javaCast("string",qryExcelData["#tempCol#"][qryExcelData.currentRow]))>
      

                                      <cfelse>
      

                                          <cfset temp = newCell.setCellValue(javaCast("string","#tempFormat#"))>
      

                                      </cfif>         
      

                                  </cfif>
      

                                  <cfset decimalLen = 0 />
      

                              </cfloop>
      

                          </cfoutput>
      

                          
      

                          <!--- auto expand columns --->
      

                          <cfloop from="0" to="#ArrayLen(arguments[argIndex + argTemp])#" index="index">
      

                              <cfset newSheet.autoSizeColumn(javaCast("int",index)) />
      

                          </cfloop>
      

                  
      

                  <cfset sheetNumber = sheetNumber + 1>
      

                  <cfelse>
      

                      <cfset argargTemp + 5 />  
      

                  </cfif>
      

              </cfloop>
      

              
      

              <!--- Write Excel File --->
      

              <cfset writeableWorkbook.write(xlsFile)>
      

              <cfset xlsFile.close()>
      

              <cfset writeableWorkbook.dispose() />
      

              
      

              <cfcatch>
      

                  <cfset dataStatus = "Excel creation failed.<br>#cfcatch.message# #cfcatch.detail#" />
      

                  <!--- <cfset application.debug(cfcatch) /><cfabort> --->
      

              </cfcatch>          
      

              </cftry>
      J
      jfb00AuthorCorrect answer
      Inspiring
      May 20, 2014

      It works fine now. The key was in this line:

      <cfset writeableWorkbook = createObject("java","org.apache.poi.xssf.streaming.SXSSFWorkbook").init(1000) /> 

      I didn't have the 1000 buffer before. Thanks!

      BKBK
      BKBK
      Brainiac
      May 17, 2014

      Shouldn't the 'write' line involve the file? What about replacing

      <cfset setFile = createObject("java","java.io.FileOutputStream") />

      <cfset xlsFile = setFile.init(filepath) />

      with

      <cfset xlsFileOut = createObject("java","java.io.FileOutputStream").init(filepath) />

          

      and

      <cfset temp = writeableWorkbook.write(setFile)>

      <cfset temp = setFile.close()>

      with

      <cfset temp = writeableWorkbook.write(xlsFileOut)>

      <cfset temp = xlsFileOut.close()>

        J
        jfb00Author
        Inspiring
        May 19, 2014

        Thanks for your reply and help.

        I did the change BKBK.

        Carl : that helps, i can do more than 17K but no more than 40K. I am getting Error: Java heap space.

        Any ideas?

        Anit_Kumar
        Community Manager
        Anit_KumarCommunity Manager
        Community Manager
        May 19, 2014

        Setting a high value for Xmx or permgen is probably not a good idea. You need to tune the values, not exactly, but appropriately. You may refer to http://www.adobe.com/devnet/coldfusion/articles/coldfusion_performance.html for performance tuning.

        You can use jmeter to test whether the Java heap space error is due to, heap memory or non-heap memory.

        For load testing, download jmeter from the link https://jmeter.apache.org/download_jmeter.cgi

        Here are the steps to be followed:-

        1. Test paln -> Right click --> Add - Thread group
        2. Right click on Thread group --> Add - Sampler- HTTP Request
        3. Right click HTTP Request --> Add- Listener - View Results in Table
        4. Click on HTTP REquest  and enter the server name like localhost or ip address, then port number of your site like 80
        5. Enter the path as : http://localhost:81/hello.cfm
        6. Click on Thread Group in left panel. Increase the number of threads as 40 or something like that. Enter the loop count like 5000 or so
        7. Now click on the green start icon at top and click on view results in table

        NOTE: click on apache-jmeter-2.10.zip under binaries

        If the non-heap memory is increasing then, the perm gen value needs to be recalculated. Whereas, if the heap memory is increasing then the Xmx needs to changed.

        Hope that helps

        Regards,

        Anit Kumar

        J
        jfb00Author
        Inspiring
        May 17, 2014

        All,

        I increase the  from 512 to 2048. It process more records but still short memory if i included all records.

        Any ideas?

        Thanks

        C
        carl type3
        Brainiac
        May 17, 2014

        in Server Settings > Java and JVM > JVM Arguments

        increase -XX:MaxPermSize=192m to say 512m. Restart CF to apply.

        HTH, Carl.

        Terms & ConditionsAccessibility statement
        Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices