Where to place an in-memory cache to handle repetitive bursts of database queries from several downstream sources, all within a few milliseconds span

Question

I'm working on a Java service that runs on Google Cloud Platform and utilizes a MySQL database via Cloud SQL. The database stores simple relationships between users, accounts they belong to, and groupings of accounts. Being an "accounts" service, naturally there are many downstreams. And downstream service A may for example hit several other upstream services B, C, D, which in turn might call other services E and F, but because so much is tied to accounts (checking permissions, getting user preferences, sending emails), every service from A to F end up hitting my service with identical, repetitive calls. So in other words, a single call to some endpoint might result in 10 queries to get a user's accounts, even though obviously that information doesn't change over a few milliseconds.

So where is it it appropriate to place a cache?

1. Should downstream service owners be responsible for implementing a cache? I don't think so, because why should they know about my service's data, like what can be cached and for how long.

2. Should I put an in-memory cache in my service, like Google's Common CacheLoader, in front of my DAO? But, does this really provide anything over MySQL's caching? (Admittedly I don't know anything about how databases cache, but I'm sure that they do.)

3. Should I put an in-memory cache in the Java client? We use gRPC so we have generated clients that all those services A, B, C, D, E, F use already. Putting a cache in the client means they can skip making outgoing calls but only if the service has made this call before and the data can have a long-enough TTL to be useful, e.g. an account's group is permanent. So, yea, that's not helping at all with the "bursts," not to mention the caches living in different zone instances. (I haven't customized a generated gRPC client yet, but I assume there's a way.)

I'm leaning toward #2 but my understanding of databases is weak, and I don't know how to collect the data I need to justify the effort. I feel like what I need to know is: How often do "bursts" of identical queries occur, how are these bursts processed by MySQL (esp. given caching), and what's the bottom-line effect on downstream performance as a result, if any at all?

I feel experience may answer this question better than finding those metrics myself.

Asking myself, "Why do I want to do this, given no evidence of any bottleneck?" Well, (1) it just seems wrong that there's so many duplicate queries, (2) it adds a lot of noise in our logs, and (3) I don't want to wait until we scale to find out that it's a deep issue.

Answer 1

Whether this is a big issue for you or not, it is a really good use case for caching. The cost of transmitting data from the database to your server is pretty much guaranteed to be much slower than retrieving it from memory. It might be 1000 times faster or more. But if it's taking 100ms to pull the data from the server, do you really care? Getting some metrics will help here. Better yet, bake them into your application logging because they can change for any number of reasons.

The reason I am asking about whether this is a restful web service is that, when properly designed, caching GET calls is trivial, even automatic from a client perspective. In fact the browser you are using right now is likely caching much of what you are looking at on this page.

This is one of the big benefits of (proper) REST-style services. Caching is stupid-simple and you can't beat the client cache. Coming full-circle: the time to pull data across the network is far slower than reading from local memory.

Answer 2

We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil. Yet we should not pass up our opportunities in that critical 3%.

I'd definitely wait for some evidence of this being important before coding additional functionality to handle it. Caching is a form of complexity. Moreover, it can make performance worse when used inappropriately. Caching relies on getting a high hit rate. If you get it, it's faster, but if you do not you're effectively doing a worthless extra step before doing the main work to fulfill the request.

This doesn't mean that you have to wait for it to be a system critical bottleneck, but you do want hard evidence that you will both have a high cache hit rate and that caching the data will yield some sort of benefit. If the volume is sufficiently low or the query sufficiently cheap it may be technically correct while providing little or no value.

Assuming, though, that you have all this well in hand and that you will definitely provide value for caching repetitive queries, I think you can feel comfortable implementing caching in your endpoints prior to making the database call (so option #2). This means that the little tinkerings and optimization to get the best throughput don't effect the clients directly and that you are free to change caching strategies and technologies without forcing changes outside of your API.