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