This subject is much larger than the blog title suggests.  I’m going to discuss some basics of using a caching system to take the load off of your database and speed up your website.  I’ll also discuss how cache should be handled by your software and what the pitfalls of caching can be.

Caching Data Calls

First of all, you can speed up your database routines by using a cache system configured as a helper.  Here’s a diagram of what I mean by configuring a cache system as a helper:

Basically, what this abstract diagram shows is that the website front-end retrieves it’s data from the database by going through the cache server.  Technically, that is not how the system is setup.  The cache and database are accessed through the back-end, but the cache instructions are setup with the following flow-chart:

You’ll notice that the first place to check is the cache system.  Only if the data is not in the cache, does the program connect to the database and read the data from there.  If the data is read from the database, then it must be saved in the cache first, then returned to the calling method.

One other thing to note here, is that it is vitally important to design and test your cache system in a way that it will work without crashing if the cache server is off or not responding.  If your cache server crashes, you don’t want your website to crash, you want it to continue operating directly from the database.  Your website will operate more slowly, but it will operate.  This will prevent your cache system from becoming a single point of failure.

Why is Caching Faster?

Programmers who have never dealt with caching systems before might ask this question.  It doesn’t seem like a good way to speed up a system by just throwing an extra server in the middle and adding more code to every data read.  The reason for the speed increase is due to the fact that the cache system is typically RAM based, where databases are mostly hard-drive based (though all modern databases use caching of their own).  Also, a Redis server costs pennies compared to a SQL server just in licensing costs alone.  By reducing the load on your SQL server you can reduce the number of processors needed for your SQL instances and reduce the amount of your licensing fees.

This sounds so magical, but caching requires great care and can cause a lot of problems if not implemented correctly.

Caching Pitfalls to Avoid

One pitfall of caching is how to handle stale data.  If you are using an interactive interface with CRUD operations, you’ll want to make sure you flush your cached objects for edits and deletes.  You only need to delete the key of the data in the cache server that relates to the data changed in the database.  This can become complicated if data being changed shows up in more than one cache key.  An example is where you cache result sets instead of the raw data.  You might have one cache object that contains a list of products at a particular store that contains the store name and another cache object that contains product coupons offered by a store containing the store name.  Caching is not a normalized structure and in this instance the cached instance is matched to the web page that needs the data.  Now, think about an example where the store name is changed for one reason or another.  If your software is responsible for clearing the cache when data is changed, then the store name change administration page must be aware of all cache keys that could contain the store name and delete/expire those keys.  The quick and dirty method of such an operation is to flush all the cache keys, but that method could cause a slowdown of the system and is not recommended.

It’s also tempting to cache every result in your website.  While this could theoretically be done, there is a point where caching can become more of a headache than a help.  One instance is in caching large result sets that constantly change.  One example is if you have a list of stores with sales figures that you refer to often.  The underlying query to compute this data might be SQL intensive, so it’s tempting to cache the results.  However, if the data is constantly changing, then each change in data must clear the cache and the underlying operation of caching and expiring cache keys can slow down your system.  Another example is to cache a list of employees with a different cache key per sort or filter operation.  This could lead to a large number of cached sets that can fill up your cache server and cause the server to expire cached items early in order to make room for the constant saving of new data.  If you need to cache a list, then cache the whole list and do your filtering and sorting in your website after reading it from the cache.  If there is too much data to cache, you can limit the cache time.

Sticky Cache

You can adjust the cache expire time to improve system performance.  If your cached data doesn’t change very often, like a lookup table that is used everywhere in your system, then make the expire time infinite.  Handle all cache expiring through your interface by only expiring the data when it is changed.  This can be one of your greatest speed increases.  Especially if you have lookup tables that are hit by all kinds of pages, like your system settings or user rights matrix.  These lookup tables might get hit several times for each web page access.  If you can cache that lookup, then the cache system will take the hit instead of your database.  This type of caching is sometimes referred to as “Sticky” because the cache keys stick in the system and never expire.

Short Cache Cycles

The opposite extreme is to assign a short expire time.  This can be used for instances where you would cache the results of a list page.  The cached page might have an expire time of 5 minutes with a sliding expire time.  That would allow a user to see the list, click on a next page button until they find what they are looking for.  After the user has found the piece of data to view or edit, the list is no longer needed.  The expire time can kick in and expire the cached data, or the view/edit screen can expire the cache when the user clicks on the link to go to that page.  It can also reduce database calls by using a cached set that is just the raw list.  Sorting and searching can be done from the front-end.  When the user clicks on the header of a column to sort by that column, the data can be re-read from the cache and sorted by that column, instead of being read from the database.

This particular use of caching should only be used after careful analysis of your data usage.  You’ll need the following data points:

  • Total amount of data per cache key.  This will be the raw query of records that will be read by the user.
  • Total number of times database is accessed first time arrival at the web page in question, per user.  This can be used to compute memory used by cache system.
  • Average number of round trips to the database the website uses when a user is performing a typical task.  This can be obtained by totaling the number of accesses to the database by each distinct user.

Multiple Cache Servers

If you get to a point where you need more than one cache server there are many ways to divide up your cached items.  If you’re using multiple database instances, then it would make sense to use one cache server per instance (or one per two database instances depending on usage).  Either way, you’ll need to make sure that you are able to know which instance your cached data is located on.  If you have a load-balanced web farm setup with a round-robin scheme, you don’t want to perform your caching per web server.  Such a setup would cause your users to get cache misses more often than hits and you would duplicate your caching for most items.  It’s best to think of this type of caching as being married to your database.  Each database should be matched up with one cache server.

If you have multiple database instances that you maintain for your customer data and you have a common database for system related lookup information, it would be advisable to setup a caching system for the common database system first.  You’ll get more bang for the buck by doing this and you’ll reduce your load on your common system.  Your common data is usually where your sticky caching will be used.  If your fortunate, you’ll be able to cache all of your common data and only use your common database for loading the system when there are changes or outages.

Result Size to Cache

Let’s say you have a lookup table containing all the stores in your company.  This data doesn’t change very often and there is one administrative screen that edits this data.  So sticky caching is your choice.  Analysis shows that each website call causes a read from this table to lookup the store name from the id or some such information.  Do you cache the entire table as one cache item or do you cache each store as one cache key per store?

If your front-end only looks up a store by it’s id, then you can name your cache keys with the store id and it will be more efficient to store each key separately.  Loading the cache will take multiple reads to the database, but each time your front-end hits the cache, the minimum amount of cached data is sent over the wire.

If your front-end searches or filters by store name or by zip code or by state, etc.  Then it’s wiser to cache the whole table into the cache system as one key.  Then your front-end can pull all the cached data and perform filtering and sorting as needed.  This will also depend on data size.

If your data size is very large, then you might need to create duplicated cached data for each store by id, each zip code, each state, etc.  This would seem wasteful at first, but remember the data is not stored permanently.  It’s OK to store duplicate results in cache.  The object of cache is not to reduce wasted space but to reduce website latency and reduce the load on your database.


Leave a Reply