https://www.youtube.com/watch?v=rSCDuZLP9UM
@bobbycrosby9765
5 hours ago
We did a lot of this stuff for our monolithic Facebook apps back in the late '00s.  Oh, the memories.

During peak load we would have around 2k writes/sec to our database, and around 15k reads/sec even with heavy caching.  I only mention this because people talk about "scaling" but rarely talk actual numbers.

The database was really the hard part.  The code was shared nothing and we could just pile another server on top, but the database was another story.  We had something like 5 webapp+memcached servers, but a total of 9 MySQL servers.

A seemingly mysterious problem we ran into was our working dataset no longer fitting into the database's memory.  Previously instant queries start taking tens of ms, which is way too slow - this was an easy fix, we just bought more memory (eventually, 128GB per server).  We also ran into a problem of replication lag - replicated servers couldn't keep up with the master since replication in MySQL was single threaded.  To help we had replicas dedicated to certain groups of tables and skip the rest.  We also made sure to hit master and inject the user's freshest data where necessary.

A problem with a lot of memory, at least in MySQL, was that cold restarts were painful.  After coming back up, a database server wasn't ready to serve requests - it took it an hour or two to "warm up" before it could serve live requests without choking.

I believe the not-in-cache problem is a "thundering herd" - as in, all the requests coming in stampede the database and kick over your sandcastle when it isn't in the cache.  We resolved this by also adding a cache-lock key in memcached: if something isn't in the cache, before going to the database, you must set the cache lock key.  If you fail, you aren't allowed to go to the database.  There's tradeoffs here - the process that has the lock key could die.  We set it to expire at something somewhat reasonable for our system - around 100ms.

We were lucky in that our database schema was relatively mundane.  It would have been much more difficult with some of the more complex schemas I've worked with over the years.

It would be a lot easier to accomplish these days, at least for this particular project.  There wasn't much in the way of actually transactional data, and for the hardest hit tables we could have easily gotten by with some of the NoSQL databases that came a bit later.  And nowadays, hardware is much more powerful -  I would have killed for an SSD instead of spinning rust.