2

I am building a web app in php with mysql as database.

I am using memcached to store sessions and I am thinking if I should also store (in another memcached instance), database records.

The db is going to be pretty small and updated once in a while. There are going to be a lot of selects though, as we expect high traffic.

For a small database would I benefit if I store records in memcached or should I leave it to the mysql cache and tune the db for lots of connections (would appreciate some pointers for optimizing mysql)?

Thanks

bcosca
  • 17,371
  • 5
  • 40
  • 51
Thomas
  • 4,641
  • 13
  • 44
  • 67

1 Answers1

0

Why dont you run some benchmarks and then spread that load !!

http://www.php.net/manual/en/intro.apc.php

Page generated in 0.003202 secs using 2 DB calls and 0 DBCache hits (users ttl=5 countries ttl=5 and both items have expired)

Page generated in 0.002728 secs using 1 DB calls and 1 DBCache hits (users ttl=5 countries ttl=immortal and users has expired)

Page generated in 0.000067 secs using 0 DB calls and 2 DBCache hits (users ttl=5 countries ttl=immortal and both are fetched from cache)

how fast - 0.000067 ouch !!

Sample PHP Script using APC

<?php

require_once "CacheDB.php";

ob_start(); 

echo "<h3>APC info:</h3>";

print_r(apc_sma_info());

try{

    //I assume you've already have a db connection available for the cacheDB to use

    $db = @new mysqli("127.0.0.1","foo_dbo","pass","foo_db",3306);

    if ($db->connect_errno)
        throw new Exception("Could not connect: " . $db->connect_error);

    //start the demo...

    $startTime = microtime(true);

    $cacheDB = new CacheDB($db);

    $rows = $cacheDB->Query("call list_users()", CacheDB::TTL_5); //5 second Time To Live (TTL) (30 secs might be more realistic)
    if($rows){
        echo "<h3>Users:</h3><ul>";
        foreach($rows as $row) echo sprintf("<li>%s</li>", $row["username"]);
        echo "</ul>";
    }

    $rows = $cacheDB->Query("call list_countries()", CacheDB::TTL_IMMORTAL); //never expires

    if($rows){
        echo "<h3>Countries:</h3><ul>";
        foreach($rows as $row) echo sprintf("<li>%s</li>", $row["name"]);
        echo "</ul>";
    }

    echo sprintf("<p><b>Page generated in %s secs using %d DB calls and %d DBCache hits</b></p><p>Refresh me !!</p>",
            number_format(microtime(true) - $startTime, 6, ".", ""), 
            $cacheDB->GetDBHits(), $cacheDB->GetCacheHits());

    $db->close();
}
catch(Exception $ex)
{ 
    ob_clean(); 
    echo sprintf("zomg borked - %s", $ex->getMessage());
}

//finally

ob_end_flush();

?>
Jon Black
  • 16,223
  • 5
  • 43
  • 42
  • memcache support distribution - http://stackoverflow.com/questions/815041/memcached-vs-apc-which-one-should-i-choose – ajreal Nov 21 '10 at 16:14
  • so true but it comes at a cost http://www.mysqlperformanceblog.com/2006/09/27/apc-or-memcached/ (i dont know about you, but i'm a speed freak and so what if i duplicate over my load balanced web servers) – Jon Black Nov 21 '10 at 16:21
  • Depends on how you see it, data/code cached at APC, only available for server that execute the APC. While using memcache, execute once, stored the values and re-use across multiple servers – ajreal Nov 21 '10 at 16:39
  • @aj - yup get that but i'd duplicate my country resultset over all my web servers vs. having a single instance of it and taking the x5 slower performance hit vs. the redunancy of duplicating the data over n web servers. The only time i can see your method being of use is when you store user specific data and can't guarantee, due to a stateless architecture/load balancing, which server the next user request may hit. Personally I wouldnt store user specific data in my cache - you'd flood the web server with cached data and may as well just hit the DB. – Jon Black Nov 21 '10 at 17:12
  • I have done some benchmarks with ab, with both memcached caching and without and I see that not using memcached is faster, as I am getting some errors, during the benchmark, which have to do with memcached gets failing. Anyone else experienced get() failures? – Thomas Nov 21 '10 at 19:44