500 Null errors with POIutility – java.lang.OutOfMemoryError

I use Ben Nadel’s great POIUtility custom tags to create Excel spreadsheets in Coldfusion MX 7. I have two applications that run on a shared server. Occassionally, the spreadsheet generation stops with the dreaded 500 Null error and I can finally shed some light on the problem. I can often rerun the same generation process with the same data and sometimes it works, sometimes not. Frustratingly, I could not get it to fail on my local development machine until today – when I threw a much larger dataset at it and finally it crashed with the 500 Null error.

So, digging into my exception.log fileĀ  I found what I had suspected was probably happenning on the shared server but couldnt proveĀ  – java.lang.OutOfMemoryError. My service provider said they couldnt see any errors in the log files which wasnt helping my debug efforts!

The final spreadsheet is 1,491 kb on the drive and is 187 columns x 380 rows = 71,060 cells.

I set up some tests on my local development machine and using the same dataset, adjusted the JVM settings and restarted CF each time to work out which setting was the problem. Here are the results:

JVM Heap size Mb MaxPermSize Mb Worked?
512 64 No
750 64 No
750 512 No
925 64 Yes
925 512 Yes
1024 64 Yes
1024 128 Yes
1024 512 Yes

So, it looks like the heap size is the critical setting.

Using CreateObject("java","java.lang.Runtime").getRuntime() and maxMemory = runtime.maxMemory() it looks like the server setting is probably 512Mb.

Interestingly, I also used the java runtime.freeMemory() in the cell.cfm custom tag (using cflog) whenever a new cell object was created to monitor the memory useage and even when the 500 Null errors occurred the memory always seemed to be at least approx 28 Mb and when the last log entry was written each time there was substantially more than that! I guess I was expecting the freeMemory to fall to something close to zero. My guess is that the garbage collection or some other process must have been grabbing some memory?

Now, I just need to convince my service provider to increase their settings. Or, find another service provider!

I hope this helps others with a similar problem.