エムスリーテックブログ

エムスリー(m3)のエンジニア・開発メンバーによる技術ブログです

Bulk insertでも20時間以上かかっていたMySQLへのインサート処理を1時間以内にする

この記事はエムスリー Advent Calendar 2022の30日目の記事です。 前日は id:kijuky による チームメンバーのGoogleカレンダーの休暇予定一覧をスプレッドシート+GASで作った でした。 AI・機械学習チームの北川(@kitagry)です。

今回はMySQLへのインサートを20倍以上高速化した話について書きます。

仕事をちゃんとしてるか見張る猫

TL; DR

  • バイナリログをオフにする
  • LOAD DATA INFILEを使う
  • インデックスを一時的に消す

はじめに

AI・機械学習チームではサイトトップからアプリに至るまで多くの推薦システムがあります。 そこでは推薦ロジックの作成から、実際に推薦ロジックを運用できるようなエンドポイントの作成まで行なっています。 また、m3には会員なら誰でもみられるようなニュースなどのコンテンツから、特定のユーザのみに向けたコンテンツなど幅広くあります。 今回はコンテンツ横断の推薦システムを作っているときに出会ったMySQLのインサートの高速化について書きます。

今回のテーブル

今回のテーブルは仮に以下のようなテーブルについて考えます。 このテーブルは特に正規化などは考えていませんが、その分取得を高速化できるため大量のアクセスには向いていると言えます。

なぜcontentsテーブルにuser_idが入っているかというと、前章で述べた通りターゲティングされているユーザーがいるためです。

CREATE TABLE contents (
    user_id INTEGER,
    item_id VARCHAR(32),
    item_type VARCHAR(32),
    title VARCHAR(256),
    thumbnail VARCHAR(256),
    PRIMARY KEY(user_id, item_id, item_type)
);

最初にこのテーブル設計で3億行のデータをインサートしてみましたが、(タイトル通り)20時間かかっても終わりませんでした。 このままの設計だと推薦のデリバリ時間に間に合わないためインサートの高速化が急務になりました。 ちなみに20時間経ったところで処理を中断してしまったので、実際にかかる時間は不明です。

バイナリログを無効化する

バイナリログとはMySQLのテーブル作成操作やテーブルデータへの変更などのデータベース変更が格納されているログです。 バイナリログはレプリケーション時にレプリカに送信されたり、データリカバリのために使用されます。 AI・機械学習チームの場合はこれらのデータはDWHから取得しているため、リカバリの必要はほとんどありません。 このバイナリログはインサート時にはログとして出力されるため、処理速度に影響します。

バイナリログが有効になっているかどうかは以下のコマンドで確認できます。

mysql> SHOW GLOBAL VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.18 sec)

このバイナリログを無効化することによって、インサート時のログの書き込み処理が無くなり一定の高速化ができます。 AI・機械学習チームではGCPのCloud SQLを使っているため、以下のコマンドで無効化できます(Google Cloudのドキュメントより)。

gcloud sql instances patch INSTANCE_NAME --no-enable-bin-log

さて、この施策によってどれくらい高速化できたかというと、、、

変わらず20時間経っても終わりませんでした。 この時点ではインサートが一度も終わっていないのでどれくらい高速化できたかはまだ謎のままでした。

追試

ローカルで2000万行ほどのデータの挿入時間をみてみたら(ただしテーブル構成は異なります)、以下のような結果になりました。

バイナリログ有効 バイナリログ無効
3m44.004297203s 2m54.129858201s

これを見る限りではかなりの効果があるように見えます。

LOAD DATA INFILE

LOAD DATA INFILEはテキストファイルからテーブルに行を高速に読み取るMySQL独自(?)の記法になります。 多くの場合はcsvファイルを書き出し、そのファイルを使ってこの文法を使用することになります。

Pythonで書くと以下のようになります。

conn = pymysql.connect(..., local_infile=True)

insert_data = pd.DataFrame([...])

with tempfile.NamedTemporaryFile('w+') as f:
    # LOAD DATA INFILE treat "\N" as null value.
    insert_data.to_csv(f, index=False, na_rep='\\N')
    f.seek(0)
    sql = f"""LOAD DATA LOCAL INFILE '{f.name}'
        INTO TABLE {table_name}
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        LINES TERMINATED BY '\n'
        IGNORE 1 LINES
        ({', '.join(df.columns)})"""

ここでこの文法を使用する上での注意点について述べます。

  • localであることを明示する
  • nullデータの扱い
  • カラム順の指定

まず最初のlocalであることの指定についてです。 今回のようにPythonから外部のMySQLのサーバーに繋ぐ場合はファイルの置き場所がローカルにあることを指定する必要があります。 そのため、まず pymysql.connect のときに local_infile オプションを有効にしておく必要があります。 次にSQL文については LOAD DATA LOCAL INFILE のように LOCAL のファイルであることを明示しておく必要があります。

次にnullデータの扱いですが、LOAD DATA INFILEではnullを\Nで扱うようにしています。 今回はpandasのto_csvメソッドで na_rep='\\N' と指定することによってnullを扱っています。

最後にカラムの指定です。LOAD DATA INFILEでは、CSVファイルの記載順の通りにカラムの順番を指定する必要があります。 今回の例ではpandas.DataFrameのcolumns名を指定しています(上のコードの最後の行)。

さて、この施策によってどれくらい高速化できたかというと、、、

変わらず20時間経っても終わりませんでした。 まだまだ、20時間経っても終わりません。

追試

ローカルで2000万行ほどのデータの挿入時間をみてみたら(ただしテーブル構成は異なります)、以下のような結果になりました。

Bulk Insert LOAD DATA INFILE
2m54.129858201s 2m11.075840151s

これを見る限りではやはり一定の効果がありそうです。

テーブルの正規化

上2つの施策を行なっても変わらず遅いままだったので、titleなどのデータの大きなカラムが問題なんじゃないかと仮説を立てました。 そこで、テーブル構成を以下のように変更しました。

CREATE TABLE contents (
    item_id VARCHAR(32),
    item_type VARCHAR(32),
    title VARCHAR(256),
    thumbnail VARCHAR(256),
    PRIMARY KEY(item_id, item_type)
);

CREATE TABLE target_user (
    user_id INTEGER,
    item_id VARCHAR(32),
    item_type VARCHAR(32),
    INDEX idx_user_id(user_id)
);

userとcontentの紐付けを行うための中間テーブルを別に作成した形になります。 また、基本的にAPIからデータを引くときにはuser_idからitem_idとitem_typeを引く形になるので、あえてインデックスはuser_idにしか貼っていません。 このようにテーブルを正規化することによってcontentsデータは1000行ほどで、target_userデータは変わらず3億行ほどでした。

さて、この施策によってどれくらい高速化できたかというと、、、

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

インデックスを一時的に剥がす

上の2時間で解決でも良かったのですが、念の為1億行ごと増やしていったときのインサートにかかる時間について調べてみました。

行数 かかった時間
1億行 20分
2億行 45分
3億行 110分

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

インデックスを外したら予想外のスピードアップに歓喜する人々

なんとインサートが30分で終わります。その後ALTER TABLE ADD INDEXを単体で行うと15分ほどで終わります。 合計すると45分ほどで3億行のインサートが終わったことになります。 おそらく仮説通り、インデックスのリバランシングか何かによってインサート時間が伸びているようでした。

今回の用途だけを考えるなら、プロダクトコードにインデックスの情報をハードコードし、インサート前後でインデックスを消したり戻したりするという対応になるでしょう。 しかし、今後同じ問題にぶつからないように、社内用のライブラリとして以下のように一時テーブルをインデックスが外れた状態で作成し、その後インデックスを付け直すというようなコードを実装しました。

※ これは降順インデックスなどには対応していないため、そのままの使用はお控えください。もしこの機能に対応する既存ライブラリがあればぜひ教えてください。

from pymysql.cursors import DictCursor

@contextmanager
def _create_tmp_table_for_insert(cursor: DictCursor, target_table: str):
    """
    target_tableと同じカラムをもつtmp_tableをインデックスなしで作成する。
    with句を抜ける時にインデックスを付与する。
    """
    tmp_target_table = f'tmp_{target_table}'
    create_table_sql = f'CREATE TABLE {tmp_target_table} LIKE {target_table};'
    cursor.execute(create_table_sql)

    cursor.execute(f'SHOW INDEX FROM {tmp_target_table}')
    indices = cursor.fetchall()
    keys: DefaultDict[str, List[Dict[str, Any]]] = defaultdict(list)
    for index in indices:
        keys[index['Key_name']].append(index)

    for key_name in keys.keys():
        cursor.execute(f'DROP INDEX `{key_name}` ON `{tmp_target_table}`')

    yield tmp_target_table

    for key_name, index_list in keys.items():
        index_columns = [f'`{index["Column_name"]}`' for index in index_list]
        if key_name == 'PRIMARY':
            cursor.execute(f'ALTER TABLE `{tmp_target_table}` ADD PRIMARY KEY ({",".join(index_columns)})')
        elif index_list[0]['Non_unique'] == 0:
            cursor.execute(f'ALTER TABLE `{tmp_target_table}` ADD UNIQUE KEY `{key_name}`({",".join(index_columns)})')
        else:
            cursor.execute(f'ALTER TABLE `{tmp_target_table}` ADD KEY `{key_name}`({",".join(index_columns)})')


with _create_tmp_table_for_insert(cursor, target_table) as tmp_table:
    insert(tmp_table, insert_data)

これによって最初は20時間経っても終わらないインサート処理が1時間かからずに終わるようになりました!

また、インデックスがここまで処理を遅くしていたことを考えると前章で正規化による高速化の影響は実は PRIMARY KEY(user_id, item_id, item_type)INDEX(user_id) のみになったことが大きいのかもしれません。 PRIMARY KEYのようなUNIQUE処理も入るインデックスよりも、ただのBツリーを実装するだけの方が時間が短いと考えられるからです。

また、ここまで大々的にインデックスを外さなくても、UNIQUE処理をなくすだけでも良いという方は以下のようなコマンドでも簡単に高速化できます。 詳しくは公式ドキュメントが用意してくれている高速化Tipsを参照してください

SET unique_checks=0;
... SQL import statements ...
SET unique_checks=1;

まとめ

僕は今まで数億単位のデータを一度にインサートするという経験がなく、チームメンバーに助けてもらいながら高速化ができました。 僕にとっては3億行くらいなら1時間以内で行けるんだなという新しい発見があり、とても楽しく開発できました。

We are hiring!!

弊社では既存システムのパフォーマンスを20倍にしてくれるエンジニアを募集しています。 以下のURLからカジュアル面談お待ちしています!

jobs.m3.com