Avoiding Database Overload with Memcached
Overview
Probably the biggest challenge for a newly popular site these days is scalability. The most common point of failure in applications seems to always be the database. These days our applications are totally reliant on some sort of relational database to store the information for our website’s content. Each and every page load brings many queries that read or insert information into this database all while rendering the web page in a timely manner. This isn’t so much a problem for your Mom & Pop business, but when your website starts to get some real traffic pointed towards it, your underlying architecture that you thought was impervious to anything will crumble before your eyes. The solution? Stop making so many queries to your database.
Caching
Caching is nothing new. Servers have been caching content for many years quite successfully. There are many different ways you can cache data in your application but right now we will just be talking about using Memcached. Memcached is a high-performance, distributed memory object caching system. So what does this mean? Basically it can store any kind of value that you would normally store in your database and retrieve it much much faster than your database can.
Memcached Flow
When you implement memcached you use it along side of your database. Neither takes the place of each other, memcached just takes some of the load off of your database server after your database server has previously done the grunt work. It works on a key pair system. Meaning it has no tables, rows, columns etc. You give memcached a name and pass it data and it stores that data just as you passed to it. You can pass it an object, array, binary, etc and it will output the same way when you go to retrieve it.
So how does this all work with the database? First, you add code that checks to see if the memcached object (blogpost-45 for our example) exists before it checks the database for the data. If it does you return that data and never touch the DB. If the memcached object does not exist you retrieve data from your database, take the array that is passed back and store it in memcached with the key of “blogpost-45″. So the next time you try to get the data for blogpost-45 it will return from memcached and not your database. This is where the load savings occurs.
So what happens when I want to change the data? When you change the data in your database you should delete the key in memcached. You shouldn’t re-add the data after you delete it, let the select query do that to avoid complications.
CodeIgniter + Memcached
I have created a CodeIgniter Library that can help you get a jump start on your integration with memcached, its available on my github page and will be updated when I get time.
Hello, I’ve been using this library awhile and would like to submit a patch:
public function add_server($server)
{
extract($server);
return $this->m->addServer($host, $port, $weight);
}
is NOT compatible with 2.0.0+ versions of memcached as $persistent is added between $port and $weight, which defaults to TRUE which any value in $weight will set as well. The new line should be like this:
public function add_server($server)
{
extract($server);
return $this->m->addServer($host, $port, FALSE, $weight);
}
OR $persistent if you change the config-file as well.
This unpatched problem caused our webservers to die randomly for about two weeks, *VERY* nasty problem. =)
Other than that thanks for an awesome wrapper! =)
@Kalle
Thanks for the patch! ill get this into the github repo ASAP.
Hi Tom,
I’m using your memcached library for CI. So far its pretty cool and wraps all the neccessary memcached functions.
I’m using ActiveRecord for most of my CI queries. How does one store a activerecord result set in memcached?
if i do this
$query = $this->ci->db->query($sql);
$this->memcached->add($key, $query, $expiration);
and then shortly after
$query = $this->memcached->get($key);
$query never contains a resultset
what am i missing?
thanks!
@zayin – dont store the $query object. store the result_array or row_array (depending if your returning one row or many).
$query = $this->ci->db->query($sql);
$this->memcached->add($key, $query->result_array(), $expiration);
Hi Tom,
I think You have error with config in Library.
I mean expiration.
$this->config['expiration']
>
$this->config['config']['expiration']
@Viktor – Thanks! I fixed it before on my own project but forgot to update the repo. It is updated on github now.