PHP PDO::MYSQL_ATTR_USE_BUFFERED_QUERY Performance

php pdo mysql

ふとネットサーフィンをしていると、こんな質問を見つけました。

PHPのPDOでMySQLから30,000行のレコードを“\`PDOStatement-\>fetch”で取り出して行単位に処理してるのに、何でこんなにメモリ使ってるの？ という質問なのですが、Answerを見てびっくりしました。

After creating the connection, you need to set PDO::MYSQL_ATTR_USE_BUFFERED_QUERY to false:

<?php
$pdo = new PDO('mysql:host=127.0.0.1', 'foo', 'bar', array(
    PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,
));
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

// snip
var_dump(memory_get_usage());
var_dump(memory_get_peak_usage());

This outputs:

int(39508) int(653920) int(668136) Regardless of the result size, the memory usage remains pretty much static.

PDO::MYSQL_ATTR_USE_BUFFERED_QUERYなんて設定がデフォルトでOnになっていたのか！！ しかもint(43018120)からint(668136)までメモリの最大使用量が落ちるほど使っているとは思いもしませんでした。

ここまでの差が出るなら是が非でも導入しないといけませんね。

というわけで、まずは導入する前に計測です。

Benchmark

1万行ずつをMySQLに最大100万行までINSERTして、そのさいの開始前、開始後のPeak Memoryの差と実行時間を計測します。 環境としては下記のものになります。

  • OS X Marvericks 10.9
  • PHP 5.4.17
  • MySQL 5.6.13（t1.micro）

Code

test.php

<?php
// Args
$label = $argv[1];
$enable = (boolean)$argv[2];
$num = intval($argv[3]);
// Performance
$time = microtime(true);
$mem = memory_get_peak_usage();
function performance() {
    global $label, $num, $time, $mem;
    file_put_contents("result", '"'.$label.'", "'.$num.'", "'.(memory_get_peak_usage()-$mem).'", "'.(memory_get_peak_usage()).'", "'.(microtime(true) - $time).'"'."\n", FILE_APPEND);
}
register_shutdown_function("performance");
// Use buffered query test
$pdo = require 'pdo.php';
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, $enable);

$sth = $pdo->prepare('SELECT * FROM Buffer');
$sth->execute();

foreach ($sth as $record) {
    $record['buf'];
}

このtest.phpを使用して実際にパフォーマンスの計測を行っています。

prepare.php

<?php
$pdo = require 'pdo.php';
// Create table
$sql = "
    CREATE TABLE IF NOT EXISTS Buffer (
        buf varchar(256)
    )
";
$pdo->exec($sql);

for ($i = 0; $i < intval($argv[1]); $i++) $data[] = str_pad('', 256);
$sql = "INSERT INTO Buffer VALUES " .implode(",", array_fill(0, count($data), "(?)")) . "";
$stmt = $pdo->prepare($sql);
$stmt->execute($data);

このprepare.phpを使用してtest.phpを実行する前にDBに件数を登録していきます。

Memory

Time

最終的に100万行の取得処理ではPDO::MYSQL_ATTR_USE_BUFFERED_QUERYがOnになっていると65Mbに達しているのに対して、Offの場合は一貫して11kbを保っています。 まさか、ここまでの差が出るとは思いもしませんでした。 逆に実行時間のほうは差は全くでていません。

これはもうサーバー負荷を下げるために、デフォルトでOffにしておいてにしておきたいですね。

ただし、ここれでOffにしてしまうと、以下のようなコードは動かなくなります。

$sth1 = $pdo->prepare('SELECT * FROM Buffer');
$sth1->execute();

foreach ($sth as $record) {
    $sth2 = $pdo->prepare('SELECT * FROM Buffer');
    $sth2->execute();
}

取ってきた値を使って、さらにSQLを実行することは多々あるので、こういうときはPDOStatement::fetchAllを使用してくださいと、PHP Manuialに記載されています。 fetchAllなんて使ったら件数によってはメモリ不足になるので、結局Onの方がメモリ使用量が少なく済むという不思議な誘導ですね。

PDO::prepareでPDOのオプションをオーバーライド出来るので、PHP+MySQLではPDO::MYSQL_ATTR_USE_BUFFERED_QUERYをデフォルトでOnにしておいて、CSV出力やページングのない一覧出力なんかをするときだけ、オーバーライドしてOffにするのがベターなやり方になりそうです。