(Purely for learning purposes)

Say the DB contains 1 billion rows with 200 bytes per row = 200 GB of data.

The traffic at peak is 1000 requests/s, with each request asking for one DB row.

What cache size would I begin with to ease off the load on the DB? I realize that this is determined best empirically and can be tuned as time goes on.

Caches are usually not too large given memory constraint (unless you go for a distributed cache like redis), so we can't have the in-memory cache be more than say 200 MB of space, which accounts for way less than 1% of the DB size and seems too small. The cache might just spend all its time being 100% occupied with 95% misses and evicting entries and caching new entries using a simple LRU scheme.

Perhaps there's no point bothering to cache anything in-memory here. In that case, how would you go about coming up with an initial cache size in a redis cache?

  • 2
    each request asking for one DB row Are these all different rows or are the same rows being requested multiple times? That's a huge factor in how you design your cache. – Dan Wilson Jun 27 '20 at 23:03

It partly depends on how much memory you have - my laptop has 16GB, 200MB seems a bit mean if you have something like that, but it really depends on the access patterns.

It will also depend on how fast your secondary storage is - my laptop has 475 GB of solid state memory. That should easily handle 1,000 reads of 200 bytes per second, without any caching.

What sort of data is in these billion rows? How is it accessed and for what reason?

In most database applications I have made, the frequently accessed data is a relatively small subset of the whole - for example a 200GB database might be mainly document storage, with most documents being very rarely accessed, the "core" database which is frequently accessed might be only 1GB, say.

  • Thanks George. Yes, with growing memory, one could envision a 1GB cache. How do find out the subset of the most frequently accessed data? Do you track it over a period of time and then size the cache? Or would you start with a base size of 1GB and then grow/shrink it as time goes? – user2495123 Jun 28 '20 at 22:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.