edit: a few additional details - from a few years ago, alas http://highscalability.com/blog/2011/3/3/stack-overflow-arch...
This math is obviously an average, but even if you halve that number you are still very much above everything most of us web devs are ever going to build or even see.
Also the data size they have of a TB or so means they can easily ("cheaply" enough) shove it into RAM, using a database like VoltDB (horizontal scaling/sharding, which seems like it'd be a perfect fit for vote recording).
There's still fun stuff outside of large sites. I'm writing a network search engine (record every packet and index it all) and even for small $10M telco, it can easily be a TB a week in signaling, plus a few TB of audio (but we don't tend to record every audio stream).
You'd be surprised how many intermediaries a call will go through. Sometimes even calling a neighbor, on landlines, will hit a tiny VoIP company in the middle. It's fair to assume, in such cases, random techies can record all sorts of end user calls without any real oversight. But I'm unaware of anyone actually bothering with this, usually the signalling provides all you need to figure problems out.
The only routine call recording I'm aware of is for 911 calls, for obvious reasons.
Anyways, my only point was there's lots of data even outside top websites and you can still have fun writing cool algorithms and systems. E.g. one of my todos is to implement SSE-optimized indexes. Maybe only a few percent boost, but fun and fairly justifiable use of time:)
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.
I don't know how many users you have now and how quick you grow, but you can buy a server with more than 10x as much RAM as you have now. You may not "be able to scale vertically forever", but if you hit that spot you reached at least 5x Stack Overflow level. Congrats!
> 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.
Sure why not. Use what fits your needs. But I guess you used Redis and Elasticsearch for it's features, not because it scales better - for that, some more RAM would have done it.
Huh?
I assume you're not referring to 122 MB of RAM. What's the "m"?
122GB.
That's still an abnormal amount, but it's in AWS RDS, on a db.r3.4xlarge instance.
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Concep...
Here's a grab from bosun taken just now of web tier load on those 9 servers: http://i.imgur.com/RJeEiYV.png
And here's a view of overall traffic coming into the NYC data center (except WebSockets): http://i.imgur.com/LV30989.png
Nick Craver Stack Exchange Developer & SRE
Companies with fewer users could have scaling problems earlier if their load is very write-heavy, if they generate artificial load from things such as crawling, if they have stricter latency requirements...
Moreover, note that Redis server. Stack Exchange uses caching very agressively [1], it is not the relational database doing all the work.
All that being said, I don't disagree in general: most startups should stop worrying about how they will scale their DB and stick with simple solutions that work.
For example in my company, we went with Mongo because as I was told: "that's the industry standard", "unlake relational databases it let us scale and handle our huge amount of data" and also "it is schemaless so we are no longer constrained by DBAs".
Side note, the schemaless part is a bit fake benefit, because the data still has a schema, but the schema is now part of your. It seems like it makes things a bit easier, but you'll pay it with interest because as you are making changes the application is becoming more complex. Also I know there are exceptions, but if your data is highly relational (like in my company) storing it in NoSQL will come back to you back with vengeance.
Back to the original topic, we have several databases, we split them by function. One of them is a database that is used for geolocation (a bit more than that, but roughly matching IP to zip, or latitude/longitude to zip). I should also add that we previously used Coherence to hold that data and we keep it in RAM within 32 instances of m3.large (on AWS). Recently we decided to migrate away from Coherence to Mongo and it is considered a success because we replaced 32 of those m3.large instances with just 3 x m3.large instances. Huge savings it seems.
In addition to those instances keeping the data we have an infrastructure developed to move data from our authoritative source (Postgres databases) to Coherence/Mongo instances. We use AMQP for that :) so we have nodes that are used solely as brokers (and of course we have 10 of them because a single one was crashing under the load) we have producers(that fetch data from Postgres and push as a message) we have consumers (that take the data and put to Mongo) and several other nodes to control all of that and of course that's also "industry standard".
Of course we need all of that because (and this was especially true with Coherence) if something happens all data is lost and we need to "bulk-load" it. That process takes several hours because AMQP was not designed for huge binary transfers like this because it is a messaging protocol. And because we can't afford to be offline for entire day when something like this happen we have multiple environments so we can cut over to a backup one. Mongo supposedly helps here a lot, because the data is also kept on disk.
It's kind of interesting to see how one decision made many years ago in the past can affect bunch of subsequent ones.
Anyway, I did POC out of curiosity. My reasoning was that if we treat Postgres as the authoritative source, why not keep it in a way that is directly query-able (currently the data is stored in a way that makes it easier to bulk load it).
The results were better than expected. Not only it is faster (with PostGIS and ip4r extensions every query takes fraction of millisecond to run), but also the data which was about 8GB of it, after loading it to Postgres it was only 500MB.
How is that possible? Well, because Mongo (and I suspect Coherence) has just simple indexing capability, in other to have a reasonable performance, developers decided that for every row that holds IP range they would simply list all IPs in that range as 64 bit integers and placed an index on that. I would love to see what would they think of if they would have to add support for IPv6 as well[1].
I'm currently having an uphill battle to convince other teams that this is the right path. I really would like to skip the crap and go with this higher, but that's probably unwise.
[1] 2^128 is more than estimated number of stars in the universe.
Because the data is stored in such inefficient manner in order to scale at one point they decided to separate it into different databases by type of the data, I only used geolocation as example.