MySQL Sharding at Quora
quora.com
quora.com
That is, I can't imagine anyone I know having deliberately visited Quora and used it as a networking site. But I can sure imagine most of the non-tech community searching for a random fact and clicking a Quora link. I'd have to wonder what that means for traffic/load. Certainly I'd expect you could cache a substantive portion of page hits.
A couple of years ago, our infrastructure team
implemented the ability to move a table from one
MySQL host to another. This allowed us to have
multiple masters with different tables on different
masters, which we refer to as “vertical sharding”.
Each one of these masters, along with its slaves,
is called as a “partition” at Quora.
Naming things is hard, but why would they choose a term ("partition") that has already existed for many decades in the same context?
That's nonsensical.
Here's a more relevant link. https://dev.mysql.com/doc/refman/5.7/en/partitioning.html
It's like if you had a MySQL table that contained some sort of metadata that helped you find other data more quickly. Would you call that table an "index?" I mean, yeah, sure, it's an index (in the abstract sense of the word) but that would be awfully confusing in in the context of a MySQL database since it already has a pretty specific meaning there.
Quora took an existing database practice that already has a name (sharding) and decided to refer to it by the name of another existing database practice (partitioning).
For your analogy to make sense, we'd have to use two established titles with very specific meanings.
"Here at Quora, we have medical doctors. Internally, we refer to them as nurses."
And any other logical word I can imagine ("version", "copy", "split", "part", etc.) seems just as confusing as "partition". And at least "partition" decently suggests the general idea. The other strategy -- using a made-up word like "flimflam" -- is potentially even more confusing.
What are you suggesting instead?
If they were using MySQL's built-in capabilities to do such a thing, and wanted to combine things at the database level there is already a specific term for that: federation. https://dev.mysql.com/doc/refman/5.6/en/federated-create-ser...
Or perhaps they took a more service-oriented approach, in which the databases don't talk directly to each other but instead expose their data via some application layer. In which case there's a term for that: a service-oriented architecture.
Nice article from Raph Koster. Especially if you played Ultima Online by any chance.
'obvious in retrospect' I suspect to a lot of teams that add sharding to their system
That was probably before the change last fall [1] to have an SLA for single-"node" instances. At $0.90/hr per "node" for a Regional setup in Iowa, that's just over $650/month. Note that I put "node" in quotes, because it's hard to remember that "num nodes" != num replicas [2].
We "require" (I don't know if this is a hard requirement, but let's assume it is) a node per 2 TB of storage [3], so the example for Quora of "tens of TB without counting replicas" would probably be 15 nodes at 30T and say 50 nodes at most (100TB).
Assuming 50T as a balance, you'll need at least 25 nodes and let's assume regional pricing (since the MySQL example isn't going to be multi-regional by comparison). That's 25 x $.9/hr x 730 hours/month (~$16500) plus 50T of storage at $.3/GB/month (another $15k). So just over $30k/month or $360k/year. As for qps, we quote the multi-regional variants at 7k read qps per node (so 175k in this example) and ~2k write (so 50k).
That's all in though, so you'd need to compare to the cost of running a fleet of MySQL boxes too (at a minimum you'll also need ~75-ish boxes, just like the 25x3 for Spanner), 150T of raw storage (assuming 1 master and 2 read replicas per shard, which is pretty common) and then Zone to Zone egress (I believe Quora is on EC2). I'm assuming they're using gp2 volumes at $.10/GB/month, so that's the same $15k/month of storage. Being cost-conservative and assuming r5a's and 3-yr RIs, you pay about $18/month per thread, so we can buy up to 833 threads of r5a for the remaining $15k/month. That's maybe close to feasible (~11 threads per box), but honestly you probably need 16+ or even 32 for traffic this high (depends on query caching).
tl;dr: I don't think Spanner's pricing is higher than the sharded mysql fleet in this example! I do think it's not available on AWS or on-premises though, so that's a fine disqualifier for Quora.
[1] https://cloud.google.com/spanner/docs/release-notes#Septembe...
[2] https://cloud.google.com/spanner/docs/instances#available-co...
With Vitess I believe most get started with just deploying VTGate and just proxying their existing queries to their existing MySQL server, then once that is working nicely, start to range shard and expand the cluster sitting behind VTGate.
I realize depending on your data model some of this is unavoidable once you shard, but it sounds like they did this even before sharding.
What's the reason to do that? Seems like you either end up with long transactions or doing MVCC in app code
> Joins inside MySQL were strongly discouraged in the codebase so that we would have more freedom in choosing which tables to move for scale out. The result is that we do very few joins in MySQL.
And that comes at the end of the section describing their “vertical scaling” strategy and its motivations.
As for your hypothesis about long transactions, this is not actually a problem in my experience (on larger scale CRUDy apps, specifically). In this scenario, you’re choosing to trade consistency for the ability to break out arbitrary tables to discrete databases. You’re going to lose that consistency after splitting data between multiple databases anyway (we’ll gloss over distributed transactions here) so why not be proactive about it? And on a “simple” platform like Quora, data is going to have a predominantly hierarchical relationship (users have posts which have comments which have likes, etc.) so internal consistency between content is rarely a significant concern.
Another reason to avoid joins is that they it can be difficult to consistently write joins that execute quickly. Often, a poorly performing join can be written as a sequence of queries which each perform well, and joined with reasonable performance on the client side. Usually, it's easier to scale the client (webserver) tier than the database tier, so moving work to the client makes sense for that reason too.
Of course, this has to be considered in the context of the service. These days it's pretty easy to get a database server with 64 cores and a couple terrabytes of ram, and several terrabytes of fast SSD storage. You can do a lot with that, and you may never need to shard.
What is the point of using a RDBMS if you never do queries?
What is HNs opinion on MySql vs MariaDB?
The only difference I remember from having been forced to use MariaDB in the past was that one of the 5.x releases didn't support NOT NULL virtual columns when the equivalent 5.x MySQL release did support them... to me, MariaDB is just a strange, slightly incompatible fork, but maybe someone else here has more experience with both. (I prefer postgres)