How to design around webapp Out of Memory crashing on export from SQL DB to Excel?

Question

I have a monolith CRUD application with a feature - possibility to export some DB entires to an Excel file. The export is done within a background task. Often, however, when user presses the button and expects to get a link with completed download, they may trigger a crash for the server where the webapp is hosted (not the DB server). Exporting reserves a lot of memory, the more rows one exports - the higher the chance to get Out of Memory.

How should I design my system around that?

I presume, it has something to do with chunking (load just a part of data from DB, append to the excel-file, then load and append another, etc), but don't quite understand - if I use chunking, shouldn't the chunked objects still persist in memory for some time (until, obviously, being garbage-collected) making the monolithic application still prone to Out of Memory crashing?

What are the best practices and common architectural solutions in regards to such stuff?

Answer 1

append to the excel-file, then load and append another, etc), but don't quite understand - if I use chunking, shouldn't the chunked objects still persist in memory for some time (until, obviously, being garbage-collected) making the monolithic application still prone to Out of Memory crashing?

Pulling out chunks of data (vs the entire result set) will only go so far to mitigate memory issues. You're still keeping a 'copy' of that data (the file contents itself) in memory; and that's just going to keep adding up until you do something with that excel file, which I suspect is the root of your issue.

I had the opposite problem where I had to load massive excel files into memory and parse out things like notes/comments and other analytic data to be saved to the database. Keeping the file in memory all at once did not scale, and we needed to change to load a piece of the file at a time for processing.

You're probably going to want to flush the file contents to disk (or a response stream) every X records, and only keep the minimum needed in memory.

Answer 2

Before trying to fix it, you should understand the problem and know the root cause. It is well possible that separating Excel file creation from normal web request processing will improve the stability, but you should still analyze the cause of OOM errors.

At first, you may want to calculate how big your files can be (number of populated cells), and what memory requirements are reasonable. I don't know which Excel writing library you use, but I would estimate that an overhead of ~50-100 bytes per cell (in addition to its contents) would not be unreasonable.

If your worksheets have something like 10000 rows of 50 columns each, that would be 25-50 MBytes of overhead. Regardless of 32bit vs. 64 bit, that shouldn't break a server.

If your numbers are orders of magnitude bigger, you may use the wrong tools. Excel files are for calculations on client machines, not for listing millions of records.

Now if your worksheet sizes are reasonable and memory consumption still goes through the roof, architectural changes probably won't help, you will need to debug your code. Maybe there's a memory leak, or you create zillions of unnecessary copies, or whatever.

Answer 3

if I use chunking, shouldn't the chunked objects still persist in memory for some time (until, obviously, being garbage-collected) making the monolithic application still prone to Out of Memory crashing?

No. You are misunderstanding what causes an out-of-memory error (OOME). In any reasonably decent garbage collected runtime, the garbage collector will always‡ run before throwing an OOME. In other words, an OOME occurs when the garbage collector cannot reclaim enough memory to support new allocations.

The key thing is that you need to make the data collectable. That means there are no more live references to it. So chunk your data and make sure it's unreferenced after you write it.

If you are planning to simply 'chunk' the results retrieved from the DB and build a huge Excel file in memory, you may not resolve the issue. One of the reasons you get a OOME in .NET (which is what I assume you are using) is creating a large object (such as an array) which needs a contiguous section of memory.

‡ There are some potential corner-cases around race-conditions with multithreaded collectors but they are probably not relevant here.

Answer 4

the monolithic application

This is probably your biggest issue. It seems you are trying to do two very different things in one application:

• Serve regular web requests. There are lots of these and they need to be served quickly, but (individually) are low resource usage.
• Export large amounts of data. This is a high resource usage task, but (I'm guessing/hoping) much rarer than regular web requests, and also don't need to served nearly so quickly.

The solution to this is hopefully now somewhat obvious - you need to separate the two classes of request so that your exports cannot have a negative impact on regular requests. You could somehow try and do this inside your monolithic application by attempting to track the resource usage of the export requests, but it seems much easier just to create a separate "export service" - you can then do whatever you need to do to control the behaviour of this service, for example queueing requests, spilling temporary results to disk or whatever else.