A well-architected caching system can lead to orders-of-magnitude performance improvement for some of your queries, allowing your existing database infrastructure to meet higher levels of overall demand.
A cache consists of a designated region of memory that can store the result sets of queries executed in your database. It acts like a fancy key-value store. The keys can be the queries themselves or some other pre-determined identifier, and the values stored are the results of running those queries on your DB. When the cache receives a lookup request, it can check for results before passing it on to the database. If it sees that it already has the results stored, it can just return those, allowing you to skip executing the query on the DB entirely. Since caches are generally fully in-memory, they can fetch these results sets exceptionally quickly, allowing for fast response times.
There are a variety of possible architectures for caching systems. In some cases, the caching logic can be implemented directly as a part of the application code. In this case, the application would have to be written to check the cache for values, and if they don't exist, then send a request to the database and update the cache when result sets arrive.
Caches can also be implemented as a separate layer or components in your application stack. There are several options for where to place this separate layer, with the pros and cons of each.
A look-aside cache is a system that sits outside your database's data access path. In this configuration, it is the application layers' job to first check the cache to see if it contains the data that is being requested. If it does, the cache will return it, and the application can avoid sending a query to the database altogether! This is known as a cache hit. If the data is not in the cache (cache miss), the application will be notified of the lack of result by the cache and then proceed to request the data from the database. It will also be the job of the application to ensure that, after it gets the result back from the DB, it places that data in the cache (known as hydration) for the next time it is required.
In a look-through architecture, the cache sits in line with the data access path in front of the database. All data requests would go to the cache system. The cache will either return the data from itself or request it from the database and hydrate itself before returning to the code. The advantage of this architecture over the look aside is that the process of checking for entries in the cache and updating elements in the cache are handled by a separate layer. However, a downside is that it gives the application layer less control to look at the cache or the database individually.
Some databases can even have integrated caches, allowing you to enable or disable them without the need to add a component to your architecture. MySQL used to have an integrated cache, but it was deprecated in 5.7 and removed in version 8. MariaDB is an example of a DBMS with a built-in query cache.
Say that we have the results of a query in the cache, but then we decide to add a new row to a table. Who is responsible for ensuring that the cache gets adequately updated? Well, it depends on the architecture. In a look aside architecture, the application would need to have the logic to do so. But how does it know that the results have changed? It's imporant to ensure you have rule(s) in place for cache invalidation.
One solution to the cache invalidation problem is to update the cache value whenever data changes (i.e., INSERT, UPDATE, DELETE). This method works well on data that changes infrequently. Another option is to set an expiration value on the data, where the value will stay the same for a fixed duration before being cleared from the cache. This is known as "time to live" or TTL since it sets a specific max time that a result can live in the cache.
Another issue is known as the thundering herd problem, where many callers are attempting to access or update data within the cache simultaneously. Typically, this happens when a cache value expires, and many concurrent requests suddenly fall through the database.
Using a query cache is a great way to boost the performance of your database system. In the next video, I show you two different ways that you can set up a cache for a MySQL database. One uses the ProxySQL, which requires manual setup and is a form of pass-through caching. The other is PlanetScale Boost, which is very easy to configure and has several advanced features that make it better than many other caching solutions.