openpyxlのメモリ使用量

openpyxlでExcelファイルを生成する際、ちょっとでかいワークブックを作るとすごい勢いでメモリを消費します。
どうにかならないかなぁと困っていたら、公式ドキュメントのWrite-only modeに「write_onlyモードにするとメモリ使用量を10MB以下に抑えられる & write_onlyではlxmlの使用を推奨」とありました。
https://openpyxl.readthedocs.io/en/stable/optimized.html#write-only-mode

• It is able to export unlimited amount of data (even more than Excel can handle actually), while keeping memory usage under 10Mb.
• Here again, the regular openpyxl.worksheet.worksheet.Worksheet has been replaced by a faster alternative, the openpyxl.worksheet._write_only.WriteOnlyWorksheet. When you want to dump large amounts of data make sure you have lxml installed.

ソース見てみたら、内部的にはlxmlの有無で動作を変えているほかにもdefusedxmlの有無でも挙動を変えていることがわかりました。
そのため、以下のように条件を変えた場合のメモリ使用量および実行速度を比較しました。

• write_onlyモードの有無
• lxml, defusedxmlの使用

結論

• write_onlyモードにするとメモリ使用量が劇的に少なくなる。
  • 1万回のappendで半分、1千万回のappendで0.11%。
  • 10MBは言い過ぎだけど、実行時RSSは30MBで収まった。write_onlyモードではデータ量にかかわらずメモリ使用量が一定となる。
• lxml, defusedの有無の影響は以下の通り。
  • lxmlの導入ではwrite_onlyモードにかかわらず実行時間が2割短縮。メモリ使用量は変わらず。
  • defusedxmlの導入では実行時間・メモリ使用量に大きな効果はなし。

write_onlyモードはワークシートへのデータ書き込みがappendのみでcellが使えないので制限は強いですが、大きくメモリ使用量を節約できるので、使いどころがあれば強みを発揮できそうです。
とはいえ、write_onlyの制限は強いので、使用に当たっては要件を満たせるかきちんと確認することが必要ですね。

実験内容

以下のソースをもとに、実行回数の変更と実行時のメモリ使用量を取得して表示するように修正しました。
https://dev.classmethod.jp/articles/openpyxl-use-lxml-with-lambda/

import argparse
from openpyxl import Workbook, load_workbook
from openpyxl.xml import LXML, DEFUSEDXML
import os
import subprocess
import time

def get_process_status():
    pid = os.getpid()
    status_file = f"/proc/{pid}/status"
    vm_size = None
    vm_rss = None
    with open(status_file) as f:
        for line in f:
            if line.startswith("VmSize:"):
                vm_size = line.strip()
            elif line.startswith("VmRSS:"):
                vm_rss = line.strip()
    return f"{vm_size}, {vm_rss}"
 
# https://dev.classmethod.jp/articles/openpyxl-use-lxml-with-lambda/
def lambda_handler(wb, siz):
    ws = wb.create_sheet()

    begin_write = time.perf_counter()
    for i in range(siz):
        ws.append([f"{i}-{j}" for j in range(0, 10)])
    end_write = time.perf_counter()
    write_status = get_process_status()
#    print(f"Write time: {(end_write - begin_write)} s, {get_process_status()}")

    begin_save = time.perf_counter()
    wb.save("hello_world.xlsx")
    end_save = time.perf_counter()
    return f"{(end_write - begin_write)}, {end_save - begin_save}, {write_status}, {get_process_status()}"

def main():
    parser = argparse.ArgumentParser(description='Read an Excel file using openpyxl.')
    parser.add_argument('file', help='Path to the Excel file')
    parser.add_argument('--size', type=int, default=50000, help='Number of rows to write (default: 50000)')
    parser.add_argument('--write_only', action='store_true', help='Use write_only mode (default: False)')
    args = parser.parse_args()
    wb = Workbook(write_only=True) if args.write_only else load_workbook(args.file)
    result = lambda_handler(wb, args.size)
    print(f'LXML:{LXML}, DEFUSEDXML:{DEFUSEDXML}, write_only:{args.write_only}, size:{args.size}, {result}')

if __name__ == '__main__':
    main()

上記プログラムを以下の通り実行しました。

• ワークシートへのappendを1,000回、10,000回、100,000回、1,000,000回、10,000,000回実行
• write_onlyモードの有無
• xml追加ライブラリなし、lxmlのみインストール、defuedxmlのみインストール

実験は以下の物理マシンを占有して実施しています。実行中VSSに対してメモリ空き容量が十分に大きくメモリ不足の可能性がないため、測定結果ではRSZの値のみ記載しています。

要素	値
CPU	Ryzen 3 3200G
メモリ	64GB
ディスク	SSD(500GB)

追加のxmlライブラリなし

• write_only無効時はデータ量に比例して使用メモリ量も増えるが、write_only有効時はデータ量にかかわらず25MB程度で収まっている。
• 総実行秒数はあまり変わらないが、append完了までの秒数とsave完了までの秒数の内訳は異なる。

write_only無効

append回数	append完了までの秒数	save完了までの秒数	総実行秒数	append完了時のRSZ(KB)	save完了時のRSZ(KB)
1,000	0.0	0.1	0.2	27,976	28,828
10,000	0.3	1.3	1.5	53,120	56,380
100,000	3.2	13.4	16.6	289,928	310,668
1,000,000	32.1	120.1	152.2	2,727,276	2,730,064
10,000,000	342.8	1,192.0	1,534.8	29,151,932	29,195,268

write_only有効

append回数	append完了までの秒数	save完了までの秒数	総実行秒数	append完了時のRSZ(KB)	save完了時のRSZ(KB)
1,000	0.1	0.0	0.2	25,416	25,800
10,000	1.4	0.2	1.5	25,420	25,804
100,000	13.7	2.5	16.2	25,420	25,676
1,000,000	136.8	6.5	143.4	25,424	25,624
10,000,000	1,370.7	70.9	1,441.6	25,416	25,672

lxmlをインストール

追加のライブラリなしに比べて以下の傾向。

• write_only有効・無効にかかわらず実行時間が2割程度短縮。
• write_only有効・無効でのメモリ資料量傾向は大きな変化はない。

write_only無効

append回数	append完了までの秒数	save完了までの秒数	総実行秒数	append完了時のRSZ(KB)	save完了時のRSZ(KB)
1,000	0.0	0.1	0.1	33,120	34,400
10,000	0.3	1.0	1.3	58,024	62,572
100,000	3.2	10.5	13.6	294,828	316,672
1,000,000	32.5	97.6	130.1	2,732,328	2,777,116
10,000,000	343.5	917.0	1,260.5	29,156,848	29,200,868

write_only有効

append回数	append完了までの秒数	save完了までの秒数	総実行秒数	append完了時のRSZ(KB)	save完了時のRSZ(KB)
1,000	0.1	0.0	0.1	30,436	30,820
10,000	1.1	0.2	1.3	30,428	30,880
100,000	11.2	2.4	13.7	30,432	30,944
1,000,000	111.1	6.3	117.3	30,556	30,812
10,000,000	1,113.8	70.9	1,184.8	30,556	31,068

defusedxmlをインストール

追加のライブラリなしに比べて以下の傾向。

• write_only有効・無効での実行時間・メモリ資料量傾向は大きな変化はない。

write_only無効

append回数	append完了までの秒数	save完了までの秒数	総実行秒数	append完了時のRSZ(KB)	save完了時のRSZ(KB)
0.0	0.1	0.2	28,416	29,312
0.3	1.2	1.5	53,196	57,792
3.2	13.1	16.3	289,996	310,944
32.5	118.7	151.1	2,727,500	2,730,292
344.3	1,196.3	1,540.6	29,152,516	29,197,280

write_only有効

append回数	append完了までの秒数	save完了までの秒数	総実行秒数	append完了時のRSZ(KB)	save完了時のRSZ(KB)
0.1	0.0	0.2	25,728	26,112
1.4	0.2	1.5	25,600	25,984
13.5	2.6	16.2	25,728	26,112
136.1	8.1	144.2	25,472	25,856
1,361.3	72.3	1,433.5	25,600	25,856