I have a mobile game API that does ~415rps and about 2100qps. (About 1500qps are in MySQL, more below.) We do that with one DB server with 122m RAM. While we can continue to scale RAM, we can see the handwriting on the wall - indexes will continue to grow, and we won't be able to scale vertically forever.

BTW - CPU usage on a SQL server is usually low - as long as indexes are in RAM, lookups should be CPU-light.

In any case, SQL considers the oldest record with equal importance as the newest, and so index sizes can (and likely will) eventually be larger than data in some cases (two-column relation-tables with 20m records for instance). The options are basically 1. shard, or 2. consider a different approach.

We use NoSQL to store certain types of info, just as SO does - Redis, Elasticsearch, and so on. The more data we store and the more we use it in new ways, the more types of non-relational DBs we use.

I don't think we'll ever have a situation where MySQL goes away entirely, but it stops being the singular solution pretty quickly as you scale up.