Node-RED で SQLite を使ったときに INSERT や UPDATE を複数レコードまとめて送って負荷軽減したメモです。
状況
自分個人が使っている WEB サービスのログを API から定期的に取得した場合に、以下のシチュエーションに遭遇しました。
ざっくり書くと、
- node-red-node-sqlite (node) で SQLite を扱っている
- API から 5000 件くらいのデータを offset や limit を駆使して 100 件ごとに取得している
- API で受け取った 100 件配列を split ノードで 1 件ごとに分けてから SQLite に一気に 100 回書き込みする
- 初期記録は INSERT で、同じデータは UPDATE で定期的に更新する
- 2000 件くらいまではサクサク動くが、3000 件、4000 件と増えてくると SQLite の書き込みが蓄積するようになりめちゃくちゃ遅くなる
- 当初 2000 件くらいまで軽快に動いていたので 5 分くらいで再取得していたら、その前の SQLite の書き込みが終わり切らず、どんどん溜まっていく
- 最悪の場合、負荷がかかりすぎて Node-RED が強制再起動してしまう
といった、状況です。
原因
Node-RED で SQLite を触れていたら、今回の取得件数だと途中で負荷が高かったんだけど良くなりました。
1件ずつ UPDATE や INSERT の SQL を送るのが原因らしく 100 件一気に登録する SQL 文にしたら高速に処理がさばけるようになりました！よかったー。 #nodered #noderedjp #sqlite— Tanaka Seigo (@1ft_seabass) August 3, 2021
いろいろチューニングして分かったこととしては
API で受け取った 100 件配列を split ノードで 1 件ごとに分けてから SQLite に一気に 100 回書き込みする
というところがネックになっていました。Node-RED の処理も影響してますが SQL文の作り方もチューニングする必要がありそうです。
フローはこんな感じです。
split ノードでバラバラに分けてしまったおかげで、一気に SQL 文を 100 件分書き込もうとするところが、遅くなりがちというところでした。
Node-RED でなく Node.js のようなプログラムで書くなら for 構文で 配列分けて、その中に、非同期の書き込み処理を一気に 100 個走らせたイメージです。実際、Node.js で似た挙動になるコードで試してみたところ、かなり重くなりました。
調べてみると、
- sqlite3 1万件を超えるならUPDATE/DELETEは使っちゃダメ: Script
- SQLiteのINSERT、UPDATE、DELETEが遅い | IT
- SQLiteデータベースのチューニング：Android Tips（21）（1/2 ページ） – MONOist
このような話題を見かけました。私の状況とは、一概に一致してはいないのですが、少なくとも「SQLiteを一気に大量に書き込む処理は遅くなる場合がある」という空気を感じます。
こういうときは 100 件を 1 つのSQL文で一気に書き込む
今回はまず split ノードでバラバラにしたものを、SQLite に1 件ごと書き込むのをやめました。
ちゃんと1件ごとのSQL文を一旦 join ノードでまとめてから、100 件を 1 つのSQL文で一気に書き込む流れにしたところ、スッキリ動作するようになりました。
INSERT の場合
実は、新規に書き込む INSERT 文はそんなに重くなかったのですが、早いほうがいいだろうし、この機会に覚えておこうということでやってみました。
1回のINSERT（インサートSQL）で複数行のレコードを一括挿入（追加）する | JOHOBASE
こちらの記事を参考に、
INSERT INTO record_datas (create_at, id, data) VALUES ('2021/08/16 23:00', 'ID1', '{"record":"AAA"}');
と
INSERT INTO record_datas (create_at, id, data) VALUES ('2021/08/16 23:01', 'ID2', '{"record":"BBB"}');
と
INSERT INTO record_datas (create_at, id, data) VALUES ('2021/08/16 23:04', 'ID3', '{"record":"CCC"}');
～100 件つづく～
1つずつ送っていたものを、
INSERT INTO record_datas
(create_at, id, data)
VALUES
('2021/08/16 23:00','ID1','{"record":"AAA"}'),
('2021/08/16 23:01','ID2','{"record":"BBB"}'),
('2021/08/16 23:02','ID3','{"record":"CCC"}');
と、100件まとめて送りました。VALUES 以下は、結構変換しやすい流れでした。
参考までに、仮に 3 件を一気に INSERT するフローを作ってみました。インポートできる JSON はこちらです。
[{"id":"dd3f430.142dac","type":"inject","z":"1484ad44.86eed3","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":310,"y":200,"wires":[["2b85fe3f.103f12"]]},{"id":"2b85fe3f.103f12","type":"change","z":"1484ad44.86eed3","name":"APIデータ例 3件","rules":[{"t":"set","p":"payload","pt":"msg","to":"[[\"2021/08/16 23:00\",\"ID1\",\"{\\\"record\\\":\\\"AAA\\\"}\"],[\"2021/08/16 23:01\",\"ID2\",\"{\\\"record\\\":\\\"BBB\\\"}\"],[\"2021/08/16 23:02\",\"ID3\",\"{\\\"record\\\":\\\"CCC\\\"}\"]]","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":530,"y":200,"wires":[["29977091.4d6bc"]]},{"id":"29977091.4d6bc","type":"split","z":"1484ad44.86eed3","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":710,"y":200,"wires":[["789c538c.60d2bc"]]},{"id":"50e4eb9a.bccab4","type":"join","z":"1484ad44.86eed3","name":"","mode":"auto","build":"string","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":"false","timeout":"","count":"","reduceRight":false,"x":1070,"y":200,"wires":[["5521829f.7a9d0c"]]},{"id":"789c538c.60d2bc","type":"change","z":"1484ad44.86eed3","name":"insert 文準備","rules":[{"t":"set","p":"payload","pt":"msg","to":"\"(\" &\t\"'\" & payload[0] & \"',\" &\t\"'\" & payload[1] & \"',\" &\t\"'\" & payload[2] & \"'\" &\t\")\"","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":870,"y":200,"wires":[["50e4eb9a.bccab4"]]},{"id":"5521829f.7a9d0c","type":"change","z":"1484ad44.86eed3","name":"SQL insert 複数行化","rules":[{"t":"set","p":"topic","pt":"msg","to":"\"INSERT INTO record_datas \\n\" &\t\" (create_at, id, data) \\n\" &\t\"VALUES \\n\" &\t$join(payload, \",\\n\") & \";\"\t","tot":"jsonata"},{"t":"set","p":"payload","pt":"msg","to":"{}","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":720,"y":360,"wires":[["b59a0840.2d67e8"]]},{"id":"b59a0840.2d67e8","type":"debug","z":"1484ad44.86eed3","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"topic","targetType":"msg","statusVal":"","statusType":"auto","x":980,"y":360,"wires":[]},{"id":"4d563c04.357e24","type":"comment","z":"1484ad44.86eed3","name":"SQL insert 複数行 対応","info":"","x":310,"y":160,"wires":[]}]
実際に node-red-node-sqlite と絡めて使う場合は、すでに上記のフローで msg.topic で SQL 文 を作ってあるので node-red-node-sqlite にむけて送ればよいです。
このような感じです。
UPDATE の場合
UPDATE の方がパフォーマンスが落ちやすい印象で、対応が急務でした。
SQL文としては、こちらのほうが複数行にまとめるのが大変でした。
こちらの記事を参考に、
UPDATE record_datas SET create_at = '2021/08/17 12:00' , data = '{"record":"111"}' WHERE id = 'ID1';
と
UPDATE record_datas SET create_at = '2021/08/17 12:01' , data = '{"record":"222"}' WHERE id = 'ID2';
と
UPDATE record_datas SET create_at = '2021/08/17 12:02' , data = '{"record":"333"}' WHERE id = 'ID3';
～100 件つづく～
1つずつ送っていたものを、
UPDATE record_datas SET
create_at = case id
WHEN 'ID1' THEN '2021/08/17 12:00'
WHEN 'ID2' THEN '2021/08/17 12:01'
WHEN 'ID3' THEN '2021/08/17 12:02' END,
data = case id
WHEN 'ID1' THEN '{"record":"111"}'
WHEN 'ID2' THEN '{"record":"222"}'
WHEN 'ID3' THEN '{"record":"333"}' END
WHERE id IN ('ID1','ID2','ID3');
という形でまとめました。
苦労したところとしては、INSERT のまとめ方よりも、WHERE IN あたりで狙う値を決めつつも、各値 create_at , data の中で case で WHERE IN で狙う値を再度呼び出してから、1つ1つの WHEN THEN で書き換える値をしていくという見た目の流れが、あべこべに入り乱れていたので作るのが大変でした。
参考までに、仮に 3 件を一気に UPDATE するフローを作ってみました。インポートできる JSON はこちらです。
[{"id":"10ee26f.7dbcbd9","type":"inject","z":"1484ad44.86eed3","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":320,"y":500,"wires":[["e1a566cf.b67ca8"]]},{"id":"e1a566cf.b67ca8","type":"change","z":"1484ad44.86eed3","name":"APIデータ例 3件","rules":[{"t":"set","p":"payload","pt":"msg","to":"[[\"2021/08/17 12:00\",\"ID1\",\"{\\\"record\\\":\\\"111\\\"}\"],[\"2021/08/17 12:01\",\"ID2\",\"{\\\"record\\\":\\\"222\\\"}\"],[\"2021/08/17 12:02\",\"ID3\",\"{\\\"record\\\":\\\"333\\\"}\"]]","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":540,"y":500,"wires":[["92fdfd3.ff265"]]},{"id":"92fdfd3.ff265","type":"split","z":"1484ad44.86eed3","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":710,"y":500,"wires":[["2360f9df.535396"]]},{"id":"c5741a5c.3839e8","type":"join","z":"1484ad44.86eed3","name":"","mode":"auto","build":"string","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":"false","timeout":"","count":"","reduceRight":false,"x":1080,"y":500,"wires":[["7e352330.c5323c"]]},{"id":"2360f9df.535396","type":"change","z":"1484ad44.86eed3","name":"update 文準備","rules":[{"t":"set","p":"payload","pt":"msg","to":"[\t \"WHEN '\" & payload[1] & \"' THEN '\" & payload[0] & \"'\",\t \"WHEN '\" & payload[1] & \"' THEN '\" & payload[2] & \"'\",\t \"'\" & payload[1] & \"'\"\t]","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":890,"y":500,"wires":[["c5741a5c.3839e8"]]},{"id":"7e352330.c5323c","type":"change","z":"1484ad44.86eed3","name":"SQL update 複数行化","rules":[{"t":"set","p":"topic","pt":"msg","to":"\"UPDATE record_datas SET \\n\" &\t\"create_at = case id \\n\" &\t$join(payload.*[0],\"\\n\") & \" \" & \t\"END, \\n\" &\t\"data = case id \\n\" &\t$join(payload.*[1],\"\\n\") & \" \" &\t\"END \\n\" &\t\"WHERE id IN (\" & $join(payload.*[2],\",\" ) & \");\"","tot":"jsonata"},{"t":"set","p":"payload","pt":"msg","to":"{}","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":730,"y":660,"wires":[["d50daf10.dba8e"]]},{"id":"d50daf10.dba8e","type":"debug","z":"1484ad44.86eed3","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"topic","targetType":"msg","statusVal":"","statusType":"auto","x":990,"y":660,"wires":[]},{"id":"c0cdf719.33c8d8","type":"comment","z":"1484ad44.86eed3","name":"SQL update 複数行 対応","info":"","x":330,"y":460,"wires":[]}]
INSERT 文と同様に、実際に node-red-node-sqlite と絡めて使う場合は、すでに上記のフローで msg.topic で SQL 文 を作ってあるので node-red-node-sqlite にむけて送りましょう。
おかげさまで、今回の対策によって、一旦、これくらいのボリュームでは良い感じな速度で記録できるようになりました。SQL 文や SQLite の文献を見ると、より多いレコード数だと、さらなるチューニングの世界があるようなので、必要に応じて取り入れていきたいと思います！