How does memcache with MySQL work?

Question

I am trying to understand (and probably deploy) memcached in our env.

We have 4 web servers on loadbalancer running a big web app developed in PHP. We are already using APC. I want to see how memcached works? At least, may be I don't understand how caching works.

We have some complex dynamic queries that combine several tables to pull data. Each time, the data is going to be from different client databases and data keeps changing. From my understanding, if some data is stored in cache, and if the request is same next time, the same data is returned. (Or I may be completely wrong here).

How does this whole memcache (or for that matter, any caching stuff works)?

Answer 1

Cache, in general, is a very fast key/value storage engine where you can store values (usually serialized) by a predetermined key, so you can retrieve the stored values by the same key.

In relation to MySQL, you would write your application code in such a way, that you would check for the presence of data in cache, before issuing a request to the database. If a match was found (matching key exists), you would then have access to the data associated to the key. The goal is to not issue a request to the more costly database if it can be avoided.

An example (demonstrative only):

$cache = new Memcached();

$cache->addServer('servername', 11211);

$myCacheKey = 'my_cache_key';

$row = $cache->get($myCacheKey);

if (!$row) {

    // Issue painful query to mysql
    $sql = "SELECT * FROM table WHERE id = :id";

    $dbo->prepare($sql);
    $stmt->bindValue(':id', $someId, PDO::PARAM_INT);

    $row = $stmt->fetch(PDO::FETCH_OBJ);

    $cache->set($myCacheKey, serialize($row));
}

// Now I have access to $row, where I can do what I need to
// And for subsequent calls, the data will be pulled from cache and skip
// the query altogether
var_dump(unserialize($row));

Check out PHP docs on memcached for more info, there are some good examples and comments.