Mysql – Memcache implementation

memcachedMySQLoptimizationPHP

I have a WordPress site that uses WP Super Cache plugin in order to make my blog posts and pages stay as HTML so that less PHP/MySQL code is executed. However, there's a featured box that can't get cached. So, I configured it manually to bypass cache and stay dynamic (code is executed on every page load).

So, most part of the entire page is executed using HTML and a specific part (a featured bar box) uses a PHP/MySQL.

The code that runs on every page load are some simple <?php $query = mysql_query("SELECT * [... rest of my code here ...]"); ?> to select some data from my database and some $fetch and <?php echo [...] ?> to print the results in the webpage, of course. But instead of everytime calling database to get this results, it would be much better to cache this results. So, I have three options:

  1. Let WP Super Cache caches my entire page. But I can't do that, because inside my box that currently isn't getting cached, the database data can change anytime (normally 1 time per day). So, I can't cache this box and we have to discart this option 1.
  2. Activate query_cache in my.cnf. However, this will only cache MySQL and don't PHP. 🙁
  3. Than, we have memcache (or APC, I don't know much about it's difference). After all my explanations, here's the final question: I'd like to know if installing memcache in my server is enought to get it working imediatally or I have to make any changes in my code to adapt it with memcache; I'd like to know if it will really help me with both PHP and MySQL for this specific part of code I have to cache or it's results is similar to enabling query_cache? Or is using APC rather than memcache better in my case?

Thanks,
André.

Best Answer

You should decide what exactly you need, before deciding those 3 options.

My suggestion is as following:

  1. Install memcache and the necesary php module to use with memcache. Once you install memcache, you will have an IP and PORT to connect to, for SET / GET operations

  2. Decide where exactly in your Wordpress php code you need to inject the code that displays the "featured" stuff

  3. Add a php code similar to:

    • CONNECT to memcache
    • Check for the existance of a KEY/VALUE pair, named "FEATURED STUFF"
    • If there is, GET that pair, and display the pair's value
    • If not, run the SELECT query, FETCH the results, build a string that will contain the HTML body of "FEATURED STUFF", PUT the string in memcache as a KEY/VALUE pair.

  4. The code above should not be cached with WP Super Cache. Let memcache do the cacheing for you.

If you need help with the actual implementation, let me know.

Sample PHP Code:

$memcache = new Memcache;
$memcache->connect('localhost', 11211) or die ("Could not connect");

if ($gotten = $memcache->get('featured_stuff')) {
    echo 'CACHE HIT'</br>';
    echo $gotten;
} else {
    # Never mind - fetch it and store for next time!
    $gotten = dbfetch($query, ...);
    $memcache->set($featured_stuff,$gotten);
    echo $gotten;
}
Related Question