High peak memory usage when writing large matrices

[HinanaV]

Hello!
When writing a large dataset(580k * 21) to a new .xlsx file using XLSX.jl in mode="w", peak memory usage becomes extremely high (≈ 20GB in my case) . This happens even without rw editing.

using JLD2, Random

nrow = 580_000
ncol = 21
Random.seed!(123)
X = rand(Float32, nrow, ncol)
jldsave("X_580k_21.jld2", true; X)
println("saved: X_580k_21.jld2")

using XLSX, JLD2

T = JLD2.load("X_580k_21.jld2")
XLSX.openxlsx("mem_test.xlsx", mode="w") do xf
    sh = xf[1]
    sh["A1"] = T["X"]
end

My source data is stored in JLD2 and is relatively small (580_000 × 21 × 4 bytes = 48,720,000 bytes ≈ 46.5 MiB).
The resulting XLSX file is around 151 MB on disk after saving.

Julia 1.9.3
Windows11

[TimG1964]

Hello!

Yes, XLSX.jl has quite a heavy memory footprint. This is inherent in the way it is designed. It is not really fair to compare it in size with a "simple" matrix of Float32 values. For example, in XLSX.jl v0.11.0-Dev, a single cell containing one of these Float32 values looks like this:

julia> c=XLSX.getcell(s, "C254")
XLSX.Cell(C254, "", "", "0.6758789420127869", XLSX.Formula("", nothing))

and, using About.jl, you can see that this is a lot more than 4 bytes:

julia> about(c)
XLSX.Cell (mutable) (<: XLSX.AbstractCell <: Any)
 Memory footprint: 56B directly (referencing 118B in total)

There are over 12m such cells in the XLSXFile you are creating. In fact, just considering the worksheet cache that XLSX.jl creates, About.jl says

julia> about(s.cache)
XLSX.WorksheetCache{XLSX.SheetRowStreamIterator} (mutable) (<: XLSX.SheetRowIterator <: Any), occupies 64B directly (referencing 2.0GB in total)

Remember, too, that an Excel file is a compressed zip archive. The internal sheet1.xml file in mem_test.xlsx achieves an 81% compression ratio. Inside XLSX.jl, the data are all uncompressed.

Some progress has been made in reducing the memory footprint in v0.11.0-Dev by eliminating the duplication of sheetrow and sharedStrings data that were previously stored in both the copies of the individual .xml files in f.data and in each worksheet cache, s.cache. In your example of mem_test.xlsx, the worksheet file in f.data is reduced from over 500MB (in v0.10.4) to only 981B in v0.11.0-Dev by cutting out the duplicated sheetrow data.

During writing, the data in the cache needs to be reinserted into the internal .xml files. While this happens, both copies of the data exist simultaneously until each file is written out. This is only temporary and only one file at a time. Even so, it is during this write process that memory demand reaches the maximum.

There is undoubtedly more that could be done in future to reduce the memory footprint of XLSX.jl, particularly while writing.

[TimG1964]

My source data is stored in JLD2 and is relatively small (580_000 × 21 × 4 bytes = 48,720,000 bytes ≈ 46.5 MiB).
The resulting XLSX file is around 151 MB on disk after saving.

Just for comparison, I created an array of 580000 x 21 values between 0 and 1 natively in Excel itself. This file is 176MB in size. So I don't think the files XLSX.jl produces are excessively big compared with Excel itself, despite the significant use it makes of working memory.