mysql5.1以降のpreparedステートメントのfree

[satomif]

bear ver） bear version 0.9.15.

現象） バッチ処理にて、あるテーブルにデータがあるかどうかselect -> あれば update, なかったらinsertという処理中、SQLが　MDB2 Error: unknown error でこける。

原因） mysql5.1以降で導入された、max_prepared_stmt_count のデフォルト値が16382 であるため、prepareステートメントをfreeせずに16382 回以上連続して使用することができなくなった。

対策） BEAR/Query.php の、selectファンクション及び_selectRowファンクション内にて使用しているprepareステートメントの後に$sth->free();を呼ぶことで、問題が起きなくなることを確認しておりますが、なんらかの方法で$sthオブジェクトを取得できるようにしていただいてもいいかと思います。

ひとまずは、BEAR/Query.phpは修正せずに、バッチ内で10000回のクエリごとに、disconnectするように修正しました。

[koriym]

報告と検証ありがとうございます！

これはユーザー側でmax_prepared_stmt_countの値を変える事で解決はできないのでしょうか？ （この機能について理解が十分でないのですが、付加された機能をフレームワーク側で切ってもいいのかと思いまして。）

[satomif]

返答ありがとうございます。

確かに、フレームワーク側でfreeが必ず実行されるというのは、負荷を気にするようなシステムでは良策ではないですね。ただ、普通に使用している位のユーザーが、そういったDBの環境に関わる点まで意識しているかといえば、そうではない場合が多く、大多数のライトユーザにはfreeを勝手にしてくれるほうが使い勝手がいいのでは、とも思えます。

蛇足ですが、max_prepared_stmt_countについて。。 ご存知とは思いますが、prepasedステートメントの実行結果はセッション毎にmysql側でキャッシュしており、使いまわされるようです。そして、max_prepared_stmt_countですが、freeすると初期化されることから、このキャッシュ値に影響すると思われます。(マニュアルによると、本来は、DoS攻撃対策のための値ということですが、一度disconnectして再度connectすればいいのであまり意味があるとは思えません。)max_prepared_stmt_count=0に設定することで回避できるようですが、同時にキャッシュもしなくなってしまうことから、この値の変更はDBの負荷に関わってくるのと、レンタルサーバ等共有で使用している環境で使用することを想定すると、なんらかの方法でfreeができるほうがよさそうです。

そうだとすると、 デフォルトの動作は勝手にfreeされる ymlで設定して勝手にfreeできなくもできる（その場合は$sthを取得できるようにして、自分でfreeするか、free用のファンクションを呼べるようにする） がいいのかなと思いました。

いかがでしょうか。

[koriym]

説明のおかげで状況がよく分かってきました。ありがとうございます。

勝手にフリーされるといいうのはデフォルトで例えば10000という風に回数を持っていてその回数毎にフリーするということですよね。その方法が良さそうです。 (ユーザー手動のフリーは現状でもgetConfig()でdbというキーのMD2オブジェクトを取得する事でも出来ないでしょうか。)

[satomif]

ありがとうございます。 ある回数ごとにフリーする、というのは良いですね！とても使いやすいです。

ユーザーの手動フリーについてですが、Roクラス内でgetDb()して取得したBEAR_Mdb2を使ってfreeができないか試してみたのですが、dbオブジェクトにはprepareステートメント実行結果の含まれるMDB2_Statement_mysqlオブジェクトが入っていないようでした。

[koriym]

再考してみたのですがやはり、既存のものに変更を加える（ライブラリ側で加えられた実装をFWで切ってしまう）のはやはりあまりいいアイデアではないように感じられて来ました。

対策としてやはり1)mysqlの設定値を変える 2)ユーザー都合で1)をしたくないときはユーザーのライブラリで「ある回数ごとにフリーする」処理を行い対処する。（onInject()でBEAR_Queryに依存するのではなく修正したApp_Query等に依存するようにします。）

のどちらかが良いと思います。（※コメント修正しました）

[satomif]

なるほど。2の方法で、このような感じでやってみまして、動作確認できましたのでご報告します。

■やりたいこと ・ある一定回数ごとにprepareステートメントをfreeする ・回数はapp.ymlに設定しておく(なければ毎回free)

■修正点1 App_Queryクラスを作成、prepareの実行カウント用メンバー変数を用意 　(prepare実行後のオブジェクト内に回数がわかる要素がなかったので。) class App_Query extends BEAR_Query protected $_counter = 0;

■修正点2 App_QueryクラスにBear_Queryのselectファンクションをコピー、prepreステートメント後ろに以下処理を追加 $prepare_free = (isset($this->_config['prepare_free'])) ? $this->_config['prepare_free'] : 1; $sth = $db->prepare($query); // prepareステートメントを適宜freeしないとエラーになるので $this->_counter++; if($this->_counter > ($prepare_free-1)) { $sth->free(); }

■修正点3 App_RoのonInjectでyml内の設定値をセット $this->_queryConfig['prepare_free'] = $app['App_Db']['prepare_free'];

■修正点4 App_Queryオブジェクトの生成 ループ内で使用しているApp_Ro_xxx(App_Roをextend)のonInjectで $this->_query = BEAR::dependency('App_Query', $this->_queryConfig);

dependencyでカウンター数を保持します。但しdbオブジェクトがslaveに向いている場合があるため、更新系のファンクションは同じクラス内で実装してはいけなくなります。更新系がある場合は、別クラスにしてBEAR::factoryで呼び出します。

[koriym]

旅行中で返事遅れてすいません。

良いものを作成されたようで、本体にも取り込みたいと思いますがいかがでしょうか。 BEAR_Query_Freeというクラス名にしてPull Request頂けますでしょうか？

[satomif]

スミマセン、私のコメントに誤りがあって検証していたため返事が遅くなりました。

まず、誤っていた点ですが、prepareステートメントの回数は、freeをしても初期化されない（=freeをしていないprepareステートメントの累計がmax_prepared_stmt_countを超えるとエラー）ことがわかり、

1.毎回freeする 2.（同じSQLをループ内で使用する場合）prepareステートメント実行結果を使い回す のいずれかの方法で対応する必要が出てきました。

そのため、バッチ内ではprepareステートメントの実行結果を使い回すことにしましたのでご報告いたします。 そのために用意したApp_Queryの修正点は以下の通りです。

■パターン１の場合 ・selectファンクション内、prepareののちにfree()を呼ぶように修正、この場合はBear::factoryで呼び出してもOK。

■パターン２の場合 ・新しくselectSharePrepareというファンクションを作成し、クラス内protected変数にprepare結果を保存しておくよう修正、この場合はBear::dependencyでApp_Queryを呼び出す必要がある。 使い回した場合でも、prepareステートメントの実行回数がmax_prepared_stmt_countを超えるとエラーになるので、使い終わったらfreeをできるようにファンクションの引数にフラグを追加する。

という作りにしました。

BEAR_Query_Freeという名前でリクエストしておきますので、修正点あれば修正頂き、取り込み頂ければと思います。

[koriym]

0.9.16としてパッケージも作成しました http://pear.bear-project.net/