Bulk insertでも20時間以上かかっていたMySQLへのインサート処理を1時間以内にする - エムスリーテックブログ

この記事はエムスリー Advent Calendar 2022の30日目の記事です。 前日は id:kijuky による チームメンバーのGoogleカレンダーの休暇予定一覧をスプレッドシート+GASで作った でした。 AI・機械学習チームの北川(@kitagry)です。 今回はMySQLへのインサートを20倍以上高速化した話について書きます。 仕事をちゃんとしてるか見張る猫 TL; DR はじめに 今回のテーブル バイナリログを無効化する 追試 LOAD DATA INFILE 追試 テーブルの正規化 インデックスを一時的に剥がす まとめ We are hiring!! TL; DR バイナリログをオフにする LOAD DATA INFILEを使う インデックスを一時的に消す はじめに AI・機械学習チームではサイトトップからアプリに至るまで多くの推薦システムがあります。 そこでは推薦ロ

[GENS]

タイトル見てインデックスやろなあって見たらインデックスだった(MSSQLで同じ経験あり)。うちは一時テーブルにBulk Insertしてから本テーブルに差分反映させてる。

[theatrical]

知ってた。と言えば知ってたけど、じゃあそれをブログに書いたり社会に有益になる形で発信したか？と言うとしていないので、してくれている彼らは素晴らしいと思う。

[nippondanji]

他のところから持ってこれる（壊れても良い）データなら、バイナリログだけでなくinnodb_flush_log_at_trx_commit=0もつけるともっと速くなるよ。あと、ソート用のスレッドとバッファを https://bit.ly/3IcYWFL https://bit.ly/3vq7hOX

[frkw2004]

インデックスでselect処理が速くなる、って勉強した時に、その代わりinsertが遅くなる、って聞いてなかった？ ORACLEもsqlサーバーも大量インサートで一時的にインデックスを無効にする手法は覚えておいた方がいい。

[shikiarai]

TLDRだけ読んで満足した。これ結局体験しないと覚えないよね。対処しないと思ってる10倍は遅くなるからね。

[tkmkg8m]

バイナリログ無効化、LOAD DATA INFILE、正規化（これもインデックス削減効果疑惑）、インデックス無効化。なるほど。インデックスでインサート遅くなるのは知ってたけど、ここまで変わるもんなんだな。

[ya--mada]

インデックス張っててなんの工夫も無ければインサートが重いのは当然なので、最初の20時間という表現はある意味で盛り過ぎである。

[ryuichi1208]

3億行...うっ...

[lesamoureuses]

“ここで10倍以上早くなったことになります。 contentsテーブルへのインサートについては数十秒で終わっていたので、target_userテーブルへ2時間かかっていたということになります”

[pinkumohikan]

👀

[ouest]

インデックスは効果あるのか...

[mak_in]

個人的には、使い方雑なRDBのテーブルは、入れても100万行までかな、っていうざっくりな感覚があり、ここから先のRDBは性能含めちゃんと考える必要が出てくるので、面倒な時はもうNoSQL使っちゃう場合多いかな。

[mobius118_7]

当たり前の話をさも新発見のように書く。小学校の算数をやらずに中学数学を解こうとして筆算式を見つけたような話。

[quabbin]

そもそも、こんな大量のデータ投入は初期投入以外にはあまりないので、そこまで時間は気にならなかったり。初期投入以外で必要な場合は、DWHで対応できないかを考えたり、DB設計を見直したりするべきだ

[weakref]

新人っぽいから選べないんだろうけど、1憶レコード以上を想定する機械学習環境でMySQLを選定しちゃったことが根本原因だよね。会社としてブログの査読とかしないのかね…

[satosssi]

データ本体と同じ程度にインデックスがでかくなったテーブルを育てたことがあるからわかるよ😂

[hxs]

正規化でそんなに変わるのかぁ 1カラムのデータ量はそんなに大きくないのに

[taguch1]

load data 等のSQLパーサーを通さない取り込みとindexあたりであとは誤差って気がしてる。20年は変わってないからここら辺は落としどころが固定化されとるはず。

[d6rkaiz]

主要因は正規化の問題のような？

[dorapon2000]

“1億行から2億行に増えたときには大体倍くらいの時間に増えているので妥当な感じがしていますが、3億行をインサートするときには何故か一気に時間が増えてしまっています。 これはインデックスのBツリーのリバラン”

[findup]

インデックス剥がすの20年くらいの大昔から割と当たり前の選択肢にあったけど、最近はマシンパワーでぶん殴るからそう当たり前な事でもないってことなのかな。

[ed_v3]

よくやると思ってるけど、1億行だとインデックスの数とかによっては再作成の時間も結構かかりそう。どれくらい時間かかってるかが気になる。

[miquniqu]

いつか使うかも。

[mayumayu_nimolove]

mysqlってそんな時間かかるのか

[dagama]

データ移行とかやったことあれば常識の知見だった

[aa_R_waiwai]

id:frkw2004 聞いててもパッと出てこないことは多いし、そもそもインサート後にまたインデックスを戻すってのも、スマートではない考えもある。インデックスを剥がさずに目的の時間に到達できてるなら、それで良い。

[sasasin_net]

20時間で諦めた件は、諦めず放っといたら何時間かかるのか気になります

[sigwyg]

インデックスだろうなと思ったらインデックスだった。

[takuya_1st]

一次記憶メモリ128GB積んじゃおう

[x100jp]

必要になった時に、バカやる前にこういうエントリを思い出せますように。