Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Why Uber Engineering Switched from Postgres to MySQL

eng.uber.com

Post reply on HN
Print view View original

141–150 of 306 posts

Re: Why Uber Engineering Switched from Postgres to MySQL

#141

Post by nine_k »

jedberg wrote: post #132
yeukhon wrote: post #128

Earlier quoted context omitted.

I was wondering how could that happen? It sounds like someone is trying to do two things in parallel. I would expect def my_view(request): try: receipt = generate_receipt(...) receipt.send_email(...) except SomeKindOfEmailError as e: # okay do something else and this should be synchronous and thus blocking. So to not block, they either wrote co-routines (asynchronous) or execute things in parallel. Have I interpreted…

Open db connection get data for receipt generate receipt send email write success to database close connection To a junior programmer this would probably look reasonable, and to be fair, it takes some experience and getting burned, or good training, to know it is not.

This still can be a pretty reasonable and correct course of action. Everything depends on the amount of data you are locking, and amount of transactions open simultaneously.

When there's a noticeable contention due to the number of parallel transactions, one should consider ways to loosen their data guarantees (the status can be unset for some time) and go for an explicit asynchronous approaches.

Re: Why Uber Engineering Switched from Postgres to MySQL

#142

Post by jhartmann »

yeukhon wrote: post #105
lazyant wrote: post #29

Earlier quoted context omitted.

from what I understand FB uses Mysql as permanent storage, not as relational database

I believe they use it for almost everything. They built all the graphs on top of MySQL data. I don't know what permanent storage is, but if you are referring to storing images and videos, I doubt. Highly doubt that. They may be storing pointers, but as a file system, I doubt. But I wouldn't know for sure, I don't work there. Oh, they do have Cassandra (well they built Cassandra)...

They use a system called haystack for binary large object storage. They very much don't use mysql for those.

Re: Why Uber Engineering Switched from Postgres to MySQL

#143

Post by yeukhon »

jedberg wrote: post #132
yeukhon wrote: post #128

Earlier quoted context omitted.

I was wondering how could that happen? It sounds like someone is trying to do two things in parallel. I would expect def my_view(request): try: receipt = generate_receipt(...) receipt.send_email(...) except SomeKindOfEmailError as e: # okay do something else and this should be synchronous and thus blocking. So to not block, they either wrote co-routines (asynchronous) or execute things in parallel. Have I interpreted…

Open db connection get data for receipt generate receipt send email write success to database close connection To a junior programmer this would probably look reasonable, and to be fair, it takes some experience and getting burned, or good training, to know it is not.

Thanks for the illustration. But if Uber team was using ORM, then expect ORM to take care of the pool of connections.

get data for receipt would be a SELECT and only write success to database would do INSERT or UPDATE. I expect junior programmer to complete the above in at least two SQL calls. I have a feeling they were trying something smart.

EDIT: hmm reading the other commenter above, probably they are trying to lock on the data for full data integrity. Okay. That makes sense then. I was looking at the problem from the wrong angle.

Re: Why Uber Engineering Switched from Postgres to MySQL

#144

Post by ad-hominem »

gkop wrote: post #119
xaprb wrote: post #110

Earlier quoted context omitted.

Justify that with actual math, please? I don't think you know how computers (processors, bus, memory access, etc) work. How exactly do you think an RDBMS can query an index at a hundred billion rows per second?

Illniyar is right, a b-tree with a branching factor of 100 can index 100M rows in 4 levels. Even with the index on a spinning disk, should have no trouble coming in well under 100ms.

And that right there's the math

Re: Why Uber Engineering Switched from Postgres to MySQL

#145

Post by yeukhon »

jhartmann wrote: post #142
yeukhon wrote: post #105

Earlier quoted context omitted.

I believe they use it for almost everything. They built all the graphs on top of MySQL data. I don't know what permanent storage is, but if you are referring to storing images and videos, I doubt. Highly doubt that. They may be storing pointers, but as a file system, I doubt. But I wouldn't know for sure, I don't work there. Oh, they do have Cassandra (well they built Cassandra)...

They use a system called haystack for binary large object storage. They very much don't use mysql for those.

That's my feeling they wouldn't use MySQL as a blob store.

Re: Why Uber Engineering Switched from Postgres to MySQL

#146

Post by jedberg »

nine_k wrote: post #138
jedberg wrote: post #116

> MySQL supports multiple different replication modes: > Statement-based replication replicates logical SQL statements (e.g., it would literally replicate literal statements such as: UPDATE users SET birth_year=770 WHERE id = 4) Postgres has that too (using a 3rd party tool, but it's an officially supported tool). We were using it on reddit 10 years ago. It caused a lot of problems. I wouldn't call that an advantage…

Sending an email to a local MTA should be pretty fast. That is, if you cannot and will not do async stuff in your program, there are ready-made tools that will do that particular thing asynchronously for you, and have been doing so for years (or even decades).

True, but really your local MTA is just acting like a specialized queue, since the first thing it will do is send the message to your relay.

Re: Why Uber Engineering Switched from Postgres to MySQL

#147

Post by nickpsecurity »

smoodles wrote: post #114
jobu wrote: post #91

Earlier quoted context omitted.

> It sounds like Uber is using MySQL as just a data bucket with primary keys They have a couple posts about "Schemaless", but I still don't understand why they used MySQL as the data store instead of something like Cassandra. ( https://eng.uber.com/schemaless-part-one/ ) From that post it looks like they basically built a no-sql database on top of a relational database. The only reason given was operational trust ( "…

Operating Cassandra at the scale that Uber is going to require is going to be painful and as operationally draining as MySQL if not more. There are really not a large number of options here anymore with the departure of FoundationDB from the market. CockroachDB might be an option in a few years, though I'm still confused why they are moving towards a SQL-ish vs key-value interface...

"departure of FoundationDB from the market"

Pissed me off so much. Only thing close to Google's F0 RDBMS on the market, at a reasonable rate, and the beginning of a good offer to enterprises. Then, "poof!" It's a good example of why I tell companies to not put anything critical into something from a startup. If they do, better have a synchronized, backup option tested and ready to go.

"why they are moving towards a SQL-ish vs key-value interface..."

That's easy: most databases and buyers use SQL. Key-value is preferred by startups & non-critical, side projects in big companies you see here a lot but aren't representative of most of the market. Need first-rate, SQL support. I think EnterpriseDB shows that it's also a good idea to clone a market leader's features onto alternative database.

Re: Why Uber Engineering Switched from Postgres to MySQL

#148

Post by tormeh »

jedberg wrote: post #132
yeukhon wrote: post #128

Earlier quoted context omitted.

I was wondering how could that happen? It sounds like someone is trying to do two things in parallel. I would expect def my_view(request): try: receipt = generate_receipt(...) receipt.send_email(...) except SomeKindOfEmailError as e: # okay do something else and this should be synchronous and thus blocking. So to not block, they either wrote co-routines (asynchronous) or execute things in parallel. Have I interpreted…

Open db connection get data for receipt generate receipt send email write success to database close connection To a junior programmer this would probably look reasonable, and to be fair, it takes some experience and getting burned, or good training, to know it is not.

So the correct version is, I guess:

Open db connection 1

get data for receipt

Close db connection 1

generate receipt

send email

open db connection 2

write success to database

close db connection 2

I guess you could speed this up a lot by doing it in bulk instead of opening and closing db connections twice for every email. Anyway, the version you wrote sounds reasonable for everything but really big operations to me, but then again I'm fairly junior.

Re: Why Uber Engineering Switched from Postgres to MySQL

#149

Post by markoot »

fjordan wrote: post #76
ledjon wrote: post #51

I would argue that most of these Postgres "flaws" are actually advantages over MySQL when you look at them holistically rather than the very specific Uber use-case. Postgres's MVCC is superior (can rollback DDL, can add indexes online, can have open read transactions for a VERY long time without impacting other parts of the system) Postgres supports many types of indexes, not just b-tree. One thing it doesn't have is…

Tools such as the percona toolkit ( https://www.percona.com/software/mysql-tools/percona-toolkit ) provide the ability to perform safe and performant online alters for MySQL. Behind the scenes it actually creates a new table with the new schema and utilizes triggers to apply writes to the new table as the original data is being copied. Once it completes the table is renamed and the triggers are removed. Percona also…

I have used this tool many times in production, and in accordance with advice about avoiding foreign keys.

It only failed catastrophically, causing a critical production incident, twice.

Each time took dozens of engineer hours to vet in advance, thus costing thousands of dollars.

Online DDL changes and indexing with pg cost us... basically nothing, and never caused downtime.

My and Pg each have their place... but if you want to modify large tables, MySQL is almost certainly the wrong tool for the job.

Re: Why Uber Engineering Switched from Postgres to MySQL

#150

Post by peatmoss »

madhusudhan000 wrote: post #103
ledjon wrote: post #51

I would argue that most of these Postgres "flaws" are actually advantages over MySQL when you look at them holistically rather than the very specific Uber use-case. Postgres's MVCC is superior (can rollback DDL, can add indexes online, can have open read transactions for a VERY long time without impacting other parts of the system) Postgres supports many types of indexes, not just b-tree. One thing it doesn't have is…

Well, for one thing, if they say schemaless, then you are using the wrong tool for the job. Sure postgres has JSONB (comparing to mongo db), Key value store such as HStore, but they do well if they fit on one machine. The moment you hit that scale you have to realize that there are tools specifically built for this. There is the phoenix project https://phoenix.apache.org/ that salesforce is using for scaling. Definit…

The term schemaless within Uber is more of a product name. It's kind of a joke with many of the engineers, that it's anything but devoid of schema.
Post reply on HN