Migrating Facebook to MySQL 8.0 • Hacker News Board

Re: Migrating Facebook to MySQL 8.0

#311

Post by dijit » Fri, Jul 23, 2021, 4:55 PM UTC

bonzini wrote: post #297

dijit wrote: post #201

Earlier quoted context omitted.

I’m not saying semver is a universal standard but this is _wildly_ different to everything else. Nobody expects defaults to change between patch/minor versions. This is another example of how MySQL does not give a flying F about the footguns it leaves lying around. Putting the onus on the user is not reasonable; especially in a world where we’re trying to reduce toil or get rid of ops completely.

> _wildly_ different to everything else Linux?

Linux never changes backwards comparability. Linus is literally famous for saying as much.

Re: Migrating Facebook to MySQL 8.0

#312

Post by MuffinFlavored » Fri, Jul 23, 2021, 5:43 PM UTC

dweekly wrote: post #273

packetslave wrote: post #251

Earlier quoted context omitted.

Kubernetes, no. Twine (aka "Don't call it Tupperware"), yes https://engineering.fb.com/2019/06/06/data-center-engineerin... Also, that "30,000 servers" number is off by... a lot. No comment on by how many orders of magnitude. :-)

Yep. Here's how I'd napkin math it. Facebook has purchased 6GW of renewable energy to power its datacenters [1]. Its datacenters have a PUE of about 1.06 last they published (they unfortunately seem to have taken their dashboards down) so that's about 5.6GW of electricity actually powering real load. Some of that will power the networking fabric so we could conservatively estimate 5GW of compute load. The OCP Yosemit…

5 Gigawatts to Kilowatts = 5000000

5000000 kilowatts / 1kW per machine = 5,000,000 machines internationally?

Re: Migrating Facebook to MySQL 8.0

#313

Post by notacoward » Fri, Jul 23, 2021, 6:59 PM UTC

kevinventullo wrote: post #261

pbreit wrote: post #245

Earlier quoted context omitted.

Any idea what Facebooks stores in MySQL? Core user database?

To a first approximation, everything. Most product engineers interact with it by querying TAO, which you can read about in various blog posts, but is basically a cache in front of MySQL (this is a vast oversimplification).

Hi, Kevin. :) As of the time I left, the bulk storage system I worked on - images, video, machine-learning datasets, backups of other things, etc. - held most of the bytes at Facebook. The MySQL/TAO complex had orders of magnitude more addressable objects, undoubtedly handled many times more requests and bytes in flight per second, but when it comes to bytes at rest they're not even number one.

Re: Migrating Facebook to MySQL 8.0

#314

Post by notacoward » Fri, Jul 23, 2021, 7:07 PM UTC

pbreit wrote: post #245

ksec wrote: post #6

MySQL 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…

Any idea what Facebooks stores in MySQL? Core user database?

Basically all the small stuff. Every user's identity, preferences, history, and connections. If you post text, that's there too. If you post media, that's somewhere else. Connections are the hard part, because it's a gigantic graph with the potential to make every query - e.g. to construct a user's timeline - hit vast numbers of servers. There's all sorts of special sauce to do with partitioning, caching, etc. to absorb as much as possible of that load before it hits the MySQL servers, but it's still a gigantic service even by Facebook standards.

Re: Migrating Facebook to MySQL 8.0

#315

Post by zepearl » Fri, Jul 23, 2021, 7:13 PM UTC

tpetry wrote: post #246

zepearl wrote: post #174

Earlier quoted context omitted.

I got sad when I read that TokuDB in Percona & MariaDB was being dropped :( Replaced TokuDB with MyRocks but it's totally different => now finally managed to have good ~constant performance insert&deletes (had to test & tune a lot), but "updates" remain a problem (they write a lot as I understand that any update always has to rewrite the entire row). Using InnoDb instead of MyRocks is NOK for me (problems with delete…

You can enforce how to execute parts of your PostgreSQL with pg_hintplan. And it has more options than mysql‘s approach ;)

Thanks a lot!!! I really mean it!

This might change everything for me. I'm already using MariaDB+MyRocks and Clickhouse for some special/dedicated tasks, but I was really missing a good DB for normal/typical OLTP tasks.

So far I used (as mentioned above) MariaDb+TokuDB (but the optimizer of MariaDB can be a bit crazy from time to time), had multiple times during the past months thoughts about PostgreSQL but the lack of hints always made me take a step back from it => this addon seems to be exactly what I wished for.

Looks like that my weekend will be all about PG - the last time that I set it up its version number had a single digit => cannot remember anything anymore... .

Again, thanks for the hint :P

Re: Migrating Facebook to MySQL 8.0

#316

Post by evanelias » Fri, Jul 23, 2021, 7:26 PM UTC

throwdbaaway wrote: post #262

evanelias wrote: post #199

Earlier quoted context omitted.

No, since MySQL 8 doesn't ever claim to use SemVer. They have an explicitly stated policy of including features in point releases. Why would it be "pretty scary" that a non-SemVer software doesn't use SemVer? SemVer is an arbitrary versioning scheme, not a universal standard. It's the operator's responsibility to understand the versioning scheme prior to upgrading.

It is pretty scary because most of these new features or enhancements are buggy. By not adopting SemVer, users of MySQL who prefer stability (oxymoron?) have no obvious upgrade path. They could be happily using version 8.0.123, and then upgrade to version 8.0.126 to get some security fix, and suddenly encounter a bunch of functionality and/or performance regressions. Some examples... WL#10310 Redo log optimization: F…

In my experience, it is quite an exaggeration to claim that "most" post-GA 8.0 features or enhancements are buggy, especially in the general case. Yes, some of them may have edge-case bugs or performance issues, but typically only affecting some users with very specific environmental or workload situations.

Operators must test before upgrading. Luckily some third-party software makes this easier, e.g. Percona's pt-upgrade or ProxySQL's mirroring feature.

I'll state again unambiguously, I'm not personally fond of 8.0's release policy of rolling out new features post-GA. But I view it as an annoyance, and at least one with some upsides (not having to wait 2 years for a massive feature dump all at once) rather than being uniformly negative or "pretty scary". Personally I was careful about testing point release upgrades before 8.0, and I'm still careful about it now.

Re: Migrating Facebook to MySQL 8.0

#317

Post by dkersten » Fri, Jul 23, 2021, 8:38 PM UTC

fn1 wrote: post #304

dkersten wrote: post #172

Earlier quoted context omitted.

I absolutely love Postgres' JSON and array support. Its saved me from needing a document database in a few cases, which drastically simplified the overall deployment (one DB instead of two, or trying to represent relational stuff in a document DB). I really like JSON for stuff that is user defined, that I don't need to query into, but postgres' support means I still can should I ever need to. Which has happened once…

> but postgres' support means I still can should I ever need to. And since postgres supports indices on arbitrary expressions you can use CREATE INDEX for the json-queries in your WHERE: CREATE INDEX ON publishers((info->>'name'));

Yes, Postgres' jsonb and array support lets you not only query into these fields, but also, like you say, index into them too. Its really the best of both worlds: you get a great solid relational database, but if you need document database features, you don't really lose out on anything by using json, except that the queries are a bit more ugly. I'm super happy with postgres these days, its rock solid and full of fantastic features.

Re: Migrating Facebook to MySQL 8.0

#318

Post by vosper » Fri, Jul 23, 2021, 9:21 PM UTC

evanelias wrote: post #177

vosper wrote: post #171

Earlier quoted context omitted.

> the painful "shove everything in a huge singular AWS Aurora instance" approach :) I haven't used Aurora - what's painful about this approach (apart from potentially being locked-in to AWS / Aurora)?

Aurora lets you grow your db storage up to 128 TB, but things become difficult and slow long before this point. This is true operationally (e.g. schema changes, restoring from a backup, etc) as well as potentially for application / query performance issues. That said, sharding can be very painful too in other ways. But at least it permits infinite horizontal scaling, and it makes many operational tasks easier since t…

Interesting, thanks for the insights

Re: Migrating Facebook to MySQL 8.0

#319

Post by mdcallag » Fri, Jul 23, 2021, 11:50 PM UTC

mackman wrote: post #149

Wow. 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.

You did an awesome job on the 5.0 to 5.1 migration, especially in chasing down at least one obscure problem in InnoDB.

Re: Migrating Facebook to MySQL 8.0

#320

Post by kevinventullo » Sat, Jul 24, 2021, 12:14 AM UTC

notacoward wrote: post #313

kevinventullo wrote: post #261

Earlier quoted context omitted.

To a first approximation, everything. Most product engineers interact with it by querying TAO, which you can read about in various blog posts, but is basically a cache in front of MySQL (this is a vast oversimplification).

Hi, Kevin. :) As of the time I left, the bulk storage system I worked on - images, video, machine-learning datasets, backups of other things, etc. - held most of the bytes at Facebook. The MySQL/TAO complex had orders of magnitude more addressable objects, undoubtedly handled many times more requests and bytes in flight per second, but when it comes to bytes at rest they're not even number one.

Fair enough :)