Q&A
すみません。
prepareを毎回行っているわけではありませんでしたね。
失礼しました。
統計は取ってみたらよいのではと思います。
PHPのリファレンスには、プリペアードステートメントについて以下のように記載されています。
プリペアドステートメントを使用すると、この 解析/コンパイル/最適化 の繰り返しを避けることができます。 端的に言うと、プリペアドステートメントは使用するリソースが少なくいため 高速に動作するということです。
http://php.net/manual/ja/pdo.prepared-statements.php
そこで、バルクインサート文 (INSERT INTO test (num) VALUES (1), (2), ...) の実行にかかる時間を
プリペアードステートメントを使用した場合と使用しなかった場合で比較してみたところ、
意外な事に
- 1度に挿入する行数が1〜256行の辺りまでは双方とも同程度のパフォーマンスで、
- 256行を超えた頃から、プリペアードステートメントを使用しない方がパフォーマンスが上がる
という、公式のリファレンスの説明とは逆の結果になりました。
この結果について、何か知見をお持ちの方はいらっしゃいますでしょうか？
青 : プリペアードステートメントを使用しない (emulate prepare = ON)
赤 : プリペアードステートメントを使用する (emulate prepare = OFF)
なお、詳細な検証手順と結果は以下に記載しております。
http://qiita.com/kmotoki/items/7a0df427c8c2217ebbb4
こちらもご一読いただけると幸いです。
検証方法についてのご指摘なども、大歓迎です。
ちなみにstack overflow でも同様の質問があり、こちらでは
「パフォーマンスに差は出ない」
という結論になっていました。
http://stackoverflow.com/questions/14166798/mysql-pdo-prepared-faster-than-query-thats-what-this-simple-test-shows
回答4件
0
公式の記述のほうは、同じSQLを繰り返し使うならプリペアドステートメントが有利と言うものだと思います。
SQLはスクリプトが渡されるとオプティマイザがそれを解釈して最適な実行モジュールを作成します。
作成された実行モジュールはキャッシュに一定時間残ります。
従って、同じSQLを短期間に多数回使うなら(コンパイルも省けるし次々作成されるキャッシュがメモリを圧迫することも無いので)このキャッシュを利用したほうが有利です。
プリペアならデータ部分がマスクされているので、データが変わっても他が同じなら同じSQLとみなせキャッシュがあれば利用可能です。
検証のほうは1回のクエリで多くのデータを入れようとしている(?)ように見えます。
1回のクエリで挿入する行数を次第に増やしていく。
このやり方ではプリペアを指定したとしても毎回SQLが変わるので、実行モジュールのコンパイルも都度行われると思います。
そうするとキャッシュされた実行モジュールに値を渡す操作が(プリペアを使わない場合に比べて）余分に増えるので、それだけでもコストが高くなると思います。
こうではなく、1行づつインサートするSQLを65000回発行するなら、プリペアの方が早くなりそうに思います。
投稿2015/12/12 18:55
編集2015/12/12 18:57
総合スコア2068
0
気になる点を上げます。
prepareをクエリ発行の度に行っているようですが、
これは最初に一回だけで良いのでは？
内部動作は分かっていませんが、prepareの度にDBアクセスされて、
プリペアドステートメントの場合が不利な気がします。
マニュアルでも以下の方法で書かれています。
<?php $stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)"); $stmt->bindParam(':name', $name); $stmt->bindParam(':value', $value); // 行を挿入します $name = 'one'; $value = 1; $stmt->execute(); // パラメータを変更し、別の行を挿入します $name = 'two'; $value = 2; $stmt->execute(); ?>
http://php.net/manual/ja/pdo.prepared-statements.php
また、理由が分からないのであれば、
mysqli_get_client_statsなどを使って、統計を取ってみては？
参考情報です。
KiyoshiMotokiさんの結果を、こちらで散布図にしてみたところ、
以下の様に、顕著にO(n)の傾向が見えます。
どうも、おかしいような気がします。
また、性能検証には向かない環境ですが、Windows 7上のOracle Virtualboxを
DBサーバとして、Windows 7のホストから負荷をかけてみました。
環境は以下の通りです。
APサーバ（ホスト）
OS : Windows 7
CPU : Intel(R) Core(TM) i7-4700MQ CPU @ 2.40GHz
Memory : 16GB
スクリプト : PHP 5.6.8
DB接続ドライバ : PDO
Oracle Virtual Box 5.0.8
DBサーバ(ゲスト)
OS : Ubuntu 14.04.3
Memory : 2GB
DB : MySQL 5.5.46
グラフ化した結果は以下の通りです。
顕著な差は出ませんでした。
mysqli_get_client_statsの情報(値が0の項目は省きました)
[bytes_sent] => 11823835 [bytes_received] => 5191393 [packets_sent] => 131488 [packets_received] => 131278 [protocol_overhead_in] => 525112 [protocol_overhead_out] => 525952 [bytes_received_rset_header_packet] => 135 [bytes_received_rset_row_packet] => 3418420 [bytes_received_prepare_response_packet] => 1769418 [packets_sent_command] => 65714 [packets_received_rset_header] => 15 [packets_received_rset_row] => 65654 [packets_received_prepare_response] => 65534 [non_result_set_queries] => 65654 [ps_prepared_once_executed] => 1 [rows_affected_normal] => 491520 [rows_affected_ps] => 491520 [command_buffer_too_small] => 196 [connect_success] => 30 [connect_failure] => 2 [active_connections] => 18446744073709551584 [explicit_close] => 30 [explicit_free_result] => 15 [explicit_stmt_close] => 15 [com_quit] => 30 [com_query] => 32887 [com_stmt_prepare] => 15 [com_stmt_execute] => 32767 [com_stmt_close] => 15
(2015/12/15 21:02 追記)
バイナリログを出力する様になっていたりしませんか？
Bug #67676 prepared statement is very slow while binlog was enabled
という不具合があるようです。
試しに、こちらの環境でバイナリログを出力する様にしたところ、
以下の結果となり、KiyoshiMotokiさんの結果に少し近くなりました。
mysqli_get_client_statsの情報(実行前後の差分)
[bytes_sent] => 47295406 [bytes_received] => 20765932 [packets_sent] => 525952 [packets_received] => 525112 [protocol_overhead_in] => 2100448 [protocol_overhead_out] => 2103808 [bytes_received_rset_header_packet] => 540 [bytes_received_rset_row_packet] => 13673680 [bytes_received_prepare_response_packet] => 7077672 [packets_sent_command] => 262856 [packets_received_rset_header] => 60 [packets_received_rset_row] => 262616 [packets_received_prepare_response] => 262136 [non_result_set_queries] => 262616 [ps_prepared_once_executed] => 4 [rows_affected_normal] => 1966080 [rows_affected_ps] => 1966080 [command_buffer_too_small] => 784 [connect_success] => 120 [connect_failure] => 5 [active_connections] => 18446744073709551491 [explicit_close] => 120 [explicit_free_result] => 60 [explicit_stmt_close] => 60 [com_quit] => 120 [com_query] => 131548 [com_stmt_prepare] => 60 [com_stmt_execute] => 131068 [com_stmt_close] => 60
投稿2015/12/13 00:50
編集2015/12/15 12:04総合スコア1546
eripong様
> 理由が分からないのであれば、 mysqli_get_client_statsなどを使って、統計を取ってみては？
ご提案ありがとうございます。
統計情報も確認してみたいと思います。
引き続き検証を続けておりますので、進捗がありましたら、ご報告させていただきます。
0
hirohiro 様の「公式の記述のほうは、同じSQLを繰り返し使うならプリペアドステートメントが有利と言うものだと思います。」という回答が、ドキュメント本来の意図なのは、間違いないと思います。
時間がないので検証しておりませんが、グラフが左右対称に近いことも考えると
bind する数が過剰なため bindValue 自体に時間がかかっている
のではないかと思います。
bind された値はどう管理されるか…ということを考えると、MySQL 側では、
- bind される値分のメモリを確保(prepare の時点で、ループ外で行われている可能性が高い)
- bind 1回ごとに「格納すべき場所(メモリのどこか)を探し、そこに値を格納」
- execute の時に、値を読み込んでいって解釈
していそうです。そのうち、2番目のところに時間がかかりそうですね。
O(n) ということなら、ここの探索部分が線形になっている…とかではないでしょうか。Oracle は考慮され、最適化されているのでしょう。
bind する値を「ループで最初の1つの値だけ変わる」や「最後の1つの値だけ変わる」場合についての計測を行えば、execute と bindValue のどちらにより時間がとられていそうか、なんとなくわかるかもしれません。(MySQL のコードを読むのが一番だとは思いますが)
bind する $i の値について考察すると理由が読めてくると思います。
PHP 側で、(100), (47), ... という「文字列を生成」した場合のパフォーマンスも見てみたら面白いかもしれませんね。
話は変わりますが、追試内容は「質問文に追記」した方が見易くて良いのではないかと思います。
投稿2015/12/15 07:38
総合スコア1111
bindに時間がかかる可能性は、確かにありそうです。
0
ATTR_EMULATE_PREPARES=1はPDO側でpreparedステートメントをエミュレートするのでbindがたくさんあるとオーバーヘッドがえらい事になりそうですよね。
バルクで大量投入するより１件づつ大量インサートか、ある程度複雑な参照系で試せば、
ATTR_EMULATE_PREPARES=1の方が性能出そうな予感がします。
投稿2015/12/13 11:17
総合スコア43
ooi様、ご回答ありがとうございます。
> バルクで大量投入するより１件づつ大量インサートか、ある程度複雑な参照系で試せば、 ATTR_EMULATE_PREPARES=1の方が性能出そうな予感がします。
ちょうど、追加検証の結果を質問欄に反映させていただいたところでしたが、
1件ずつインサートする場合は
ATTR_EMULATE_PREPARES=0
の方が微妙ながら、良いパフォーマンスを得られました。
今のところ、クエリを再利用する回数が少なければ
ATTR_EMULATE_PREPARES=1
の方が有利で、回数を増やすに従って
ATTR_EMULATE_PREPARES=0
の方が性能を発揮していくのではないかな?と予想しております。
引き続き検証を続けておりますので、進捗がありましたら、ご報告させていただきます。
あ、なるほど、勘違いしてました。
確かに右に行くほどmysql自体のプリペアードステートメントを使う方が遅くなるのは、再利用される回数が減るので分かりますね。
左側で性能がほとんど変わらないのは何とも残念ですが、単純なSQLだとPDOでサニタイズした生SQLを送信するのと、MySQLがプリペアードステートメントを利用するのはほとんど変わらないレベルの処理なのかもしれないですね。
MySQL5.6とか5.7だとどうなんでしょう、という興味もあります。
進捗楽しみにしております。
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2015/12/12 19:42
2015/12/13 05:35 編集
2015/12/13 11:30
2015/12/15 13:02