22 ms·
Migrating Facebook to MySQL 8.0
- hitekker 5y agoSeems like good, old-fashioned engineering work to me. No breathless evangelism or careerist claptrap in sight.
- linspace 5y agoIt seems that "move fast and break things" is not for billion dollar companies. My impression too. Very meticulous work, just the facts.
- alberth 5y agoIt's interesting to see FB migrating so much over to Oracle. 2 weeks ago was FB use of GraalVM (from Oracle). And now this. https://news.ycombinator.com/item?id=27782475 https://news.ycombinator.com/item?id=27782475
- maxk42 5y agoOracle has a very complete and much cheaper cloud solution relative to AWS. I've begun migrating my own projects over to their cloud. The savings on egress costs alone pays dividends.
- luhn 5y agoThat has nothing to do with MySQL. Facebook operates their own DCs and hasn't indicated any interest migrating elsewhere.
- jmnicolas 5y agoTying yourself to Oracle is quite a risky choice in my opinion. After having used their non cloud products 10 years ago, I wouldn't do any business with them. At all.
- na85 5y agoI'd say the same about Facebook.
- mr_overalls 5y agoI find the comments in this article very accurate: https://news.ycombinator.com/item?id=15160149 https://news.ycombinator.com/item?id=15160149 > ORA is the elephant's graveyard of software. > Once something gets bought by them, you know it is done. Slowly, but surely. > They perform a function akin to the maggots that destroy cadavers in nature. Part of the overall ecosystem. > ORA stopped being a tech co a while ago, now it is a finance play. Use cash to buy a business for its locked in customers, gut it to squeeze max money out of it until last customer is gone. Rinse, repeat.
- adrianmsmith 5y agoThis is not so with Java. They have moved Java along a lot since they bought it; they released 1.8 finally, and have introduced lots of new features such as type inference since then. MySQL is also going strong after the acquisition. Lots of new features.
- BossingAround 5y agoAnd yet MySQL is very much alive..? Java has fully opensource implementations too, and has had innovations like GraalVM and Jakarta EE. I think I'm too young to hate Oracle with a burning passion. I would be very scared of their sales/corporate team, but I mean, even having large contracts with Google or Amazon is difficult to say the least.
- danpalmer 5y agoMySQL is alive for companies already using MySQL, but would well informed companies be choosing it today? I'm not so sure. I think you either want it to have more functionality and you choose Postgres, you want a particular performance profile and you pick MariaDB, or you want the enterprise support and you pick Oracle's database. Why you would pick Oracle-supported MySQL today I'm not sure.
- abledon 5y agodidn't the new basecamp "Hey" email product choose to use MySQL?
- sly010 5y agoTotally off topic, but I found it funny that in the offical oracle cloud intro video the robot arm in the background misses the box, right as the narrator mentions the advantages of automation at 1:10: https://www.youtube.com/watch?v=f_BDfMdGn3M&t=61s https://www.youtube.com/watch?v=f_BDfMdGn3M&t=61s
- topspin 5y ago> Oracle has a very complete and much cheaper cloud solution relative to AWS Tell us more. Ignore the haters please. I'd love to have your insights.
- maxk42 5y agoIt's all on their site - https://www.oracle.com/cloud/ https://www.oracle.com/cloud/ Their price list is at https://www.oracle.com/cloud/price-list.html https://www.oracle.com/cloud/price-list.html and the egress is under the "networking" section. Basically they give you 10TB outbound data transfer for free and charge as little as $0.0085 per GB after that. That's a savings of 90% (or 100% if you use less than 10TB transfer, which most apps do) Additionally their cloud servers (and most other services as well) are much cheaper than comparble EC2 instances and every one of their services I've used so far also includes a free tier so you can try them out without paying anything. All in all pretty pleased so far.
- gunapologist99 5y agoYeah, AWS (and Azure, and GCP) really has notoriously expensive bandwidth, and it's the one thing they will never budge on in negotiations, since they know it's where they have nearly 100% profit margin; you can avoid using most services, or use compute etc more efficiently, but it's hard to consume less bandwidth! It sounds like Oracle is actually pricing bandwidth at a reasonable price. This is quite a shock, coming from a historical perspective!
- cranekam 5y agoIt's a stretch to claim this is a "migration". Facebook has used MySQL since its very early days — certainly since before Oracle acquired Sun and thus MySQL AB.
- dcolkitt 5y agoI guess one could dream of the remote possibility that Facebook outright acquires Oracle. It seems like a weird match, but it'd give FB a toehold in a lot of verticals that it currently has no presence in. Whatever criticisms you have of FB, it's at least a pretty open source friendly company.
- amelius 5y agoWhat would happen to Oracle's infamous legal department then?
- munk-a 5y agoThen they'd leverage social media oversharing to find more targets to litigate - it'd be a win-win! /s
- aloisdg 5y agoYou mean pretty much the whole company? /s
- rattray 5y agoFabulously lavish retirements?
- dylan604 5y agoThey'll start serving ads and sharing personal data of all of the people they sue?
- rrdharan 5y agoFacebook has no interest in infrastructure / enterprise revenue streams (Facebook for Work notwithstanding). See the history of Asana, Cloudera and Phabricator (and even Dropbox, from an employee/recruiting perspective, kinda).
- suyash 5y agoYou can see Oracle or you can see Open Source Awesome technology they are using, both of these are solid OSS projects.
- hodgesrm 5y agoThe MySQL 8.0 upgrade is not a migration to Oracle. Facebook has run MySQL for many years at this point and, as the article describes, operates on a private fork. They have employed some of the leading engineers in the MySQL community to support their version of MySQL.
- cmrdporcupine 5y agoMy old-man brain still can't compute this statement: "MySQL, an open source database developed by Oracle". I know this is now factually true, but late-90s me keeps looking over my shoulder and freaking out.
- jtbayly 5y agoAgreed. And in one sense (English is complicated) it's not true in its most obvious interpretation—that Oracle is the reason it exists. It was developed by others. Changing it to "currently being developed by Oracle." It's not that "developed by" can't mean "currently being developed by." It just isn't the most obvious meaning.
- soperj 5y agoshould read: "an open source database bought by Oracle".
- kaba0 5y agoAnd then further developed…
- blantonl 5y agoThe hair on my neck still stands up when thinking about dealing with Oracle (and Sun) contracting, purchasing, and sales teams in the 90's.
- briffle 5y agoI dealt with them in 2016. I assure you, not much has changed.
- cmrdporcupine 5y agoDon't forget IBM
- zerop 5y agoAgree. I have used IBM web sphere in 2008. IBM, Oracle, Sun is a tribe.
- pmarreck 5y agocan it do DDL commands in a transaction yet?
- spullara 5y agoNope. In terms of broad usage I think only Postgres supports it.
- gfody 5y agosql server and db2 also support ddl in transactions, oracle still does not
- SPBS 5y agoEven SQLite supports transactional DDL. MySQL (and Oracle) are the exceptions here.
- hans_castorp 5y agoFirebird supports it as well
- jjeaff 5y agoI always see people asking about this. But why? Are you making schema changes that frequently?
- handrous 5y agoSchema changes are pretty much the riskiest damn thing you can do with a database, so it's natural people would be extremely interested in being able to confine as much of that as possible to transactions, even if changes are rare (which, arguably, they're rare in no small part because they're so risky).
- simonw 5y agoIt's my opinion that schema changes should be cheap, fast and unexciting. Unfortunately, in my experience once you are operating at scale they are expensive, slow and fraught with peril. The result is that engineering teams often chose to build poorly designed schemas and take on technical debt rather than make a schema change to a core table. This is a terrible anti-pattern! I'm massively in favour of anything that makes schema changes cheaper and more boring. I really enjoy working with the Django schema migration system for this reason - though it won't help you as much if you need to make schema changes to older versions of MySQL (I've not tried schema changes against 8.0 yet) since you'll likely still need to use GitHub ghost or Percona pt-online-schema-change.
- ksec 5y agoMySQL 5.6 was released on 5 February 2013. MySQL 8.0 was released on 19 April 2018. >The 8.0 migration has taken a few years so far. We have converted many of our InnoDB replica sets to running entirely on 8.0. At the scale of Facebook I wonder if they are the largest MySQL user on the planet. And I take this opportunity to ask, does anyone know how does the MySQL roadmap works? What sort of features are coming or when is 9.0 expected to arrive.
- Thaxll 5y agoYoutube also use MySQL.
- dheera 5y agoWow. Why don't they use in-house Google databases, which are supposedly vastly more scalable than MySQL?
- SQueeeeeL 5y agoProbably because a lot of those benefits don't actually translate that well towards their problem space. These aren't a bunch of 20 year olds throwing together a MongoDB, they're professionals using tools to actually make a product
- williamdclt 5y agoI don't think the people behind Spanner are a bunch of 20yo throwing together a MongoDB
- jacquesm 5y agoBecause there is a substantial cost to such migrations. If it works don't fix it.
- mastersummoner 5y agoWhile "if it ain't broke, don't fix it" is often a perfectly valid approach, there are going to be times when an upgrade can result in significant performance improvements or fewer admin headaches.
- theandrewbailey 5y agoAnyone else notice that bad scaling and jumping on the animation? If they're going for an old informational film aesthetic, awesome!
- pradeep1288 5y agothis should be fixed now
- dpcx 5y agoWhat I take away from this is that they are (or were, until recently) still running production systems on MySQL 5.6...
- gregoriol 5y agoSome migrations are very hard!
- filmgirlcw 5y agoYeah, but a highly-customized version of 5.6 with its own storage engine. It’s not like it was off the shelf MySQL 5.6.
- justapassenger 5y agoThere are production systems that run on 30+ years old software. Upgrade for a sake of upgrade is often a wrong decision.
- thebouv 5y agoOnly commenting to say their site makes me mad cause it breaks the browser history. I click the link, go to the fb engineering site, and the back button has no history to go back to HN. Dark Pattern UX -- gross.
- Semaphor 5y agoCan’t confirm. It works exactly as expected in Firefox (private mode because FB links open in the FB container otherwise), so maybe it’s some extension you have installed? FB Engineering links are usually pretty clean, and they don’t even have a reason to try and keep you there.
- mrcarruthers 5y agoYeah if you're using Firefox and you have the FB Container, it's working as advertised
- forgotpwd16 5y agoTried with clean profile Firefox and Chromium. Back button works fine with both.
- biesnecker 5y agoWorks fine for me in Chrome 91.0.4472.164 on a Mac.
- joepmath 5y agoSame here. Only in firefox (using v90.0)
- andremendes 5y agoAnyone else puzzled by the shaking migration animation? The original file has a 1920x1080 resolution. I wonder if it is related to the shaking. Good article nevertheless.
- andrewmcwatters 5y agoIf you look closely, you can see the engineers nervously rolling out changes to production, which is where the vibrations in the .gif come from.
- Normal_gaussian 5y agoIt has ?w=1024 causing resizing effects: Shaking: https://engineering.fb.com/wp-content/uploads/2021/07/CD21_390_ENG_MySQL_Animation_FINAL.gif?w=1024 https://engineering.fb.com/wp-content/uploads/2021/07/CD21_3... Not shaking: https://engineering.fb.com/wp-content/uploads/2021/07/CD21_390_ENG_MySQL_Animation_FINAL.gif https://engineering.fb.com/wp-content/uploads/2021/07/CD21_3...
- zestyping 5y agoI see it too. It's weird and distracting — something must have gone wrong in the rendering.
- pradeep1288 5y agothanks, this is fixed now :)
- andrewmcwatters 5y agoI wonder what order of magnitude of rows they deal with and how they optimize indices for querying that data.
- datavirtue 5y agoLike everyone else?
- andrewmcwatters 5y agoI didn't downvote you, but you were downvoted for this. So maybe I'll explain further, instead, to stir conversation. As you deal with more and more rows, it becomes imperative that all of your where clauses hit indexes/indices, but even beyond that, with large enough row sizes, it's not enough to provide fast responses. I suspect they deal with this in the form of some sort of caching outside of MySQL, but I haven't read into it. I'd be curious about how you respond to such challenges at the billions or trillions of rows orders of magnitude. Millions can be difficult enough, but beyond that you may have queries that effectively never return if you do not plan. Related reading: [1]: https://dba.stackexchange.com/questions/20335/can-mysql-reasonably-perform-queries-on-billions-of-rows https://dba.stackexchange.com/questions/20335/can-mysql-reas... [2]: http://webscalesql.org http://webscalesql.org
- evanelias 5y agoFacebook is heavily sharded, which keeps the size of each physical table at reasonable levels. Similar story at nearly every large tech company using MySQL, aside from some more recent ones that go for the painful "shove everything in a huge singular AWS Aurora instance" approach :)
- andrewmcwatters 5y agoThanks for that insight! Sounds like maybe you've seen an interesting thing or two given your background.
- 5y ago
- fukmbas 5y agoLmao FB is on mySQL? Someone fucked up
- markus_zhang 5y ago>It took a couple of years to complete porting all of these features. By the time we got to the end, we had evaluated more than 2,300 patches and ported 1,500 of those to 8.0. This is some tremendous effort as I see. I wonder how many people work on these evaluations.
- simonw 5y agoI'd love to know roughly how many engineers (or engineer hours) this took - sounds like a mammoth project!
- takeda 5y agoPerhaps it's due to me being in the PostgreSQL camp, but what happened to MariaDB? I was convinced that's where MySQL developers went and that's where development proceeded, while MySQL was just half dead, because Oracle did not care about it.
- lucideer 5y agoThat was the expectation but the Oracle camp surprised everyone by actually giving it some attention, and meanwhile MariaDB's promise of being a "100% compatible drop-in replacement" came with more and more caveats with each release (as is to be entirely expected with any fork). When you consider the network effect of all the stacks already heavily invested in MySQL, all Oracle really needed to do was put in a modicum of effort to MySQL to stave off the MariaDB migrations (and from my understanding they've put in quite a bit more than a modicum). Lastly, Postgres is an interesting player. Migrating from MySQL to Postgres might be a scarier prospect than to MariaDB, but since the Oracle acquisition a lot of MySQL-veterans were looking for something non-MySQL for new projects and in that I get the sense Postgres beat out MariaDB.
- munk-a 5y agoI carried out a (quite ill-advised in retrospect) migration from MySQL to Postgres on a mature codebase with absolutely no automated test coverage and it went pretty smoothly. We had some bumpy performance directly after the production migration but we were able to reach parity after about two months of observing and adding targeted indices. I absolutely adore Postgres's tweaks to the SQL language, that dialect is amazing and has served us extremely well. And, since we passed pg10 a while back, the performance tuning you can do on it is pretty amazing.
- slownews45 5y agoSomehow Facebook and Oracle seem like fitting partners. Too horrible companies. One fighting the fight with lawyer / sales teams, the other with clickbait / outrage
- suyash 5y agoGo MySQL - solid open source database rocking for the last almost 3 decades!
- hodgesrm 5y agoWell...I love MySQL but there was a time when it was anything but rock solid. It was pretty painful until InnoDB replaced MyISAM in 2010 as the default storage engine. After that it became rare to lose data.
- dijit 5y agoExcept in cases where the constraints may be violated and it just inserts the next nearest thing. Or the times where the transaction isolation just abruptly ends and commits your transaction midway; leaving no chance of a rollback. Or when you alter a column to a new type and the cast doesn’t work in a few cases and just leaves corrupted garbage on every row. Or when the replication misses a few transactions and your replica now looks different to your primary. (These aren’t hypothetical, these are things I’ve seen)
- derekperkins 5y agoHave you seen any of those since 5.7?
- hodgesrm 5y agoDon't get me wrong, there are still occasional problems. Most of those I've encountered have been applications misusing MySQL. We can debate whose fault that is. But at least people stopped doing REPAIR TABLE after hard restarts of MySQL. It's been a long time since I last had to do that.
- neop1x 5y agoI love MariaDB/MySQL. I had only like 2 crashes and 1 broken migration over the decade of it in production. It very powerful if you design tables and indices right. Postgres may be great too but I started with MySQL and I don't want to work with more DBs in parallel as there are quite a lot of differences for some queries. And I don't have capacity to write all queries in two forms. :D ORM is universal but doesn't always do queries optimally. And fulltext works completely differently between the two, for example.
- chovybizzass 5y agoI wonder if FB could run on sqlite
- schuyler2d 5y agoI'd be interested more in what features were in their (3): > Non-MyRocks Server: Features in the mysqld server that were not related to our MyRocks storage engine were ported. i.e. things that they haven't shared with the MySQL community but aren't related to the storage layer. Why would/should they keep those private? Considering that RocksDB is open-source, clearly it's unlikely it's because they're 'trade secrets'.
- HarrisonFisk 5y agoThe code is open source: https://github.com/facebook/mysql-5.6 https://github.com/facebook/mysql-5.6
- bratao 5y ago+1 for MyRocks. If you can, try it. We store some text data and the compression is over 10 times better than InnoDB with a significative speedup.
- gigatexal 5y agoWhere can I read more about the custom things FB added to MySQL 5.6? Their work on replication and the DDL work seems fascinating. Are there scholarly papers, white-papers, blogs? Are they storing the friend graph in MySQL?
- HarrisonFisk 5y agoDirect source is available at: https://github.com/facebook/mysql-5.6 https://github.com/facebook/mysql-5.6 The social graph is indeed stored in MySQL via TAO. A recent white paper about MyRocks is: https://research.fb.com/publications/myrocks-lsm-tree-database-storage-engine-serving-facebooks-social-graph/ https://research.fb.com/publications/myrocks-lsm-tree-databa...
- midom 5y agothere's also this team page, although it has been somewhat lower on volume lately - https://www.facebook.com/MySQLatFacebook/ https://www.facebook.com/MySQLatFacebook/
- gigatexal 5y agoI've not got a FB account due to reservations about FB and their business model but that doesn't mean I am above learning from what they do, thanks for the link, I'll have to find info elsewhere though.
- throwdbaaway 5y agoI don't think you need a FB account to read the posts there, you can just treat it like a blog with a crappy interface (but very good content). Probably some mandate to "eat your own dog food".
- sriram_sun 5y ago"MySQL, an open source database developed by Oracle" - Gawd! Were most devs at FB born after the acquisition?
- evanelias 5y agoIt didn't say originally developed by Oracle. The statement is accurate; MySQL is currently developed by Oracle. At big companies, sometimes the corporate lawyers are overly picky about wording in blog posts and public presentations, e.g. "make it clearer that MySQL isn't our in-house product" type of thing. fwiw, several of FB's top database folks originally worked for MySQL AB, so they're well aware of the development history.
- mackman 5y agoWow. I worked on the upgrade from 5.0 to 5.1 (I think). We had fewer than 100 patches at the time. 1700 sounds like a nightmare. I apologize for any and all of the patches I wrote.
- mdcallag 5y agoYou did an awesome job on the 5.0 to 5.1 migration, especially in chasing down at least one obscure problem in InnoDB.
- desireco42 5y agoSo first sentence is: "MySQL, an open source database developed by Oracle, powers some of Facebook’s most important workloads. " Oracle didn't develop MySQL, they bought it... Am I nit-picky here, opinions? Otherwise, congrats on shipping!
- ksec 5y agoI would have had problems too if this was early 2010s, but I mean Oracle has been developing MySQL for more than a decade now, so I think it is fair to say "developed by Oracle".
- dafty4 5y agoThanks, HermanL and PradeepN, for sharing this migration story with HN! You wrote: "Despite all the hurdles in our migration path, we have already seen the benefits of running 8.0." May I ask what are the main performance benefits you have measured/noticed?
- wewe2fg23 5y agoOnly oldies uses Facebook in 2021
- FpUser 5y ago>"MySQL, an open source database developed by Oracle, " Sorry but there is a difference between "developed" as in originally created and "being developed" which is the case here in relation to Oracle.
- sdevonoes 5y agoDo you know if they use the Percona toolkit with MySQL? Does it even give any benefits if one is running MySQL 8?
- rajandatta 5y agoReally well done article. In-xeptjh treatment of a problem that few will actually tackle - a migration at Facebook's MySQL scale. Would be really good to see how long the project took and the migration speed they achieved.
- 1024core 5y ago> MySQL, an open source database developed by Oracle This makes it sound like Oracle created it, when it fact the reality is the opposite: Oracle fought it, and bought Sun just so they could get their hands on MySQL. How did regulators let that happen is beyond me.
- cat199 5y agosun had plenty of valuable assets in addition to mysql - don't think one can reasonably claim they bought sun just for mysql without inside knowledge
- jahewson 5y agoNo. The most valuable part of sun at the time was arguably the SPARC chip architecture and Solaris which gave Oracle a vertical compute platform to compete with IBM Db2 on POWER running AIX. In our new cloud world this is now quaint and irrelevant but it was a big deal when on-prem was the only way. There was also Java.
- ablekh 5y ago> MySQL, an open source database developed by Oracle Dear Facebook: Please fix the wording emphasized above ASAP before we revolt here in outrage with your lack of attention to detail. MySQL was developed by relevant open source project contributors (initially, MySQL AB folks), but not by Oracle and even not by Sun Microsystems. MySQL has been in development for ~12 years before Sun acquired MySQL AB. Please set the record straight. Sincerely, MySQL user (not currently, but in the past and maybe in the future [though unlikely, because Postgre...] :-)
- thayne 5y ago> A few applications hit repeatable-read transaction deadlocks involving insert … on duplicate key queries on InnoDB. 5.6 had a bug which was corrected in 8.0, but the fix increased the likelihood of transaction deadlocks. Interesting, I've run into a bug in MySQL 5.6 involving select for update where it deadlocked when it shouldn't. I wonder if the two were related.
- throwdbaaway 5y ago> First, we could not upgrade servers in place and needed to use logical dump and restore to build a new server. However, for very large mysqld instances, this can take many days on a live production server and this fragile process will likely be interrupted before it can complete. This part is quite interesting. I'd think it will take maybe a day to dump/restore a database with a single 10TB table with no parallelism, at a speed of about 120MB/s. How big is these mysqld instances such that the process will take many days?