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

Re: Why Uber Engineering Switched from Postgres to MySQL (2016)

#71

Post by chousuke » Sat, Feb 27, 2021, 3:33 PM UTC

Quekid5 wrote: post #30

user5994461 wrote: post #26

Earlier quoted context omitted.

The replication was redone around that time (not sure which version exactly). It's still working on the same principles though, sending queries and redoing them on each replica. Before in short, the WAL was sent every minute and always 10MB even if there were no changes. Now it's more adaptive, actually doing nothing when they are no changes, and picking up quicker when changes begin. I am surprised they don't mentio…

I don't understand this. Why would it be sending queries and redoing on a replica and sending the WAL? Just sending the WAL would seem to be sufficient, or alternatively: sending queries would be redundant if you just send the WAL and apply directly at the secondaries.

In streaming (physical) replication, PostgreSQL sends the WAL only, and it's applied on the replica; no "sending of queries" is involved, or even possible; with physical replication, the secondary has to keep itself identical with the primary, otherwise replication will fail. This is why you can't use physical replication across major versions, since they can't be bit-for-bit identical.

In more recent versions there's "logical replication", which sort of "sends the queries", in that the secondary node has its own database state that does not have to be exactly identical with the primary, allowing for replication across major versions.

In my opinion though, unless you really need logical replication for some reason, stick with streaming replication. It's much easier to understand and there are fewer failure modes.

Re: Why Uber Engineering Switched from Postgres to MySQL (2016)

#72

Post by llarsson » Sat, Feb 27, 2021, 3:34 PM UTC

spuz wrote: post #69

chousuke wrote: post #65

Earlier quoted context omitted.

Being used to PostgreSQL, this one actually bit me once during a production upgrade. A database migration failed due to a Galera transaction size limit that I unfortunately hadn't caught testing the migrations on a single database, and I had to restore from the pre-upgrade backup before resolving the issue and continuing. It wasn't a major issue (the upgrade finished well within the acceptable downtime window), but u…

Which database are you talking about? Postgres or Mysql?

Something that happened when they were on MySQL, before PostgreSQL.

The phrasing at the start and "Galera" mention shows this.

Re: Why Uber Engineering Switched from Postgres to MySQL (2016)

#73

Post by Tostino » Sat, Feb 27, 2021, 3:42 PM UTC

petergeoghegan wrote: post #70

I committed a patch that added a mechanism I called "bottom-up index deletion" recently: https://www.postgresql.org/docs/devel/btree-implementation.h... https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... Bottom-up deletion is specifically designed to ameliorate what the blog post refers to as "write amplification". Testing has shown that it's very effective with many workloads.

Just wanted to say how impressed I was with this solution and the results it achieved when I was following the development on -hackers.

Re: Why Uber Engineering Switched from Postgres to MySQL (2016)

#74

Post by amenonsen » Sat, Feb 27, 2021, 3:49 PM UTC

chousuke wrote: post #71

Quekid5 wrote: post #30

Earlier quoted context omitted.

I don't understand this. Why would it be sending queries and redoing on a replica and sending the WAL? Just sending the WAL would seem to be sufficient, or alternatively: sending queries would be redundant if you just send the WAL and apply directly at the secondaries.

In streaming (physical) replication, PostgreSQL sends the WAL only, and it's applied on the replica; no "sending of queries" is involved, or even possible; with physical replication, the secondary has to keep itself identical with the primary, otherwise replication will fail. This is why you can't use physical replication across major versions, since they can't be bit-for-bit identical. In more recent versions there'…

> In more recent versions there's "logical replication", which sort of "sends the queries"

What it sends is not the queries, but a logical description of the changes to each row that were made by running the query. So an UPDATE that changes N rows would generate N changes to be applied to the corresponding rows (usually identified by primary key) on the logical replica, not a single update that had to be "re-executed".

Re: Why Uber Engineering Switched from Postgres to MySQL (2016)

#75

Post by petergeoghegan » Sat, Feb 27, 2021, 3:50 PM UTC

Tostino wrote: post #73

petergeoghegan wrote: post #70

I committed a patch that added a mechanism I called "bottom-up index deletion" recently: https://www.postgresql.org/docs/devel/btree-implementation.h... https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... Bottom-up deletion is specifically designed to ameliorate what the blog post refers to as "write amplification". Testing has shown that it's very effective with many workloads.

Just wanted to say how impressed I was with this solution and the results it achieved when I was following the development on -hackers.

Thanks

Re: Why Uber Engineering Switched from Postgres to MySQL (2016)

#76

Post by evanelias » Sat, Feb 27, 2021, 3:51 PM UTC

corty wrote: post #8

DaiPlusPlus wrote: post #3

Earlier quoted context omitted.

Define: "complicated"

PostgreSQL: "your date 2020-02-31 isn't a date, fix that" MySQL: "2020-02-31? Whatever man, I'll just enter something..."

MySQL's default settings reject invalid dates for over 5 years now, since MySQL 5.7.

How long are people going to keep repeating this complaint? Literally every version of MySQL and MariaDB that allows invalid dates by default (MySQL 5.6 and older, MariaDB 10.1 and older) has reached end-of-life for upstream support from the vendor!

Re: Why Uber Engineering Switched from Postgres to MySQL (2016)

#77

Post by Tostino » Sat, Feb 27, 2021, 3:53 PM UTC

asah wrote: post #50

chris_wot wrote: post #37

Earlier quoted context omitted.

The response was: https://news.ycombinator.com/item?id=14222721

Even though 99.9% of applications will never run into Uber's issues, it's been 4 years and 4 major versions later, and I'd love to review these complaints and see if they still apply to PG 13.

Wait until 14 for that comparison and it'll look much better. Bottom up index deletion helped solve some of the write amplification issues.

Re: Why Uber Engineering Switched from Postgres to MySQL (2016)

#78

Post by evanelias » Sat, Feb 27, 2021, 3:54 PM UTC

BitPirate wrote: post #59

b112 wrote: post #36

Earlier quoted context omitted.

https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mo... Much of your MySQL complaint is not a MySQL issue, but a config issue. And yes, powerful config options are good, not bad.

One can still complain about mysqls dumb defaults. https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.ht... "Hey, let's just not act transactional on a timeout by default"

That default is sensible. You're making it sound like this setting applies to all "timeouts" regardless of type. That is not the case.

This setting applies to lock-wait timeouts. The default value allows applications to decide on the correct course of action: either re-try just the statement that timed out (without having to re-do the previous parts of the transaction), or rollback the transaction.

The application still receives an error on the timeout regardless of this setting. The database doesn't automatically commit the previous statements in the transaction regardless of this setting.

Re: Why Uber Engineering Switched from Postgres to MySQL (2016)

#79

Post by chousuke » Sat, Feb 27, 2021, 3:59 PM UTC

mgkimsal wrote: post #64

looperhacks wrote: post #18

Earlier quoted context omitted.

How is creating a new user complicated? The normal CREATE USER is all I've ever needed to create a new user in postgres (assuming I don't have set up the pg_hba so that I need to allow every user separately)

Most tutorials/instructions I read have you use "createuser" command from the system shell. But... you have to be able to switch to a system 'postgres' user first , which ... perhaps you don't have privileges to do, or need sudo access or whatnot. If you can install postgres, connect to it directly with some sort of root identity , then immediately create users and databases (as is the case with pretty much every mys…

With MySQL, you'll still have to switch to root to connect by default? I honestly don't remember, since it's been ages since I set up MySQL manually.

If MySQL actually allows administrative access out-of-the-box without any kind of special authorization, then that's a terribly insecure default.

With PostgreSQL, you have to switch to the superuser to configure things further because that's the only sane default you can have on an unconfigured system. If you can run commands as the user PostgreSQL is running as, you are "safe" to trust, and PostgreSQL will let you in.

UNIX ident authentication is also is extremely convenient for local applications, since you don't even have to have a password for the account, or make the PostgreSQL server network-accessible in any way.

Oracle can do the same thing, and so can MySQL, apparently (with IDENTIFIED VIA unix_socket).

MySQL user management has its own complexity in that you have to manage "user@address" identities, and the same user at different addresses or auth methods can have different permissions. How's that "simple"? With PostgreSQL, your users will at least map to the same user regardless of how they authenticate themselves.

Re: Why Uber Engineering Switched from Postgres to MySQL (2016)

#80

Post by amenonsen » Sat, Feb 27, 2021, 4:04 PM UTC

Quekid5 wrote: post #30

user5994461 wrote: post #26

Earlier quoted context omitted.

The replication was redone around that time (not sure which version exactly). It's still working on the same principles though, sending queries and redoing them on each replica. Before in short, the WAL was sent every minute and always 10MB even if there were no changes. Now it's more adaptive, actually doing nothing when they are no changes, and picking up quicker when changes begin. I am surprised they don't mentio…

I don't understand this. Why would it be sending queries and redoing on a replica and sending the WAL? Just sending the WAL would seem to be sufficient, or alternatively: sending queries would be redundant if you just send the WAL and apply directly at the secondaries.

It doesn't make sense to you because everything in the comment you're replying to is wrong.

Neither log shipping (copying WAL files one by one) nor streaming replication (sending a stream of WAL) works by sending queries. WAL segments are 16MB by default, and the default archive_timeout is 0, not 1 minute (and the archive timeout is not applicable to streaming replication anyway). There is also nothing "adaptive" about the replication—when there is no traffic, there will be ~no changes, and when there are changes, they will be sent to the replica.

I don't understand what the comment is suggesting used to happen in periods of no activity that made replication unusable, but it is also probably incorrect, and has nothing to do with the write amplification problem.