MySQL Sharding at Quora(quora.com)
quora.com
MySQL Sharding at Quora
https://www.quora.com/q/quoraengineering/MySQL-sharding-at-Quora?share=1
11 comments
Is Quora really big outside the US or something? I find it difficult to reconcile their scale challenges with the fact that I don't know a single person that uses Quora outside of when the come up in google search results.
I suppose I would reply with the question "is being the top rated Google answer for all sorts of questions enough?".
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.
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.
While this is a great read, I'm excited to find out more about the tech challenges behind their login wall.
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.
Hmm... well I'm not aware of an existing term that refers specifically to either the master or slave in a master-slave scenario.
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?
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?
I'm not sure exactly what they were doing. It doesn't sound like anything novel. (Also doesn't sound like master/slave, which typically refers to a replication setup) Sounds like they had some tables in one database and some tables in another.
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.
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.
What's nonsensical about it? A partition is just a logical split of your data. You can do horizontal partitioning (aka "horizontal sharding"), like splitting the same table to different databases by key. Or you can do vertical partitioning, where you put different tables in different databases.
I always thought vertical partitioning meant to split by columns (that is, have fewer columns per table, and you need to join to get the original back).
For one thing, there's already a term for what they're doing. It's called sharding. Why make shards and decide to call them partitions, when "partitions" means something else in this context and there is already a name (shards) for what's being done?
https://en.m.wikipedia.org/wiki/Partition_(database)
https://stackoverflow.com/questions/20771435/database-shardi...
https://stackoverflow.com/questions/20771435/database-shardi...
They're talking about MySQL specifically, not databases in the abstract. Sure, yeah, you can call anything "paritioning" in a general sense when it comes to data storage, including building a physical wall between your database servers, or adding dividers to your shoebox full of recipe cards.
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.
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.
You must not like the term computer to refer to electronic computers because it already refers to a human profession.
https://en.m.wikipedia.org/wiki/Computer_(job_description)
https://en.m.wikipedia.org/wiki/Computer_(job_description)
Poor analogy.
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."
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."
https://www.raphkoster.com/2009/01/08/database-sharding-came...
Nice article from Raph Koster. Especially if you played Ultima Online by any chance.
Nice article from Raph Koster. Especially if you played Ultima Online by any chance.
'shard user scale data, centralize or mirror small scale data' (the A1 A2 B C D E diagram) is a useful point
'obvious in retrospect' I suspect to a lot of teams that add sharding to their system
'obvious in retrospect' I suspect to a lot of teams that add sharding to their system
Would Google Cloud Spanner work well for a workload like this one? Why or why not?
By all means yes, I'd argue spanner would be the ideal candidate for the task. It scales beyond anything else I've come across. Mind you, so do the costs of running it. I've only used it for training exercises and numbers add up like nothing else. IIRC running a production instance at the bare minimum would set you back around 9k/month.
Disclosure: I work on Google Cloud.
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...
[3] https://cloud.google.com/spanner/quotas#notes
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...
[3] https://cloud.google.com/spanner/quotas#notes
It'd probably work. They cite cost as a reason for not going third party in the article.
Since it's range based sharding, was Vitess ever considered (a popular CNCF MySQL range sharding cluster approach that came out of YouTube)?
Different company. We evaluated Vitess in comparison to ProxySQL. While Vitess was very promising, it was a large refactor and an all or nothing proposition to switch to it. ProxySQL allowed us to move to it piecemeal and adopt a crawl, walk, run solution in adopting it.
Interesting, didn't realize it imposed a lot of client side changes, I'm curious to hear your experience on that? Was it the change in MySQL compatibility that makes it an upfront cost?
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.
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.
It was several years ago and I don't recall the details. And it is always possible we missed something or that things have changed on the Vitess side. I do recall the impression that we would have to have a "cut over" and it would be going from zero queries to full load. It seemed doable but leadership decided the risk was too high.
We've come a long way in Vitess in terms of usability. And we're adding even more tooling to support onboarding and migrations.
> The result is that we do very few joins in MySQL
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
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
As a sibling has noted, joins across database hosts with SQL is somewhere between hard and impossible. So it made sense to discourage and remove them on that basis, given that the datasize was growing/has grown beyond what can be managed reasonably on a single host.
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.
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.
The preceding sentence is
> 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.
> 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.
> The result is that we do very few joins in MySQL
What is the point of using a RDBMS if you never do queries?
What is the point of using a RDBMS if you never do queries?
Comment from the representative (probably) of the "Chinese Quora" is interesting as well :)
Thanks! Posting the case study here for more information: https://pingcap.com/success-stories/lesson-learned-from-quer...
Interesting that they still use MySql and did not switch to MariaDB.
What is HNs opinion on MySql vs MariaDB?
What is HNs opinion on MySql vs MariaDB?
Why do you find it interesting that they didn't switch? What is the compelling reason to switch if you don't care about Oracle owning MySQL?
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)
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)
Their site(use case) looks almost similar to stackoverflow.com.
The stackoverflow.com has few MS SQL servers with a Memcached as cache.
I guess if reads are the heavy load it makes perfect sense just to continue with your current SQL DB + sharding.
Did stack overflow even do any sharding?
Here is what stack overflow does: https://nickcraver.com/blog/2016/02/17/stack-overflow-the-ar... It's from 2016, but probably hasn't changed much. Also StackExchange: https://stackexchange.com/performance
This is going to be very useful for one of the projects I’m working on, thanks for sharing