Menu
I've been reading a lot of articles that suggest putting a Memcached (or Velocity, etc) in front of a database is more efficient than hitting the database directly. It will reduce the number of hits on the database by looking up the data in a memory cache, which is faster than hitting the database.
However, SQL Server has it's own memory cache for objects in the database. When data is retrieved, SQL Server maintains its cache and will (if necessary) pull the row from it's memory and not hit the disk.
So if SQL Server has it's own cache, what is the benefit of an external Memcached (or similar) server?
Most of the articles I have been reading are around social networking sites, that mostly use MySql. However, an article about MySpace, that uses SQL Server, suggests caching is used on that system as well.
This article explains when caching should be used and this article is a counterpoint.
383
In SQL Server, the buffer cache is the memory that allows you to query frequently accessed data quickly.
When storing data, Redis stores data as specific data types, whereas Memcached only stores data as strings. Because of this, Redis can change data in place without having to re-upload the entire data value. This reduces network overhead.
Redis uses a single core and shows better performance than Memcached in storing small datasets when measured in terms of cores. Memcached implements a multi-threaded architecture by utilizing multiple cores. Therefore, for storing larger datasets, Memcached can perform better than Redis.
With Memcached you can build highly scalable distributed caching solutions designed to provide fast and consistent performance.
So if SQL Server has it's own cache, what is the benefit of an external Memcached (or similar) server?
Yes SQL Server has its own cache but he caches only:
- Query plans
- pages from the database files
but he does NOT cache:
- results from a query
e.g. you have a complex query which uses some aggregation on a lot of data ( think of: how many different countries we have in our customer database : SELECT DISTINCT Country from Customers GROUP BY country )
SQL Server will scan th WHOLE customer table, but your resultset will only a few entries long. When you reissue your query, SQL Server will reuse the query plan and will rescan the customer table, ( and if you are lucky the pages are still in memory )
When you use memcached you may store the few rows of your resultset and reuse them over and over again without connecting to the database server. So it takes some load from your database server.
NOTE: Beware of some stale data, if your data changes on the SQL server !!
80
Another benefit can also be that SQL Server is expensive to scale whereas adding a new web/caching server can be cheaper to achieve.
We use caching at an application level to store all sorts of things, not all of them from a database either. You may manipulate data objects in your code then add to cache for instance.
You can even store markup if necessary (output caching).
In one day by using caching we moved our site from being able to handle 150 concurrent session while stress testing to well over 800. I strongly recommend using it!!
31
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
