PHP MemcacheとSQLiteインメモリDBの比較

memcache_get.php

<?php
set_time_limit(60);
session_start();

$keys = range(0, 100000);


//-----------------------------------------------------------------------------

$host = 'localhost';
$port = 11211;
$persistent = true;

$memcache = new Memcache();
$memcache->addServer($host, $port, $persistent);

$started_at = microtime(true);
$results = array();
foreach ($keys as $key) {
    $results[$key] = $memcache->get($key);
}
$to_get = microtime(true) - $started_at;

echo '<pre>';
echo sprintf("Memcache version %s\n", $memcache->getVersion());
echo sprintf("Took %f seconds to get.\n", $to_get);
var_dump(isset($_SESSION['items']) && $_SESSION['items'] === $results);
echo '</pre>';

$started_at = microtime(true);
$results = $memcache->get($keys);
$to_get = microtime(true) - $started_at;

echo '<pre>';
echo sprintf("Took %f seconds to get.\n", $to_get);
var_dump(isset($_SESSION['items']) && $_SESSION['items'] === $results);
echo '</pre>';


//-----------------------------------------------------------------------------

$db = new PDO('sqlite::memory:', null, null, array(
    PDO::ATTR_PERSISTENT => true,
));
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$results = array();
$started_at = microtime(true);
$statement = $db->prepare('SELECT value FROM items WHERE id = :id');
foreach ($keys as $key) {
    $statement->execute(array(':id' => $key));
    $results[$key] = $statement->fetchColumn(0);
}
$to_get = microtime(true) - $started_at;

echo '<pre>';
echo sprintf("PDO version %s persistent in memory\n", $db->getAttribute(PDO::ATTR_SERVER_VERSION));
echo sprintf("Took %f seconds to get.\n", $to_get);
var_dump(isset($_SESSION['items']) && $_SESSION['items'] === $results);
echo '</pre>';

$results = array();
$started_at = microtime(true);
$statement = $db->query('SELECT id, value FROM items WHERE id IN (' . implode(',', $keys) . ');');
while ($row = $statement->fetch(PDO::FETCH_NUM)) {
    $results[$row[0]] = $row[1];
}
$to_get = microtime(true) - $started_at;

echo '<pre>';
echo sprintf("Took %f seconds to get.\n", $to_get);
var_dump(isset($_SESSION['items']) && $_SESSION['items'] === $results);
echo '</pre>';


//-----------------------------------------------------------------------------

$filePath = dirname(__FILE__) . DIRECTORY_SEPARATOR . 'db.sqlite';
$db = new PDO('sqlite:' . $filePath);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$results = array();
$started_at = microtime(true);
$statement = $db->prepare('SELECT value FROM items WHERE id = :id');
foreach ($keys as $key) {
    $statement->execute(array(':id' => $key));
    $results[$key] = $statement->fetchColumn(0);
}
$to_get = microtime(true) - $started_at;

echo '<pre>';
echo sprintf("PDO version %s file strage\n", $db->getAttribute(PDO::ATTR_SERVER_VERSION));
echo sprintf("Took %f seconds to get.\n", $to_get);
var_dump(isset($_SESSION['items']) && $_SESSION['items'] === $results);
echo '</pre>';

$results = array();
$started_at = microtime(true);
$statement = $db->query('SELECT id, value FROM items WHERE id IN (' . implode(',', $keys) . ');');
while ($row = $statement->fetch(PDO::FETCH_NUM)) {
    $results[$row[0]] = $row[1];
}
$to_get = microtime(true) - $started_at;

echo '<pre>';
echo sprintf("Took %f seconds to get.\n", $to_get);
var_dump(isset($_SESSION['items']) && $_SESSION['items'] === $results);
echo '</pre>';
?>
<p><a href="memcache_set.php">memcache_set.php</a></p>

memcache_set.php

<?php
set_time_limit(60);
session_start();

$keys = range(0, 100000);
$items = array_combine($keys,
    array_map(function($i) {
        return sha1($i);
    }, $keys)
);
$_SESSION['items'] = $items;


//-----------------------------------------------------------------------------

$host = 'localhost';
$port = 11211;
$persistent = true;

$memcache = new Memcache();
$memcache->addServer($host, $port, $persistent);
$memcache->flush();

$started_at = microtime(true);
foreach ($items as $key => $value) {
    $memcache->set($key, $value);
}
$to_set = microtime(true) - $started_at;

echo '<pre>';
echo sprintf("Memcache version %s\n", $memcache->getVersion());
echo sprintf("Took %f seconds to set.\n", $to_set);
echo '</pre>';


//-----------------------------------------------------------------------------

$db = new PDO('sqlite::memory:', null, null, array(
    PDO::ATTR_PERSISTENT => true,
));
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$query = <<< SQL
CREATE TABLE IF NOT EXISTS items
(
    id    INTEGER      NOT NULL PRIMARY KEY,
    value VARCHAR(40)  NOT NULL
);
SQL;
$db->exec($query);
$db->exec('DELETE FROM items;');

$db->beginTransaction();
$started_at = microtime(true);
$statement = $db->prepare('INSERT INTO items VALUES(:id, :value);');
foreach ($items as $key => $value) {
    $statement->execute(array(':id' => $key, ':value' => $value));
}
$to_set = microtime(true) - $started_at;
$db->commit();

echo '<pre>';
echo sprintf("PDO version %s persistent in memory\n", $db->getAttribute(PDO::ATTR_SERVER_VERSION));
echo sprintf("Took %f seconds to set.\n", $to_set);
echo '</pre>';


//-----------------------------------------------------------------------------

$filePath = dirname(__FILE__) . DIRECTORY_SEPARATOR . 'db.sqlite';
$newFile = (!file_exists($filePath));
$fileHandle = fopen($filePath, 'wb');
if ($newFile) {
    chmod($filePath, 0666);
}

$db = new PDO('sqlite:' . $filePath);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$query = <<< SQL
CREATE TABLE IF NOT EXISTS items
(
    id    INTEGER      NOT NULL PRIMARY KEY,
    value VARCHAR(40)  NOT NULL
);
SQL;
$db->exec($query);
$db->exec('DELETE FROM items;');

$db->beginTransaction();
$started_at = microtime(true);
$statement = $db->prepare('INSERT INTO items VALUES(:id, :value);');
foreach ($items as $key => $value) {
    $statement->execute(array(':id' => $key, ':value' => $value));
}
$to_set = microtime(true) - $started_at;
$db->commit();

echo '<pre>';
echo sprintf("PDO version %s file strage\n", $db->getAttribute(PDO::ATTR_SERVER_VERSION));
echo sprintf("Took %f seconds to set.\n", $to_set);
echo '</pre>';

?>
<p><a href="memcache_get.php">memcache_get.php</a></p>

SQLiteが思いのほか速いので、ファイルストレージの場合もやってみた。

---

SET
Memcache version 1.2.6
Took 3.713627 seconds to set.

PDO version 3.7.3 persistent in memory
Took 0.893594 seconds to set.

PDO version 3.7.3 file strage
Took 2.112437 seconds to set.

---

GET
Memcache version 1.2.6
Took 3.375603 seconds to get.
bool(true)

Took 0.177190 seconds to get.
bool(true)

PDO version 3.7.3 persistent in memory
Took 0.987806 seconds to get.
bool(true)

Took 0.886014 seconds to get.
bool(true)

PDO version 3.7.3 file strage
Took 9.032683 seconds to get.
bool(true)

Took 0.958007 seconds to get.
bool(true)

---

ファイルストレージは書き込みは遅いだろうとは思ったけど、それ以上に同じファイルでも読み込みで繰り返し取得した場合とIN述語で一括取得した場合の差が極端についてます。