YappoLogs

re: Text::Xslateで未設定の変数を検出する a.k.a Text::Xslate hash_with_default best practice

むかしごろーがText::Xslateで未設定の変数を検出する - Islands in the byte streamで、未定義のテンプレート変数を使おうとしたら警告だすソリューション作ってたけど、それだと [% IF unknown_var %] とかした時に true 扱いになってしまってひどい目に合うので、いい感じにするソリューション書いてみた。

hash_with_default の callback では undef を返しつつ、存在しない事を目立つ感じでレンダリングするかんじ。

で、これは毎回 controller が hash_with_default するのめんどいので Proj::Web::render を生やして Amon2 で透過的に使えるようにしようとおもった。
そして、 tie hash とかアクセスしまくるとパフォーマンスに悪影響あるから本番で動かさないの鉄則ね。

HASH の中の HASH の key も調べたいなら hash_with_default_walker みたいな関数でやればよさそう。

あと Text::Xslate 3.1.2 は if (exists $self->[0]{$key}) すべきところを if (defined $value) してるので、バージョン上がるのまったほうがいい(pull request 投げたので、次のバージョンで治ってる)

Yokohama.PM #10 でひとつ上のmysql について話してきました #yokohamapm

最近ここで色々調べてた、あまり良く知られてない DBD::mysql の便利機能についてまとめて発表してきました。

http://yappo.github.io/talks/2014-yokohamapm10-dbd_mysql/

このへん知っとくとひと皮むけた感じに成長出来ると思います。僕は結局 mysql_use_result だけたまに使ってます。

発表終わった帰り道にZごろうさんとかにに「避け入ってる場にしては内容がむづかしすぎるよ！」と言われました。

なぜ SQL_CALC_FOUND_ROWS や LIMIT OFFSET のページングが良く無いのか

ここ最近の大規模サービス関連したデータページング考です。

mysql 5.5.34 で試して記事書いてます。 bigdata テーブルは id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) なカラムがある前提です。もちろん InnoDB です。

2014年なんだからCOUNT(*)とかSQL_CALC_FOUND_ROWSとかLIMIT OFFSETのページングはやめようぜ - Togetterまとめが発端にみえるけど、わりと昔から話されてる事なんだけど、「nippondanji SQL_CALC_FOUND_ROWS」でググっても有用な情報ないし文書化されてないからしとく。

SQL_CALC_FOUND_ROWS

ページング処理で使われがちな機能です。
SQL_CALC_FOUND_ROWS と言うのは LIMIT 付きのクエリでも、そのクエリから LIMIT を抜いたら実際何件の結果が取得できるかを計算してくれる MySQL 4.0 から追加された SELECT 文の拡張機能である。
SELECT SQL_CALC_FOUND_ROWS * FROM bigdata WHERE id > 10 LIMIT 10;
のように使うと、次のクエリでSELECT FOUND_ROWS();と打ち込むと LIMIT なしで得られるはずの行数が貰える。

ある程度のデータサイズのサービスを実践で開発していない人だと SQL_CALC_FOUND_ROWS はとても便利に使えます。しかし bigdata テーブルの中身が膨大になってくると徐々に「あれ、、どっかが重い。。。」っていう異変が出てくるんですね。

理由は単純で SQL_CALC_FOUND_ROWS を付けると、内部的には LIMIT 無しの状態で全てのレコードを読み込むまで処理をして、結果を返す時に LIMIT で指定された行数に絞り込んでるだけなんです。内部処理的には LIMIT してないので遅い。
簡単な理由です。

実際手元に1000万行近くかつ2GBくらいのデータの bigdata テーブルがあったのでSELECT * FROM bigdata WHERE id > 10 LIMIT 10;を実行すると即座に結果が帰るのに対してSELECT SQL_CALC_FOUND_ROWS * FROM bigdata WHERE id > 10 LIMIT 10;は30秒くらいかかってました。

「じゃぁ COUNT() 使えばいいじゃん」っていう突っ込みもあるでしょうがSELECT COUNT(*) FROM bigdata WHERE id > 10;も遅いしSELECT COUNT(*) FROM bigdata FORCE INDEX(PRIMARY) WHERE id > 10;も変わらないです。詰んでます。

両方とも、全ての結果行数を得る為には全部読み込まなきゃいけないのでとても遅い。

それでも何とか早く COUNT() する時にはnipondanjiさんの解説を良く読むといい。小さいサイズの index を読むだけで済むようにしたら早く COUNT() 出来るみたいな話である。
結局は結果の行数が多ければ多い程読む場所増えるので遅くなっていくんですけどね。

LIMIT OFFSET

これは、ページング処理で利用されやすいポピュラーな機能ですね。
というか SQL_CALC_FOUND_ROWS/COUNT() と組み合わせて「全件n件あるうちx行からy行まで表示しています」みたいな UI の為だけに使ってる人も多そう。

まぁこれも、マイクロなデータセットのサービスでは機能するでしょう。これが
SELECT * FROM bigdata WHERE id > 10 LIMIT 10 OFFSET 3000000;
とかになってくると事情は変わってきます。

これも理由は簡単ですが、与えられた条件から300万行目を正しく出す為に mysql は律儀に300万行分のデータに到達出来るまで条件判断していってるのです。
「この条件なら id=3000000 までスキップすれば良いじゃん」とか思う人も居るかもしれないけど、途中の行とか消されてたら無理ゲーすよね?

一つの index でクエリが処理出来れば少しは早くなるけど、それでも index の中を OFFSET の所まで読み進めるのでおそい。

(追記)あと、わりと言及が少ないけど URL パラメータに offset を渡す方式だと、レスポンスの HTML を作った後にユーザがリンクをクリックするまでの間にレコードの削除とか追加が行われて順番が変わった時に対応できないよね。ヘタするとさっき見せた行を次のページでももう一度出しちゃうよねってのある。

落とし穴

SQL_CALC_FOUND_ROWS を使う対象のクエリは件数が1000件以内とかの超小規模なので問題が起きないのは自明。
ページング処理で1000件以上のリクエストは受け付けないから LIMIT OFFSET でもほぼ問題が起きない。

などの意思を持って実装するかもしれませんが、自宅用のプログラムで無い限りは避けるべきです。
なぜなら自分以外の誰かがメンテナンスをする事になった時に、その誰かが良くわからないでコピペで新機能を作ってしまう、その新機能がわりとデカイデータを取り扱う仕様だった！
という状況は日常にありふれていて、そういう時に急にアラートが鳴りだしてオフィスから椅子が無くなってしまう。
そんな悲劇が起こりえるので、業務用のコードではなるべくこれらを使わないように行きていくのが処世術です。

ちなみに 他人=未来の自分 でもあるので、自分で自分の首を絞める可能性も大きい。

解決方法

SQL_CALC_FOUND_ROWS の件に関して言うなら、件数を取りたい WHERE 条件が index だけで済むケース等では、カウント用の別テーブル(例えば bigdata_count)を bigdata テーブルへの WHERE と同じ WHERE を処理出来るカラムと count 用のカラムを用意しておき bigdata 側の TRIGGER を仕掛けておいて、 bigdata テーブルに write 処理をするたびに bigdata_count を書き換えておく。そして検索時に bigdata テーブルへの検索と同時に bigdata_count テーブルも検索かければ時間がかかるスキャン等を行わないで、一度の index 探索で全件数取得が可能になる。
これは、検索条件に対する全件数表示がやむなく必要な時によく使う手段です。
(追記)いわゆるサマリーテーブルってやつです。

LIMIT OFFSET に関しては WHERE 条件の中に必ず PRIMARY KEY を含める事が可能になるような index を貼っておいて
SELECT * FROM bigdata WHERE id > ? ORDER BY id LIMIT 10;
のようなクエリを使います。次ページへのパラメータは上記クエリの最後の行の id の値を渡す事で OFFSET の代わりとなり得るのである。
しゅぱっ！と目的の OFFSET まで index で飛んでってさくっと結果を返すのでちょうはやい！

PRIMARY KEY 以外のカラムで WHERE した場合でも安心で、 WHERE 対象にちゃんと index が貼られてれば(常識的に index 貼ってないカラムに WHERE とかしないよね?) PRIMARY KEY 以外の index 定義では暗黙的に PRIMARY KEY が含まれたと同等になるので、KEY (foo)のような index を貼っておいてもWHERE foo=? AND id > ? ORDER BY idというクエリを投げても処理速度を落とさなくても同等の事が可能です。ただ EXPLAIN でちゃんと確認した上で必要なら FOURCE INDEX とかが必要です。
参考文献: 漢(オトコ)のコンピュータ道: 知って得するInnoDBセカンダリインデックス活用術！

暗黙的に主キーの値が含まれるのである。そのため、次のような検索は自ずとCovering Indexとなるため高速である。そして、ソートにもインデックスが利用される。

逆順ソートにしたい場合は普通に DESC とか付ければいい。

(追記の追記)
これじゃ次にページがあるかどうかわからない！っていう人のために、必要数 + 1 の数だけ LIMIT に指定して、結果が全部帰ってきたら最後の行だけ抜いて返して、 "hasNext":true みたいな response を返すといいと思います。
前のページが有るかどうかは UI 工夫してパラメータ引き回すでもいいし、そんなの信用出来ない場所だったら(SELECT * FROM bigdata WHERE id <= ? LIMIT 1 ORDER BY id DESC) UNION (SELECT * FROM bigdata WHERE id > ? LIMIT 10 ORDER BY id)とかして、前のレコードが存在してたら "hasMaenopage":true とか返せばいいですね。 UNION 使ったらクエリは一個なんでそんなにパフォーマンス落ちない。
(/追記の追記)

ちなみに index とかはりきれないような全文検索とか必要なケースだと Groonga を mysql の外部 index として利用してますね。
Groonga の _key に PRIMARY KEY をつっこんでる。え、ぐるんがってすとれーじえんじんだったけ、そうだっけぼくよくわかんない。

まとめ

SQL_CALC_FOUND_ROWS や LIMIT ? OFFSET ? でのページング処理がなぜ遅いのかと、その代替手段の紹介をしてみました。
もちろん「前へ 1 2 3 4 5 6 次へ」みたいなリッチなページングの実装には役に立たないかもしれません、でも果たしてそういったページング処理は本当に必要なんでしょうか?
「なんとなく Google っぽいのにしたいから」みたいな安易な理由じゃないですか?
正しくスケールするサービスを提供するには、企画書段階から UI の妥当性とパフォーマンスにどの程度影響するかを考えて取り組む必要があります。
UI を工夫する事でサーバ側の処理負担を大幅に減らせる機会は沢山あるので積極的にサービス全体を見ていく能力も2014年代のスケールするサービス構築には必要な能力だと思ってます。
それでもやっちゃいけないのが、技術的にむづいから企画をねじ曲げる事ですね。使える手段は常に多く持っておいて、適材適所できる人間になりたいものですね！

ちなみに SELECT * FROM bigdata WHERE id > ? ORDER BY id LIMIT 10; に一番相性がいいのはスマホのリストビューです。理由はきっと誰かがはてなに書く。

という事を「ISUCON3 の本戦行ったけど見事に空中分解して惨敗したよ」と「 CROSS 2014 コードレビューぶつかり稽古に登壇してきた」という報告の代わりとしてこのエントリを役立ててくださいということをもちまして、このエントリを〆させていただきます。

追記

ちなみにこれが具体的にどんなにコストがかかるかってことを僕は9年くらい前から mysql のソースコードレベルで理解していた。

深遠な理由でこれやって大変な事になってる人が「paging できる API とかは hasNext とかそういうのにすべき」って言ってた。サーバ側で totalRows みたいなのは出さないで hasNext/hasHitotumae みたいなのだけ返せば軽い API 設計ができるとかそういうの。

mysql_use_result on DBD::mysql

DBD::mysql において、例えばテーブルの中に大量のレコードが入っているテーブルにクエリを投げる場合は execute で凄いブロックされた経験は誰にでも有るはず。

my $sth = $dbh->prepare('SELECT * FROM okkiinari');

$sth->execute; # ここで okkiinari テーブルのデータを全部読み込んでる

だからみんなは LIMIT OFFSET とか頑張ったりするんですが、これを回避するための mysql_use_result っていうオプションがある。

connect する時に

my $dbh= DBI->connect('DBI:mysql:test;mysql_use_result=1', 'root', '');

と指定したり、途中で

$dbh->{mysql_use_result}=1; 

(これは prepare 前にやる)したり

$sth->{mysql_use_result} = 1

したり

$sth->prepare($sql, { mysql_use_result => 1 })

したときに有効になる。

このオプションを利用すると、通常は mysql_store_result を利用して mysqld からの結果を受け取っていたものを mysql_use_resultするので、 okkiinari テーブルの全データを読み込めるだけのバッファメモリを確保しなくても省メモリで全データを舐める事が可能なのです。

mysql_use_result を使うと $sth->rows で結果行を取ろうとしても0が帰ってきたり、全レコード取得する or $sth->finish するまで、該当レコードが READ LOCK かかるみたいな雰囲気でしたが、 fetch row を1秒おきにするスクリプトを走らせながら裏でレコード更新してもロックされない(当然 fetch 中のスクリプトの内容はクエリ発行前の値が出る)とかなのでよくわかりませんでした。

ちなみに DBD::mysql は async API を使うと mysql_use_result の併用が出来ないので悲しい。
mysql_db_async_result の中で rows を取ってきて、結果があったかどうかで分岐してるんだけど、ざっとコード見た感じ使えない理由がないからよくわからない。

async API を使えても使えなくても mysql_fetch_row するとこの前で select して監視すると、 mysqld のクエリ処理が終わった後のデータ転送を似非非同期的に取り扱う事は可能ではある。
その場合は烏賊みたいなめんどっちい感じ(ちゃんと使うにはもっとだるいことになる)をすると良い。

$sth-->execute;

while (1) {

    while (1) {

        my $ret = IO::Select-->new($dbh-->mysql_fd)-->can_read(0);

        last if $ret;

        ...; # *1 read 出来るまでの間にやっときたいこと

    }

    my $row = $sth-->fetchrow_hashref;

    last unless $row;

    ...  

}

なんで似非非同期的かというと、mysql_fetch_row の中で read_one_row するために read_one_row 経由で cli_safe_read する時に全部のパケットを読み込んでるので(参考)パケットのキリが良い時は延々と cli_safe_read の中でブロックするし MAX_PACKET_LENGTH がでっかかったら、そもそもずっと読んでるし運が良くない限り *1 とかに処理はいらないっていうオチです。そもそも普通の web アプリに於いては1個のパケットの中で結果が戻ってくるケースばっかじゃないでしょうか。

あと mysql_use_result して全件取ってきてもクライアントには優しいですが、サーバ側でどんなことになるかはよく知らないのできっと偉い人が解説してくれて、ブクマコメントに url 入れてくれるとおもいます。

MYSQL_ASYNC

dbd_st_prepare で { async => 1 } のとき
imp_sth->is_async = TRUE;
imp_sth->use_server_side_prepare = FALSE;

mysql_st_internal_execute
dbh の時
async = (bool) (imp_dbh->async_query_in_flight != NULL);
sth の時
async = imp_sth->is_async;
if(async) {
imp_dbh->async_query_in_flight = imp_sth;
} else {
imp_dbh->async_query_in_flight = NULL;
}
非同期で mysql_send_query が成功したら return 0

dbd_st_execute
if(imp_dbh->async_query_in_flight) {
DBIc_ACTIVE_on(imp_sth);
return 0;
}

dbd_st_fetch
if(imp_dbh->async_query_in_flight) {
if(mysql_db_async_result(sth, &imp_sth->result) <= 0) {
return Nullav;
}
}

dbd_st_finish
D_imp_dbh_from_sth;
if(imp_dbh->async_query_in_flight) {
mysql_db_async_result(sth, &imp_sth->result);
}

mysql_db_async_result
impl_sth->result imp_dbh->async_query_in_flight = NULL して、に結果を入れて 結果行数

mysql_db_async_ready
fds.fd = dbh->pmysql->net.fd;
fds.events = POLLIN;

retval = poll(&fds, 1, 0);

do(dbh, statement, attr=Nullsv, ...) で { async => 1 } のとき
use_server_side_prepare = FALSE; /* for now */
imp_dbh->async_query_in_flight = imp_dbh;

imp_dbh->async_query_in_flight が立ってたらだめなの
dbd_db_commit(SV* dbh, imp_dbh_t* imp_dbh)
dbd_db_rollback(SV* dbh, imp_dbh_t* imp_dbh) {
int dbd_st_execute(SV* sth, imp_sth_t* imp_sth)
int dbd_bind_ph(SV *sth, imp_sth_t *imp_sth, SV *param, SV *value,
IV sql_type, SV *attribs, int is_inout, IV maxlen) {
SV *mysql_db_last_insert_id(SV *dbh, imp_dbh_t *imp_dbh,
SV *catalog, SV *schema, SV *table, SV *field, SV *attr)

type_info_all(dbh)
_ListDBs(dbh)
do(dbh, statement, attr=Nullsv, ...)
ping(dbh)
quote(dbh, str, type=NULL)
void _async_check(dbh)

まとめ

• libmysqlclient は関係なくて DBD::mysql 独自
• server side prepared statement は使えない
• execute とかで mysql_send_query したあと mysql_store_result せずに即座に戻って、 fetch とかする関数の最初に mysql_store_result を呼び出す感じであとで読むしてる
• なので mysql_async_result 呼ばないで fetchrow_hashref とかいきなり呼んでも何も支障ないというか mysql_async_result 呼ぶだけコストかかる感じがある
• fetch するか mysql_async_result 呼ぶまではトランザクション終了させたり新規のクエリは投げられない(同じ sth で)
• mysql_async_ready は内部的に poll 呼んでるだけ
• mysql_fd が読み込み可能状態じゃ無くても fetch とか mysql_async_result を急に呼び出しても何も問題ないが、単純にそこでブロックするだけだから async api 使う意味ない
• mysql_db_async_ready は poll(&fds, 1, 0) してるだけで、 readable じゃなければ即戻ってきちゃうのでマジ使う意味ないので mysql_fd を自前で select してハンドリングしたほうがいい。とは言え、重いクエリを裏で投げてて perl 側で思い処理を並列にやるユースケースだと、 perl 側の処理終わったら mysql_async_ready 呼びまくってもいいけど、それって先に mysqld 側で処理終わってる前提なので、その前提外れると busy loop するから結局使わない方がいい
• エラーメッセージが時たま内部の構造体のメンバ変数名だしてきてウケる
• 大事なことですが、 mysqld から result が送られて来てからの respons 受信処理は非同期に出来ない。あくまでも mysqld 側のクエリが終わるまでの間を自由に使えるってこと

DBD::mysql Async API

MySQL の Async API 使って思いクエリを並列処理したら早いかと思ったらそうでも無い風味。
Web アプリの時のように、クライアント側の並列度があがれば差が縮まる感じだけどそうでもない。

ある程度重いクエリの想定で SELECT SLEEP(0.05) とか投げてみたけどやっぱり普通に使った方が早い。
Async API 使うのにコストがかかるのかな、と思って IO::Select 使ってみたらかなり早くなったので AnyEvent がわりとボトルネックっぽい。
とは言え微妙な誤差ではあるので、普通に DBI 使ってればいい気がしてきた。

perl 5.18.2
DBI 1.63
DBD::mysql 4.025
AnyEvent 7.07
IO::Select 1.21

async-mysql-ioselect.pl が全入りベンチ(思考回数少なめ)
async-mysql.pl は IO::Select 使う前に数回ベンチとったやつ。

async-mysql-ioselect.plを10倍ループ回したのは以下のとおりだった。

1 process

                Rate   Async-Serial Async-Parallel         Normal      IO-Select

Async-Serial   559/s             --            -4%           -15%           -17%

Async-Parallel 583/s             4%             --           -11%           -14%

Normal         656/s            17%            12%             --            -3%

IO-Select      676/s            21%            16%             3%             --

8 process

                 Rate Async-Parallel  Async-Serial         Normal      IO-Select

Async-Parallel 17.0/s             --           -0%            -7%           -13%

Async-Serial   17.1/s             0%            --            -7%           -13%

Normal         18.4/s             8%            8%             --            -6%

IO-Select      19.6/s            15%           15%             6%             --