1

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

3
  • 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, 2020 at 23:03
  • 3
    I’m voting to close this question because asking for a specific technical configuration of Redis for its purpose seems more appropriate on dba.stackexchange.com/questions/57323/search-a-redis-database
    – Walfrat
    Mar 26, 2021 at 8:10
  • All free memory....Place your database on a dedicated machine and dedicate all free memory to cache.
    – Pieter B
    Aug 24, 2021 at 10:47

2 Answers 2

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.

0

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.

1
  • 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? Jun 28, 2020 at 22:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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