1

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?

8
  • @DocBrown OOM is "out of memory" (now clarified by the OP) I'm assuming it is the web server that is running out memory when attempting to export (say) 2 GB of data from the database, rather than the database itself. Jan 2 at 11:45
  • Ok, clarification appreciated. However, I think for what you described, the implementation details of the export matters, and I think this problem is unlikely to be solved at an architectural level. Ideally, you need to use an export method which processes the data in a sequentially streaming manner. But how this looks like depends on the DB access technology used in your code, the DBMS itself, and maybe its version. In case you want to ask a question on those details, better ask a new one at Stackoverflow, tailored for that site.
    – Doc Brown
    Jan 2 at 12:07
  • It should be nigh on impossible to get an out of memory exception in a managed language on a modern computer. What are you doing wrong?
    – Ewan
    Jan 2 at 16:43
  • @Ewan: maybe the existing app is 32 bit app. I would not expect this to be necessary for a server app today, but we don't know which legacy 3rd party components might be involved.
    – Doc Brown
    Jan 2 at 20:00
  • 2
    @ewan Even on a 64-bit .NET application, memory is not infinite. If you want to believe that not being able to specify a limit to the size of the VM is a feature, I suppose ignorance is bliss. The OP has not specified the resources available or what else might be using them. The point of asking the question is essentially to figure out what they are doing wrong.
    – JimmyJames
    Jan 7 at 16:04
3

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.

7
  • 1
    This answer is a little confusing (especially the first sentence.) 'Chunking' is a way to avoid keeping everything in memory.
    – JimmyJames
    Jan 6 at 16:01
  • I'm interpreting it as OP is grabbing chunks of data from the DB and writing to a file entirely in memory. Could be wrong; if OP wants to chime in happy to clarify my position.
    – Ian Jacobs
    Jan 6 at 16:33
  • I believe the problem is that the OP is trying to create the entire file in memory. I take the idea of 'chunking' to mean writing smaller parts of file one at a time. This should resolve the issue. The OP is under the impression that you can have an OOME without GC running which is common misunderstanding e.g. people often think forcing GC will resolve OOMEs. OOMEs are always preceded by a GC-cycle unless your runtime is fundamentally broken.
    – JimmyJames
    Jan 6 at 18:02
  • Yep I said the same thing; with the same suggestion. Just trying to explain this in a way that works with their current framing of the question.
    – Ian Jacobs
    Jan 7 at 16:36
  • That's why I don't understand 'very likely' as the initial response. As far as I can tell, chunking should fix it but you are suggesting that it won't and seemingly confirming the OP's misconception.
    – JimmyJames
    Jan 7 at 17:22
1

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.

1

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.

-2

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.

3
  • If I separate the logic and create an exporting service - how do I prevent the service itself from crashing due to OOM?
    – miqem
    Jan 2 at 11:47
  • 1
    Monitor your free RAM as you get every chunk from the database. If free RAM is low, do something like "spilling temporary results to disk". If you're actually trying to export more data than you can handle in any way with the resources available to you, either get more resources or push back on the requirement. Jan 2 at 11:52
  • I don't see how this has anything to do with OOMEs. You can have OOMEs in monolithic apps, you can have them non-monolithic applications. All things equal the total memory needed should be roughly the same, possibly even a bit higher for the decomposed application.
    – JimmyJames
    Jan 6 at 15:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.