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

61–70 of 306 posts

Re: Why Uber Engineering Switched from Postgres to MySQL

#61

Post by brianwawok »

vbezhenar wrote: post #48

Why would anyone run hundreds of connections? A server can only process number_of_processor_cores connections at once. Sure, few connections might wait for I/O, but not hundreds, unless database is very untypical.

For example: You are using Python and you have 10 web servers and 20 background servers connected to a common DB. Each server has 10 threads, and each thread holds 1 connection open. That is 300 open connections.

Opening and closing connections is very slow and expensive, so almost always better to keep these 300 connections open than to try to be fancy.

You COULD try to say give each server only 3 connections and make them share, which cuts you to 90 connections.. but then you have to try to share state between different python processes (not easy), and will often end up with deadlocks and sync overhead.

Re: Why Uber Engineering Switched from Postgres to MySQL

#62

Post by pritambaral »

0xmohit wrote: post #37
edgyswingset wrote: post #6

Earlier quoted context omitted.

From the post: > [...] This design difference means that the MySQL replication binary log is significantly more compact than the PostgreSQL WAL stream. Doesn't sound like what you described at all.

Isn't WAL-based replication more reliable?

It is. But at least MySQL provides both ways of shipping changes, WAL-shipping like row-based replication and the less reliable statement-based replication, and the DBA can choose which to use when.

Combining that fact with the way Postgres and InnoDB handle secondary indexes means WAL-shipping not only ships the entire new row, but also all the disk blocks of all the secondary index updates, unlike MySQL's row-based replication.

This is actually something I greatly like and want to see in Postgres. Perhaps the decision to choose secondary indexes with direct links to rows was taken because at that time (before replication) it was less read-heavy and the write-amplification wasn't such a concern. But now, when replication is a common requirement (and network IO is not as fast as disk IO) it makes a lot of sense to switch to a single-point-of-update allowing way of storing secondary indexes

Re: Why Uber Engineering Switched from Postgres to MySQL

#63

Post by rspeer »

zeeshanm wrote: post #12

I like the sample data they have used: id first last birth_year 1 Blaise Pascal 1623 2 Gottfried Leibniz 1646 3 Emmy Noether 1882 4 Muhammad al-Khwārizmī 780 5 Alan Turing 1912 6 Srinivasa Ramanujan 1887 7 Ada Lovelace 1815 8 Henri Poincaré 1854

Except it's MySQL, so by the time you read it out it says:

    id  first         last            birth_year
    1   Blaise        Pascal          1623
    2   Gottfried     Leibniz         1646
    3   Emmy          Noether         1882
    4   Muhammad      al-Khw�rizmī  780
    5   Alan          Turing          1912
    6   Srinivasa     Ramanujan       1887
    7   Ada           Lovelace        1815
    8   Henri         Poincaré       1854
(I know, I know. It is possible to configure MySQL encodings correctly. And given that they've put a lot of engineering thought into choosing MySQL, they certainly have.)

Re: Why Uber Engineering Switched from Postgres to MySQL

#64

Post by Roboprog »

vbezhenar wrote: post #48

Why would anyone run hundreds of connections? A server can only process number_of_processor_cores connections at once. Sure, few connections might wait for I/O, but not hundreds, unless database is very untypical.

Like you said, the connection is still in use while waiting for I/O, at which point, another process or thread runs a bit and can also kick off some I/O on yet another connection.

Numbers vary, but that's the principal that uses more connections than cores.

Re: Why Uber Engineering Switched from Postgres to MySQL

#66

Post by tracker1 »

millamox wrote: post #30
thinkingkong wrote: post #11

Posts like this are important. We too often rely on a buzz-word heuristic and that's how you end up with dozens of random technologies that are harder to maintain and don't necessarily solve any of your problems. This method is good, because it shows that when you understand the problem the right way, you can find the right solution, even if by popularity it looks like a "step backwards" Massive Kudos.

I strongly disagree. it would have been useful f they'd stuck to problems without well-known solutions. Sadly, they also mixed in issues which are easily solved, or in a particularly egregious case, where they just complain about a bug. As though MySQL never had a bug. That was silly. My read of it was: Postgres annoyed us a few times, and we got fed up with its, so now something different will annoy us. Please look…

I got a similar impression... though with Uber's scale, funding and resources, they probably could have worked with and through their issues with Postgres. I'm actually surprised they didn't take a multi-pronged approach to their issues. Since they're using Schemaless, I'm curious why they didn't go for one of the many non-sql databases that may well be a much closer match to their use case.

It seems to me that Cassandra (C*) may have been a better match to their needs... yes it means more administrative and application tuning, but would definitely scale to meet their needs. RethinkDB would also likely be a better match to what they are wanting to do.

That said, I've been holding out for some time on PostgreSQL's in the box replication story to take root and mature. There are definitely more mature features and solutions to sharding and replication around MySQL, I just tend to find MySQL to be brittle and every time I've ever worked with it, I have at least a half dozen WTF moments... from binary data handling/indexing, foreign key syntax, ANSI out of spec, and others. PostgreSQL has some great features, and once the replication issues settle in, it will become the default choice for a lot of projects in a lot of organizations. Though, mySQL/maria and even MS-SQL are currently better options for many SQL use cases.

Re: Why Uber Engineering Switched from Postgres to MySQL

#67

Post by muddyrivers »

antonius wrote: post #33
zeeshanm wrote: post #12

I like the sample data they have used: id first last birth_year 1 Blaise Pascal 1623 2 Gottfried Leibniz 1646 3 Emmy Noether 1882 4 Muhammad al-Khwārizmī 780 5 Alan Turing 1912 6 Srinivasa Ramanujan 1887 7 Ada Lovelace 1815 8 Henri Poincaré 1854

Mind explaining the significance? I didn't pick up on it.

It caught my eyes as well. Like it very much.

Re: Why Uber Engineering Switched from Postgres to MySQL

#68

Post by sandGorgon »

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.

Re: Why Uber Engineering Switched from Postgres to MySQL

#69

Post by forgotpwtomain »

Great write up. A couple points -

I'm not sure this post is illustrative of any generally applicable considerations (re: the title) in the choice of Postgresql vs MySQL, since Uber seems to no longer be using a relational model for most of their data and is using MySQL effectively as a key-value store.

> say a developer has some code that has to email a receipt to a user. Depending on how it’s written, the code may implicitly have a database transaction that’s held open until after the email finishes sending. While it’s always bad form to let your code hold open database transactions while performing unrelated blocking I/O, the reality is that most engineers are not database experts and may not always understand this problem, especially when using an ORM that obscures low-level details like open transactions.

I have to very seriously disagree here, ORMs make a lot of things easy - and you can get away with building stuff for a while without understanding the underlying databases or SQL but only to a certain scale (I'd say more like medium-scale, definitely not large or Uber level). If you have engineers writing code that interacts with a database without understanding transactional semantics, the engineer in question not the database is the problem.

> We started out with Postgres 9.1 and successfully completed the upgrade process to move to Postgres 9.2. However, the process took so many hours that we couldn’t afford to do the process again.

There seem to be ways [0][1] to do online upgrades with Postgres (before logical decoding in 9.4), although I haven't personally used them. Not sure if they explored these options at Uber or not?

[0] https://github.com/markokr/skytools [1] http://slony.info/

Post reply on HN