Ask HN: Real-world anecdotes of MySQL at scale?
What is the biggest MySQL database you've every worked with?
Any interesting stories to tell?
I'm trying to get a sense of what MySQL is like at massive scale.
Thanks ^_^
Any interesting stories to tell?
I'm trying to get a sense of what MySQL is like at massive scale.
Thanks ^_^
96 comments
[ 2.0 ms ] story [ 141 ms ] threadhttps://vitess.io/
depends what you mean by massive scale, but mysql can certainly handle a website of say, stackoverflows size, on beefy hardware with read replicas. facebook would be another story entirely
This is not correct at all.
Facebook has many distinct MySQL tiers (distinct sets of shards) supporting different purposes. And each tier has many tables.
The UDB tier (user database / primary product backing store) had several hundred tables when I worked on Facebook's MySQL team 7+ years ago.
This was before the MyRocks migration though, and it's possible the UDB layout changed to reflect MyRocks' extensively different file layout vs InnoDB. But in any case, especially keeping in mind all the various distinct tiers, saying all data at Facebook is one table is egregiously incorrect.
A decade later, I still remember how good their presentation was! I wish I could remember the engineers' names.
[0] https://www.youtube.com/watch?v=fYnrtjO-Ne0
[1]: https://www.youtube.com/watch?v=NfS5ZLNPxS4
[2]: https://www.youtube.com/watch?v=kP6undC_HDE
(though it's only used as the backing data store for their graph database https://www.usenix.org/system/files/conference/atc13/atc13-b...)
Or at least it was as of a few years ago; AFAIK this is still the case. For example the internal MySQL DBaaS tier had hundreds of distinct use-cases throughout the company.
This is nearly all the data stored by Facebook. Every profile post, comment, photo, story, marketplace listing is a row in one MySQL table. Even internal tasks and code changes are rows stored in this one table. I don’t think a bigger MySQL table exists.
Fun fact - You can also access any object if you know it’s ID with Facebook.com/id. For example, Mark Zuckerberg’s profile was the 4th object created, so that’s where https://facebook.com/4 leads.
The UDB tier used to use the InnoDB engine; nowadays it's MyRocks: https://research.facebook.com/publications/myrocks-lsm-tree-...
https://engineering.fb.com/2013/06/25/core-data/tao-the-powe... https://research.facebook.com/publications/flighttracker-con... https://engineering.fb.com/2021/08/18/core-data/ramp-tao/ https://engineering.fb.com/2022/09/07/open-source/taobench/
I work on TAO consistency at FB and we've put out a bunch more articles/papers in the past couple years. Until 2 years ago, there was just the 2013 paper on TAO itself.
https://www.google.com/search?q=mysql+site%3Ashopify.enginee...
What are some Postgres examples?
As a Postgres fan, I'm genuinely curious. Please don't take my comments as troll-y.
* web indicates that it was HTTP traffic, a subset of all internet traffic
Following this thread closely - we are following in the footsteps of some of these co’s, and out is the way forward.
[1]: https://www.xarg.org/2011/10/optimized-pagination-using-mysq...
[more]: https://stackoverflow.com/a/32360867
haha.
MySQL comes with a bunch more built-in that SQLite does, but Facebook and GitHub aren't scaling purely with the built-in functionality, they're writing their applications to be sharding-aware, they're writing custom proxies, they're running custom extensions, they're picking and choosing which bits of core functionality they allow in order to minimise scaling issues.
I think in a systems design interview these issues need to be addressed, to show that you have the skills to be able to implement this, and I think that a good interviewer should be open to this discussion!
Did they give an example of a database that "scales"?
There were always concerns about the latency involved in the replication. Whenever I did large scale batch operations that needed to write lots of records in master servers, I would monitor the replication queue length and suspend operations when it got too far behind to keep things sane and healthy.
The biggest failure in this architecture was sharing sql queries as opposed to sharing service calls. This is something that was being fixed as I left. Switching it to work with service calls gives more flexibility in the underlying implementation and opens more options for improving the system.
For most kinds of large systems I’d recommend thinking about the architecture in terms of distributed Actors hosted in something like DAPR, Orleans, Akka, or Erlang/OTP and then the technical details of the underlying database become less of a constraint. It grants you flexibility for your underlying storage that you would not otherwise have.
Fun side fact: Bos, the CTO of meta, was a key person working on making all the magic happen there back then.
Read-only replication stores a copy of the whole database on other machines, so that any query which doesn't write can be handled by asking the remote machine. Writes are bottlenecked through the primary machine, which then sends changes to the remotes.
Yes, these are separate server instances that are essentially separate from each other. All of the records for a given user live on a single particular server. Although MySQL doesn't do this kind of partitioning inherently, this kind of partitioning can be implemented in the application.
Beyond CRUD, I'm not so sure, and any application that's big enough to use sharding probably has some pretty complicated queries. At that point you really don't want to be depending on a library to handle the low level details. You need to think about the performance implications of the access patterns and the trade-offs inherent in different design choices. If a library makes these choices for you then you are unlikely to get optimal results.
Interested to know how you'd avoid duplicates and lost transactions while maintaining a comfortable level of performance/latency for an online game.
noob question but what do “sharing queries” and “sharing service calls” mean?
One of the most pernicious dangers in shipping a successful project is you paradoxically both cargo cult and critique random parts of your success. Looking back, you might decide that choice A was correct, while choice B was incorrect. But you don't have the benefit of having tried all of those other options. So don't trust postmortems too much, they are single data points in a sea of failures. They could very well be random chance.
Did you do this at SL? Because this could be a case of "If I had to do it over again I'd make it perfect" (they say hindsight is 20/20, but it only seems to be).
In 2021: "We saw 1.8M queries per second on our massively sharded MySQL databases." https://twitter.com/codeascraft/status/1466174452459196420
Here's a post from a decade ago: https://www.etsy.com/codeascraft/two-sides-for-salvation
1) RDS/Cloudsql is awesome but too expensive, we can't afford it. The things it provides are not that special with VMs anyway (images, backups, replication).
2) The industry moved to vitess db, but converting to this is too daunting, we are focused on automation and cost reduction instead.
3) Use index based sharding (no math shards % total, no consistent hashing, no drama). When you get a new user, setup where they should be on each shard and keep that on a single table [user_id,video_cluster_id,session_cluster_id,..]. This way moves are easy. Then video_cluster_id is a load balanced host infront of a fleet of replicas.
4) The hardest thing you will run into is write saturation. You can't write fast enough to a disk. Advanced topologies and 3) helps here. Example: [MasterA] <- [Slaves,0..n] <- [MasterB] <- slaveb,0..k]. This way you can push more writes to MasterB, and add replication filters so that slaveb doesn't need to perform all the writes MasterA needs.
5) SSDs. Don't bother with anything else.
6) We would pay a million dollars for this tool: https://docs.percona.com/percona-toolkit/pt-online-schema-ch...
7) GTID took away the hard parts of mysql replication.
8) Hardcore mysql experience is disappearing. With RDS/Cloudsql/Postgres less and less developers are going able to patch a production problem.
https://dev.mysql.com/doc/refman/5.7/en/change-replication-f...
Many budget hosting providers supporting CMSs like WordPress and Joomla run very large multitenant MySQL or MariaDB instances. These instances often get into performance trouble when one or more of the customer sites on them grow large or get traffic spikes. The CMSs offer elaborate caching strategies (based on redis, memcache/d) to help mitigate this. But of course the multitenant nature of these instances means that individual customers must configure the caching. And the lowest-end hosting providers don't offer redis or memcache/d.
It's possible to monitor various operational parameters (via `SHOW GLOBAL STATUS` and similar SQL statements) to detect problems. It's very hard for customers to mitigate configuration problems (like insufficient buffer pool sizes or too few maximum connections). But customers can change table indexes.
Other hosting providers operate large numbers of small separate MariaDB and MySQL instances, one per customer. Considering that WordPress powers something like 30 - 40% of the sites on the internet, the total number of instances is vast.
WordPress.org, a vast site, uses multiple read replicas.
Version upgrade inertia is a big problem for hosting providers. One provider (whom I will not name) runs a MySQL version that reached end-of-life well over a year ago. New-start MySQL / MariaDB project designs should make provisions for DBMS version updates. Updates are worth applying because the development teams do lots of performance-enhancement work.
High QPS is in the eye of the beholder, but let's say something like a few hundred thousand QPS and up.
If you have scale in just one of these 3 dimensions, the solutions are relatively easy (except maybe for high write QPS, depends on the nature of the writes). If you are at scale on all three dimensions, solutions get harder (sharding, non-innodb storage engines).
Of course, there is also the wildcard: the nature of your queries/transactions. Large transactions along with scale will make your life a living hell.
At this point differences between databases start to shrink. Most of the complexity and features are being moved to the application layer, and your database is becoming dumber.
More subtle (and frankly boring) features are becoming important. Like how cost-effective is it to run, observe, backup/recover, etc.
Honestly, I feel like most interesting stories are before "at scale". Stories about how you delayed the need to "scale" for several years (and probably saved the company by allowing to focus on product instead of infra). But unfortunately those stories aren't something people tend to brag about. Nobody writes stories about how they chose to be pragmatic and not scale because they could just buy a bigger server or add a caching layer.
That would be a pretty boring talk, to be fair.