Copy link to clipboard
Copied
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!
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!
Copy link to clipboard
Copied
All,
I increase the from 512 to 2048. It process more records but still short memory if i included all records.
Any ideas?
Thanks
Copy link to clipboard
Copied
in Server Settings > Java and JVM > JVM Arguments
increase -XX:MaxPermSize=192m to say 512m. Restart CF to apply.
HTH, Carl.
Copy link to clipboard
Copied
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()>
Copy link to clipboard
Copied
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?
Copy link to clipboard
Copied
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:-
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
Copy link to clipboard
Copied
Thank for you reply and help.
In the view results i see only sample,start time,thread name, label, sample time, status, bytes, latency:
Example
64087 | 11:40:34.847 | Thread Group 1-18 | HTTP Request | 4587 | Warning | 7151 | 4587 |
64088 | 11:40:34.849 | Thread Group 1-30 | HTTP Request | 9098 | Warning | 7151 | 9098 |
64089 | 11:40:34.849 | Thread Group 1-17 | HTTP Request | 9098 | Warning | 7151 | 9098 |
64090 | 11:40:39.335 | Thread Group 1-40 | HTTP Request | 9324 | Warning | 7151 | 9323 |
64091 | 11:40:39.392 | Thread Group 1-3 | HTTP Request | 9267 | Warning | 7151 | 9267 |
64092 | 11:40:39.392 | Thread Group 1-5 | HTTP Request | 9267 | Warning | 7151 | 9267 |
64093 | 11:40:48.659 | Thread Group 1-40 | HTTP Request | 94081 | Warning | 2358 | 0 |
64094 | 11:40:39.454 | Thread Group 1-23 | HTTP Request | 103287 | Warning | 2358 | 0 |
64095 | 11:40:39.454 | Thread Group 1-19 | HTTP Request | 103286 | Warning | 2358 | 0 |
64096 | 11:40:39.393 | Thread Group 1-21 | HTTP Request | 103350 | Warning | 2358 | 0 |
64097 | 11:40:39.454 | Thread Group 1-29 | HTTP Request | 103291 | Warning | 2358 | 0 |
64098 | 11:40:39.454 | Thread Group 1-34 | HTTP Request | 103293 | Warning | 2358 | 0 |
What is the non-heap memory and heap memory?
Copy link to clipboard
Copied
I accidentally typed the incorrect tool. Use JConsole or JVisualvm to find the memory, causing the error.
It seems like, you JVM needs tuning (http://docs.oracle.com/cd/E13222_01/wls/docs81/perform/JVMTuning.html). Heap Memory, which is the storage for Java objects and Non-Heap Memory, which is used by Java to store loaded classes and other meta-data. Thus Xms and Xmx contribute to Heap memory and XX:MaxPermSize or permgen to Non-heap memory. You can get more details at Heap and Non-heap.
jfb00 wrote:
What is the non-heap memory and heap memory?
Regards,
Anit Kumar
Copy link to clipboard
Copied
I see this:
Time: | 2014-05-19 17:30:09 |
---|---|
Used: | 21,549 kbytes |
Committed: | 30,208 kbytes |
Max: | 3,714,048 kbytes |
GC time: | 0.281 seconds on PS MarkSweep (3 collections) |
0.121 seconds on PS Scavenge (28 collections) |
The heap memory for most of the time was 50%, it when to 75% and at the end use 100% for little bit time.
The non-heap memory was 25% all the time.
How can I determine the values to use? Sorry but I am trying to learn this.
Thanks
Copy link to clipboard
Copied
>Setting a high value for Xmx or permgen is probably not a good idea.
Agreed. One would do well to set an initial setting for Xms (initial heap) and PermGen (EG -XX:PermSize=256m non heap) as well as tuned maximum.
>How can I determine the values to use? Sorry but I am trying to learn this.
You might like to watch a CF Java presentation I did end of 2010. Versions have changed since then and 64 bit alters the Java memory scope however the principles are still the same.
http://experts.adobeconnect.com/p55663036/
>Use JConsole or JVisualvm to find the memory, causing the error.
This talk demonstrated use of Jconsole, more to look at CF tomcat connector than memory but again that might provide you some assistance with using Jconsole.
http://experts.adobeconnect.com/p8l51p4s9m4/
Regards, Carl.
Copy link to clipboard
Copied
<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.
Copy link to clipboard
Copied
They seems to me, Apache poi for excel. Though i am not sure and even unclear about their usage here.
Regards,
Anit Kumar
Copy link to clipboard
Copied
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>
Copy link to clipboard
Copied
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!
Copy link to clipboard
Copied
That is great. Please mark the thread as answered, so that others are benefited with the same.
Regards,
Anit Kumar