optimize memory allocation #722

Theodoree · 2020-10-29T04:21:09Z

PR Details

1.align xlsxRow struct.
2.optimization workSheetWriter
3. add NewSheetWithRowNum

Description

Mainly to optimize memory allocation

Related Issue

l wish somebody can optimize xlsxC struct. it's too bigger.Maybe it can be optimized smaller.

Motivation and Context

sometime l use this package. memory will alloc 500m ~ 1000m. it's too expensive

How Has This Been Tested


go test -run=none -bench=BenchmarkNewSheetWithRowNum -benchmem -count 10

name                   old time/op    new time/op    delta
NewSheetWithRowNum1-8     667µs ± 5%     679µs ±15%     ~     (p=0.968 n=9+10)

name                   old alloc/op   new alloc/op   delta
NewSheetWithRowNum1-8     643kB ± 0%     588kB ± 0%   -8.46%  (p=0.000 n=10+10)

name                   old allocs/op  new allocs/op  delta
NewSheetWithRowNum1-8     10.3k ± 0%      9.2k ± 0%  -10.68%  (p=0.000 n=10+10)


go test -run=none -bench=BenchmarkFile_SaveAs -benchmem -count 10
name           old time/op    new time/op    delta
File_SaveAs-8     738µs ±13%     698µs ±10%     ~     (p=0.105 n=10+10)

name           old alloc/op   new alloc/op   delta
File_SaveAs-8     643kB ± 0%     588kB ± 0%   -8.46%  (p=0.000 n=10+10)

name           old allocs/op  new allocs/op  delta
File_SaveAs-8     10.3k ± 0%      9.2k ± 0%  -10.67%  (p=0.000 n=9+10)

Types of changes

[ x ] New feature (non-breaking change which adds functionality)
[ x ] Breaking change (fix or feature that would cause existing functionality to change)

Checklist

[x ] My code follows the code style of this project.
My change requires a change to the documentation.
I have updated the documentation accordingly.
[ x ] I have read the CONTRIBUTING document.
[ x ] I have added tests to cover my changes.
[ x ] All new and existing tests passed.

codecov-io · 2020-10-29T06:16:44Z

Codecov Report

Merging #722 into master will increase coverage by 0.00%.
The diff coverage is 100.00%.

@@           Coverage Diff           @@
##           master     #722   +/-   ##
=======================================
  Coverage   96.65%   96.65%           
=======================================
  Files          31       31           
  Lines        8553     8557    +4     
=======================================
+ Hits         8267     8271    +4     
  Misses        163      163           
  Partials      123      123

Impacted Files	Coverage Δ
xmlWorksheet.go	`100.00% <ø> (ø)`
lib.go	`100.00% <100.00%> (ø)`
sheet.go	`97.28% <100.00%> (+0.01%)`	⬆️

Continue to review full report at Codecov.

Legend - Click here to learn more
Δ = absolute <relative> (impact), ø = not affected, ? = missing data
Powered by Codecov. Last update 9d470bb...a2b0cae. Read the comment docs.

xmlWorksheet.go

xuri · 2020-10-29T06:59:04Z

sheet.go

@@ -64,6 +64,19 @@ func (f *File) NewSheet(name string) int {
 	return f.GetSheetIndex(name)
 }

+// NewSheetWithRowNum provides the function to create a new sheet by given a worksheet and a given number of rows.


This new API seems unnecessary, SetCellValue will preallocate cells for any new worksheets.

Sometime will need this. Realloc cause huge GC pressure.
Example:
I need export a million row form db and write into excel, data is encrypt , need decrypt .
slice often realloc we cause huge GC pressure 、copy spending

I have encountered this situation before.
l remember alloc 1-3G mem. it's too Impressive.

Sorry for my late reply. We can using streaming API NewStreamWriter to generate huge data spreadsheet, by evaluation and follow the principle of least availability, I don't purpose to add this API. If you have any questions, let me know.

Theodoree · 2020-11-03T06:52:24Z

@xuri

Theodoree · 2020-11-03T07:26:02Z

@xuri use NewStreamWriter will take more Resources.

go test -run=none -bench=BenchmarkNewSheet -benchmem -count 10

name time/op
NewSheetWithRowNum-8 631µs ±12%
NewSheetWithOutRowNum-8 636µs ± 2%
NewSheetWithStreamWriter-8 1.07ms ± 3%

name alloc/op
NewSheetWithRowNum-8 508kB ± 0%
NewSheetWithOutRowNum-8 616kB ± 0%
NewSheetWithStreamWriter-8 880kB ± 4%

name allocs/op
NewSheetWithRowNum-8 9.18k ± 0%
NewSheetWithOutRowNum-8 9.20k ± 0%
NewSheetWithStreamWriter-8 12.6k ± 0%

Theodoree · 2020-11-03T07:26:54Z

go test -run=none -bench=BenchmarkNewSheet -benchmem -count 10

name time/op
NewSheetWithRowNum-8 631µs ±12%
NewSheetWithOutRowNum-8 636µs ± 2%
NewSheetWithStreamWriter-8 1.07ms ± 3%

name alloc/op
NewSheetWithRowNum-8 508kB ± 0%
NewSheetWithOutRowNum-8 616kB ± 0%
NewSheetWithStreamWriter-8 880kB ± 4%

name allocs/op
NewSheetWithRowNum-8 9.18k ± 0%
NewSheetWithOutRowNum-8 9.20k ± 0%
NewSheetWithStreamWriter-8 12.6k ± 0%

Theodoree · 2020-11-03T07:32:38Z

l will Think about it, maybe I'll delete this api. NewSheetWithRowNum

sheet_test.go

xuri · 2020-11-03T07:46:05Z

FYI, here is a graph that shows the performance comparison of normal API and streaming writing API. I'll public testing code later.

Theodoree · 2020-11-03T08:00:47Z

@xuri I have deleted that api. l was testing. l find NewStreamWriter will save more mem.but Execution time will be longer.

xuri · 2020-11-03T08:19:18Z

Usually, the streaming API will use less memory and faster than normal API, but affected by business scenario, you need trade-off by yourself. Try profiling your program by pprof to get more details.

Theodoree · 2020-11-03T09:45:48Z

@xuri so.when can merge PR ?

xuri · 2020-11-03T09:48:59Z

/lgtm Thanks @Theodoree

* optimize marshal * optimize mem alloc * add benchmark testing * add NewSheetWithRowNum testing * sync struct fields order * add BenchmarkNewSheetWithStreamWriter * delete NewSheetWithRowNum and benchmark test

Theodoree added 3 commits October 28, 2020 22:40

optimize marshal

dac7f18

optimize mem alloc

474a8ba

add benchmark testing

7a16205

add NewSheetWithRowNum testing

88426e3

xuri added the size/M Denotes a PR that changes 30-99 lines, ignoring generated files. label Oct 29, 2020

xuri requested changes Oct 29, 2020

View reviewed changes

xmlWorksheet.go Show resolved Hide resolved

xuri reviewed Oct 29, 2020

View reviewed changes

sync struct fields order

d384dff

add BenchmarkNewSheetWithStreamWriter

7f82da1

Theodoree closed this Nov 3, 2020

Theodoree reopened this Nov 3, 2020

xuri reviewed Nov 3, 2020

View reviewed changes

sheet_test.go Outdated Show resolved Hide resolved

delete NewSheetWithRowNum and benchmark test

a2b0cae

xuri merged commit fcca8a3 into qax-os:master Nov 3, 2020

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

optimize memory allocation #722

optimize memory allocation #722

Theodoree commented Oct 29, 2020

codecov-io commented Oct 29, 2020 •

edited

Loading

xuri Oct 29, 2020

Theodoree Oct 29, 2020

Theodoree Oct 29, 2020

xuri Nov 3, 2020

Theodoree commented Nov 3, 2020

Theodoree commented Nov 3, 2020 •

edited

Loading

Theodoree commented Nov 3, 2020

Theodoree commented Nov 3, 2020

xuri commented Nov 3, 2020

Theodoree commented Nov 3, 2020 •

edited

Loading

xuri commented Nov 3, 2020

Theodoree commented Nov 3, 2020

xuri commented Nov 3, 2020

optimize memory allocation #722

optimize memory allocation #722

Conversation

Theodoree commented Oct 29, 2020

PR Details

Description

Related Issue

Motivation and Context

How Has This Been Tested

Types of changes

Checklist

codecov-io commented Oct 29, 2020 • edited Loading

Codecov Report

xuri Oct 29, 2020

Choose a reason for hiding this comment

Theodoree Oct 29, 2020

Choose a reason for hiding this comment

Theodoree Oct 29, 2020

Choose a reason for hiding this comment

xuri Nov 3, 2020

Choose a reason for hiding this comment

Theodoree commented Nov 3, 2020

Theodoree commented Nov 3, 2020 • edited Loading

Theodoree commented Nov 3, 2020

Theodoree commented Nov 3, 2020

xuri commented Nov 3, 2020

Theodoree commented Nov 3, 2020 • edited Loading

xuri commented Nov 3, 2020

Theodoree commented Nov 3, 2020

xuri commented Nov 3, 2020

codecov-io commented Oct 29, 2020 •

edited

Loading

Theodoree commented Nov 3, 2020 •

edited

Loading

Theodoree commented Nov 3, 2020 •

edited

Loading