How do I decide an initial in-memory cache size given my DB size and expected load throughput?

Question

(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?

Answer 1

Cache size should depend on access pattern; if the most common access pattern is 'show me the most recent 100 rows' then you'd want a cache large enough to hold those hundred rows plus a little more so they don't get pushed out by other random queries. Other patterns will have other requirements. The best you can do in the lab is try and simulate what you think real-world common and worst case access patterns are and make sure they work reasonably.

Answer 2

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.