Skip to content

Memory usage for large datasets #276

Open

Description

@guilleva

Hi, I'm facing a memory issue when trying to generate a xlsx file with >600 columns and ~10k rows. The memory usage grows up to 3GB aprox and it's making the dyno be restarted by heroku.

I'm wondering if there is an known way to generate it without making a heavy use of memory.

The way I'm doing it can be represented by this script:

Axlsx::Package.new do |p|
  p.workbook.add_worksheet(:name => "Test") do |sheet|
    11_000.times do
        sheet.add_row ["test data"] * 600
    end
  end
  p.serialize('tmp/test.xlsx')
end

Activity

  1. michaeldauria commented on Jan 15, 2014

    @michaeldauria

    We're experiencing the same issue, perhaps RubyZip could be replaced with https://bitbucket.org/winebarrel/zip-ruby/wiki/Home ?

  2. jurriaan commented on Jan 15, 2014

    @jurriaan
    Collaborator

    RubyZip isn't the issue I think, axlsx is.
    One of the problems is that axlsx creates an enormous string which is then passes to RubyZip. That's very inefficient..

  3. jurriaan commented on Jan 15, 2014

    @jurriaan
    Collaborator

    Tried to fix it @ https://github.com/jurriaan/axlsx/tree/fix_enormous_string Please report if this helps (it should reduce usage to 1.5 GB or so)

  4. jurriaan commented on Jan 17, 2014

    @jurriaan
    Collaborator

    @guilleva @michaeldauria Did this change work for you? It should be possible to reduce the memory usage even further, but it's a beginning :)

  5. guilleva commented on Jan 17, 2014

    @guilleva
    Author

    Hi jurriaan, thanks for looking into this, I haven't been able to test it, I really sorry, I will do my best to do it today and I will let you know.

    Thanks again

  6. jurriaan commented on Jan 17, 2014

    @jurriaan
    Collaborator

    Just pushed some new commits, the example now uses ~700 MB on my system..

  7. michaeldauria commented on Jan 22, 2014

    @michaeldauria

    I will try this out today and report back

  8. jurriaan commented on Jan 22, 2014

    @jurriaan
    Collaborator

    It is possible it won't help in your situation though.. the fact is that atm axlsx allocates way too much objects and causes the heap to grow very big.. I'm trying to reduce the number of allocated objects..

  9. michaeldauria commented on Jan 28, 2014

    @michaeldauria

    This did not end up helping too much, memory seemed more stable, but we still ran out anyway.

  10. jeremywadsack commented on May 29, 2014

    @jeremywadsack

    We have a similar issue. We're building sheets with hundreds of thousands of rows and as many as 100 tabs. axlsx uses 5.3GB to build a 111MB xlsx file.

    I would be happy to test some changes on this, but your branch is no longer available. Any further updates on this?

  11. jurriaan commented on Jun 19, 2014

    @jurriaan
    Collaborator

    @jeremywadsack I've merged most of the changes into the master branch, but it's still using a lot of memory. I hope to have some time to look at this issue again in the coming weeks

  12. maxle commented on Sep 18, 2014

    @maxle

    Hi. Any updates on this?

  13. srpouyet commented on Feb 3, 2015

    @srpouyet

    Our Delayed Job workers aren't processing jobs (out of memory), probably because of this issue. Any updates on the status? Cheers!

  14. pallymore commented on Apr 27, 2015

    @pallymore

    +1 on this. We are having the same issue with huge datasets :/

  15. jeremywadsack commented on Apr 27, 2015

    @jeremywadsack

    We just ended up spinning up more hardware to deal with this for the time being. :/

  16. 13 remaining items

  17. jeremywadsack commented on Oct 27, 2016

    @jeremywadsack

    #352 is a similar request to append rows to an existing file.

  18. Paxa commented on Apr 23, 2017

    @Paxa

    I've been struggling with same issues, when generating > 100k rows our servers started to use swap and become very slow.

    Solved by making own excel library https://github.com/paxa/fast_excel, it works 3-4 times faster and consume 5-20 MB ram regardless of data size

  19. cseelus commented on Nov 28, 2017

    @cseelus

    The problem with these memory issues we face using AXLSX via the to_spreadsheet gem is, that the memory won't be free after xslx creation. This means we have to manually restart our worker dyno for now and also limit the maximum number of rows per Excel sheet.

    @Paxa Nice work. Depending on the answer of the maintainer of the to_spreadsheet gem (allows a Rails app to render Excel files using the existing slim/haml/erb/etc views) I'll see if fast_excel could be used as an alternative to AXLSX.

  20. crystianleao commented on Mar 22, 2018

    @crystianleao

    So, @cseelus, i'm having issues with this lib's memory usage too. What did you do? @Paxa 's lib or to_spreadsheet?

    Thank you!

  21. cseelus commented on Mar 25, 2018

    @cseelus

    For now we limit the number of rows per xslx file to mitigate the memory problems using the to_spreadsheet gem.

  22. barttenbrinke commented on Apr 4, 2018

    @barttenbrinke

    According to my basic memoryprofile skills, the main issue is here: https://github.com/randym/axlsx/blob/master/lib/axlsx/workbook/worksheet/row.rb#L156 This is never released, expanding tot up to 3GB in my testcase.

    retained memory by location
    -----------------------------------
       3643008  /usr/local/var/rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/bundler/gems/axlsx-776037c0fc79/lib/axlsx/workbook/worksheet/row.rb:156
        654864  /usr/local/var/rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/bundler/gems/axlsx-776037c0fc79/lib/axlsx/util/simple_typed_list.rb:20
        497029  my_report.rb:146
    
  23. simi commented on Apr 5, 2018

    @simi

    @barttenbrinke can you share your code to reproduce and measure this problem?

  24. comictvn commented on Apr 13, 2018

    @comictvn

    anyone already fixed the issue ?

  25. barttenbrinke commented on Apr 13, 2018

    @barttenbrinke

    @simi https://github.com/SamSaffron/memory_profiler @comictvn This wil not be an easy fix, seeing that row.rb has not been touched in > year, and the fact that this ticket is from 2014, I would not get your hopes up.

  26. jeremywadsack commented on Apr 13, 2018

    @jeremywadsack

    Well, yes, @jurriaan said this in 2014: "the fact is that atm axlsx allocates way too much objects and causes the heap to grow very big."

    I think there are two approaches here, neither is likely to be "easy":

    1. Significantly reduce the size of the Cell objects.
    2. Flush rows to disk as they are created.

    The problem with the first approach is that we might generate a large excel file with 20+ tabs and each of them with five million rows (I know Excel doesn't support that many rows, but the file format does). I think you'd need to reduce the size of the Cell object to under 50 bytes to make a significant difference.

    The problem with the second approach is that it means the developer would have to write rows in sequential order. That's a change to the interface, so would need to be "opt-in".

  27. iamtheschmitzer commented on Aug 3, 2018

    @iamtheschmitzer

    👍 for solution 2

  28. mkhoa1412 commented on Aug 21, 2018

    @mkhoa1412

    +1, this is a huge issue for me. I'd be happy to hear about possible workarounds.

  29. janklimo commented on Sep 25, 2018

    @janklimo

    We've been very happy with axlsx but the memory usage really started to hit us. This may not apply to everyone, but if all you need is simple XLSX formatted output, I'd look into rubyXL. Here's a benchmark showing that its retained memory is constant, while memory retained by axlsx grows linearly with the number of rows/columns.

  30. gdimitris commented on May 3, 2019

    @gdimitris

    @randym any update on this ?

  31. wangDvp commented on Dec 16, 2025

    @wangDvp

    I use the fast_excel instead of this gem,
    this is my local benchmark test result
    `

    === FastExcel 1000 x 20 ===
    Time: 0.08s
    RSS:  26.3MB -> 28.4MB
    Major GC: +1
    
    === FastExcel 1000 x 50 ===
    Time: 0.16s
    RSS:  28.6MB -> 32.1MB
    Major GC: +1
    
    === FastExcel 1000 x 100 ===
    Time: 0.31s
    RSS:  32.1MB -> 37.9MB
    Major GC: +1
    
    === FastExcel 10000 x 20 ===
    Time: 0.69s
    RSS:  37.9MB -> 51.8MB
    Major GC: +1
    
    === FastExcel 10000 x 50 ===
    Time: 1.64s
    RSS:  51.8MB -> 92.4MB
    Major GC: +1
    
    === FastExcel 10000 x 100 ===
    Time: 3.2s
    RSS:  92.4MB -> 164.4MB
    Major GC: +1
    
    === FastExcel 100000 x 20 ===
    Time: 6.89s
    RSS:  164.4MB -> 318.3MB
    Major GC: +1
    
    === FastExcel 100000 x 50 ===
    Time: 20.41s
    RSS:  318.3MB -> 722.6MB
    Major GC: +1
    

    `

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions