5

We have a Coldfusion application that is running a large query (up to 100k rows) and then displaying it in HTML. The UI then offers an Export button that triggers writing the report to an Excel spreadsheet in .xlsx format using the cfspreadsheet tags and spreadsheet function, in particular, spreadsheetSetCellValue for building out row column values, spreadsheetFormatRow and spreadsheetFormatCell functions for formatting. The ssObj is then written to a file using:

<cfheader name="Content-Disposition" value="attachment; filename=OES_#sel_rtype#_#Dateformat(now(),"MMM-DD-YYYY")#.xlsx">
<cfcontent type="application/vnd-ms.excel" variable="#ssObj#" reset="true">

where ssObj is the SS object. We are seeing the file size about 5-10 Mb.

However... the memory usage for creating this report and writing the file jumps up by about 1GB. The compounding problem is that the memory is not released right away after the export completes by the java GC. When we have multiple users running and exporting this type of report, the memory keeps climbing up and reaches the heap size allocated and kills the serer's performance to the point it brings down the server. A reboot is usually necessary to clear it out.

Is this normal/expected behavior or how should we be dealing with this issue? Is it possible to easily release the memory usage of this operation on demand after the export has completed, so that others running the report readily get access to the freed up space for their reports? Is this type of memory usage for a 5-10Mb file common with cfspreadsheet functions and writing the object out?

We have tried temporarily removing the expensive formatting functions and still the memory usage is large for the creation and writing of the .xlsx file. We have also tried using the spreadsheetAddRows approach and the cfspreadsheet action="write" query="queryname" tag passing in a query object but this too took up a lot of memory.

Why are these functions so memory hoggish? What is the optimal way to generate Excel SS files without this out of memory issue?

I should add the server is running in Apache/Tomcat container on Windows and we are using CF2016.

James A Mohler
  • 11,060
  • 15
  • 46
  • 72
teaman
  • 469
  • 7
  • 18

2 Answers2

5
  • How much memory do you have allocated to your CF instance?
  • How many instances are you running?
  • Why are you allowing anyone to view 100k records in HTML?
  • Why are you allowing anyone to export that much data on the fly?

We had issues of this sort (CF and memory) at my last job. Large file uploads consumed memory, large excel exports consumed memory, it's just going to happen. As your application's user base grows, you'll hit a point where these memory hogging requests kill the site for other users.

Start with your memory settings. You might get a boost across the board by doubling or tripling what the app is allotted. Also, make sure you're on the latest version of the supported JDK for your version of CF. That can make a huge difference too.

Large file uploads would impact the performance of the instance making the request. This meant that others on the same instance doing normal requests were waiting for those resources needlessly. We dedicated a pool of instances to only handle file uploads. Specific URLs were routed to these instances via a load balancer and the application was much happier for it.

That app also handled an insane amount of data and users constantly wanted "all of it". We had to force search results and certain data sets to reduce the amount shown on screen. The DB was quite happy with that decision. Data exports were moved to a queue so they could craft those large excel files outside of normal page requests. Maybe they got their data immediately, maybe the waited a while to get a notification. Either way, the application performed better across the board.

Adrian J. Moreno
  • 14,350
  • 1
  • 37
  • 44
  • Thanks @Adrian-j-moreno. That is very helpful info! I like the queue idea and would like to hear more about that. Like what criteria you used to decide on queue vs generate now. Also, is the queue basically pacing the reports per every x minutes or are they scheduled for later (at night?) We have 8GB allocated. We have a separate instance just for reports so that it can have a much longer timeout instead of the short timeout for requests to the main website server instance, to protect it from big report runs. – teaman Oct 05 '18 at 06:22
  • Reports are generally based on employee counts, so large companies have many records. I suppose we could try partitioning the reports up by last names starting with A-C, D-F, etc and put each on it's own Sheet and run separate reports a sheet at a time, then merge them into one SS file. I've not tried generating a SS with multiple sheets and don't know if that will help the memory usage or not. Do you know if that is a useful path to go down in this type of situation? – teaman Oct 05 '18 at 06:23
  • 1
    Everything would go into the queue and it would be processed one at a time. The speed of creating the export depends on the volume of data per request. That's what I meant by "maybe the got their data immediately". Check how much memory is available for the server and see if you can bump that up, then bump up the memory allocated to each instance. I've got 16 GB allocated per instance at my current gig, only a few instances. Last place we had at least 16 GB per instance, but we had 8 instances per server x 50+ servers. – Adrian J. Moreno Oct 05 '18 at 14:48
1

Presumably a bit late for the OP, but since I ended up here others might too. Whilst there is plenty of general memory-related sound advice in the other answer+comments here, I suspect the OP was actually hitting a genuine memory leak bug that has been reported in the CF spreadsheet functions from CF11 through to CF2018.

When generating a spreadsheet object and serving it up with cfheader+cfcontent without writing it to disk, even with careful variable scoping, the memory never gets garbage collected. So if your app runs enough Excel exports using this method then it eventually maxes out memory and then maxes out CPU indefinitely, requiring a CF restart.

See https://tracker.adobe.com/#/view/CF-4199829 - I don't know if he's on SO but credit to Trevor Cotton for the bug report and this workaround:

  1. Write spreadsheet to temporary file,
  2. read spreadsheet from temporary file back into memory,
  3. delete temporary file,
  4. stream spreadsheet from memory to user's browser.

So given a spreadsheet object that was created in memory with spreadsheetNew() and never written to disk, then this causes a memory leak:

<cfheader name="Content-disposition" value="attachment;filename=#arguments.fileName#" />
<cfcontent type="application/vnd.ms-excel" variable = "#SpreadsheetReadBinary(arguments.theSheet)#" />

...but this does not:

<cfset local.tempFilePath = getTempDirectory()&CreateUUID()&arguments.filename />
<cfset spreadsheetWrite(arguments.theSheet, local.tempFilePath, "", true) />
<cfset local.theSheet = spreadsheetRead(local.tempFilePath) />
<cffile action="delete" file="#local.tempFilePath#" />
<cfheader name="Content-disposition" value="attachment;filename=#arguments.fileName#" />
<cfcontent type="application/vnd.ms-excel" variable = "#SpreadsheetReadBinary(local.theSheet)#" />

It shouldn't be necessary, but Adobe don't appear to be in a hurry to fix this, and I've verified that this works for me in CF2016.

Sev Roberts
  • 1,295
  • 6
  • 7
  • Very interesting find. What about using cfcontent + file + deleteFile=true instead of SpreadsheetReadBinary? – SOS Jul 17 '20 at 02:53
  • That's a good question. I didn't think to test that, but next time I'm on that box I'll give it a try and update with findings. – Sev Roberts Jul 18 '20 at 09:57