この記事は MySQL Casual Advent Calendar 2013 の 17 日目です。
前日は @Yuryu さんの MySQLにMHA を導入してハマったところでした。

今日は、私が CIEN Pattern (シャンパターン) と名付けた SQL のイディオムをご紹介します。
これは複雑な集計をカジュアルに行うためのもので、私は日常的に使っています。

イディオムの紹介とか言いつつ、大半は前に社内で書いた便利ツール (オープンソースでない) の紹介だったりします。

本日ご紹介するイディオム

ここでいう「複雑な」というのは、例えば「掲示板に書き込んでいるユーザのうち 20 台男性のユニークユーザ数・30 台女性のユニークユーザ数・全体のユニークユーザ数・全体の投稿数を SQL 一発で全部集計したい」みたいなヤツです。
「サブクエリ 7 重にもネストしている上相関サブクエリだ」とか「なんかテーブルを 30 個ぐらい JOIN している」とかそういうのではありませんが、そういう地獄の SQL にも応用は可能です。

SELECT
  COUNT(1) AS `全体の投稿数`,
  COUNT(DISTINCT p.user_id) AS `全体の UU`,
  COUNT(DISTINCT IF(u.age BETWEEN 20 AND 29 AND u.sex = 'm', u.id, NULL)) AS `20 台男性の UU`,
  COUNT(DISTINCT IF(u.age BETWEEN 30 AND 39 AND u.sex = 'f', u.id, NULL)) AS `30 台女性の UU`
FROM
  bbs_posts AS p
LEFT JOIN
  users AS u
ON
  p.user_id = u.id
;
+--------------+-----------+----------------+----------------+
| 全体の投稿数 | 全体の UU | 20 台男性の UU | 30 台女性の UU |
+--------------+-----------+----------------+----------------+
|        12220 |      1032 |             83 |            211 |
+--------------+-----------+----------------+----------------+
1 row in set

こんな感じの結果が得られます。

ここで注目して欲しいのは SELECT 対象のフィールドです。
最初のふたつはどこのご家庭でも使われているであろうごく普通の COUNT(expression) や COUNT(DISTINCT expiression) です。
今回ご紹介するイディオムで特徴的なのは、COUNT(DISTINCT expression) の expression 中に、何やら複雑な IF() を突っ込んでいるところです。

ちなみに GROUP BY を使った場合も同様のイディオムが使えます。

仕組みとしての詳細は後で紹介するとして、こういったクエリが必要になった背景について紹介します。

背景

そんな MECE でない集計にどんな意味が???みたいなツッコミ等あると思いますが、現実は複雑なものです。
耐えるしかない。

なんか「あれとあれのデータ集計出してもらえる?来週いっぱいまで。毎日」みたいなリクエストに対していちいちクエリ叩いて Excel を更新するには人間の一生はあまりに短い。
かといっていちいち専用のツール実装にかまけてられるほど人生暇でもない。

ソリューション、SQL Executor

というわけで作ったのが「あらかじめ SQL を登録しておくことでいつでもその結果をカジュアルに確認できておまけに結果を保存しておける」みたいなツールです。
仮に SQL Executor と呼称することにします。

予め言っておくと、再利用可能な実装になってないので今のところ公開してませんが、大したものでもないのでオープンな形で公開できればと思ってますし、あわよくば誰か作ってくれないかと強く願っています。
まぁとても簡単なツールなので、新卒エンジニアにでも作らせると良いのではないでしょうか。一晩で。

もうちょっと詳しく機能を紹介すると

  • 開発者ユーザは SQL を登録できる
  • ユーザ (非開発者含む) は登録された SQL をいつでも実行できる
  • 実行結果は実行時間とともに保存することができる (JSON で保存)
  • 実行結果は様々なフォーマットで出力可能 (HTML テーブル・テキストテーブル・TSV・morris.js による時系列グラフ)
  • すべてのクエリと全ての実行結果は固有のパーマリンクを持つ

というものです。
多分 phpMyAdmin なんかにもそういう機能はありそうですが、如何せん何でもできてしまうところが最大のネックです。
僕はおっかなくて業務内で利用したことはありません。
その点を、このツールでは接続先を、集計専用に用意したスレーブに限定することで、うっかり UPDATE とかでデータが壊されないようになっています。

出力フォーマットは必要に応じてたまに増やしてます。
morris.js で時系列グラフを出力するやつがお気に入りです。
一番目の列を時刻として、以降の列を全て値というルールで、SELECT クエリひとつ書けばすきなメトリクスの時系列グラフがすぐに得られます。

あとは TSV 出力もお気に入りです。
とりあえず SELECT さえ書けば「あとは EXCEL にでもコピペして適当にこねくり回しておいて」という具合に丸投げできます。

何よりもお気に入りなのはパーマリンク機能です。
URL さえ渡せばみんなでクエリや集計データを共有できるので、メールやチャットでカジュアルにやり取りできます。
データを収集するツールにはパーマリンクが欠かせない、ということ事実に Cacti はいい加減気付いて欲しいところです。

SQL Executor を作って見て

この SQL Executor を作って見てなんですが、日々のストレスのうちごく一部ではありますが、確実に減りました。
SQL 叩いて Excel に貼りつけてメールして、みたいな業務をかなり減らすことができました。

もちろんこれまでも何でもかんでも手動で出していたわけではなく、よく使われるものは何かしらのツールを実装して自動化していました。
ですが、例えば数日間しか必要無いもののような、専用ツール化するほどでもないものについて、実装の手間をかける必要が無くなりました。

また、今時のフルスタックでイケてるエンジニアの皆様は Fluentd で収集したログを MongoDB なり ElasticSearch につっこんで Kibana で眺めてはニヤニヤしていることかと思われますが、そういった素敵なツールに組み込む前の段階のプロトタイピングとしても、なかなかの威力を発揮します。
とりあえず SELECT 文をこねくりまわして非エンジニアの人にも見せつつ、本当に必要なデータがわかってきたところで、上記のような素敵ツールに組み込んでいくと便利だと思います。

Treasure Data はそういった用途にも対応できる機能が揃っていると思いますが、如何せんそんなに安いものではない (個人の感想です) ですし、データ規模によっては Hadoop/Hive に頼るのは大袈裟だったりすると思うので、適材適所使い分けていければ良いんじゃないかと思います。

あとよかったのは、今までそれぞれの手元で眠りがちだった SQL が共有されるようになったことです。
これまでも Wiki に便利 SQL 集みたいな形で共有していたりしましたが、その場で実行できると凄く楽なので、情報共有が加速したんじゃないかと思います。

まぁそんなこんなで「とりあえず何でも SQL 一発でゴチャゴチャと集計しちゃおうぜ!」みたいな文化が醸成され、このイディオムが発見されるに至りました。
とは言え、なんでもかんでもゴチャ混ぜにした集計は神 EXCEL 問題と同様のアレを孕むものなので、使いどころは限定するのが良いんでしょうが。

というわけでようやくイディオムの解説に戻ります。

COUNT() とは何だったのか

まず COUNT(expression) や COUNT(DISTINCT expression) について振りかえる必要があります。
なお、COUNT(DISTINCT expression, [expression...]) という具合に、集計対象の expression は複数指定することもできますが、ここでは単純化のために考えないことにします。

COUNT(expression) はどういう関数だったかというと、「expression が NULL でない行の数を数える」みたいな感じだと思います。
つまり user_id が 1, 2, 2, NULL となっている場合、COUNT(user_id) の結果は 3 となります。
実装を読んだわけじゃないしマニュアルも意外とそこまで詳しく書いてないのですが、手元でいろいろ試した結果そういう感じっぽかったので、そういうものとして話を進めます。

次に COUNT(DISTINCT expression) はというと、expression のユニーク数を数えます。もちろん NULL は除外します。
user_id が 1, 2, 2, NULL となっている場合、COUNT(DISTINCT user_id) の結果は 2 となります。

IF() の結果を COUNT() する

これで「COUNT(expression) は expression が NULL でない行の数を数える」ということがわかりました。
つまり、集計対象から除外したい行は NULL にすれば良いのです。

というわけで、20 台の男性だけを集計したいときはこうです。

IF(DISTINCT u.age BETWEEN 20 AND 29 AND u.sex = 'm', u.id, NULL)

こうすることで 年齢が 20 ~ 29 でかつ性別が m の行以外は NULL として除外されるようになります。
また、u.id による DISTINCT な COUNT なので、該当ユーザのユニーク数が集計されるというわけです。

多分このイディオムを使っている方は少なからずいると思うんですが、如何せん呼び名が無いので、ノウハウとして共有されていない気がしています。名前重要。
COUNT IF EXPRESSION NULL を略して CIEN Pattern (シャンパターン) とか呼ぶと格好が良いのではないでしょうか。
並び変えたら NICE になりますし、CIEN パターンで NICE かつカジュアルな集計!とかどうでしょうか。
だめでしょうか。

まとめ

カジュアルにクエリ叩ける Web ベースのツールあると便利なのでオススメという話でした (イディオムはオマケ)。

明日は @RKajiyama さんです。

, , ,

昨日の 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 分半もかかってしまいました。

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

同時インサート数 処理時間
1000 44.256sec
500 10.800sec
200 9.478sec
120 8.091sec
100 7.242sec
80 6.721sec
50 5.508sec
40 6.267sec
30 6.956sec
10 10.709sec
1 1m30.200sec

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

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

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

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

Bulky を使ってみる

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

まとめ

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

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

最近は非同期処理に興味があります。

Reactor パターンというのは、非同期処理の実装パターンのひとつで、例えば HTTP 問い合わせなんかで「読み込みが完了するのを監視しつつその他のタスクを進め、読み込みが完了したタイミングでその結果を処理する」みたいなヤツです。
例えば Node.js では Reactor パターンを使ったプログラミングが比較的簡単に行えます。

PHP でも同じことをやりたい、というときには React というフレームワークがあって、少し前に話題になりました。

PHPでもリアルタイムWeb。node.php「React」

現状 React ではファイル I/O やソケット通信を非同期に行えるようになっております。
が、MySQL で非同期処理を行う方法は今の所提供されていません。

PHP でも mysqli_poll を使えば非同期に MySQL にクエリを投げられるということを知りました。
以下の記事が詳しいです。

PHPの非同期クエリで並行処理をやってみる

ただマニュアルのままの使い方だとさすがに辛いだろうと思ったので、コールバックを使って書きやすくするためのライブラリを書いてみました。

yuya-takeyama/async_mysql

これを使うと、例えばこんな感じに書けるようになります。

このように、Node.js っぽいインターフェイスで非同期問い合わせができます。

ローカルで試したい場合は以下のようにしてみてください。

localhost に root ユーザがパスワード無しでいることを前提にしているので、そうでない場合は以下のようにしてみてください。

$conn = $loop->connect(‘HOST’, ‘USER’, ‘PASSWORD’);

この例では数秒 SLEEP() するだけのクエリをいくつか投げており、直列に実行した場合は SLEEP() の合計時間が全体の実行時間となってしまうところを、並列に実行することで、ほぼ同時に全てのクエリが発行され、一番長いクエリの分だけの実行時間で済みます。

ただし、実行するクエリそれぞれ別々にコネクションが確率されてしまうため、実際にちゃんと使うにはキューの仕組みが必要だと思われます。
例えば最大で 16 並列までしかクエリを投げない、みたいなことができないと、KEN_ALL.csv の全件 INSERT を試みるだけで too many connections になってしまうことでしょう。 (まだ試してないですが)

ただ、これの開発を継続して行うつもりはなくて、あくまでも実装の例として作っています。
最終的には React に組み込めたらいいなー、なんて思っているんですが、React がイベントループに使用している stream_select() は MySQL では使えないため、どうしたもんかというところです。
(libevent を使った実装もあるけど、そもそも libevent に対する理解が足りていないのでどうすべきかイメージが湧いてない)

とりあえずは Ruby の EventMachine や Perl の AnyEvent あたりのコードを読んでみようと思います。

ところで話は変わるのですが、React 上で子プロセスの実行を非同期に行うものを書いていて、React の中の人たちにレビューをいただきつつ開発を進めています。

Pull Request #61: WIP: ChildProcess by yuya-takeyama · react-php/react

今は API が固まってきたので、ユニットテストを書いているところです。
(こういう自分の中でチャレンジングなものを作るときは API がコロコロ変わって、テストファーストが全然うまくいかないので、とりあえずガーッと作ってその後にテストで固めて行く、というやり方で作ることが多いです)

これについてはマージされたら改めて紹介しようと思います。

, , ,