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

261–270 of 306 posts

Re: Why Uber Engineering Switched from Postgres to MySQL

#261

Post by niccaluim »

dunk010 wrote: post #225
darkr wrote: post #178

Earlier quoted context omitted.

MySQL has had solid and flexible replication options for a long time. Postgres has only just started to catch up in the last couple of years. Don't get me wrong, I would generally choose Postgres over MySQL for an RDBMS with replication requirements these days, but I'm not sure I would have made that same descion a few years ago. There are valid reasons that long established companies such as Google, Twitter, Faceboo…

> There are valid reasons that long established companies such as Google, Twitter, Facebook and countless others chose MySQL as their primary data store. I think you're misrepresenting things here. While all do use MySQL for some specific tasks, it's clear that all also have many other central datastores which are not MySQL. In Google's case this is a gross overstatement.

Twitter's underlying data stores are and always have been MySQL.

Re: Why Uber Engineering Switched from Postgres to MySQL

#262

Post by vanviegen »

pritambaral wrote: post #256
vanviegen wrote: post #200

One major advantage of MySQL's clustered indexes the article doesn't mention is that, although secondary key reads may be a little slower, primary key reads will be faster. The row data lives in the primary key index, so there is no need for referencing an additional database page (possibly causing random I/O). This is especially relevant when doing range queries over the primary key. Imagine a table containing billi…

Uhh ... Postgres supports Index-only scans; as long as the data you're asking for is in the index, that is. So if you have an index on (conversation_id, message_id), and you try to retrieve message ids of a specific conversation, only the index will be touched.

Doh, you're right of course! We were having this problem in the Postgres 9.1 era, before index-only scans were a thing.

Still, it's quite inefficient maintaining an extra copy of the data that is never actually used. Though no longer multiple orders of magnitude less efficient.

However, I'd guess that programmers don't often think to add these seemingly useless fields to an index, as it feels inefficient and just wrong. But at least this offers an out in pathetic cases.

Re: Why Uber Engineering Switched from Postgres to MySQL

#263

Post by garyclarke27 »

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…

Agreed, they even admit Postgres is faster for querying because MySQL requires 2 index lookups. I like the immutable data design of Postgres, is more robust and enables fast transactional DDL. My design never deletes or alters a tuple, so all of these problems that Uber is obsessing about, go away. Question is - Why do they need so many updates? Is there a problem with their data capture?

Re: Why Uber Engineering Switched from Postgres to MySQL

#264

Post by nsxwolf »

continuational wrote: post #259
Negitivefrags wrote: post #226

Earlier quoted context omitted.

None of what you said addresses the issue that I (or they) are talking about. On Postgres an update requires a rewrite of every index of the row. On MySQL it only requires an update of the indexes that were touched by the update. If you have a table with 10 indexes then this means doing 10 extra writes physically to the disk.

10 indexes on one table seems a bit much. It sounds like a table that hasn't been normalized.

Though that is quite proper for a data warehouse.

Re: Why Uber Engineering Switched from Postgres to MySQL

#265

Post by sam_pointer »

sandGorgon wrote: post #68
sam_pointer wrote: post #43

We did something very similar at EA Playfish, at least one alumni of which is part of the Uber engineering team. We used a 2 column InnoDB-backed table for all of our data storage, massively sharded, and run in a 3-host master-slave-slave configuration. At that time EC2 would routinely kill hosts without the courtesy of a poke via ACPI and as such we became very good at quickly recovering shards. In a nutshell this m…

this is frikking awesome! do you have any of the lvm and pipe scripts publicly available ? i'm kinda struggling with building and setting up lvm on ec2 automatically and was wondering if there is any tidbits you can pass along.

Unfortunately not. That company and codebase is very much dead, and I don't own any of it. Even if I did it would be a bunch of context-less shell (in the first iteration) and then a bunch of context-less Chef (in the second platform iteration).

Things I do remember:

- We used ephemeral volumes for all data stores. This was pre-provisioned IOPs and EBS was flaky as heck back then. I can't remember the disk layout, although we did experiment a great deal.

- We took great pains to ensure there was enough space to make the snapshot (IIRC is was a telemetry/monitoring item)

- The pipe scripts were essentially "netcat".

The best I can offer is this talk: http://vimeo.com/57861199

Re: Why Uber Engineering Switched from Postgres to MySQL

#267

Post by stubish »

jedberg wrote: post #220
Negitivefrags wrote: post #219

Earlier quoted context omitted.

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

Both vacuuming and logical replication are discussed in the article. In particular, vacuuming is easier with InnoDB since the changed records all exist in the redo log whereas PostgreSQL needs to scan the whole table. pglogical is mentioned for people running PG9.4+ as a way of doing minimal downtime cross version upgrades, which wasn't an option back with PG9.2 unless you go with something like slony1 or londiste.

Re: Why Uber Engineering Switched from Postgres to MySQL

#268

Post by madhusudhan000 »

So let me try to summarise this.

Poor replica MVCC support

They are actually pointing to a blog article written in 2010 -> http://blog.2ndquadrant.com/tradeoffs_in_hot_standby_deplo/

Do they realise that it is 2016 ?

Guess they did't bother to understand the hot standby feedback system.

> Postgres’s design resulted in inefficiencies and difficulties for our data at Uber.

What kind of inefficiency ? The explain what is purpose of WAL and replication which every database person knows about but didn't care to explain the actual problem at hand ?

Data corruption

> During a routine master database promotion to increase database capacity, we ran into a Postgres 9.2 bug

Why the heck didn't they upgrade to a newer version ? Did you report this bug to pg dev , did they take so much time to fix this, or were you just assuming that the bug could fix itself ?

> The bug we ran into only affected certain releases of Postgres 9.2 and has been fixed for a long time now. However, we still find it worrisome that this class of bug can happen at all.

Postgres 9.2 is pretty old and there has been 3 major releases after that. WTF ?

I can say countless instances where MySQL data corruption was a constant nuisance with version 5.5 and they have fixed it with newer releases.

Replication

> During peak traffic early on, our bandwidth to the storage web service simply wasn’t fast enough to keep up with the rate at which WALs were being written to it

So you have run into a hardware limitation and then blame postgres. What was limit that you hit ? I don't understand this point at all.

Concept of context switching

I am surprised that this is actually an issue, in a database the slowest part is always the disk and not the CPU. Confused on how did they hit this limitation first without actually touching others.

Time taken by a context switch : http://stackoverflow.com/questions/21887797/what-is-the-over...

Which is in microseconds.

InnoDB buffer pool

> By comparison, the InnoDB storage engine implements its own LRU in something it calls the InnoDB buffer pool

Postgres has something similar called shared_buffer. They are speaking as if postgres relies entirely on the operating system which is false.

> It makes it possible to implement a custom LRU design. For instance, it’s possible to detect pathological access patterns that would blow out the LRU and prevent them from doing too much damage

Not sure what kind of damage they are speaking. In a postgres sequential scan (full table scan), a ring buffer is used instead and does not result in the shared buffers being blown away.

If you need a custom LRU design, there is definitely something wrong in the way that you are using an OLTP database.

Connection Handling

This is complete BS. Nobody uses databases without connection pools. Agree that a thread is more lightweight than a process, but you would never hit this limit at all in real time which is in the order of microseconds again. In a production system, one would open connections immediately and then hold them in the connection pool. This overhead is almost not visible at all. If you are constantly opening and closing connections then there is something seriously wrong with your design.

> However, we have had occasional application bugs in our backend services that caused them to open more active connections (usually “idle in transaction” connections) than the services ought to be using, and these bugs have caused extended downtimes for us

So they are blaming the database for a bug in their design/system. Computers are no match for human stupidity.

> Accordingly, using pgbouncer to do connection pooling with Postgres has been generally successful for us.

Again what is the problem, the whole article smells more and more like a useless rant, just because you dont know how to use them ?

Conclusion

Another thing is that they have not given any kind of query/access pattern in which they use postgres/mysql. They put in a couple of low level things and then say that postgres is badly designed.

I can think of only two logical explanations

1) The article writer was already familiar with MySQL and they didn't bother to even dig into postgres deeper

2) They have been paid by oracle :P

Re: Why Uber Engineering Switched from Postgres to MySQL

#269

Post by fanf2 »

LoSboccacc wrote: post #184
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…

I've tried to setup a replicated postgres with autofailover and it honestly is a pita. the only sources of failover are rando scripts over the internet, that you have to download hammer in to your version dialect and hope you don't trigger one of the many uncovered failover modes. sure log shipping works, but that's far, FAR from a working solution. the gap requires ton of development hour, testing etc. can't really…

Does repmgr handle most of this?

Re: Why Uber Engineering Switched from Postgres to MySQL

#270

Post by stubish »

busterarm wrote: post #87
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…

Can't agree with this post enough. I find their whole writeup to be terribly myopic. When they started their service, Postgres was almost certainly the right choice for what they were building and their MySQL setup was not. Now Postgres is less effective for them. These kind of tech switches are _inevitable_ if you're making the right choices for your organization. This strikes me as very similar to the article where…

While the article is discussing PostgreSQL vs MySQL, Uber's actual win seems to be from switching their data model to their sharded Schemaless key=value store. I expect they would have got this win no matter what backend they chose for Schemaless.
Post reply on HN