2014年02月18日

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 入れてくれるとおもいます。

Posted by Yappo at 18:59 | Comments (0) | TrackBack

2014年02月17日

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 側のクエリが終わるまでの間を自由に使えるってこと

Posted by Yappo at 23:03 | Comments (0) | TrackBack

2014年02月12日

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% --

Posted by Yappo at 20:33 | Comments (0) | TrackBack