Installing Memcached to Improve Database Performance

SQL databases are very good at storing and retrieving data, and they can do so quickly. However, no matter how well you tune your database servers there will come a time during periods of high traffic that your database server becomes a large bottleneck. By utilizing technologies like Memcache, we can keep results of frequently used database queries in a cache stored in RAM. Using the cached results significantly decreases that amount of time and effort to retrieve data and present in our application.

Memcache is what’s known as an in-memory key-value store. The key is a unique identifier that is used to quickly search for cached strings or objects. The value is the data that has been cached. For the purpose of storing database query results, the key will typically be the query used on your database.

Key-value Pairs

A key-value pair is essential an array of data. If you have any experience in programming, you will have a good understanding of how data is stored in Memcache. If were to present a key-value for a database query in an easily read form, it would look similar to the example below.

Searching for long text strings isn’t very efficient, so storing your keys as such is a bad idea. The example above is used just to illustrate how data is stored in cache. In a typical environment you would convert your key (the SQL query) into a MD5 hashed value, for example, before storing or retrieving data from Memcache.

Typical Infrastructure

The diagram below illustrates how your infrastructure will typically look when you deploy Memcache in your environment. You will notice that the Memcache server doesn’t communicate directly with your database servers. Instead, they sit in their own pool and your application does all of the work.

Your application will first query the Memcached server(s) for cached database results. If nothing is found, the application will then query your database server(s). Any cached results from the database server will then be written to the Memcache server(s) by your application.

Of course, you can’t just simply drop a Memcached server in and expect your application to be able to use. Your application will have to be modified to utilize the Memcache server. This is outside of the scope of this tutorial, but it is important that you know.

Hardware Requirements

The hardware requirements for Memcache servers is low. There is very little CPU processing involved and virtually no disk storage needed in addition to the operating system. The only resource you need is RAM. How much will depend on what is being cached and the duration of the caches.