Born Too Late

PHP/MySQL でレコードを N 件ずつバルクインサート

昨日の ParallelHttp の話に引き続き、PHP でのバッチ処理のパフォーマンス改善の話です。
あと、昨日と同じく PHP 5.2 で使えるライブラリを開発した話でもあります。

バルクインサートとは

ひとことで言えば「複数のレコードをまとめてインサートすること」です。

例えば MySQL で言えば、

INSERT INTO `users` (`name`) VALUES ('foo');
INSERT INTO `users` (`name`) VALUES ('bar');
INSERT INTO `users` (`name`) VALUES ('baz');

ではなく

INSERT INTO `users` (`name`) VALUES ('foo'), ('bar'), ('baz');

というようにやることです。

これで何が嬉しいかというと、サーバにクエリを投げてその結果を受け取って、という往復が少なくて済むので、その分効率よくたくさんのレコードを INSERT でき、実行時間が節約できます。

このように手書きで SQL を書く分には何てこと無いですが、プログラムの中でやるとなると微妙に面倒だったりします。

バルクインサート専用ライブラリ Bulky

というわけで作りました。

yuya-takeyama/bulky

Bulky は DB へのバルクインサートだけを行うためのライブラリです。
SELECT や UPDATE といったことは一切できません。

Perl とかだとバルクインサートのできるライブラリがいくつか見つかるのですが、PHP だと今のところ見つけられてません。
皆さんどうやっているんでしょうね。

Bulky を使うと、以下のように一見バルクインサートを思わせないコードで、バルクインサートを実行することができます。
この例では、10 万件のレコードを 50 件ずつバルクインサートしています。

Bulky によるバルクインサートの仕組み

$queue->insert() メソッドを読んだ瞬間は実際の INSERT は実行されません。
とりあえずはキューに溜めて、設定した数 (ここでは 50) に達したタイミングで自動的に $queue->flush() メソッドが呼ばれて 50 件分のバルクインサートが実行されます。

50 件に達しなかった場合も、$queue->flush() を明示的に呼べばキューに溜まっている分だけのレコードをバルクインサートします。
仮に明示的に呼ばなかったとしても、デストラクタで $queue->flush() を実行するようになっているので、暗黙的に全てのレコードが INSERT されるようになっています。

Bulky でのエラーハンドリング

上記の例では、$queue->insert() 呼び出し時にエラーハンドリングを行っていません。
何故かというと、そもそもできないからです。
$queue->insert() は特に値を返しません。
(引数のレコードのカラム数が一致しなければ例外は投げます)

INSERT が成功したかどうかは実際にバルクインサートが実行されるまでわかりません。
なので、予めエラーハンドラをコールバックとしてセットしておき、INSERT に失敗したときにはそれが呼び出されるようになっています。

バルクインサートでは 1 つでも失敗すると、そのクエリ中の全てのレコードが失敗になります。
この例でいうと、1 度の失敗は同時に 50 レコード分の INSERT に失敗したことになります。

エラーハンドラではその 50 レコード全てを配列で引数に受けるので、どのレコードの INSERT に失敗したのかをログに残すなりアラートを飛ばすなりできるようになっています。

MySQL 以外への対応について

この記事のタイトルには MySQL としていますが、Bulky の設計上は特に MySQL には依存していません。

DB 操作それ自体には様々なライブラリを使用できるよう、GoF パターンでいうところの Adapter Pattern を用いて実装しています。
現在のところは PdoMysqlAdapter というアダプタだけが実装されていて、これを使えばデータベース操作は PDO によって行われます。

PDO は様々なデータベースの差異を吸収してくれる組込みライブラリですが、敢えてここで MySQL としているのは、僕が他の DBMS でのバルクインサートをやったことが無いからです。
DBMS によっては同じ形式のクエリでは動かないかもしれないし、動くかもしれない、ということでとりあえずは自分に必要な MySQL 以外については放置しています。

簡単なベンチマーク

Bulky を使うことでどれだけパフォーマンスに違いが見られるか、ということで簡単なベンチマークを行いました。

使用したサーバは仕事で使っている開発用サーバです。
開発用サーバとはいえ、そのサーバ内ではいろんなものが動いていて、無風状態とは言えない適当なベンチマークなので、その辺りはご了承ください。
マシンの詳細なスペックも省略します。

また、適当なので time コマンドを使って、PHP スクリプトの実行時間を計測しています。
スクリプトの実行前には予め対象テーブルを TRUNCATE しているので、一応は平等な条件のもとで計測しています、と言えるんでしょうか。

まずは通常の INSERT の計測です。
何てことは無い、PDO でベタ書きの INSERT 文を実行するだけのものです。

結果は 1m25.381s、約 1 分半もかかってしまいました。

以下は、最初に掲載したサンプルコードを元に、バルクインサートの単位だけ調節しつつ計測したものです。

この環境では同時インサート数 50 前後でのバルクインサートが一番処理時間が短く、それより多くても少なくてもより多くの処理時間がかかってしまうことがわかります。

予想としては 500 や 1000 ぐらいが一番効率いいんじゃないか、とか思っていたんですが、意外と少ない方がいいようです。

詳しい検証は全くできていませんが、同時インサート数が多いときは何がネックになっているんでしょう?
クエリの組み立てロジックが適当なので、そこら辺の文字列操作がネックになっているのかもしれません。
または、MySQL サーバ側でも、あまりクエリが大き過ぎるとかえって非効率になったりするんでしょうか?

ともかく、今回の場合は同時インサート 50 のときのパフォーマンスで充分だったので、とりあえずはこのまま実運用に投入する方向で検討しています。

Bulky を使ってみる

ParallelHttp と同様、Packagist に登録してあるので、Composer を利用してインストールできます。
Composer をよく知らないという方はこのあたりの資料をご覧ください。

まとめ

MySQL による大量の INSERT を高速化する方法と、それを手軽に実現するライブラリ Bulky について紹介しました。

仕事でコードを書く機会が減りつつある今日この頃ですが、休日にこういう便利で着実に効率の上がるライブラリを開発しつつ、日々の仕事にフィードバックしていくのが 20 台後半に差し掛かった自分なりの生存戦略かなぁと考えています。
あと、今回は要件が PHP 5.2 だったのでニッチな感じになってしまいましたが、チャンスがあれば割とイケイケな OSS 方面にももっと貢献していきたいと考えているので、皆様今後ともよろしくお願い致します。
(React の件いろいろ放置してて本当に申し訳ございません...)