とある案件でSQL Serverからデータを100万件ぐらい引っ張ってきて別のDBにそれをINSERTする必要があった。
普通にActive Recordを使ったら重すぎたのでその時のメモ。
複数のDBに繋ぐ
Azure上で動いているSQL Serverからデータを取ってきて、別のVMで動作しているMySQLにデータを投入する必要があったので複数のDBに接続するよ必要がありました。
これは
ActiveRecord::Base.establish_connection(:local_database)
とか
ActiveRecord::Base.establish_connection(:azure_database)
をSQL発行前に実行すれば解決。
引数はconfig/database.ymlの設定名
データを取ってきてINSERTする
普通にINSERTしていったら重いだろうなぁと思い、railsでBulk Insertする方法を探してみた。
結果、activerecord-importといgemがあったので使ってみた。
配列にActive Recordのオブジェクトを突っ込みまくって、
Model.import array
みたいに実行すればBulk Insertを実行できるよって感じだったのだが、扱うレコードが多いだけにやたらと重い。
数千件ごとに分けてみたり、色々やってみたのだがやっぱり重い。
htopやらsarやらでCPUやディスクの状況など調べてみるとiowaitはほぼ発生していないがCPUが常に100%稼働している。
（ちなみに環境はAzureのA1。CPUはコア数１つ）
100万件のデータをINSERTしようとすると11時間ほどかかりそうだということがわかり、どこがボトルネックか調べることにした。
activerecord-importの中身までは見ていないが、5000件のデータを処理した時に、importメソッドだけで70秒ほどかかっていた。
その間iowaitなどは発生しておらず、CPUの負荷は常に100%で、MySQL側に特にslow logも出ていない。
調べてみるとActive Recordはオブジェクトの生成処理が重いので、大量のselectの時はpluckを使えば早くなるよとかいう記事を見つけた。
selectの部分は確かにpluckにすると早くなったので、おそらくimportメソッドが重いのもActive Recordのオブジェクト生成処理のせいだろうなぁと思い生のINSERT文を生成し、実行するようにした。
affected_rows = ActiveRecord::Base.connection.update(insert_sql)
生のINSERTはこんな感じで実行できる。
結果、5000件あたりの処理速度は140秒から10秒になり、35分ほどで100万件を処理することができるようになりました。
所感
- 大量レコードを処理する時はActive Record使わないほうが良さそう
- 今回書いてないけど、RailsからSQL Serverに接続するのが結構めんどくさかった。 主にfreetdsとかunixobdc周り。