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

Re: Why Uber Engineering Switched from Postgres to MySQL

#231

Post by brianwawok » Wed, Jul 27, 2016, 1:57 AM UTC

manigandham wrote: post #160

darklajid wrote: post #153

Earlier quoted context omitted.

SQL Server needs a couple hits with a cluebat to even satisfy the basic three requirements for a database system they listed (I'm talking about the MVCC line of course). Honestly, if a commercial database provides what you require and you have the budget? Sure, why not. But I'd always try to avoid that myself, because the serious players (MS, Oracle, is anyone still using DB2?) are really, really expensive.

> basic three requirements SQL Server works just fine and has lots of concurrency control to do whatever they need. And the way they're using the database doesn't seem to really make this an issue outside of their replication. > are really, really expensive This whole janky setup they have sounds even worse. None of the commercial relational databases are really that expensive considering what they offer, and we're t…

Maybe you should call up Google, Facebook,linked in, Twitter, and tell them they all made mistakes and should use MSSQL.

Re: Why Uber Engineering Switched from Postgres to MySQL

#232

Post by jswny » Wed, Jul 27, 2016, 2:18 AM UTC

It really is sad that we all just can't appreciate a company like Uber giving us insight into their internal engineering choices. So many people on here think Postgres is so perfect that if you don't like it you must be using it wrong. Postgres is a tool like anything else. It has good use cases, and bad use cases.

Re: Why Uber Engineering Switched from Postgres to MySQL

#233

Post by jlg23 » Wed, Jul 27, 2016, 2:28 AM UTC

mercurial wrote: post #165

djsumdog wrote: post #158

Earlier quoted context omitted.

ORMs have mostly been just painful at ever shop I've been at. I've used ActiveRecord, Squirl, Hibernate, django.db .. they're all various level of suck. The one huge advantage of an ORM is the ability to support multiple databases, but that only really works if you can do everything using the ORM. The moment you have a function too complex that you need to write some SQL, now you need some case statements and multipl…

> The one huge advantage of an ORM is the ability to support multiple databases, but that only really works if you can do everything using the ORM. It has several advantages: support for multiple databases (which is useful, sometimes), the ability to serialize/deserialize an object graph in one go, and sometimes a decent query builder which lets you compose queries as opposed to concatenating strings. Unfortunately,…

> 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 recursive queries in native SQL

[3] building queries is pretty straight forward; what ORMs usually tend to bring to the table is knowledge about the schema and therefore compile-time error reporting - but this can be done in any language where one has the level of reflection, or, in worst case, by a two-stage compilation process where stage 1 generates code from the schema that can then be used by the compiler for verification in stage 2

Re: Why Uber Engineering Switched from Postgres to MySQL

#234

Post by 1024core » Wed, Jul 27, 2016, 3:07 AM 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,…

So if the "send email" step fails (temporarily), the next worker to come along will grab the same receipt and send it again?

I think a better solution would be to use a centralized queue that actually does the mail sending, and retries in case of failure.

Re: Why Uber Engineering Switched from Postgres to MySQL

#235

Post by mdani » Wed, Jul 27, 2016, 3:10 AM UTC

spotman wrote: post #228

dunk010 wrote: post #225

Earlier quoted context omitted.

> 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.

YouTube runs several MySQL instances behind Vitess (source: http://blog.vitess.io/2015/03/scaling-mysql-in-cloud-with-vi... )

Re: Why Uber Engineering Switched from Postgres to MySQL

#236

Post by dialogbox » Wed, Jul 27, 2016, 3:12 AM UTC

Negitivefrags wrote: post #226

jedberg wrote: post #220

Earlier quoted context omitted.

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.

As I know, not every updates rewrite indexes in PG. It has single page clean up and HOT (heap only tuple) update optimization.

Please refer to 64~ page of https://momjian.us/main/writings/pgsql/mvcc.pdf.

Re: Why Uber Engineering Switched from Postgres to MySQL

#237

Post by _Codemonkeyism » Wed, Jul 27, 2016, 3:17 AM UTC

midom wrote: post #134

_Codemonkeyism wrote: post #117

Earlier quoted context omitted.

"1B+ rows in it with mysql?" Been there, really no fun.

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?

Re: Why Uber Engineering Switched from Postgres to MySQL

#238

Post by _Codemonkeyism » Wed, Jul 27, 2016, 3:19 AM UTC

maximegarcia wrote: post #182

_Codemonkeyism wrote: post #117

Earlier quoted context omitted.

"1B+ rows in it with mysql?" Been there, really no fun.

https://github.com/soundcloud/lhm Saved my life a few time :)

Thanks!

Re: Why Uber Engineering Switched from Postgres to MySQL

#239

Post by mdani » Wed, Jul 27, 2016, 3:19 AM UTC

jobu wrote: post #91

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…

> It sounds like Uber is using MySQL as just a data bucket with primary keys They have a couple posts about "Schemaless", but I still don't understand why they used MySQL as the data store instead of something like Cassandra. ( https://eng.uber.com/schemaless-part-one/ ) From that post it looks like they basically built a no-sql database on top of a relational database. The only reason given was operational trust ( "…

IMHO some possible reasons of not using Cassandra could be the following.

You can't use Cassandra if you need atomic increments (yes, they're included but painfully slow due to several trips required to satisfy PAXOS).

Also there are no transaction rollbacks (atomic batches always go one way - forward).

You may hit GC pauses if the JVM is not tuned properly.

If the use case involves its of deletes then tombstone related issues need to be considered.

Re: Why Uber Engineering Switched from Postgres to MySQL

#240

Post by _Codemonkeyism » Wed, Jul 27, 2016, 3:19 AM UTC

barrkel wrote: post #196

_Codemonkeyism wrote: post #117

Earlier quoted context omitted.

"1B+ rows in it with mysql?" Been there, really no fun.

Am there right now, it's not really a problem - you just have to plan your schema changes and use Percona OST.

Thanks!