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

221–230 of 306 posts

Re: Why Uber Engineering Switched from Postgres to MySQL

#221

Post by Thaxll »

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 fact that Postgres didn't have official replication feature prior to a recent version is just unacceptable.

Re: Why Uber Engineering Switched from Postgres to MySQL

#222

Post by paisawalla »

barrkel wrote: post #194
paisawalla wrote: post #163

Earlier quoted context omitted.

I used to be a huge proponent of ORMs everywhere, but I've come to realize that if you're writing your app in such a way that a developer needs to be able to do any arbitrary data fetch or transformation whenever they want, that's your real problem. The set of retrievals and transformations you want to support should be well-defined, and abstracted into a layer whose interface allows the level above it to only think…

I don't really agree; I think you either need to have a very thin layer between your DB facts and your domain, or else use the DB as a kind of persistence layer for a complex graph. The latter only really works if you've got a primary implementation language and aren't integrating lots of applications / libraries written in different languages communicating with the same database. You need to go down the SOA / distri…

Yeah, I'm saying that as your app grows out of being a simple CRUD app into something more useful and involved, there will be less of a relationship between what you need to store things efficiently, and what you need to present them well. Your model will become more graph-like, probably. For this reason, patterns designed around AR-style models will fail to scale. I disagree that this only works in a mono-lingual environment, although you will need tooling and infrastructure to support it; a model-centric architecture typically doesn't afford the possibility of multi-lingual support.

The code doesn't go in the models, it goes in the service/arbitration layer. DB as a store of facts is obvious -- DB as a 1:1 representation of what yet-unforseen features, UIs and platforms will need is a naive and limiting assumption. You have to build your application in a way that future product needs won't be constrained by storage and modeling decisions, which is a tension that Rails apps frequently encounter.

Re: Why Uber Engineering Switched from Postgres to MySQL

#223

Post by brianwawok »

tracker1 wrote: post #98
brianwawok wrote: post #61

Earlier quoted context omitted.

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

Agreed, which is why many clients create a pool of connections that gets reused. Connection cost is expensive, and the rdbms already handles concurrency and even a couple thousand connections shouldn't be a significant overhead.

I'm trying to find evidence on what memory usage is for MySQL for 1k connections vs postgres with 1k connections. I am finding a lot of people saying postgres has heavier connections but for 1k connections what's the difference. 1MB of memory? 1GB?

Re: Why Uber Engineering Switched from Postgres to MySQL

#224

Post by nierman »

with respect to "Difficulty upgrading to newer releases":

pg_upgade has a --link option which uses hard links in the new cluster to reference files from the old cluster. This can be a very fast way to do upgrades even for large databases (most of the data between major versions will look the same; perhaps only some mucking with system catalogs is required in the new cluster). Furthermore, you can use rsync with --hard-links to very quickly upgrade your standby instances (creating hard links on the remote server rather than transferring the full data).

that is all referenced in the current documentation: https://www.postgresql.org/docs/current/static/pgupgrade.htm...

Re: Why Uber Engineering Switched from Postgres to MySQL

#225

Post by dunk010 »

darkr wrote: post #178
mml wrote: post #157

Earlier quoted context omitted.

Experience with Mysql replication (even simple master/slave) leads me to believe Uber is going to have some rather nasty surprises at some point.

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.

Re: Why Uber Engineering Switched from Postgres to MySQL

#226

Post by Negitivefrags »

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…

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.

Re: Why Uber Engineering Switched from Postgres to MySQL

#227

Post by gnaritas »

mdpopescu wrote: post #177
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.

I was about to argue but then I realized: I've been working in the MS world for so long, I forgot that not everybody has connection pools. The MS recommendation is the opposite: get the connection early, finish with it late, let us worry about the "real" connection. I've been working on multi-TB databases like that, with hundreds of concurrent requests, and never had problems.

This has nothing to do with pooling, you can't use a connection pool in this scenario because you're in a transaction. Even in the MS world, that requires a single connection to be used throughout without releasing it back to the pool. If one isn't in a transaction then each access to the db uses a fresh connection from the pool and this problem never comes up, but the OP here is assuming the time between open and close is a transaction.

Re: Why Uber Engineering Switched from Postgres to MySQL

#228

Post by spotman »

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.

Not really, YouTube runs on MySQL. Pretty sure it's still the largest video site in the world.

Re: Why Uber Engineering Switched from Postgres to MySQL

#229

Post by spotman »

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.

Yea sure some people in the sea of 500 will be doing elegant clean things, working in the core part of the business ( usually at or near the cash register program ) but if you have reason to have 500 developers you don't simply get the luxury of having every project be written perfectly.

Landing somewhere in-between by getting things done not perfect but shippable, and doing it fast as possible is par for the course ime. The best code is always written the 2nd or 3rd time, never the first.

Especially on the case of hyper growth worrying your competitors like lyft or postmates or amazon might get something pivotal out first.

People have to learn somewhere, usually things like this they learn at scale, on the job.

Re: Why Uber Engineering Switched from Postgres to MySQL

#230

Post by ufmace »

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…

I started using CTEs much more often for complex queries actually. IMHO, they're more clear and easier to read than sub-queries, especially when those sub-queries get nested 3 or 4 layers deep.

I agree with the ideas of irregularities. The WHERE vs HAVING doesn't bother me much, and I can't think of a better syntax off hand. My SQL pet peeves are that the SELECT list is at the beginning - I don't know what columns I want exactly until after I type out the JOINs, so I usually type SELECT * and then fill in later. I'd rather put it between WHERE and ORDER BY. I'd also like UPDATE to put the WHERE before the SET, so you don't risk blowing up a ton of data if you forget or miss the WHERE.

Probably a lost cause to get that in, but I would think it wouldn't be too hard to at least support those syntax changes in addition to the current standard.

Post reply on HN