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

281–290 of 306 posts

Re: Why Uber Engineering Switched from Postgres to MySQL

#281

Post by jlg23 »

mercurial wrote: post #250
jlg23 wrote: post #233

Earlier quoted context omitted.

> It has several advantages: [1] support for multiple databases (which is useful, sometimes), [2] the ability to serialize/deserialize an object graph in one go, and [3] sometimes a decent query builder which lets you compose queries as opposed to concatenating strings. [1] was the point of the comment you replied to and it provided a very important constraint as well [2] rarely needed & easy to implement with recurs…

I don't see how recursive queries are going to help you serialize a new Foo with a new field of type Bar with a new field of type FooBar, each going in a different table. That's what I mean by serializing an object graph. As for building queries, SQL is straightforward (mostly). The problem is that it composes very badly. Any time you need to implement something like an advanced search (ie, lookup the same informatio…

> I don't see how recursive queries are going to help you

You construct a graph of objects with very few queries, transform it and write it back; with knowledge of the db-schema this will outperform any ORM-based solution and give much greater flexibility.

> As for building queries [...] The problem is that it composes very badly. [...] the best you can do is concatenating partial queries and hoping you got the parentheses count right.

There is an area between full-fledged ORMs and string concatenation. In a purely functional approach queries are composed by composing functions that compile to queries. Postmodern[1][2] is a good, open source example, though in most commercial projects we just built our own wrappers for the tasks and databases at hand. This also allows for much better performing code since for a lot of tasks hooking up the db-reader to the json emitter without going through object instantiation reduces memory and cpu consumption by an order of magnitude (or two), while in the same project, some code benefits from a OOP approach (for which you just use a reader that constructs the objects on the fly).

[1] http://marijnhaverbeke.nl/postmodern/

[2] http://marijnhaverbeke.nl/postmodern/s-sql.html (yes, it does string concatenation at run time, but it does it for you, you don't worry about getting the parenthesis count right)

Re: Why Uber Engineering Switched from Postgres to MySQL

#283

Post by pluma »

philliphaydon wrote: post #249
pluma wrote: post #192

Earlier quoted context omitted.

Technically they use it as a dumb key-value storage.

There was a great talk from a FB engineer who talked about using MySQL as a key-value store, then another engineer posted a blog about how they use MySQL and queries and joins etc, I asked about the video and he got super defensive and said it was wrong and the guy had no idea what hes talking about. It really put me off ever wanting to even consider working at Facebook. Digging around I can't find either the blog or…

I've talked to several Facebook devs who confirmed to me that FB primarily uses MySQL as a key-value store (e.g. this is why GraphQL makes so much sense for them). They were quick to point out however that of course they don't know everything FB does.

The problem with making absolute statements about what FB does is that it seems to be structured as a large number of mostly independent teams that are free to choose and develop whatever technology they need to solve their problems. There's actually a talk about scaling by a FB dev (don't have the link right now) that uses this as an example for what it means when they talk about "Facebook scale" (another example is that their monorepo outgrew git so they created their own extension to mercurial instead).

It's entirely possible that the main use of MySQL at FB is as a key-value store while at the same time there are small parts of applications using it with plain old queries and joins.

Re: Why Uber Engineering Switched from Postgres to MySQL

#284

Post by bigato »

ufmace wrote: post #230
barrkel wrote: post #195

Earlier quoted context omitted.

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…

Complex queries using CTEs may be easier for you to reason about when you are writing them, but are a nightmare to understand if you are not the one who wrote them. We have heaps of CTE-intensive technical debt and usually it takes longer to understand what they are doing than it would take to write them from scratch from a specification.

If you are using them to ease your understanding, chances are that you are doing the job in a very memory intensive way. It also tends to be slower because the generated intermediate results do not have indexes nor good statistics that could help the query planner to be efficient. Essentially, you are taking upon yourself the query planner job and assuming you'll do it better yourself.

Re: Why Uber Engineering Switched from Postgres to MySQL

#285

Post by dikei »

nsxwolf wrote: post #264
continuational wrote: post #259

Earlier quoted context omitted.

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.

A data warehouse should use much fewer transactions though.

Re: Why Uber Engineering Switched from Postgres to MySQL

#286

Post by david_k_kim »

madhusudhan000 wrote: post #268

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

I completely agree with you. The blog post seems to be seriously biased to justify their choices due to other reasons.

Re: Why Uber Engineering Switched from Postgres to MySQL

#287

Post by tracker1 »

chucky_z wrote: post #140
tracker1 wrote: post #66

Earlier quoted context omitted.

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

What replication issues are you referring too? I never once had a problem with pgsql's replication across 8.1->9.5. It would randomly die, but that was always either my fault or the applications fault, never pgsql itself. The lack of master-master seems to be the big thing everyone mentions, but PostgresXL is currently in a usable-in-production state.

But, what is the current replication setup that comes with postgres that is well documented with the PostgreSQL (current-version) documentation... the past, when I've looked there's mention of 2-3 solutions (none in-the-box) and others that require at least a 5-figure support contract.

Compare to MongoDB, RethinkDB, MS-SQL and others where the tooling for replication comes in the box. Yes, to of the examples are "no-sql" but even the mysql replication is in the box and supported as such.

Re: Why Uber Engineering Switched from Postgres to MySQL

#288

Post by david_k_kim »

xrstf wrote: post #46
swasheck wrote: post #3

this reads like a laundry list of buzzwords that were designed to justify not throwing any effort into postgresql and just going with a new shiny toy (not mysql. yes. i know it's been around for a while). it happens everywhere.

> not throwing any effort into postgresql The amount of research about the on-disk internals of both PostgreSQL and MySQL are a lot more effort than I would have probably spent (granted, I don't have a team of highly paid devs at my disposal, but still, I've seen technical decisions made on the basis of Google Trends...).

It is not much effort to understanding the storage of each database. It is like a common sense for database guys.

Re: Why Uber Engineering Switched from Postgres to MySQL

#289

Post by kasey_junk »

brianwawok wrote: post #223
tracker1 wrote: post #98

Earlier quoted context omitted.

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?

https://wiki.postgresql.org/wiki/Number_Of_Database_Connecti...

Its a little outdated but I've found it largely holds. That is, you'd want a mighty box for 1000 concurrent connections.

That said, connection queuing works really well with postgres such that throughput is frequently better at lower connection counts than at higher ones.

I have no experience with mysql that is less than 15 years out of date.

Re: Why Uber Engineering Switched from Postgres to MySQL

#290

Post by Swannie »

_Codemonkeyism wrote: post #237
midom wrote: post #134

Earlier quoted context omitted.

As long as you can shard (across multiple instances, or even within same instance, to avoid B-Tree latching), 1B+ rows within MySQL is piece of cake. Also, MySQL* is getting LSM-Tree support lately, which makes high performance data ingestion combined with OLTP workload quite feasible. * https://github.com/facebook/mysql-5.6/tree/webscalesql-5.6.2...

"I wonder how Uber adds an index to a table that already has 1B+ rows in it with mysql?" "As long as you can shard " Not sure how sharding helps with 1B+ tables when adding indices, care to share?

This is: "Split the index in your application code."

And then do your joins, in your application code.

Post reply on HN