If you are a newcomer to the caching world, you may probable thing: “Why should I use Memcached? MySQL cache is faster.”
This is true. But real life problems are quite different than theory. So, before I answer, let me set the stage using a simple scenario. I have a demo database table named “snames” which has 38.000 records with hierarchical data. And this is a query I need to execute:
select count(*) as leavesNum from
(
select * from snames
where (accepted = 1) and (path LIKE '/7/14/%' OR path = '/7/14')
) nodes
where not exists
(
select * from snames where accepted = 1 and parent_id = nodes.id
)
You don’t need to fully understand the table schema. I justed picked up a query with a level of complication out of a real case scenario I have worked with.
Now let’s see two cases. The one is using MySQLi to execute the query and the second is to retrieve the results from Memcached. For the sake of our example, Memcached and MySQL are installed on the same server and there is no significant load to this server. I timed the execution time having the MySQL cache enabled (for the first case) and these are the results:
MySQLi = 0.07 ms
Memcached = 0,35-0,65 ms
As expected, the database execution time is much faster than retrieving the results from Memcached.
So why use Memcached ?
1. MySQL cache cannot fit all query results. What happens when query diversity makes queries fall out of cached results? Let’s try to disable MySQL cache. Here are the results:
MySQLi = 49 ms
Memcached = 0,35-0,65 ms
Oops! Yeah, I know what you think! 🙂
2. What happens if in a high traffic application we have frequent writes but we can afford stale data in reads (e.g we are allowed to update the provided data every 3 hours). Writes are slower and invalidate the MySQL cache. This can reduce by a lot our capability to serve high volume of read queries. If we use Memcached for reads and MySQL for writes, we can quite solve this problem.
3. With a caching server you can flush only entries with certain prefix when a specific write action happens and so keep the major part of cache intact. Of course, Memcached does not support this functionality but you can do a trick here. You can define a prefix (or namespace) in your application for groups of keys. Any key that you set in Memcached as that prefix before it. Whenever you want to “delete” stuff from Memcached, just change the prefix. And whenever you want to lookup a key in Memcached, add that prefix to it.