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

Re: Why Uber Engineering Switched from Postgres to MySQL

#201

Post by rpcope1 » Tue, Jul 26, 2016, 10:16 PM UTC

oneweekwonder wrote: post #190

harel wrote: post #170

Earlier quoted context omitted.

You can read it in their next blog post: "How we migrated back to PostgreSql from mySql after migrating from PostgreSql to mySql". My experience with mySql replication was OK, but I did nothing fancy - just master/slave standard stuff. However the database itself was allowing you to do stuff by default (at the time at least) that is really bad. Not to mention scaling while I was using it was pretty much "you're on yo…

Personally I prefer Postgres. But at the shop we use MySQL. One of the leads personally believe in Percona Server[0] and mentions some of the old mysql folks work there. I like their docs and the idea of a more tuned mysql config. [0]: https://www.percona.com/doc/percona-server/5.7/index.html

I can second using Percona; I wasn't much of a believer in MySQL until I inherited a medium sized application using Percona 5.7. Once you get past a few of the initial warts (like making sure the database doesn't silently truncate your data--yuck), it's been really performant, stable and impressive (and we're running a setup with a fairly complex schema with master operating at ~3000 QPS average, a slave actively replicating for read load balancing and backup, and Sphinx for FTS).

Knowing what to be careful with on MySQL, I would certainly consider using the Percona flavor again in a new application.

Re: Why Uber Engineering Switched from Postgres to MySQL

#202

Post by njs12345 » Tue, Jul 26, 2016, 10:17 PM UTC

carterehsmith wrote: post #199

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…

I just want to add that newer versions of MySQL can add indexes online. In fact, it looks like all DDL can be done online, even though some if it (like adding a column) may require the table to be rebuilt, which can take time. Note that the master is still available for read and write during this. Replicas will lag though.

From the MySQL docs: http://dev.mysql.com/doc/refman/5.7/en/alter-table.html

> Updates and writes to the table that begin after the ALTER TABLE operation begins are stalled until the new table is ready, then are automatically redirected to the new table without any failed updates.

While the master may be technically up for writes during this period, it's not much a goer if your table is large and has any write traffic at all, as anything writing will stall for what may be an extended period.

Re: Why Uber Engineering Switched from Postgres to MySQL

#203

Post by carapace » Tue, Jul 26, 2016, 10:18 PM 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 ( "…

I had that same thought, that the time spent rolling their own system could be better spent just learning some existing good-enough thing.

A great way to get familiar with something is to be the folks who write it. It's also much more fun to design and implement something new than to just learn some other fella's software. I'm guilty of this myself.

But I've started to remind myself that "somebody else has had this problem" and there's probably a good enough solution out there already.

Put another way, is what you are trying to do really so novel? In the case of Uber's infrastructure, you would have to talk for awhile to convince me that they really really need something not-off-the-shelf.

Re: Why Uber Engineering Switched from Postgres to MySQL

#204

Post by fdr » Tue, Jul 26, 2016, 10:19 PM UTC

markpapadakis wrote: post #179

Great write-up. A few observations: 1. The encoding and translation schemes of Postgres and mySQL/InnoDB are well described in the blog post, and I would also agree that InnoDB’s design is, all things considered, better for all the reasons outlined in the post. 2. I don’t understand why anyone still uses lseek() followed by read()/write() and not pread()/pwrite() syscalls. It’s trivial to replace the pair of calls wi…

w.r.t. 2:

https://www.postgresql.org/message-id/6248.1046130083%40sss....

     Manfred Spraul  writes:
     > Tom Lane wrote:
     >> It seems unlikely to me that eliminating lseek on some platforms would
     >> be worth the hassle of maintaining two code paths.  lseek is mighty
     >> cheap as system calls go.
     >> 
     > It was considered expensive enough to write a syscall avoidance layer 
     > that caches the file pointer and skips lseek if fpos==offset.
     
     You're missing the point: that layer is mostly there to ensure that we
     don't foul up the kernel's readahead recognition for sequential fetches.
     It's nice that Linux doesn't care, but Linux is not the only platform
     we worry about.
     
     			regards, tom lane

Re: Why Uber Engineering Switched from Postgres to MySQL

#205

Post by CuriouslyC » Tue, Jul 26, 2016, 10:33 PM UTC

djsumdog wrote: post #158

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…

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…

This is why I like SQL Alchemy. It provides an ORM that acts as a veneer over a very powerful SQL expression library, and you can move back and forth between the two seamlessly, even mixing them in individual statements.

Re: Why Uber Engineering Switched from Postgres to MySQL

#206

Post by otterley » Tue, Jul 26, 2016, 10:41 PM UTC

0xmohit wrote: post #5

Facebook maintains it's own fork [0] of MySQL. A couple of interesting talks are also available: MySQL at Facebook, Current and Future [1] and Massively Distributed Backup at Facebook Scale [2]. [0] https://github.com/facebook/mysql-5.6 [1] https://www.youtube.com/watch?v=jqwegP9xwVE [2] https://www.youtube.com/watch?v=UBHcmP2TSvk

And so does Yahoo, and Google, and...

Re: Why Uber Engineering Switched from Postgres to MySQL

#207

Post by noir_lord » Tue, Jul 26, 2016, 10:42 PM UTC

rpcope1 wrote: post #201

oneweekwonder wrote: post #190

Earlier quoted context omitted.

Personally I prefer Postgres. But at the shop we use MySQL. One of the leads personally believe in Percona Server[0] and mentions some of the old mysql folks work there. I like their docs and the idea of a more tuned mysql config. [0]: https://www.percona.com/doc/percona-server/5.7/index.html

I can second using Percona; I wasn't much of a believer in MySQL until I inherited a medium sized application using Percona 5.7. Once you get past a few of the initial warts (like making sure the database doesn't silently truncate your data--yuck), it's been really performant, stable and impressive (and we're running a setup with a fairly complex schema with master operating at ~3000 QPS average, a slave actively rep…

I hit an in production bug with the silent truncation field thing the other day, old system (2009ish) that was been moved.

Table had a field that was varchar(128) and wasn't used (original filename but system generated a hashed filename and served it with that name (for sharding, lots of files)) so for 5 years MySQL had been silently killing everything > 128 characters and it didn't matter.

Then along comes our hero (me in this story) who does all the upgrades, tests everything thoroughly (he thought) and it's all fine, deploy, test everything fine.

Next morning, 9 bug reports "Can't upload files at all FIXITNOW!!!".

Turns out company was auto generating PDF's from some report software that dumped all the data into the filename and none of them where less than 128 chars.

MySQL 5.7 started not silently dumping data into the bitbucket in the sky and instead threw an error (when quite reasonably you tried to shove 300 characters into a varchar(128)).

TLDR: MySQL doing the right thing broke the broken.

Re: Why Uber Engineering Switched from Postgres to MySQL

#208

Post by treve » Tue, Jul 26, 2016, 10:50 PM UTC

spotman wrote: post #120

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…

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.

Re: Why Uber Engineering Switched from Postgres to MySQL

#209

Post by mrmondo » Tue, Jul 26, 2016, 10:52 PM UTC

mml wrote: post #157

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…

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

Heh, yes came here to say something rather similar...

Re: Why Uber Engineering Switched from Postgres to MySQL

#210

Post by dkarapetyan » Tue, Jul 26, 2016, 10:58 PM UTC

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…

Uber engineering in general is extremely disappointing. They consistently oversell/overhype mundane processes and technologies both software and otherwise. I suspect this is mostly because they are driven more by marketing and business needs than actual engineering bottlenecks. That is the only way I can explain all the technological missteps they keep bragging about.