Why Uber Engineering Switched from Postgres to MySQL • Hacker News Board

Re: Why Uber Engineering Switched from Postgres to MySQL

#211

Post by viraptor » Tue, Jul 26, 2016, 10:58 PM UTC

tormeh wrote: post #148

jedberg wrote: post #132

Earlier quoted context omitted.

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

Depending on the number of email sending workers, it could be closer to:

    transaction 1:
        mark an unsent receipt as being processed
        get the marked receipt
    send email
    transaction 2:
        mark the receipt as sent

Re: Why Uber Engineering Switched from Postgres to MySQL

#212

Post by carterehsmith » Tue, Jul 26, 2016, 11:01 PM UTC

njs12345 wrote: post #202

carterehsmith wrote: post #199

Earlier quoted context omitted.

I just want to add that newer versions of MySQL can add indexes online. In fact, it looks like all DDL can be done online, even though some if it (like adding a column) may require the table to be rebuilt, which can take time. Note that the master is still available for read and write during this. Replicas will lag though.

From the MySQL docs: http://dev.mysql.com/doc/refman/5.7/en/alter-table.html > Updates and writes to the table that begin after the ALTER TABLE operation begins are stalled until the new table is ready, then are automatically redirected to the new table without any failed updates. While the master may be technically up for writes during this period, it's not much a goer if your table is large and has any write traffi…

It should never be an extended period. Everyone everywhere will advise to keep your transactions as short as possible.

Sure this is sometimes boring additional work - eg you don't delete 1M records with one statement, you break it into 1,000 statements each deleting 1,000 records.

Sucks, but keeps your db and your users happy.

BTW this is true for PostgreSQL and MySQL and Oracle and every db that allows concurrent DML.

Re: Why Uber Engineering Switched from Postgres to MySQL

#213

Post by mamcx » Tue, Jul 26, 2016, 11:03 PM UTC

barrkel wrote: post #195

mamcx wrote: post #189

Earlier quoted context omitted.

But still everyone do it. That must tell something, right? And I'm not talking about NoSql borrowing some relational concepts, but the opposite, and more directly, the API. I know that most folks (including the ones that downvote!) have no clue what I'm talking about, because almost nobody (recently) have experience in talking against a database without SQL. Is like if the only way to talk to a NoSql was using Json +…

Well, data transformations are much easier to read and write in SQL than e.g. Java, what with temporary collections built up in memory, random maps, lists, etc. CTEs aren't required very often - you generally only need them for recursive CTEs, and that's iterative retrieval analogous to pointer-chasing. It's typically a sign of a data model that's poorly suited to relational storage, e.g. trees and graphs. I have iss…

Agree, but that are problems at the end, when most developers have issues at the start (basics). Baffle me when i read that relational databases are "hard" and you need to teach some basics even for experienced developers. Plus, the thing here is that yeah, everyone know SQL have issues.

So they make a ORM

Then it create worse issues. Is like the Database-side and the app-side are at war (without intention!) with each other and the database-side only concern themselves for the kind of issues that only happened for specialized tasks.

And the app-side still try to interface to the database, but poorly.

----

Articulating this better, I hope: Is like the movement now to improve the syntax and semantics JS. Or like ASM.js.

So, why not have SQL 2.0 with the fixes everyone since 20 or more years ago already know? And the libraries/API made to acknowledge that databases are not primarily used by "end-users" but app developers?

But that is probably like ask why not clean C++... ;)

I know this is just ask for the moon (obviously not exist one "SQL", only several more or less alike implementations), but the trouble with ORM and databases is more than a decade old and the answer is mostly "yep, we have that problem. Move along"

Re: Why Uber Engineering Switched from Postgres to MySQL

#214

Post by bonesmoses » Tue, Jul 26, 2016, 11:05 PM UTC

leblancfg wrote: post #166

pella wrote: post #58

Roadmaps ( PostgreSQL ) 2016-2017-... * Postgres Professional roadmap ( Pluggable storages, Multimaster cluster with sharding, Effective partitioning, Adaptive query planning, Page-level data compression, Connection pooling, Native querying for jsonb with indexing support, ....) https://wiki.postgresql.org/wiki/Postgres_Professional_roadm... * EnterpriseDB database server roadmap ( Parallelism, Replication, Vertical…

My big question here is why they decided to move over to MySQL instead of using the Citus (also open source) Postgres extension. They don't mention it, so we don't know whether it was considered, and if so, why it was not selected. Postgres' rapid feature growth in the past years is interesting in itself.

Citus currently has major problems joining non-distributed tables with distributed tables, and it doesn't fully support schemas yet. It's almost there, but not quite.

Re: Why Uber Engineering Switched from Postgres to MySQL

#215

Post by [deleted] » Tue, Jul 26, 2016, 11:12 PM UTC

treve wrote: post #208

spotman wrote: post #120

Earlier quoted context omitted.

Try enforcing this on teams that use ORMs like hibernate with 500 developers. Super, duper, common issue, you will find this at every large shop at some point in its life time, usually around the time of hiring people and expanding extremely fast, and taking on some tech debt. All functions of extreme scale, hyper growth, and yeah, not following the absolute best practices all the time, but tech debt is like any debt…

If you're team is 500 strong you definitely should be at a point where you can do things right. If not, the 'debt' is not mainly technical, it's clusterfuck of bad decision making.

[deleted]

Re: Why Uber Engineering Switched from Postgres to MySQL

#216

Post by jrochkind1 » Tue, Jul 26, 2016, 11:16 PM UTC

mamcx wrote: post #189

LoSboccacc wrote: post #185

Earlier quoted context omitted.

it's very hard to abstract away from SQL. to be honest, many nosql have integrated concepts similar to grouping, having, joins in their API as well as they matured, because that's what data elaboration needs.

But still everyone do it. That must tell something, right? And I'm not talking about NoSql borrowing some relational concepts, but the opposite, and more directly, the API. I know that most folks (including the ones that downvote!) have no clue what I'm talking about, because almost nobody (recently) have experience in talking against a database without SQL. Is like if the only way to talk to a NoSql was using Json +…

> SQL was not designed to be used by developers

Sure it was. Who else would it have been designed to be used by?

Re: Why Uber Engineering Switched from Postgres to MySQL

#217

Post by viraptor » Tue, Jul 26, 2016, 11:18 PM UTC

I'd like to see their migration strategy as well. I mean, they say moving from pgsql 9.2 to higher version (which then allows online upgrades) is too much work. Yet they'll have to migrate to mysql, which will take much more engineering effort. For anything close to realtime, they'll need to copy the old data, while at the same time forking the new writes into both pgsql slaves and new mysql servers. And they cannot use WAL for that without some advanced processing.

I hope this follows in the next blog post.

Re: Why Uber Engineering Switched from Postgres to MySQL

#218

Post by dragonwriter » Tue, Jul 26, 2016, 11:20 PM UTC

jrochkind1 wrote: post #216

mamcx wrote: post #189

Earlier quoted context omitted.

But still everyone do it. That must tell something, right? And I'm not talking about NoSql borrowing some relational concepts, but the opposite, and more directly, the API. I know that most folks (including the ones that downvote!) have no clue what I'm talking about, because almost nobody (recently) have experience in talking against a database without SQL. Is like if the only way to talk to a NoSql was using Json +…

> SQL was not designed to be used by developers Sure it was. Who else would it have been designed to be used by?

> > > SQL was not designed to be used by developers

> Sure it was. Who else would it have been designed to be used by?

More accurately than the grandparent: SQL wasn't designed exclusively to be used by developers. It was designed to be accessible to analysts (domain-focused analysts, not systems analysts.)

Re: Why Uber Engineering Switched from Postgres to MySQL

#219

Post by Negitivefrags » Tue, Jul 26, 2016, 11:25 PM UTC

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…

The actual summary of the article is "The design of Postgres means that updating existing rows is inefficient compared to MySQL".

Yes, there were some other points that were just extra annoyances for them but clearly that point was the most important to them. It's what the header image and the first 60% of the article was talking about and yet nobody seems to be engaging with that point in this thread.

Is the design choice bad? They never said it was. It's just an engineering trade off. It's very possible that most workloads benefit from this design. But if you workload involves updating lots of existing rows at large scale, then MySQL is going to be a better choice for you.

Re: Why Uber Engineering Switched from Postgres to MySQL

#220

Post by jedberg » Wed, Jul 27, 2016, 12:24 AM UTC

Negitivefrags wrote: post #219

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…

The actual summary of the article is "The design of Postgres means that updating existing rows is inefficient compared to MySQL". Yes, there were some other points that were just extra annoyances for them but clearly that point was the most important to them. It's what the header image and the first 60% of the article was talking about and yet nobody seems to be engaging with that point in this thread. Is the design…

But it's only an issue if you rely on lots of transactions for data consistency and my point was that it sounds like they are relying on transactions too much which is why they need a more "forgiving" database, which is the part I quoted.

Also they didn't mention anything about the auto vacuumer, which mostly solved the issue they are talking about.

Their lack of mention of the vacuumer and not seeming to know that Postgres supports statement level replication makes me wonder if they took a deep dive into the wrong part of the technology.