【Rails】activerecord-importによる大量データ登録時のパフォーマンス検証

作成日 2019-08-11更新日 2019-08-11

はじめに

RailsにBULK INSERT(公式)の機能が追加されるみたいです。

BULK INSERTは私も仕事で大量データを処理する際に使用していて、パフォーマンスの向上に役立ちます。

ですが、Railsには現状その機能がなかったので、activerecord-importというgemを使用してます。

RailsにもBULK INSERTが追加されるのであれば将来的には使用しなくなる？かもしれませんが、あらためてBULK INSERTのメリットを確認するために、１件毎に処理するのとどれくらい違うのか、 activerecord-importの基本的な使い方も含めて、書いておこうと思います。

実行環境

ruby: 2.5.5
rails: 5.2.3

インストール

Gemfileに以下のGemを追加します。

gem 'activerecord-import'

データのインポートを行う

gemをインストールするとimport(import!)メソッドが使用できるようになるので、それに対して、更新対象のオブジェクトの配列を指定します。

users = [
  User.new(name: "test0", address: "tokyo", email: "test@example.com"),
  User.new(name: "test1", address: "tokyo", email: "test@example.com"),
  User.new(name: "test2", address: "tokyo", email: "test@example.com")
]
User.import(users)

ログを見ると、下記のように1つのINSERT分にまとまっているのが分かります。

INSERT INTO `users` (`id`,`name`,`address`,`email`,`created_at`,`updated_at`) VALUES (NULL,'test0','tokyo','test@example.com','2019-08-04 17:00:17','2019-08-04 17:00:17'),(NULL,'test1','tokyo','test@example.com','2019-08-04 17:00:17','2019-08-04 17:00:17'),(NULL,'test2','tokyo','test@example.com','2019-08-04 17:00:17','2019-08-04 17:00:17')

また、importメソッドの戻り値は以下の４つになっています。

failed_instances
num_inserts
ids
results

failed_instancesはimportに失敗した情報が設定されます。

試しに、Userモデルのname属性に必須のvalidateを追加して、 import時に名前が空白のデータを登録しようとすると下記のようにfailed_instancesに対象の情報が設定されます。

result = User.import(users)
p result.failed_instances

# 出力
# [#<User id: nil, name: "", address: "tokyo", email: "test0@example.com", created_at: nil, updated_at: nil>, #<User id: nil, name: "", address: "tokyo", email: "test1@example.com", created_at: nil, updated_at: nil>, #<User id: nil, name: "", address: "tokyo", email: "test2@example.com", created_at: nil, updated_at: nil>]

num_insertsは発行されたSQLの個数が設定されます。

３件のデータを登録する前提で、batch_sizeオプションを変更し、戻り値の変化を確認してみます。

※batch_sizeは、importする際に1回で登録するデータ数を設定するオプションです。

デフォルトは、登録するデータ件数になります。

まず、batch_size指定なし。（１回のSQLでデータ数分のINSERTを行う）

result = User.import(users)
p result.num_inserts #=> 1が表示される

#ログでSQLを確認
INSERT INTO `users` (`id`,`name`,`address`,`email`,`created_at`,`updated_at`) VALUES (NULL,'test0','tokyo','test0@example.com','2019-08-10 12:18:18','2019-08-10 12:18:18'),(NULL,'test1','tokyo','test1@example.com','2019-08-10 12:18:18','2019-08-10 12:18:18'),(NULL,'test2','tokyo','test2@example.com','2019-08-10 12:18:18','2019-08-10 12:18:18')

INSERTが文が１回しか発行されていないので、num_insertsは１となります。

次に、batch_sizeに２を指定します。（1回のSQLで2件登録するので、SQLの発行は2回になる）

result = User.import(users, batch_size: 2)
p result.num_inserts #=> 2が表示される

#ログでSQLを確認
INSERT INTO `users` (`id`,`name`,`address`,`email`,`created_at`,`updated_at`) VALUES (NULL,'test0','tokyo','test0@example.com','2019-08-10 12:20:47','2019-08-10 12:20:47'),(NULL,'test1','tokyo','test1@example.com','2019-08-10 12:20:47','2019-08-10 12:20:47')
INSERT INTO `users` (`id`,`name`,`address`,`email`,`created_at`,`updated_at`) VALUES (NULL,'test2','tokyo','test2@example.com','2019-08-10 12:20:47','2019-08-10 12:20:47')

INSERTが文が２回発行されるので、num_insertsは２となります。

最後に、batch_sizeに１を指定します。（1回のSQLで１件登録するので、SQLの発行は３回になる）

result = User.import(users, batch_size: 1)
p result.num_inserts #=> 3が表示される

#ログでSQLを確認
INSERT INTO `users` (`id`,`name`,`address`,`email`,`created_at`,`updated_at`) VALUES (NULL,'test0','tokyo','test0@example.com','2019-08-10 12:30:57','2019-08-10 12:30:57')
INSERT INTO `users` (`id`,`name`,`address`,`email`,`created_at`,`updated_at`) VALUES (NULL,'test1','tokyo','test1@example.com','2019-08-10 12:30:57','2019-08-10 12:30:57')
INSERT INTO `users` (`id`,`name`,`address`,`email`,`created_at`,`updated_at`) VALUES (NULL,'test2','tokyo','test2@example.com','2019-08-10 12:30:57','2019-08-10 12:30:57')

INSERTが文が３回発行されるので、num_insertsは３となります。

ids、resultsはPostgreSQL専用の戻り値で、それ以外のDBだとになります。

idsは登録したデータのidの配列、resultsは更新結果が設定されるようです。

(私はMySQLで検証しているので、未確認です。詳細は公式へ)

upsert(データがあれば更新、なければ登録)を行う

あるテーブルのユニークキーに合致するデータが存在すれば、更新をする。

なければ、登録を行うといった使い方も可能です。

下記のように、on_duplicate_key_updateオプションを使用します。

on_duplicate_key_updateには、更新する項目を配列または、Hashで指定します。

User.import(users, on_duplicate_key_update: [:name, :address, :email])

※ここでは、id列がユニークです。

さっそく、Userテーブルを下記の状態にして検証してみます。

id	name	address	email
1	test0	tokyo	test0@example.com
2	test1	tokyo	test1@example.com
3	test2	tokyo	test2@example.com

更新するデータはこちら

user1 = User.find(1)
user1.attributes = { name: '田中', address: '北海道', email: 'test1@example.com' }
user2 = User.find(2)
user2.attributes = { name: '佐藤', address: '山形', email: 'test2@example.com' }
user3 = User.find(3)
user3.attributes = { name: '鈴木', address: '沖縄', email: 'test3@example.com' }
users = [
      user1,
      user2,
      user3,
      User.new(name: '新規', address: '東京', email: 'test4@example.com')
    ]

id１〜３が更新、４つ目のデータが新規登録される想定です。

実行結果は下記のようになりました。

id	name	address	email
1	田中	北海道	test1@example.com
2	佐藤	山形	test2@example.com
3	鈴木	沖縄	test3@example.com
4	新規	東京	test4@example.com

想定どうりに、upsertができました。

インポート時のエラー情報の取得

failed_instancesのところでも確認しましたが、import時に個々のデータに対してvalidationは実行されています。

なので、エラー情報の取得も下記のように可能です。

result = User.import(users)
p result.failed_instances.first.errors

# 出力
#<ActiveModel::Errors:0x0000561402d6d3e8 @base=#<User id: 2, name: "", address: "山形", email: "test2@example.com", created_at: "2019-08-10 12:30:57", updated_at: "2019-08-10 13:24:46">, @messages={:name=>["can't be blank"]}, @details={:name=>[{:error=>:blank}]}>

大量データ登録時の性能比較

1件毎にINSERT文を発行した場合とactiverecord-importを使用したBULK INSERTを行なった場合でどれくらいの性能差が出るのか検証します。

railsのsaveはデフォルトでトランザクションを張るが、activerecord-importは張らないので、念のため張る場合も検証します。

検証に使用するコードは、以下のものです。

class Tasks::Performance
  # docker-compose exec app bundle exec rails runner Tasks::Performance.execute
  def self.execute
    users = []
    p Time.zone.now.strftime("%H:%M:%S")
    10000.times do | i |
      user = User.new
      user.name = "test#{i}"
      user.address = "tokyo"
      user.email = "test@example.com"
      user.save
    end
    p Time.zone.now.strftime("%H:%M:%S")
  end

　# activerecord-import
  def self.execute2
    users = []
    p Time.zone.now.strftime("%H:%M:%S")
    10000.times do | i |
      user = User.new
      user.name = "test#{i}"
      user.address = "tokyo"
      user.email = "test@example.com"
      users << user
    end
    p User.import(users)
    p Time.zone.now.strftime("%H:%M:%S")
  end

  # activerecord-import(add transaction)
  def self.execute3
    users = []
    p Time.zone.now.strftime("%H:%M:%S")
    User.transaction do
      100000.times do | i |
        user = User.new
        user.name = "test#{i}"
        user.address = "tokyo"
        user.email = "test@example.com"
        users << user
      end
      p User.import(users)
    end
    p Time.zone.now.strftime("%H:%M:%S")
  end
end

検証結果

パターン	10000件登録	100000件登録
1件ずつINSERT文を発行	58秒	10分11秒
activerecord-importを使用	2秒	22秒
activerecord-importを使用(transaction有)	2秒	20秒

activerecord-importを使用する方が圧倒的に早いことが分かります。

ログでSQL単体でどれくらいかかるの確認

#1件毎(一部のデータ抽出)
(0.5ms)[0m  [1m[32mINSERT INTO `users`  # 長いので省略
(2.0ms)[0m  [1m[35mCOMMIT[0m
#1件の処理にコミット含めて2.5msなので
#10000回実行すると => 25s
#100000回だと => 250s

#activerecord-import
#1万件
(233.0ms)[0m  [1m[32mINSERT INTO `users` # 長いので省略
#10万件
(2632.0ms)[0m  [1m[32mINSERT INTO `users`　# 長いので省略

#activerecord-import transaction有り
#1万件
(243.0ms)[0m  [1m[32mINSERT INTO `users`　# 長いので省略
(5.1ms)[0m  [1m[35mCOMMIT
#10万件
(2169.7ms)[0m  [1m[32mINSERT INTO `users`　# 長いので省略
(68.2ms)[0m  [1m[35mCOMMIT

SQLレベルでも、10万件の場合は、250秒と2.6秒で100倍近くの差があることがわかります

まとめ

１件毎にINSERT文を発行する場合は、データ量が増えると目に見えてパフォーマンスが大幅に劣化することが確認できました。

activerecord-importを使用したBULK INSERTの場合では、１件毎の時よりも明らかにパフォーマンスが良いことが分かりました。

大量データを登録する際は、パフォーマンスを考慮しBULK INSERTできないかを検討するようにしたいです。

参考

<<【Rails】find_eachによるメモリの使用量の変化

【MySQL】INSTRを使った部分一致検索>>

はじめに
データのインポートを行う
upsert(データがあれば更新、なければ登録)を行う
インポート時のエラー情報の取得
大量データ登録時の性能比較
まとめ
参考