Using Memcache with MySQL and PHP

Posted on May 21, 2008. Filed under: Memcache, mysql+, PHP | Tags: , , , , , |

Memcache is a great caching tool available for nearly every scripting or programming environment. I use it with PHP to speed up some applications I have written by completely avoiding asking the database for information. I wanted a very clean way of implementing this in my various projects, with as little change to the existing code as possible. Since memcache is an OBJECT caching system, you can’t simply drop your mysql_query resource into memcache, ask for it at another time, and get your results back … Which is how I had originally thought it worked the first time I read about it. The good news is that it can also store Strings, Integers, Floats, Arrays etc … Below I have a few other things I’ve learned whilst using memcache as well as the caching script I use for database queries.

What I’ve learned:

  1. memcache is great for storing slow queries that return small data sets [1 – 50 results, depending on the average row weight]
  2. memcache is not so great for any query that returns large data sets [100 – ∞, depending on the average row weight]

… in fact, I’ve found that memcache can occasionally be slower than running high-yield queries again [that is, if you have your MySQL server caching queries as well]. It all really boils down to benchmarking the scripts yourself: test every situation with and without the cache! [the more realistic the DB load, the better]

Caching is faster? Yay! Cache it!
DB query is faster? Yay! DON’T cache it!

So, basically, this isn’t a plug-and-play solution for ALL slow page loads / queries, just some of them.

Here’s the code I use to cache MySQL queries:


<?php
    # Connect to memcache:
    global $memcache;
    $memcache = new Memcache;

    # Gets key / value pair into memcache ... called by mysql_query_cache()
    function getCache($key) {
        global $memcache;
        return ($memcache) ? $memcache->get($key) : false;
    }

    # Puts key / value pair into memcache ... called by mysql_query_cache()
    function setCache($key,$object,$timeout = 60) {
        global $memcache;
        return ($memcache) ? $memcache->set($key,$object,MEMCACHE_COMPRESSED,$timeout) : false;
    }

    # Caching version of mysql_query()
    function mysql_query_cache($sql,$linkIdentifier = false,$timeout = 60) {
        if (($cache = getCache(md5("mysql_query" . $sql))) !== false) {
            $cache = false;
            $r = ($linkIdentifier !== false) ? mysql_query($sql,$linkIdentifier) : mysql_query($sql);
            if (is_resource($r) && (($rows = mysql_num_rows($r)) !== 0)) {
                for ($i=0;$i<$rows;$i++) {
                    $fields = mysql_num_fields($r);
                    $row = mysql_fetch_array($r);
                    for ($j=0;$j<$fields;$j++) {
                        if ($i === 0) {
                            $columns[$j] = mysql_field_name($r,$j);
                        }
                        $cache[$i][$columns[$j]] = $row[$j];
                    }
                }
                if (!setCache(md5("mysql_query" . $sql),$cache,$timeout)) {
                    # If we get here, there isn't a memcache daemon running or responding
                }
            }
        }
        return $cache;
    }
?>

The function mysql_query_cache() will return an array filled with the results. Since I don’t use this for large result sets, I don’t free the MySQL resource … you may want to free the resource after it’s been used if you get larger data sets.

Like I had mentioned before, I wanted the code to be as easy-to-use as possible when using it. So, I’ve set up a before and after test scenario showing how to retrofit your code with the new caching code:


<?php
    $sql = "
        SELECT `dataID`, `dataTitle`
        FROM `tbldata`
        WHERE `dataTypeID` BETWEEN 2 AND 2093
        AND `dataStatusID` IN (1,2,3,4)
        AND `dataTitle` LIKE '%something%'
        ORDER BY `dataDate` DESC
        LIMIT 10
    ";

    # Before: [without memcache]
    $rSlowQuery = mysql_query($sql);
    # $rSlowQuery is a MySQL resource
    $rows = mysql_num_rows($rSlowQuery);
    for ($i=0;$i<$rows;$i++) {
        $dataID = intval(mysql_result($rSlowQuery,$i,"dataID"));
        $dataTitle = mysql_result($rSlowQuery,$i,"dataTitle");

        echo "<a href=\"/somewhere/$dataID\">$dataTitle</a><br />\n";
    }


    # After: [with memcache]
    $rSlowQuery = mysql_query_cache($sql);
    # $rSlowQuery is an array
    $rows = count($rSlowQuery);
    for ($i=0;$i<$rows;$i++) {
        $dataID = intval($rSlowQuery[$i]["dataID"]);
        $dataTitle = $rSlowQuery[$i]["dataTitle"];

        echo "<a href=\"/somewhere/$dataID\">$dataTitle</a><br />\n";
    }

?>

Easy, huh? Run print_r() on the returned array to get an idea of how the array is structured if need be.

As always, if you have a better, more efficient, objective, more adaptable solution than mine, please leave a comment! I am 100% open to constructive criticism :-)

About these ads

Make a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

57 Responses to “Using Memcache with MySQL and PHP”

RSS Feed for Adventures in PHP / DHTML / CSS and MySQL Comments RSS Feed

cool, new to memcached and was looking for somthing like this. question though, how can you tell if data is being served by memcached or the db?

You could add a row to the array it returns… something like:
$cache["from"] = "db";
and set it up logically so that it will populate this row based upon what the function does.

it working fine. I would like to know how can i manipulate the cache

Hi, new to all of this, so please forgive the newspeak. I have a daemon running (I think) on the default port.

I needed to add $memcache->connect(‘localhost’, ‘11211’); below the line $memcache = new Memcache; in order to not fall into the ‘no daemon running’ case in your script.

Does this sound correct?

That’s correct … You’ll need to instantiate the Memcached object before running the connect() method on it.

One issue I am having…

When I use this to check for the existance of one item in the database, for example SELECT name_id FROM names WHERE name = ‘Bill'; and Bill is not in my test dB.

count($rSlowQuery); returns a value of one, even if nothing is found. Doing print_r on the the above displays nothing, yet count returns a value of 1… any ideas?

same issue here count($rSlowQuery); always returns a value of one, have you found a solution?
thanks for sharing

[…] PHP Caching (Wikipedia), memcached & PHP, Official Ruby on Rails caching guide, Excellent Article on Rails caching, Caching with Smarty, […]

I don’t understand a part of this code, someone have the answer ?

You don’t put the ‘mysql_query($sql);’ in a if statement like :

if($we_have_this_request_in_cache){
show from cache
}else{
ask the bd
}

Maybe i’m wrong but why the mysql_query is not executed each time the page reload ?

Thanks
By the way, thank you for sharing this page:)

$fields = mysql_num_fields($r);

Should be outside the for loop..

think it can be a bit faster in total
function mysql_query_cache($sql,$linkIdentifier = false,$timeout = 60) {
if (($cache = getCache(md5(“mysql_query” . $sql))) !== false) {
$cache = false;
$r = ($linkIdentifier !== false) ? mysql_query($sql,$linkIdentifier) : mysql_query($sql);
if (is_resource($r) && (($rows = mysql_num_rows($r)) !== 0)) {
for ($i=0;$i<$rows;$i++) {
$cache[$i] = mysql_fetch_assoc($r);
}
if (!setCache(md5("mysql_query" . $sql),$cache,$timeout)) {
# If we get here, there isn't a memcache daemon running or responding
}
}
}
return $cache;
}
for the rest thx for the example

I think the code is just not working .. They are some logical error in the php. The request always ask for the database and never touch the memcached.

The second time we reload the page, is more fast but we just hit the MySQL cache, not the memcached itself…

It need a little modification to work, but not too much.

I think ;)

I just have one comment about the “is memcache faster?” question. Whether memcache is faster or not it is often more scalable. So if it takes .01 seconds to get something from the database and .015 seconds to get it from memcache it might take .015 seconds to get it at 100/sec. from memcache whereas the database will start to take longer at higher concurrency. It’s something to consider.

[…] Using Memcache with MySQL and PHP (tags: php mysql cache howto code emon) […]

Was going to comment on “is memcache faster?” but see Stephen beat me to it. Getting data from memcached should always be faster than getting it from the database, if it is not then something is wrong with your setup. It uses non-blocking network I/O – and is of course very scalable. That said not all data is suited for caching. Memcached is a very good tool for the right job.

In my test, it wasn’t faster to always pull from the cache… and it wasn’t tenths or hundredths of seconds, it was several seconds difference. I can’t remember the exact queries I was caching, but it never hurts to benchmark your code and adjust accordingly instead of always caching no matter what.

Now that I think about it, the non-cache condition I found may have applied to your thoughts on “not all data is suitable for caching”… I think it may have been for a pretty large result set [200 – 300K]. But still, benchmarking is always a good idea :-)

NIce one found abt the mysql n memcached,but i didn’t get how u quote the script without connect(),newbe will be in trouble, plzz add this if its poosible.whatever its a remarkable for those who are working first time with cache

Cool Tutorial,Thanx a lot.
Good for memcache beginners.
Is there any PHP mysql wrapper for using memcache??

Thanx a lot

if (($cache = getCache(md5(“mysql_query” . $sql))) !== false)

why is this set to false?

I’m not 100% sure what you mean. It’s testing to see if the query is in Memcache, assigning it to $cache and then testing to see if $cache is NOT false, which is what Memcache::get() will return upon on a miss. If $cache is anything other than false, it will continue into condition… is this what you mean? :-)

if (($cache = getCache(md5(”mysql_query” . $sql))) !== false)
if this condition is set to false then everytime its going inside fuction mysql_query_cache….which we dont need,if its set in cache then we are returning that value through return statement in function getcache…

ya that is to see cache not equal to false…if it is not false ,then no need to get into function…so tat condition must be === rather then !== …M not 100% sure about this…

In all the cases its going inside function and executing , then where’s caching is being done….

can i chat wth u…plzzzz,let me know the time..i wann know few things

thank you man great article. +10 points from me :P

nice post keep posting

thanks it. nice tutorial.

“Memcached is a very good tool for the right job.” — yeah,,you should know when to use memcached., you can’t always use memcached to replace all your queries…

One of the other cool things with memcached is if you are using templates in your application, you can cache the templates in memcache. At runtime, pull the template file from the cache rather than disk.

yeah, pretty cool idea, makes me think how to integrate memcache to smarty template engine

how about db connection, where i put and define DBHOST,DBUSERNAME,DBPASSWORD.

hit some mysql_connect bud before you call the function

Hi!

I have a weird problem… My script wont check the Memcache server if memcache server version is not asked in the script… $mc->getVersion(); get() in order to get it to actually retrieve the info from cacheserver.

is this code work well? i can not see any output after calling function mysql_query_cache.

thanks for the example

(From BCN)

Thanks for such a useful article… keep posting.

[…] Just wanted to share that, and will try it next week when back at work: Memcached example […]

is there any way to get all cached keys starting with common prefix. for ex key_1, key_2,key_3…i want to fetch all the keys using prefix ‘key_’

maybe better of instead storing the mysql result resource, if you are using codeigniter, serialize the results of $query->result() then put it on the cache, so that you dont need to loop to the values again everytime you retrieve it from cache

I was writing a similar function myself, but I also wanted to handle “cache miss storms” — that is, when the cache is expensive to rebuild, it expires, and many threads try to rebuild it at once, causing database overload. As an extreme example, let’s say we are caching a query that takes 5 seconds to execute, and the page which triggers this is hit 100 times per second — as you can see, when the cache expires, this will create quite a problem that may end up resulting in MySQL running out of memory.

So basically what we need is a check-and-set operation (for when we are regenerating the cache) to say “if no one else is currently regenerating the cache, then set this flag, run the SQL, and save the results to cache”. And if someone else is already regenerating the cache, we wait until they finish (or until a certain timeout period passes). I’m currently playing with it as a non-atomic separate check and then set because I just started with memcached and haven’t quite wrapped my head around the built-in cas() functionality. The downside is that if it’s a high-concurrency environment and several threads check at nearly the same time (within maybe 4ms of each other) you could have a few threads regenerating instead of just one. But still better than several hundred.

Anyway, I just started work on this function, and found your article when I was checking to see if anyone else had done a function which prevents cache miss storms. If you’re interested, I can send you my function when I’m done.

it’s not work some thing should modify

i removed linkIdentify
the setCache should be outside if(){}
mysql_query_cache it’s should be

function mysql_query_cache($sql,$timeout = 60) {
if (($cache = getCache(md5(“mysql_query” . $sql))) === false) {
$r = mysql_query($sql);
if ( ($rows = mysql_num_rows($r)) !== 0 ) {
for ($i=0;$i<$rows;$i++) {
$fields = mysql_num_fields($r);
$row = mysql_fetch_array($r);
for ($j=0;$j<$fields;$j++) {
if ($i === 0) {
$columns[$j] = mysql_field_name($r,$j);
}
$cache[$i][$columns[$j]] = $row[$j];
}
}
}
}
setCache(md5("mysql_query" . $sql),$cache,$timeout);
return $cache;
}

thank you for your code
i change and its work

I’m not a used to php so I can’t provide an example though.. but since php 5 onwards, object is supported; an abstract DAO class can be created to use cache that can be combined with ORM solutions like propel or may be just pdo. That may abstract away the cache and sql acesss.. So, the developers may effectively see only the DAO object, that they can extend to, for each entity they have and use that data access object.

To make it more effective a profiling option can be used to store with extra data with each query’s md5 hash, regarding the time it took to query and the size of resultset.. That profiling information can be used next time to decide, if cache should be checked at all or not..

Just a thought! And a nice poc for those new to memcache like me.. Thanks for the post…

HI , well put…

A question, U said , for rows >50 memcached is slower. But, FB youtube, wiki etc uses the same memcached, am 100% sure it is way greater in load then. So, why would they use it if its slower? or are their any diff method in which memcached is used for larger queries?? tnx :)

Please note that your blog entry is on top of the google rankings when searching for memcache solutions with PHP, but your mysql_query_cache function’s logic is in that way faulty, that if getCache statement is false, it simply returns an empty $cache value instead of executing the mysql_query!

if (($cache = getCache(md5(“mysql_query” . $sql))) !== false) {

}
return $cache;

It works very well with the code from Tung’s comment. You should possibly update your article with it or re-check your logic

Also a question for a memcache logic beginner – how does memcache know when the contents of the mysql result have changed ( and it should clear the cache ) without the INSERT/UPDATE statements going through memcache?

The Wikipedia article actually addresses this flaw. Possibly an update to your article would be appropriate : “However, if only this API call were modified, the server would end up fetching incorrect data following any database update actions: the Memcached “view” of the data would become out of date. Therefore, in addition to creating an “add” call, an update call would also be needed using the Memcached set function.”

http://en.wikipedia.org/wiki/Memcached#Example_code

So basically after every UPDATE/INSERT action the cache for affected tables should be cleared through the PHP code. This makes it more complicated as to hunt down :
– all MD5 keys for that table
– to decide which cache’s for that table to actually clear; Let’s say you have a huge table and one query selects the TOP 10 results, but you actually ALTER some result at the bottom of the table. The memcache would have to clear the TOP 10 key, although it’s not affected. A bit inefficient for huge tables with lots of updates?

[…] very nice article http://pureform.wordpress.com/2008/05/21/using-memcache-with-mysql-and-php/ Categories: Uncategorized Tags: Comments (0) Trackbacks (0) Leave a comment […]

[…] API’s to modify your PHP codes to enable MySQL caching. You can find the examples codes at Memcache with MySQL and PHP.Enable Memcached on WordPress SitesFor WordPress based sites, there is a plugin called Memcached […]

[…] It isn’t an easy task for all, you need to use API’s to modify your PHP codes to enable MySQLcaching. You can find the examples codes at Memcache with MySQL and PHP. […]

Try to use this class http://www.phpfastcache.com , it’s support memcache, memcached , wincache, files, pdo and x-cache too.


Where's The Comment Form?

Liked it here?
Why not try sites on the blogroll...

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: