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.

Answer 2

There are several examples on how memcache works. Here is one of the links.

Secondly, Memcache can work with or without MySQL.

It caches your objects which are in PHP, now whether it comes from MySQL, or anywhere else, if its an PHP Object, it can be stored in MemCache.

APC gives you some more functionality than Memcache. Other than storing/caching PHP objects, it also caches PHP-executable-machine-readable-opcodes so that your PHP files won't go through the processes of loading in memory-> Being Comiled, rather, it directly runs the already compiled opcode from the memory.

Answer 3

If your data keeps changing(between requests) then caching is futile, because that data is going to be stale. But most of the times(I bet even in your cache) multiple requests to database result in same data set in which case a cache(in memory) is very useful.

P.S: I did a quick google search and found this video about memcached which has rather good quality => http://www.bestechvideos.com/2009/03/21/railslab-scaling-rails-episode-8-memcached. The only problem could be that it talks about Ruby On Rails(which I also don't use that much, but is very easy to understand). Hopefully it is going to help you grasp the concept a little better.