Edited at

Railsでバルクインサートしてみたら、6時間かかっていた処理を30秒にできた話。


はじめに

こんにちは。

株式会社Nexceedにて、学生フルスタックエンジニアとして働いてる若造です。

今回は、Railsのパフォーマンスチューニング、

特に、DBへのデータのインサートとアップデートについて、行ったことを紹介したいと思います。

最近は、ビッグデータの時代と言われることもあり、

大量のデータを捌かなければならないことも少なくはないと思います。

また、そのデータを捌けるか捌けないかで、使う側のユーザーのストレスも大きく変わると思います。

ぜひ、そんな方々の課題解決の足がかりにでもなってくれたら、幸いです。


内容


パフォーマンスチューニング以前の状況

実際に行いたかった処理は、3万〜4万行 ぐらいある CSV を、

Railsアプリを介して、 Mysql にインサート(登録) or アップデート(更新)することです。

もちろん、CSV のカラム数も 20〜30個 ぐらいあり、

さらにそれなりに複数にリレーションが張られたテーブルに登録していくので、

普通にやるとめちゃくちゃ時間のかかる処理でした。

(当時は、当たり前のようにコードを書いていましたが ... :sweat_smile:

それで、CSV の取り込みにどんくらい時間かかってたかというと、

題名の通り、約6時間 かかってました。


パフォーマンスチューニング以前のコード

パフォーマンスチューニング前は以下のように取り込んでいました。

かなり簡単に書いているので、そこはご了承ください。

# csv -> アップロードされたCSV

# 例
"""
name, subject, value
yamada, math, 90
watanabe, english, 85
satou, science, 80
...
"""

csv.each do |record|
# Model -> DBに登録したいモデル
model = Model.new(
name: record[0],
subject: record[1],
value: record[2]
)
model.save
end


パフォーマンスチューニング以前のコードでだめなところ

上記のコードだと、

1件1件登録しており、パフォーマンスの低下に大きく影響を及ぼしています。

なぜ、1件1件登録することがだめかというと、

上記のようなコードを書くと、以下のようなクエリが発行されます。

INSERT INTO Model (name, subject, value) VALUES ('yamada', 'math', 90);

INSERT INTO Model (name, subject, value) VALUES ('watanabe', 'english', 85 );
INSERT INTO Model (name, subject, value) VALUES ('satou', 'science', 80);
...

このクエリが、CSVの行だけ発行されます。

つまり、3万行のCSVなら、3万個のSQL文が発行され、実行されます。

今、考えると、とんでもないぐらい遅そうな処理です。


パフォーマンス改善方法

では、どのように改善するのか。

それは、データを一括で登録できるようにすることです。

データを一括で登録することを、バルクインサートと言います。

(データを一括で更新することを、バルクアップデートと言います。)

先程のクエリを以下のようにすることができれば、1回のクエリでデータの登録ができます。


INSERT INTO Model (name, subject, value) VALUES ('yamada', 'math', 90), ('watanabe', 'english', 85 ), ('satou', 'science', 80), ... ;


Railsにおけるバルクインサート(アップデート)

では、Railsにてバルクインサートできるようにしていきます。


activerecord-importをインストール

まず、gemでactiverecord-importをインストールします。

$ vim Gemfile

# Gemfile
# 以下を追加
gem 'activerecord-import'

$ bundle install


バルクインサートを実装

先程のCSVの取り込みのコードをバルクインサートにすると以下のようになります。

# new_models は配列となります

new_models = []
# CSVの行数だけ、Model の オブジェクト が new_models に挿入されていきます。
csv.each {|record|
# Model -> DBに登録したいモデル
new_models << Model.new(
name: record[0],
subject: record[1],
value: record[2]
)
}

# 後は、オブジェクトが挿入された配列をバルクインサートするだけ
Model.import new_models

上記のように書くだけで、バルクインサートが実現することできます。


バルクアップデートの場合

バルクアップデートを実現するには、

on_duplicate_key_updateをオプションを指定することができます。

on_duplicate_key_updateを指定することで、重複したレコードの場合は、

指定したカラムの値を更新します。

# すでに存在するレコードの場合、:value の値を更新します。

Model.import new_models, on_duplicate_key_update: [:value]

上記のように書くだけで、バルクアップデートが実現することができます。



それでも処理が重いと感じたとき

レコード数が多い場合、1つの配列に大量のオブジェクトが挿入され、

処理が重くなる場合があると思うので、

何件かに分割して挿入するのがおすすめです。


バルクインサートにおけるvalidation

validationがデフォルトでtrueの状態になっています。

オフにしたい場合は、以下のようにします。

Model.import new_models, validation: false

また、validationがtrueのときに、

レコードの登録に失敗したデータを以下のように取得することができます。

result = Model.import new_models, validation: true

if result.failed_instances.present?
logger.warn result.failed_instances
end


mysqlのmax_allowed_packetの確認

バルクインサートを行うと、1つのクエリが大きくなるため、mysql側でerrorが生じることがあります。


  • max_allowed_packet 変更方法

$ mysql --help | grep my.cnf

order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf
# これらのファイルが存在する場合はそのファイルを変更。
# ない場合は、このパスのどれかに作成。

$ vim ~/.my.cnf
# .my.cnf
# 以下のように記述
[mysqld]
max_allowed_packet=16MB

# mysqlを再起動
$ mysql.server restart


  • max_allowed_packet 確認方法


SHOW VARIABLES like 'max_allowed_packet';


まとめ

今回は、Railsにおけるバルクインサートとバルクアップデートによるパフォーマンス改善を紹介しました。

バルクインサートとバルクアップデートを取り入れることで、

6時間かかっていた処理を、30秒ほどにすることができました。

ぜひ、つかってみてください!!


たくさんのコメントお待ちしてます:sunny::sunny::sunny:


株式会社Nexceed にて、一緒に働いてくれる仲間を募集中です:point_down::point_down::point_down: