Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Why Uber Engineering Switched from Postgres to MySQL (2016)

eng.uber.com

Post reply on HN
Print view View original

121–130 of 133 posts

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

#121

Post by EdwardDiego »

evanelias wrote: post #84
pizza234 wrote: post #15

Earlier quoted context omitted.

The main point, clustered vs. nonclustered indexing, is architectural, and not inherently inefficient; it depends on the use case. "Highly advanced" databases give both options, but AFAIK, MySQL/PGSQL will likely not offer this, at least for a very long time, since it requires radical changes.

On the one hand, MySQL has offered this for two decades, by virtue of pluggable storage engines being core to its design. Some storage engines use clustered indexes and some do not. The user can decide which one matches their use-case; very large companies can design their own custom special-purpose storage engines; etc. On the other hand, mixing storage engines in a single db instance has operational downsides (espe…

I still remember the "good" ol days when the default MySQL engine was MyISAM. Even after InnoDB became the default, a lot of people were still configuring it for MyISAM for their 30 user webapp because they'd heard it was faster, and besides, you can ensure data integrity in code, right?

I made a bit of money freelancing on "my database for my LAMP stack app is corrupt!" issues by a) demonstrating that InnoDB wouldn't slow down their webapp in any measurable form and then b) trying to save and normalise as much data as possible.

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

#122

Post by tpetry »

derekperkins wrote: post #101
tpetry wrote: post #54

Earlier quoted context omitted.

The best part is transactional ddl statements. You can do your database migration in a transaction, if something fails the transaction is rolled back compared to an invalid state with mysql.

That was added a couple years ago in MySQL 8, as were window functions and arbitrary checks. The two databases continue to fill in the gaps that were historically reasons to choose one over the other.

Mysql 8 supports atomic ddl, which means if you drop two tables in a single DROP TABLE query either both are deleted or none. But it does not support postgresql‘s transactional ddl feature where you can start a transaction change the structure of multiple tables, change some data etc. and commit at the end.

Mysql 8 males ddl changes atomic, but does not support transactional mode where you could rollback them.

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

#123

Post by gigantecmedia »

spiralx wrote: post #106
fma wrote: post #92

Earlier quoted context omitted.

Can we maybe get a list of company blogs that are legit? As in they practice what they preach? Facebook, Amazon, Netflix Google, Microsoft comes to the top of my head but someone is free to burst my bubble.

Figma and Instagram have had good articles on their blogs, although I'm not a regular follower of either.

agree, figma and sketch have fairly good and interesting articles. used both of them, love their products and vision.

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

#124

Post by throwdbaaway »

evanelias wrote: post #118
throwdbaaway wrote: post #117

Earlier quoted context omitted.

Thanks for the insight! So how frequent does schema change happen to UDB?

Sorry, I don't recall the exact frequency, I left FB in 2015.

Then I guess my point about UDB doesn't require schema change will still stand then? Looking at https://github.com/facebookarchive/linkbench, the table structure looks pretty much "final". And judging by https://dom.as/2015/07/30/on-order-by-optimization/, the access pattern is pretty much fixed as well.

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

#125

Post by fakedang »

denysvitali wrote: post #41
Proziam wrote: post #34

Earlier quoted context omitted.

I wish this resource existed (or that I knew it existed if it did) years ago. I always seem to learn about the things that would have made my life easier after I've already done things the hard way.

No pain no gain I guess. Learning things in a difficult way is not a waste of time tbh

There is often little distinction between difficult and suboptimal though.

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

#126

Post by mgkimsal »

chousuke wrote: post #102
mgkimsal wrote: post #96

Earlier quoted context omitted.

"With MySQL, you'll still have to switch to root to connect by default? " You connect with a root account from any account, and when installed, the root account password is part of the setup process. "and the same user at different addresses or auth methods can have different permissions".... It joe@localhost and joe@remotehost don't have to be 'the same user' in that they're not tied to a system account in any way.…

I'm just not sure how it's confusing? PostgreSQL users aren't "tied" to system accounts either. You can have any number of PostgreSQL users that have no system equivalent. In fact, the process seems to be exactly the same as with MySQL: I just tried installing the MariaDB server (dnf install mariadb-server), and it didn't prompt me for an admin user; instead, I can directly connect to the database as root using sudo,…

"Maybe some of the confusion stems from the fact that the documentation you linked seems to assume that the database is created according to convention to run as the "postgres" user (as it usually is)".

The entire point was a reply to someone saying "it's confusing". I'm pointing out how it's confusing, and you come back with that either that 1) MySQL is confusing or 2) you don't think it's confusing. Then you point to documentation which you admit might be a point of confusion.

I've had people say "I installed postgres - here's the password". Then... I can't log in. Because I can't switch to the postgres user. Or they created some login that I can't use. Or something else... because it's somewhat confusing, unless you do this (postgres administration) as part of your regular/periodic work.

re: "I just installed Maria"... If someone uses common default package managers to set up mysql/Maria, and also for postgres, you'll be able to connect to mysql/Maria from any account. You'll only be able to connect to postgres if you switch to the postgres user.

Again - point of the comment was agreeing with an earlier comment that "this is confusing". You seem to acknowledge that it can be confusing.

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

#127

Post by user5994461 »

Quekid5 wrote: post #85
amenonsen wrote: post #80

Earlier quoted context omitted.

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" abo…

Thank you for confirming my suspicions :).

I was calling the WAL the "queries" to simply, never mind that, it doesn't matter whether it contains the queries or not.

What's important is that the WAL was generated on a periodic basis and of a constant size. Say 16MB every minute. It's pretty much a plain file, that could be stored on S3/FTP.

This had a lot of drawbacks:

- Replicas were measurably late behind the current state, simply because of the built-in delay in "replication".

- It was incredibly inefficient on bandwidth and storage. Consider the time it takes to transfer large files (especially for off-site replicas) and storage costs. That further contributed to poor performance and delay.

- There could be many WAL files generated at once when there were changes happening. They would take FOREVER to be processed. It was commonplace for replicas to fall 5-10 minutes under what I consider to be minimum activity.

Long story short, the replication was reworked in a later version of PostgreSQL (3 or 4 years ago), the part about fixed size and fixed delay is not true anymore.

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

#128

Post by evanelias »

throwdbaaway wrote: post #124
evanelias wrote: post #118

Earlier quoted context omitted.

Sorry, I don't recall the exact frequency, I left FB in 2015.

Then I guess my point about UDB doesn't require schema change will still stand then? Looking at https://github.com/facebookarchive/linkbench , the table structure looks pretty much "final". And judging by https://dom.as/2015/07/30/on-order-by-optimization/ , the access pattern is pretty much fixed as well.

UDB had hundreds of tables per shard, and although there are a few common patterns, they did not all have an exactly identical structure. You have two former FB database engineers in this thread (myself + rwultsch) telling you your statement is incorrect. Nothing in Domas's post discusses lack of schema changes or tables being identical.

Linkbench is unmaintained and does not attempt to mirror the entirety of UDB, just its access patterns: point lookups by PK, and range scans over a secondary index. A fixed access pattern is not the same thing as having no schema changes.

Even putting column changes aside, the entirety of UDB was migrated from InnoDB to MyRocks in 2017, which is essentially a schema change across every single UDB table in every single UDB shard.

And besides, as I mentioned already, the non-UDB MySQL use-cases at Facebook are larger than the vast majority of companies' databases -- larger than the next-largest US social network, even. The non-UDB tiers had dozens of schema changes every single day.

As rwultsch correctly mentioned, Facebook's extreme agility with schema changes is directly what inspired me to create https://www.skeema.io, an open source project offering declarative schema change management. It's used by GitHub, Twilio, and a number of other well-known companies.

Please stop making incorrect statements based on things you have no direct experience with.

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

#129

Post by chousuke »

mgkimsal wrote: post #126
chousuke wrote: post #102

Earlier quoted context omitted.

I'm just not sure how it's confusing? PostgreSQL users aren't "tied" to system accounts either. You can have any number of PostgreSQL users that have no system equivalent. In fact, the process seems to be exactly the same as with MySQL: I just tried installing the MariaDB server (dnf install mariadb-server), and it didn't prompt me for an admin user; instead, I can directly connect to the database as root using sudo,…

"Maybe some of the confusion stems from the fact that the documentation you linked seems to assume that the database is created according to convention to run as the "postgres" user (as it usually is)". The entire point was a reply to someone saying "it's confusing". I'm pointing out how it's confusing, and you come back with that either that 1) MySQL is confusing or 2) you don't think it's confusing. Then you point…

> I've had people say "I installed postgres - here's the password". Then... I can't log in. Because I can't switch to the postgres user.

You don't need to switch to the postgres user if you have another database user and password.

Are you talking about a situation where someone has installed a PostgreSQL server but hasn't configured PostgreSQL to allow password authentication? The server admin needs to allow that explicitly, because some distributions don't allow password authentication even on localhost by default, but honestly, it's all very well documented.

> If someone uses common default package managers to set up mysql/Maria, and also for postgres, you'll be able to connect to mysql/Maria from any account.

This is not the case on Fedora at least, since fresh out of the package the MySQL root user has no password; the only way to connect is via local system authentication as the root user.

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

#130

Post by throwdbaaway »

evanelias wrote: post #128
throwdbaaway wrote: post #124

Earlier quoted context omitted.

Then I guess my point about UDB doesn't require schema change will still stand then? Looking at https://github.com/facebookarchive/linkbench , the table structure looks pretty much "final". And judging by https://dom.as/2015/07/30/on-order-by-optimization/ , the access pattern is pretty much fixed as well.

UDB had hundreds of tables per shard, and although there are a few common patterns, they did not all have an exactly identical structure. You have two former FB database engineers in this thread (myself + rwultsch) telling you your statement is incorrect. Nothing in Domas's post discusses lack of schema changes or tables being identical. Linkbench is unmaintained and does not attempt to mirror the entirety of UDB, ju…

From my point of view, neither of you have sufficiently answer the question -- does UDB go through schema change that would require rewriting the table via pt-osc? If so, at what frequency?

Until then, sorry, we will keep advocating people to stay away from MySQL (and thus indirectly, skeema), because "long wait and potential incident from schema migration" is just not something that should come up during a sprint planning.

> Even putting column changes aside, the entirety of UDB was migrated from InnoDB to MyRocks in 2017, which is essentially a schema change across every single UDB table in every single UDB shard.

I read about that, and it is definitely an impressive feat, but still, that doesn't answer the question, at all? That's just relying on MySQL native replication that works across different engines.

Post reply on HN